Hey, SAP HANA, express edition! What's your SQL po...

abdel_dadouche · ‎12-04-2017

Now that SAP TechEd season is over, I can resume my "normal" life at SAP, and start sharing some more tips and tricks that I think could be interesting for the community.

During the SAP TechEd season, I was invited to support the ESIGELEC InnoJam near Rouen in France!

Students were challenged with some interesting use case around Machine Learning, Geo Spatial and the Cloud where our partners provided us with more than 10 GB of data files that was loaded in a SAP HANA, express edition virtual machine instance hosted in their local hyper visor.

SAP HANA, express edition was a perfect fit for the challenges as it provided all the Machine Learning and Geo Spatial functionalities required, and combined to the Cloud Connector students were able to expose their results to the Cloud.

However, at that staged, we didn't anticipate that students would use something else than the SAP HANA Tools, especially the SAP HANA plugin for the Eclipse IDE, to query the data.

And guess what they did wanted to use other tools than the SAP provided ones. Many students wanted to connect using JDBC or Node.js, which requires a SQL port number.

When using the SAP HANA plugin for the Eclipse IDE, you don't need to provide this information.
Only a host name, an instance number and the tenant name are required (or the use of the System tenant).

The SYSTEM tenant database SQL port

Even if the students were not granted access to the System tenant database, it makes sense to document that part too.

By default, the SYSTEM tenant will use 39013 as the SQL port (3<instance number>13).

This is documented in the SAP HANA Administration Guide (under Landscape Management and Network Administration \ Network Administration \ Network Administration \ Connections from Database Clients and Web Clients to SAP HANA) but also in the TCP/IP Ports of All SAP Products documentation.

The Tenant database SQL port

For a tenant database, port numbers are assigned automatically from the available port number range (3<instance number>41 – 3<instance number>98) according to availability at the time the database is created.

Administrators can also explicitly specify which port numbers to use when they create the tenant database.

However, with the latest version of SAP HANA, express edition, the HXE tenant database (created by default) uses 39015 as the SQL port (3<instance number>15).

This correspond to the default port used when a single-container system is converted to support multi-tenant database containers.

Prior to the event, we did use the HXE to test the data import, and adjusted some of the content (especially the date formats). Then, a new tenant was created with the final data.

Now, the big question:

How do I get my tenant database SQL port?

The easiest way to get your tenant SQL port is to run a piece of SQL either from:

system tenant database

SELECT

	  DATABASE_NAME

	, SERVICE_NAME

	, PORT

	, SQL_PORT

	, (PORT + 2) HTTP_PORT

FROM

	SYS_DATABASES.M_SERVICES

WHERE

  (

        SERVICE_NAME      = 'indexserver'

    and COORDINATOR_TYPE  = 'MASTER'

  )

  or SERVICE_NAME = 'xsengine'

;

the tenant database itself

SELECT

	  SERVICE_NAME

	, PORT

	, SQL_PORT

	, (PORT + 2) HTTP_PORT

FROM

	SYS.M_SERVICES

WHERE

  (

        SERVICE_NAME      = 'indexserver'

    and COORDINATOR_TYPE  = 'MASTER'

  )

  or SERVICE_NAME = 'xsengine'

;

Now you can use either HDB CLI or the SAP HANA plugin for the Eclipse IDE to run one of these SQL statements and get your details.

SAP HANA HDB CLI

The SAP HANA HDB CLI is part of the SAP HANA Client can be downloaded from here.

Once downloaded, you can follow the instructions from the SAP HANA Client Installation and Update Guide.

Here is a quick example of the HDB CLI command to run from the System tenant database:

hdbsql -n <hxe host> -i <instance number> -d SystemDB -u SYSTEM -p <password> "SELECT DATABASE_NAME, SERVICE_NAME, PORT, SQL_PORT, (PORT + 2) HTTP_PORT FROM SYS_DATABASES.M_SERVICES WHERE ( SERVICE_NAME = 'indexserver' and COORDINATOR_TYPE = 'MASTER' ) or SERVICE_NAME = 'xsengine';"

The result should look like this:

DATABASE_NAME,SERVICE_NAME,PORT,SQL_PORT,HTTP_PORT

"HXE","indexserver",39003,39015,39005

"INNOJAM","indexserver",39040,39041,39042

SAP HANA plugin for the Eclipse IDE

You can find the installation instruction for the SAP HANA plugin for the Eclipse IDE here.

Once the installation is completed, switch to SAP HANA Administrative Console perspective using the menu bar Window > Perspective > SAP HANA Administrative Console.

Then under the Systems tab, you can use the Add Systems menu as displayed below.

You will be prompted with the host name, the instance number, the tenant name and then the credentials.

Want more information?

If you want to get hands on additional materials on that topic, the SAP Developer Center provides a multitude of online tutorials made by the SAP developer community for the community.

Here are a few of them: