Using the SAP HANA HDBSQL Command-Line Tool

Objectives

After completing this lesson, you will be able to:
  • Use the HDBSQL command-line tool
  • Use the hdbuserstore tool

SAP HANA Client

Business Example

SAP HANA HDBSQL is a command-line tool for entering and executing SQL statements, executing database procedures, and querying information about SAP HANA databases. Administrators can execute statements from a command line or schedule scripts that access the SAP HANA database.

SAP HANA provides client interfaces for connecting applications as part of the SAP HANA client software package. Whenever a client or application software connects to the SAP HANA database software, the SAP HANA client and its interfaces have to be used. The SAP HANA client provides a variety of application programming interfaces (APIs), which use SQLDBC and/or JDBC and connect via the SAP HANA database SQL command network protocol to the SAP HANA database server.

Overview of SAP HANA Client

Overview: SAP HANA Client

The most popular use case of a tool in the fields of administration, is the command line based HDBSQL, which comes with the installation of the SAP HANA Client software together with other tools like the hdbuserstore. When HDBSQL is initialized with fully qualified connection information, it sends the request via the SQLDBC client interface to the target SAP HANA database server.

Further tools using the client interfaces of the SAP HANA Client software are the Database Explorer (integrated in the SAP HANA cockpit), ABAP-based SAP systems with an underlying SAP HANA database system, and web-based tools using ODATA protocol, XMLA, or Node.js.

The following client interfaces exist:

  • SQLDBC

  • JDBC

  • ODBC

  • Phython DB

  • ADO NET

  • Node.js

  • Go (golang)

  • Ruby

Further interfaces can be implemented by developers using the following reference guide: SAP HANA Client Interface Programming Reference

Installation and Update, Compatibility, and Platform Matrix

The SAP HANA client software can be installed on various platforms (UNIX, Linux, macOS, Microsoft Windows).

Note

Detailed information for supported SAP HANA Client platforms can be found here: SAP Note 3165810

There is no need for the version number of an SAP HANA client driver to match the version number of the SAP HANA server to which it connects. The SAP HANA client maintains strict cross-version compatibility with all Service Pack Stack (SPS) releases of SAP HANA 2.0.

The following documents are useful concerning the compatibility of the SAP HANA client software, and Installation and update:

SAP HANA Connection with HDBSQL

SAP HANA HDBSQL is a command-line tool for executing commands on SAP HANA databases.

With SAP HANA HDBSQL, you can execute SQL statements and database procedures, and query information about the database and database objects. SAP HANA HDBSQL is installed with the SAP HANA software. It accesses databases both on your local computer and on remote computers.

Call SAP HANA HDBSQL with the command: hdbsql [options] from the location /usr/sap/<SID>/HDB<instance>/exe

Note

In a standard SAP HANA system installation, the operating system user <sid>adm is allowed to make use of the tool HDBSQL. In case you are using personal OS users, make sure the authorization to execute HDBSQL is assigned correctly on OS level.

Features of HDBSQL

HDBSQL has the following features:

  • Execute SQL statements

  • Execute database procedures

  • Request information about the database catalog

  • Execute shell commands

  • Execute commands (command syntax and options)

  • Overview of all HDBSQL call options

  • Overview of all HDBSQL commands

Options to Connect to an SAP HANA System

To use HDBSQL interactively and to execute commands, log on to the database as a database user.

If you do not specify the user name and password of a database user, the logon is attempted using Kerberos authentication.

You can log on to a database using SAP HANA HDBSQL with either a one-step or two-step procedure.

The following options are available:

  • One-step logon (with username and password)

    Specify all needed credentials and connection information in the start command of HDBSQL

  • Two-step logon (with username and password)

    1. Start HDBSQL

    2. Connect to the system (with credentials and connection information)

Hint

For security reasons avoid the specification of the password with -p <password> when using a connection string to start an interactive hsbsql session. Every command on operating system level is kept and can be retrieved - including the typed-in password.

Nevertheless, if you omit the password option in the connection string, hdbsql will ask for it and it will be declared as password and won't appear anywhere on operating system level as clear text.

Note

In a multiple-container system, the option —d specifies the name of the mult-itenant database container. If you do not specify the option -d, you will be automatically logged on to the default tenant database.

Logon with HDBSQL: Example

In this example, HDBSQL is used to connect to an SAP HANA tenant database GBX with instance number 01 on the localhost. Database user GEORG is specified with password Welcome1.

Execution of Commands

SAP HANA HDBSQL Commands

Set information for the database and for database objects by using SAP HANA HDBSQL commands.

Note

For a detailed description of the HDBSQL features, see the SAP HANA HDBSQL (Command-Line Reference).

Once the tool was started, it guides the user with a hint (see graphic above) to get an overview of the available commands, which can be called with \h. Useful display commands exist to list for example users (\du), schemas (\ds), tables (\dt), or views (\dv).

Note

Be aware of the following:

To execute commands successful in HDBSQL, an active connection has to be established before. Exceptions are \h to get help, and \q to quit.

Run Commands

HDBSQL commands can be executed in interactive and non-interactive mode. In addition to SAP HANA HDBSQL commands, SQL statements or database procedures can be executed, but must be enclosed in quotation marks.

You can execute multiple commands from a batch file, or import commands from a file and execute them in the background. Specify the separator used in the batch file to separate individual commands by using the -c <separator> command line option. The default value is a semicolon (;).

Note

When you execute from a batch file, the AUTOCOMMIT mode is activated by default. If you deactivate it, the batch file must contain an explicit COMMIT statement to ensure that HDBSQL executes the SQL statements immediately after the batch file is imported.

Note

For more detailed information about SAP HANA SQL and SAP HANA SQLScript is provided in the following guides:

Furthermore, there is an SAP training on SQLScript:SAP HANA 2.0 SPS06 SQLScript for SAP HANA

Secure User Store (hdbuserstore)

The secure user store (hdbuserstore) is a tool installed with the SAP HANA client. Use it to store connection information to SAP HANA systems securely on the client. This process ensures that client applications can connect to SAP HANA without users having to enter this information. It is used by scripts connecting to SAP HANA.

The secure user store allows you to store SAP HANA connection information, including user passwords, securely on clients. In this way, client applications can connect to SAP HANA without the user having to enter host name or logon credentials. You can also use the secure store to configure failover support for application servers in a 3-tier scenario (for example, SAP Business Warehouse) by storing a list of all the hosts that the application server can connect to.

Note

The secure user store can be used for SQLDBC and JDBC-based connections.

Program hdbuserstore

The secure user store is installed with the SAP HANA client package. After you install the SAP HANA client, the hdbuserstore program is located in one of the following directories:

  • /usr/sap/hdbclient (Linux or UNIX)

  • %SystemDrive%\Program Files\sap\hdbclient (Microsoft Windows)

The secure user store is user-specific, so only the operating system user who owns the corresponding secure store file can access the secure store. However, you can, with the appropriate operating system privileges, manage another user's secure store. This option is needed, for example, to manage the connection details for ABAP on Microsoft Windows because the application server runs under a different user (SAPService<SAPSID> instead of <sapsid>adm).

Commands of hdbuserstore

Use hdbuserstore to store and manage connection information in the secure store. Several commands are available for managing connection information stored in hdbuserstore.

For more information about the available commands, see the SAP HANA Security Guide.

Underscores, hyphens, periods, and alphanumerical characters (0-9, a-z, A-Z) are allowed. The resulting key is always upper case (0-9, A-Z).

Note

The tool does not check whether the user really exist.

Failover Scenarios with hdbuserstore

The hdbuserstore is also used to configure failover support for different use cases:

  • Failover for application servers (ABAP-based) by specifying a list of host/ports that the server can connect to. An example command (in generic format) could look like this:hdbuserstore SET DEFAULT "<hostname_node1>:3<inst>15; … ;<hostname_node(n)>:3<inst>15" <sapsid> <password>

  • Failover support for clients running on a distributed tenant database by specifying a list of host/ports pairs that the client can connect to. An example command (in generic format) could look like this:hdbuserstore SET <KEY> <hostname>:<System_DB_SQL_port>[,<hostname2>:<System_DB_SQL_port>,…..]@<database_name> <user> <password>

Work with SAP HANA HDBSQL and HDBUSERSTORE

Business Example

You want to execute statements in your SAP HANA database, but no graphical user interface, like the Database Explorer SQL Console as part of the SAP HANA cockpit, is available. Perform these activities using SAP HANA HDBSQL.

Furthermore, there is a demand that users are can make use of client applications connecting to SAP HANA without users having to enter log on information. To make this mechanism work, the hdbuserstore should be configured.

Task 1: Log On to Your SAP HANA System DB using SAP HANA HDBSQL

Log on to your SAP HANA system database using SAP HANA HDBSQL, specifying the credentials of the SYSTEM user directly.

Steps

  1. All Participants: If you have closed the HA200 SAP Learning Cloud training system landscape connection to the training landscape, open it as described in step 1 of the exercise Explore the HA200 Training Landscape.

  2. All Participants: If you have closed the PuTTY ssh session to your Linux Server, open it as described in step 3 of the exercise Explore the HA200 Training Landscape.

  3. All Participants: Log on to the SAP HANA system database, using the credentials of the SYSTEM user.

    FieldParticipant 1 ValueParticipant 2 Value
    <SID>H46H47
    <database>SYSTEMDBSYSTEMDB
    <SAP HANA database server>wdflbmt7346wdflbmt7347
    <SAP HANA OS User>h46admh47adm

    1. In the PuTTY window, switch to your SAP HANA OS user using the following command:

      Code Snippet
      1
      su – <sid>adm

    2. Connect to your SAP HANA SYSTEMDB database with the following command:

      Code Snippet
      1
      hdbsql -n <SAP HANA database server> -i 00 -d <database> -u SYSTEM -p Welcome1

    3. Confirm your connection request by pressing <ENTER>.

    Result

    Now, you are connected to the SYSTEM DB of your SAP HANA installation.

  4. All Participants: Test the connection by querying the system database.

    1. Execute the following command:

      Code Snippet
      1
      SELECT * FROM dummy

    2. To leave the result screen, enter: q.

    3. To display an overview of the HDBSQL commands, enter: \h.

    4. To display general information about the database, enter: \s.

    5. To close the HDBSQL connection and log-off from the tool, enter: \q.

Task 2: Log on to your SAP HANA tenant DB using SAP HANA HDBSQL

Login to your SAP HANA tenant database using SAP HANA HDBSQL, use the credentials of the SYSTEM user and this time enter the password hidden.

Steps

  1. All Participants: Log on to the SAP HANA tenant database, using the credentials of the SYSTEM user.

    FieldParticipant 1 ValueParticipant 2 Value
    <SID>H46H47
    <database>H46H47
    SAP HANA database serverwdflbmt7346wdflbmt7347

    1. Connect to your SAP HANA tenant database with the following command:

      Code Snippet
      1
      hdbsql -n <SAP HANA database server> -i 00 -d <database> -u SYSTEM

    2. The password will be asked in a second step. Enter the password Welcome1.

      Doing this, the password is not shown on the screen, nor in the process list, or in the history on operating system level.

  2. All Participants: Test whether the connection works by executing a SQL command.

    1. To test whether the connection works, execute the following command:

      Code Snippet
      1
      select * from "SYS"."P_USERS_"

    2. To leave the result screen, enter q.

    3. To close the HDBSQL connection, enter: \q.

    4. Keep the PuTTY window open in the context of the user <sid>adm. You will need it for the next steps.

Task 3: Check the content of the hdbuserstore and create a connection entry for the SYSTEM User of the tenant DB

Steps

  1. All Participants: Display the content of the hdbuserstore.

    FieldParticipant 1 ValueParticipant 2 Value
    <SID>H46H47
    <keyname>MYTENANTDBMYTENANTDB
    <SAP HANA database server>wdflbmt7346wdflbmt7347
    <SQL-Port>3001530015
    <database>H46H47

    1. In the PuTTY window, enter the following command:

      Code Snippet
      1
      hdbuserstore LIST

      The result shows that the data file is located in the directory /usr/sap/<SID>/home/.hdb/<hostname>/SSFS_HDB.DAT. There is already an entry for the SAPDBCTRL user, created during installation. This entry is used by the SAP Host Agent.

  2. All Participants: Create a new user-key for the tenant databases SYSTEM user, and store the password under this user-key.

    1. Enter the following command:

      Code Snippet
      1
      hdbuserstore SET <keyname> <SAP HANA database server>:<SQL-Port>@<database> SYSTEM Welcome1

    2. To display the newly created entry of the hdbuserstore, repeat the following command from a previous step, but add the name of the newly created key:

      Code Snippet
      1
      hdbuserstore LIST <keyname>

      The output shows that the new hdbuserstore key was created as follows:

      Code Snippet
      12345
      KEY <keyname>
  ENV : <SAP HANA database server>:30015
  USER: SYSTEM
  DATABASE: <database>

Task 4: Connect to your SAP HANA tenant DB with the new key

Connect to your SAP HANA database using the new hdbuserstore key.

Steps

  1. All Participants: Log on to the SAP HANA database using the secure user-store key.

    1. Enter the following command:

      Code Snippet
      1
      hdbsql -U <keyname>

    2. To check, which database you are connected to, enter \s.

      The dbname field shows which database you are connected to. This should be your tenant database.

  2. All Participants: Test whether the connection works by executing a command.

    1. Query the tenant database using the following command:

      Code Snippet
      1
      SELECT * FROM dummy

    2. To leave the result screen, enter q.

    3. To close HDBSQL, enter \q.

    4. To disconnect the user <sid>adm, enter exit and confirm with <ENTER>.

    Result

    You have successfully created a key with hdbuserstore and used it to connect to your tenant DB without being forced to provide interactively connection details. Finally, you jumped back to user haroot in your PuTTY session.

Log in to track your progress & complete quizzes

Learning

SAP FacebookSAP YoutubeSAP LinkedIn