Connecting To HANA DB Using Hdbsql

23 June 2017 - 1908 views
This article is a continuation of Install HANA Express 2.0 On Ubuntu 16 Xenial. Please read the previous article if you don't have a HANA Express system installed.

hdbsql is a command line tool that allows you to connect to a HANA database and execute queries. To use this tool, you first need to login as the hxeadm user. You can do this by simply changing users in a terminal.

[email protected]# su hxeadm
The hxeadm password is the password which you set when installing HANA. Once logged in, you can execute the hdbsql command tool.

[email protected]:/usr/sap/HXE/HDB90> hdbsql
The interactive terminal will load and a prompt will appear. Type the following connection arguments.

[email protected]:/usr/sap/HXE/HDB90> \c -n 127.0.0.1:39013 -u SYSTEM -p PASSWORD
Replace the ip address, with the host you entered when installing HANA. Also replace 90 in the port number with the instance number you entered when installing HANA. The default instance number as of current is 90.

If the connection details are correct, the terminal prompt will change to hdbsql SYSTEMDB=> with a "Connected..." message. You are now ready to execute SQL queries. Execute the following SQL query to get a list of all schema's.

Listing 1

SELECT * FROM SCHEMAS

The result of the query above will show all system schemas. A schema defines the container that holds database objects such as tables, views, and stored procedures. Before you can create your own schema, you should first create a new user, this user will then own the schema. Listing 2 below shows how to create a simple user who has no privileges.

Listing 2

CREATE USER JOHN PASSWORD Password123

Now create a new schema and assign it to user JOHN.

Listing 3

CREATE SCHEMA MY_COMPANY OWNED BY JOHN

Now reconnect to HANA using the new user.

[email protected]:/usr/sap/HXE/HDB90> \c -n 127.0.0.1:39013 -u JOHN -p Password123
You should get a message prompting you to change the password, go ahead and change the password to something memorable making sure that it is at least 8 characters long. Once you are logged in, execute the following SQL query.

Listing 4

SELECT * FROM SCHEMAS

You should now only see schemas that are owned by user JOHN. Notice that there is also a JOHN schema. This is because every user has its own schema.