Executing SAP HANA Cloud Tasks from the Command Line

hdbsql is a command line tool that is included in the SAP HANA Client which can be used to run SQL statements from a shell or script.

Additional details on how to install the SAP HANA Client and on using hdbsql can be found in the tutorial mission Use Clients to Query an SAP HANA Database.

The example shown below uses HANA_Configuration_Overview_SHC which is one of the many queries included in the SAP Note: 1969700 - SQL Statement Collection for SAP HANA. Note that the entries with the suffix _SHC are for SAP HANA Cloud, SAP HANA databases.

1. Open the SAP Note, download the zip file, and unzip its contents. It contains many diagnostic queries for an SAP HANA database. The download section shown below is near the bottom of the SAP Note.

   

   The HANA_Configuration_Overview_SHC.txt diagnostic query will be used.

   

2. Create a hdbuserstore key that contains the SQL endpoint (host:port), user, and password needed to connect to the SAP HANA database instance.

   Shell Copy

   hdbuserstore Set AdminUserKey <SQL ENDPOINT> DBADMIN <PASSWORD>

   The list of keys can be seen with the command below.

   Shell Copy

   hdbuserstore List

3. Run the command.

   Shell Copy

   hdbsql -A -o results.txt -U AdminUserKey -I HANA_Configuration_Overview_SHC.txt
   type results.txt

   An example of the result is shown below.

   

4. Review the options used.

   • -A provides an aligned output
   • -o results.txt writes the output to a file named results.txt
   • -U AdminUserKey instructs hdbsql to retrieve the host, port, user name and password from the user store. This is helpful when running scripts as the credentials are not included in the command line
   • -I HANA_Configuration_Overview_SHC.txt specifies the SQL to be executed

5. Should you wish to run a diagnostic script against an SAP HANA Cloud, data lake Relational Engine use dbisql.

   Instructions to install the data lake client and use dbisql are in the tutorial mission Use Clients to Query a Data Lake Relational Engine.

6. Create a file named diagnosticQuery.sql and add the contents below. For additional details see sa_conn_info System Procedure.

   SQL Copy

   CALL sa_conn_info();

   Execute the SQL.

   Shell Copy

   dbisql -c "uid=<USER_ID>;pwd=<PASSWORD>;host=<SQL ENDPOINT>;ENC=TLS(tls_type=rsa;direct=yes)" diagnosticQuery.sql

   Shell Copy

   dbisql -c 'uid=<USER_ID>;pwd=<PASSWORD>;host=<SQL ENDPOINT>;ENC=TLS(tls_type=rsa;direct=yes)' diagnosticQuery.sql

   An example of the result is shown below.

   

The SAP Business Technology Platform (SAP BTP) provides multiple runtimes in which services can be run. This becomes important when attempting to interact with the service from a command line interface (CLI) as there are different CLIs for each runtime environment.

Further details can be found at Consuming SAP BTP Services from Various Environments.

1. SAP HANA Cloud instances can be deployed to either the subaccount (other environment) or to a Cloud Foundry space. The screenshot below shows an instance deployed to each location.

   

2. When a service is created, if it supports multiple runtime environments, the runtime environment can be specified as shown below.

   

3. When creating an instance using SAP HANA Cloud Central, you are provided a choice to provision the instance in the subaccount (Other) or to a Cloud Foundry space.

   

   You can also choose to copy the JSON parameters that the wizard generates and then use the one of the CLIs (shown in the next section) to provision the instance.

   

4. Considerations when provisioning to the subaccount (Other):

   • BTP subaccount role collections are used for security
   • Instances can be managed using the SAP BTP CLI or SAP Service Manager

5. Considerations when provisioning to a Cloud Foundry space:

   • SAP HANA native development in the SAP Business Application Studio currently deploys to HDI containers in Cloud Foundry spaces
   • HDI containers provisioned into Cloud Foundry can be mapped to SAP HANA Cloud databases provisioned to the subaccount (Other)
   • If you already have instances deployed to Cloud Foundry spaces, you may wish to have all of your instances in the same runtime environment
   • Cloud Foundry space roles are used for security
   • Instances can be managed using the Cloud Foundry CLI

A command line interface (CLI) is a text-based interface. There are two different CLIs that can be used with an SAP HANA Cloud instance to perform administrative tasks such as start, stop, create, delete, update, or upgrade.

The CLI used depends on whether the SAP HANA Cloud instances were provisioned to the subaccount (Other) or to a Cloud Foundry space. Details on the two CLIs can be found at Create and Manage SAP HANA Cloud Instances Using the CLI.

SAP BTP CLI can create, read, update, and delete an instance provisioned in a subaccount (Other). It can also be used to read the configuration of an instance deployed in a Cloud Foundry space. More details can be found in the btp CLI Command Reference. Details on how to install the CF CLI can be found in the tutorial Get Started with the SAP BTP Command Line Interface. Additionally, the SAP BTP CLI can be used to create and configure subaccounts.

Cloud Foundry CLI (CF CLI) can be used to create, update, configure, or delete instances provisioned in a Cloud Foundry space. Further details can be found at Installing the cf CLI and Cloud Foundry Releases.

SAP Service Manager can create, read, update, and delete an instance provisioned in a subaccount (Other). It can also be used to read the configuration of an instance deployed in a Cloud Foundry space. The service manager provides a REST API that can be accessed programmatically in applications such as a Node.js application. Details on the service manager can be found at Working with SAP Service Manager APIs.

The following steps will provide examples of each CLI. The SAP Service Manager REST API will covered in the next tutorial. Please install one or both of the CLIs before proceeding.

When using a CLI to perform an update to an instance, the update operation will take an input parameter in JSON format that describes the change being requested. The parameters are common across the three CLIs. Details on the SAP HANA Cloud service specific parameters can be found at Parameter Reference.

Before proceeding ensure that your user has the subaccount service administrator role collection.

Additional details can be found at Role Collections and Roles in Global Accounts, Directories, and Subaccounts.

The commands below were executed in a Microsoft Windows PowerShell. This shell offers code completion by pressing the tab key. To try out the autocompletion, enter btp, a space, and press the tab key. If needed, you can enable autocompletion as described at Enable Command Autocompletion.

Select an action and press tab again to view the available operations for that action.

Version

btp --version

Info and help

btp --info

btp help
btp help update services/instance

Log on

btp login

or

btp login --sso

Note that the CLI server URL is https://cli.btp.cloud.sap.

After a successful login, details can be seen using the info command.

Specify the subaccount

If the subaccount is specified, then it does not need to be specified in each subsequent command.

btp target

The above command lists the available subaccounts.

Alternatively, the subaccount can be specified using its ID.

btp target --subaccount <SubaccountID>

The subaccount ID can be obtained on the BTP Cockpit Overview page for the subaccount.

Create an instance

In order to create an instance, JSON describing the instance is required as well as a plan ID.

• Create a JSON file named create.json that specifies the parameters of the instance to be created. The JSON can be generated in the SAP HANA Cloud Central instance creation wizard or can come from an existing instance.

  

  

• Edit create.json and provide a value for systempassword. This is the password for the DBADMIN user.

  

• Identify the plan ID for the SAP HANA database. Notice that the name is either hana for the paid service, hana-free for the free-tier service, or hana-trial for the trial service, the service_offering_name is hana-cloud-trial. The list of all available offerings can be found using services/offering.

  Shell Copy

  btp list services/plan --fields-filter "name contains 'hana'"

  Free tier and production service plans

  

  Trial service plans

  

  or

  Shell Copy

  btp list services/plan --fields-filter "name contains 'lake'"

  Free tier and production service plans

  

  Trial service plans

  

  Additional details on the filters available can be found at Filtering Parameters and Operators.

• With the plan ID, create a new instance.

  Shell Copy

  btp create services/instance --plan <Plan ID> --name HC_HDB --parameters create.json

  

  The instance being created can also be viewed in SAP HANA Cloud Central as shown below.

  

A more advanced example using a bash script is provided at Automate Account Operations with the Command Line Interface (CLI). Additional details can be found at Commands in the btp CLI.

Delete an instance

Do not follow this step unless you no longer require your SAP HANA Cloud instance.

btp delete services/instance --name HC_HDB

List instances

btp list services/instance --fields-filter "name eq 'HC_HDB'"

Update the label of an instance

Each instance can have one or more labels. Below we will add a contact label.

• Create a file named label.json with the contents below.

  JSON Copy

  [{
      "op": "add",
      "key": "Contact",
      "values": ["Dan at 123 456 7890"]
  }]

• Execute the below command.

  Shell Copy

  btp update services/instance --id <instance ID> --labels .\label.json

  

  This adds a label that can be seen both in the SAP BTP Cockpit as well as when viewing the instance details with the CLI as shown in the next example.

  

View instance details

The details of an instance can be viewed.

• Execute the command below.

  Shell Copy

  btp get services/instance --name HC_HDB

  or

  Shell Copy

  btp --format json get services/instance --name HC_HDB

  

• Execute the command below to see the parameters of an instance.

  Shell Copy

  btp --format json get services/instance --name HC_HDB --show-parameters

  

Update parameters of an instance

This step will be used to update the description of the instance. The existing instance parameters can be obtained from the output of the previous example or from the Copy Configuration menu item in an SAP HANA Cloud Central actions menu for an instance.

• Create a file named description.json with the contents below.

  JSON Copy

  {
      "metadata": {
          "ui.hc.sap.com/description": "Created as part of Automation Tutorial"
      }
  }

• Execute the below command.

  Shell Copy

  btp update services/instance --id <Instance ID> --parameters description.json

  

  The result is that the description has been updated.

  

Stop an instance

• Create a file named stop.json with the contents below.

  JSON Copy

  {
      "data": {
          "serviceStopped": true
      }
  }

• Run the below command to stop the specified instance.

  Shell Copy

  btp update services/instance --id <Instance ID> --parameters stop.json

Start an instance

• Create a file named start.json with the contents below.

  JSON Copy

  {
      "data": {
          "serviceStopped": false
      }
  }

• Run the below command to start the specified instance.

  Shell Copy

  btp update services/instance --id <Instance ID> --parameters start.json

Check for and apply software updates

When an upgrade is available, the details for the upgrade options are included in the instance parameters.

A tool such as jq can be used to filter the result to only include details on the available upgrade versions.

• Get details about available upgrades.

  Shell Copy

  btp --format json get services/instance --name HC_HDB  --show-parameters | c:\tools\jq-win64 "{availableUpgradeVersions}"

  

• Create a file named upgrade.json with the contents below and modify it as appropriate to match the available version.

  JSON Copy

  {
      "data": {
          "productVersion": {
              "releaseCycle":"generally-available-quarterly",
              "track": "2023.16",
              "id": "2023.16.14"
          }
      }
  }

• Run the below command to perform the upgrade.

  Shell Copy

  btp update services/instance --id <instance ID> --parameters upgrade.json

  

• Check in SAP HANA Cloud Central to see that the upgrade is occurring.

  

The following examples require a non trial or free tier SAP HANA Cloud instance.

Clone an instance

An SAP HANA Cloud database instance may be cloned. As an example, you may wish to periodically replace a QA instance with a new instance that has a copy of the latest data from a production instance. The subaccounts can be in different hyperscalers and the configuration settings such as storage size of the target instance can be different than the source. Additional details can be found at Clone an SAP HANA Database Instance.

1. Select an instance to clone. It cannot have an attached data lake. To verify that it does not have an attached data lake, confirm that it has the option Add Data Lake and that it has the option Create Template to Clone Instance.

   

2. Create a file named clone-template.json with the contents below and modify it as appropriate.

   JSON Copy

   {
       "data": {
           "requestedOperation": {
               "name": "TEMPLATE_BACKUP",
               "arguments": {
                   "template_name": "<name>",
                   "template_storage_endpoint": "<hdl-files-storage-endpoint>",
                   "hdl_access_token": "<hdl_access_token>",
                   "backup_encryption_passphrase": "<encryption_passphrase>"
               }
           }
       }
   }

   

   Notice that endpoint above is the Files REST API Endpoint prefixed with hdlfs://

   The token above is the concatenation of client.key, client.crt, and ca.crt with the newlines removed.

   Further information about data lake files and the certificates required to configure an instance can be found at step 4 of Add Databases to the SAP HANA Database Explorer.

3. Create the template.

   Shell Copy

   btp update services/instance --id <instance ID> --parameters clone-template.json

   

   After the command completes, the template files can be viewed in the specified data lake files instance.

   

4. Create a file named clone.json with the contents below and modify it as appropriate.

   JSON Copy

   {
       "data": {
           "requestedOperation": {
           "name": "TEMPLATE_RECOVERY",
           "arguments": {
               "template_name": "<name>",
               "template_storage_endpoint": "<hdl-files-storage-endpoint>",
               "hdl_access_token": "<hdl_access_token>",
               "backup_encryption_passphrase": "<encryption_passphrase>"
           },
           "provisioned_size_gib": <disk size in GB>,
               "systempassword": "<password>",
               .....
           }
       }
   }

   The contents after requestedOperation are the same as the create.json covered previously.

   

5. After the template has been created, it can be used to create the clone.

   Shell Copy

   btp create services/instance --name <instance name to create> --offering-name hana-cloud --plan-name hana --parameters clone.json

   

   After the command completes, a new SAP HANA Cloud instance named Clone is created that will have the same schema and data as the instance Clone_Source. The data is stored in the files created when the template was created so the data in the clone reflects the data that was captured during the template creation.

   

Perform a takeover

A takeover from an SAP HANA instance to a replica can performed. This can happen automatically or can be triggered from the SAP HANA Cloud Central actions menu or using the CLI command illustrated below. Additional details on the topic of replicas can be found at Increasing System Availability.

1. Find an instance to perform a takeover on. It will have a replica, and its action menu will have an option to start a takeover.

   

2. The details of the replica can be seen when after selecting Manage Configuration from the actions menu.

   

3. Create a file named takeover.json with the contents below and modify it as appropriate.

   JSON Copy

   {
       "data": {
           "requestedOperation": {
               "name": "synchronous_replication_takeover"
           },
           "arguments": {
               "secondary_az": "us-east-1c"
           }
       }
   }

4. Perform the takeover

   Shell Copy

   btp update services/instance --id <instance ID> --parameters takeover.json

   

   The instance will briefly show a starting status and when it is back to running, the source database will now be in the new availability zone.

   

Before proceeding ensure that your user has the space developer privilege which is required to perform actions such as create-service or update-service.

Additional details can be found at About Roles in the Cloud Foundry Environment.

Version and help

cf -v

Help can be obtained using the commands below.

cf -help

All the available help can be seen with -a.

cf -help -a

Log on

Log on to Cloud Foundry using an API endpoint.

• Obtain the API endpoint.

  The API endpoint can be obtained from the SAP BTP Cockpit on the subaccount Overview page in the Cloud Foundry Environment section.

  

• Set the API endpoint with the command below.

  Shell Copy

  cf api <API Endpoint>

  

• Logon with one of the commands below.

  Shell Copy

  cf login

  or

  Shell Copy

  cf login --sso

  

Create an instance

In order to create an instance, JSON describing the instance is required, as well as a service offering name and plan.

• Create a JSON file named create.json.

• Populate the JSON file.

  The JSON can be obtained in the SAP HANA Cloud Central instance creation wizard or from copying the JSON from an existing instance.

  

  

• Edit the saved JSON file and provide a value for systempassword.

  

• Get the available services.

  Shell Copy

  cf marketplace

• Get the service plan offerings for SAP HANA Cloud free-tier or paid.

  Shell Copy

  cf marketplace -e hana-cloud

  

• Get the service plan offerings for SAP HANA Cloud trial.

  Shell Copy

  cf marketplace -e hana-cloud-trial

  

• Create an SAP HANA Cloud, SAP HANA database instance.

  Shell Copy

  cf create-service hana-cloud-trial hana HC_HDB_CF -c create.json

  

  Notice that the service offering is hana-cloud-trial, the plan within that service offering is hana, and the name of the instance is HC_HDB_CF.

  The instance being created can also be viewed in SAP HANA Cloud Central as shown below.

  

Delete an instance

Do not follow this step unless you no longer require your SAP HANA Cloud instance.

cf delete-service HC_HDB_CF

List instances

cf services

View instance details

cf service HC_HDB_CF

Some operations are not permitted such as showing or updating the parameters of an instance while another operation is in progress.

Update parameters of an instance

This step will be used to update the description of the instance.

• View the instance parameters.

  The existing instance parameters can be obtained from the Copy Configuration menu item in an SAP HANA Cloud Central actions menu for an instance.

  The existing parameters can also be retrieved through adding –-params in an instance detail request as shown below.

  Shell Copy

  cf service HC_HDB_CF --params

• Create a file named description.json with the contents below.

  JSON Copy

  {
      "metadata": {
          "ui.hc.sap.com/description": "Created as part of Automation Tutorial"
      }
  }

• Run the below command to update the description of the instance.

  Shell Copy

  cf update-service HC_HDB_CF -c description.json

  The result is that the description has been updated.

  

Stop an instance

Create a file named stop.json with the contents below.

{
    "data": {
        "serviceStopped": true
    }
}

Run the below command to stop the specified instance.

cf update-service HC_HDB_CF -c stop.json

Start an instance

Create a file named start.json with the contents below.

{
    "data": {
        "serviceStopped": false
    }
}

Run the below command to start the specified instance.

cf update-service HC_HDB_CF -c start.json

Check for and apply software updates

When an upgrade is available, the details for the upgrade options are included in the instance parameters.

• Get the instance details.

  Shell Copy

  cf service HC_HDB_CF --params > out.json

  

• Create a file named upgrade.json with the contents below and modify it as appropriate to match the available version.

  JSON Copy

  {
      "data": {
          "productVersion": {
              "releaseCycle":"generally-available-quarterly",
              "track": "2023.16",
              "id": "2023.16.14"
          }
      }
  }

• Run the below command to perform the upgrade.

  Shell Copy

  cf update-service HC_HDB_CF -c upgrade.json

  

You have now executed diagnostic SQL statements from the command line, are aware of the different runtimes in the SAP BTP platform that an SAP HANA Cloud instance can be provisioned to, and have used one or more of the CLIs to manage an SAP HANA Cloud instance.