SAP HANA database interactive terminal (hdbsql) – by the SAP HANA Academy

Introduction

SAP HANA database interactive terminal: At the SAP HANA Academy we are currently updating our tutorial videos on the topic of SAP HANA administration for the latest support package stack SPS 11. You can find the full playlist here: SAP HANA Administration – YouTube

On of the topics that we have added is the SAP HANA database interactive terminal or hdbsql as it is mostly known. You can also watch them in a dedicated playlist: and SAP HANA database interactive terminal – YouTube

Overview

Hdbsql is a command line tool for executing commands on SAP HANA databases. No Fiori, no cloud, not even colourful Windows, just plain old terminal style command line. It is included with each server installation and also with each SAP HANA client, which is its strongest asset: it is always there for you to rely on.

It is called the database interactive terminal but you can also execute commands non-interactively, as a script that it is. It is probably even the most common use case.

The tool is documented on the SAP Help Portal in the last chapter of the Administration Guide: SAP HANA HDBSQL (Command Line Reference) – SAP HANA Administration Guide – SAP Library. The chapter is only a handful of pages and serves as a reference. This means, for example, that you will informed about the command line option

-S            either “INTERNAL” or “SAPR3”

but you will not be informed about the use case for SQL mode “SAPR3”. Using hdbsql for this reason can be a bit tricky and if you search in the SCN Forums, you will find that at times the tool causes some confusion and bewilderment.

Getting Started

The first tutorial video shows how to get started with the SAP HANA database interactive terminal.

There are hdbsql command line options – for example, for help use option -h from the Linux shell –

hdbadm> hdbsql -h

and there are hdbsql commands – for example, again, for help use command h or ? from the hdbsql prompt.

hdbsql> h

As with the h for help, commands and command line options sometimes use the same letter but mostly not and note that the command line options are case sensitive. The table below shows some examples.

Installing a License File

One good use case scenario for hdbsql is installing a license file. Say, you just have installed an SAP HANA system on a slim SUSE or RedHat Linux server without any (X-Windows) graphical environment. There is no Windows client at hand either. How to proceed? Simple! The interactive terminal and the command

SET SYSTEM LICENSE ‘ ’

In the tutorial video below, I show you how this is done:

Secure User Store

The next video explains how to use the secure user store. This 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.

To connect to the SAP HANA database you need to provide username and password

hdbadm> hdbsql -u DBA -p P@$w0rd!

or

hdbsql> c -u DBA -p P@$w0rd!

The SAP HANA connection defaults to with port 30015. So the connect strings above will only work if you execute them on the SAP HANA server with instance number 00. For the other 99.99% of the cases, you will need to provide the “node” information with -n or
(or just the instance number when on “localhost”). In case of multitenant databases, add -d or d with database name.

hdbadm> hdbsql -u DBA -p P@$w0rd! -n [:] -i

or

hdbsql> c -u DBA -p P@$w0rd!
[:] i

If you just provide the username and leave out the password (option or command), you will be prompted to enter it interactively. This is a good practice, of course, as you do not want the password recorded in the history file of the Linux shell or displayed on the screen. However, for any batch job or Windows service connection, this will not suffice and typically you will want to work with the secure user store.

In the secure user store the connection string (host:port + user + password) is safely stored with the password encrypted. By default, only the file owner can access this file and you can use this to connect to the SAP HANA database from a script file (backup from cron) or as Windows service (without interactive logon).

With a key in the secure user store, you can now connect using command U or option -U

hdbadm> hdbsql -U KEYNAME

or

hdbsql> c -U KEYNAME

The KEYNAME is case sensitive and is the name or alias given to a particular connection string (host:port user password). You can store as many strings as needed. As mentioned, the password is stored encrypted and cannot be extracted in any way.

The tool to manage keys in the secure user store is called hdbuserstore and is documented in the Security Guide: hdbuserstore Commands – SAP HANA Security Guide – SAP Library

SAP HANA database interactive terminal Working with Input Files

When working with hdbsql it is often convenient to use input or script files. This avoids typø errors and allows for scheduled execution. However, there is another good reason to use an input file. Say, you want to perform a select on a tables with a namespace between slashes:

SQL> SELECT count(*) from /ABCD/MYTABLE

On the command line prompt this would cause an issue and the slash (/) is considered a special character by the shell. So you would have to escape it with a backslash

hdbadm> hdbsql -U KEYNAME “SELECT count(*) from /ABCD/MYTABLE”

Unless you like ASCII art you probably will get tired of this very soon. Best to use an input file here.

Another use case is when you want to execute a procedure that contains declarations and statements terminated with a semi-colon (;). The semi-colon is also the default separator for hdbsql so it will start to execute your procedure after the first declaration. How to solve this?

DO

BEGIN

DECLARE starttime timestamp;

DECLARE endtime timestamp;

starttime = CURRENT_UTCTIMESTAMP;

select count(*) FROM sflight.sairport;

endtime =  CURRENT_UTCTIMESTAMP;

SELECT

:starttime AS “Start Time”

, :endtime AS “End Time”

, NANO100_BETWEEN( :starttime, :endtime)/10000 AS “Elapsed Time (ms)” FROM DUMMY;

END

#

Again, use an input file. End you procedure with another character, for example “#” and then start hdbsql with the command line option -c “#” and -mu for multiline mode. By default, hdbsql runs in single line mode, which means that it will send the contents of the buffer to the SAP HANA server when you hit the Return key.

hdbadm> hdbsql -U KEYNAME -i /tmp/inputfile.sql -c “#” -mu

In the video below, I show you some examples of working with input files:

SQLMode = SAPR3

But what about SQLMode? In the hdbsql command line reference, a number of commands and command line options are listed that are a little less obvious.

Fortunately, SAP HANA expert and SCN Moderator Lars Breddeman was willing to share his knowledge with me on the more obscure parameters and options. Thanks Lars!

sqlmode (internal / SAPR3) – The SQLMODE changes how semantics (NULLs, empty strings, trailing spaces, etc.) are handled. This is typically used with the SAP NetWeaver Database Shared Library (DBSL). DB Connect Architecture – Modeling – SAP Library Possible use cases are development and support.

auto commit – Same functionality as in the SAP HANA Studio: if you ever want to execute more than one command in the same transaction, you need to switch from autocommit ON to OFF and manually COMMIT or ROLLBACK. Also: all things concerning transaction management, like locking or MVCC, can only be demonstrated if you don’t immediately commit.

escape – Escape ON makes sure that all values are enclosed into “ ” (default setting). If you want export the output to a file and copy the contents of that file into Microsoft Excel, this causes all values to be interpreted as text. If you want your number to be numbers and dates to be dates, set escape OFF

prepared statements – Prepared statements behave a little bit differently internally, especially for one-off statements simply executing those statements without explicit prior preparation and invocation via parameters might be beneficial. Also, MDX statements are not executable via prepared statements. So, if you want to quickly run a MDX statement from hdbsql you’d have to switch off the usage of prepared statements. This is also how it is done in SAP HANA Studio.

saml-assertion – New with SAP HANA SPS 10 is that you can now authenticate using a SAML assertion. Hdbsql is a good tool to test SAML implementations.

SSL options – This allows for encrypted communication with the SAP HANA database. This needs to be configured on the server side first. The client parameters really deal with the certificate storage on the client side.

More Information

SAP HANA Academy Playlists (YouTube)

SAP HANA Administration – YouTube

SAP HANA database interactive terminal – YouTube

Product documentation

SAP HANA HDBSQL (Command Line Reference) – SAP HANA Administration Guide – SAP Library

Secure User Store (hdbuserstore) – SAP HANA Security Guide – SAP Library

hdbuserstore Commands – SAP HANA Security Guide – SAP Library

Install a Permanent License – SAP HANA Administration Guide – SAP Library

SCN Blogs

Backup and Recovery: Scheduling Scripts – by the SAP HANA Academy

SAP HANA Academy: Backup and Recovery – Backup Configuration Files

SAP HANA Client Software, Different Ways to Set the Connectivity Data

Primeros pasos con SAP HANA HDBSQL

Thank you for watching

The SAP HANA Academy provides free online video tutorials for the developers, consultants, partners and customers of SAP HANA.

Topics range from practical how-to instructions on administration, data loading and modeling, and integration with other SAP solutions, to more conceptual projects to help build out new solutions using mobile applications or predictive analysis.

For the full library, see https://blogs.sap.com/2017/01/18/sap-hana-academy-library-by-the-sap-hana-academy/

• Subscribe to our YouTube channel for updates
• Join us on LinkedIn: linkedin.com/in/saphanaacademy
• Follow us on Twitter: @saphanaacademy
• Google+: plus.google.com/+saphanaacademy
• Facebook:  facebook.com/saphanaacademy