SAP HANA HDBSQL Command Samples
SAP HANA Database Interactive Terminal aka HDBSQL is a command line tool that you can execute SQL commands like SELECT statements on HANA database tables or system views. This tutorial shows how to display system information using hdbsql utility. Besides you will know m_databases system view for example to display the list of all databases on current SAP HANA instance. HDBSQL command line tool can be used to create database on SAP HANA or start a stopped database, etc.
What is HDBSQL SAP HANA Database Interactive Terminal
HDBSQL SAP HANA Database Interactive Terminal is a command line tool which enables users to interact with SAP HANA databases by executing commands.
In order to launch HDBSQL SAP Hana Database interactive terminal, a user requires to logon to SAP HANA with <SID>adm user.
For example, my SAP HANA system ID is hxe which is a SAP HANA 2.0 Express Edition which can be freely downloaded from SAP.
So on SAP HANA command prompt, in order to launch HDBSQL command line utility, I can execute following command:
hdbsql -i 90 -d SystemDB -u SYSTEM -p Kodyaz11
The parameters on the above hdbsql command can be summarized as follows:
hdbsql -i [instance_number] -d [database_name] -u [database_user] -p [password]
We have successfully opened SAP HANA Database interactive terminal
Just to note with SAP HANA 2.0 the Express Edition instance number is altered from "00" to "90"
In above command I preferred to connect to SystemDB SAP HANA database using SYSTEM user.
You can see that I've already logged on with hxeadm System Admin user.
Display System Information using HDBSQL Utility
After you connect to SAP HANA database, using HDBSQL command line tool we can display system information with \s command argument as follows.
Execute SQL SELECT Statement with HDBSQL Tool
When connected to a SAP HANA database, using hdbsql command line tool users can execute SQL Select statements on the current database.
For example, following SQL Select statement displays all data from schemas table
select * from schemas
In the output the schemas table field names and the data inside is displayed with total number of rows and duration required for the SELECT sql task.
You can press "q" to exit from the SQL output list.
Display List of Databases on SAP HANA Instance using HDBSQL
All databases created on a SAP HANA instance can be queried from M_DATABASES system view easily.
You can execute following SQL Select command to list all databases existing on a SAP HANA system.
select * from m_databases
Create Database on SAP HANA using HDBSQL Tool
Besides SQL Select statements, database users can also create new databases on SAP HANA using HDBSQL SAP HANA Database interactive terminal by executing CREATE DATABASE command.
create database [database_name] system user password [password]
Database creation on SAP HANA instace will take some time. After the HANA database is created, users can see the new database in the m_databases system view.
Start Tenant Database on SAP HANA using HDBSQL
If you have downloaded the SAP HANA Express Edition 2.0 just like me, as it is noted on the official installation guide the tenant database hxe is stopped by default to save time during initial configuration.
If you have not yet started the hxe database yet, you can see that it is stopped when you query the system view m_databases to see all databases on the current SAP HANA instance.
We can now restart HXE or HXE-90 SAP HANA database using following command
alter system start database hxe
