How to use SAP HANA HDBSQL to execute SQL commands at OS level.

HDBSQL is a command line tool for executing commands on SAP HANA databases. SAP HANA HDBSQL is also used to automate the HANA Database backups using cron scripts. 

Requirement: You want to access SQL prompt using HDBSQL at OS level. 
Prerequisites : You need password of <SID>ADM user and User with HANA database access, in our example we are connecting using SYSTEM.

Steps :
  • Logon to HANA host with <SID>adm user.
  • Once you are logged in as <SID>adm  you can directly execute the hdbsql command , or you can go to following path and execute the hdbsql command.  
    • cd /hana/shared/<SID>/hdbclient 
  • Now execute the command 
    • hdbsql  -n localhost -i 00 -u SYSTEM -p Ina123  
  • To connect to particular HANA tenant in MDC system or SystemDB
    • hdbsql  -n localhost -i 00 -d <SID> -u SYSTEM -p Ina123
    • hdbsql  -n localhost -i 00 -d SystemDB -u SYSTEM -p Ina123
Please note , all HANA systems are MDC system starting HANA 2.0 , that means all has a SystemDB and at-least one tenant.

  • Once you get the command , enter \s to get the system information you are connected to.
  • In the above screenshot we execute SQL "Select * from Schemas"
  • Exit HDBSQL by entering the command: exit or quit or \q

You can also log on with user credentials for the secure user store (hdbuserstore) with -U <user_key>. Here is a separate document for hdbuserstore configuration.

HDBSQL Examples :
hdbsql  -n localhost -i 00 -u SYSTEM -p Ina123;
hdbsql  -n -i 00 -d BWD  -u SYSTEM -p Ina123;
hdbsql -S DEV -n localhost:30015 -u SYSTEM -p In123 ;
hdbsql -n localhost -i 00 -u myuser -p myuserpassword "select * from sys.users";
hdbsql -U myUserSecureStore "Select table_name, schema_name from m_tables" ;

Reference :

Tags  : How to use HDBSQL ? Example for HDBSQL.