Hey, SAP HANA, express edition! What’s your SQL port number?
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:
- Connect to SAP HANA, express edition using JDBC
- Connect to SAP HANA, express edition using Python
- Connect to SAP HANA, express edition using Data Provider for Microsoft .NET
Thank you for reading, Have fun and let us know your feedback!
You can also:
- Subscribe to the SAP Developer News monthly newsletter for updates
- Follow us on
- Twitter @sapdev
- YouTube: SAP Community
- Google+: SAP Developers
- Watch out for our next CodeJam events
And of course, if you found this blog useful, remember that “sharing is caring”! ?
thanks for the extensive blog post.
While reading it, one question didn't leave my mind: why would I want to use the exact port number in order to connect to a HANA database?
By specifying the hostname, the instance ID and the database name that I want to talk to, the client software has got all it needs to figure out the port itself. No need for any calculations, queries or anything alike.
Can you think of a reason?
Thanks for your feedback
When connecting to SAP HANA using ODBC or JDBC for example, the port number is usually required in the connect string along with the host.
Sometime the instance number can be used as an optional parameter but is not available with all drivers.
Also most developers are used to the “host:port” pattern in the connect string or url with many other database while using other tools than SAP provided tools.
My colleague @Lucia Subatin published one around the use of dbeaver for example where the port is required.
The same would also apply when using Node.js or Python where developers are used to the port number along with the host instead of the instance number.
Regards and thanks again for your feedback
Thanks for the quick reply and the potentially limited support by drivers was also one of my first thoughts.
Funnily enough, every driver I tried (ODBC, JDBC, node-hdb) fully support the "databaseName"/"instanceNumber" approach. I even was able to use it in non-SAP client software (SQLsuqirrel) without any problem.
Maybe it's time to promote this approach a bit more by mentioning it more often in the documentation and the examples. Given that so far most examples completely ignore both hdbuserstore-based credentials as well as the portless-connection string, I think it is not surprising that most developers are used to the "host:port" approach - they likely simply don't know that there are better, more flexible options.
Using the instance number is definitely much simpler, and an instance name parameter would also be a nice addition.
True that the instance number can be used with JDBC (although not sure it was there since the beginning) but you need to know the parameter syntax which is the hardest part. So most developers stick to the "common" connect string options.
hdbuserstore and SSO might actually be my next topic for a tutorial (most likely hdbuserstore first), unless you want to give it a shot and publish something around it.
PS: I think that the databaseName is only usable with non-MDC instance, which is becoming rare theses days
Hmm... knowing the documented connection parameters is harder than running SQL statements to find out the right connection port?
I found the documentation quite OK to get this knowledge:
The node-driver, although this didn't make it into the documentation, also worked nicely with "instanceNumber".
For ODBC, Python and GO one can specify "DATABASENAME"/"SERVERDB".
Those drivers don't directly support "instance no" but using the SystemDB instance port + the database name is still easier and more than actually connecting to the SystemDB, manually querying the available DBs and then connecting to the target instance.
Why you think that "databaseName" is for non-MDC is not clear to me. The parameter only makes sense, with named DBs.
Hi Lars Breddemann
I looked at that page for the different version of HANA especially the 1.0 SPS12, wher you have this paragraph:
This paragraph disappeared in the HANA 2.0 SPS01 documentation. So anytime you use the database name, you are actually doing that (ask the system db what is your port number).
In the following HANA 2.0 SPS02 documentation, in the "Connecting to the Specified Database" paragraph it recommends the use of the database name:
However, in many other places in the documentation, the connect string shows and promote primarily the use of the port number.
Regarding, the "“databaseName is for non-MDC", I think I got confused with some of the old single container capabilities (prior-HANA 1.0 SPS12).
In my opinion (and I'm sure you will agree with this), using the port number or the database name is matter of taste. 🙂
One of my goals was also to raise questions like yours, so people may learn even more, and I think I succeeded! So a big thanks for your contribution.
PS: Let me know if you want to give it a shot and publish something around hdbuserstore and/or SSO
You definitively succeeded with this blog! Thanks for taking the time to reply to my comments.
Of course there is a matter of taste to what details are used to connect to the database, but personally I agree with the direction laid out in the documentation: to use the database name. The reason for that is, when I use a DB, I am mainly concerned with connecting to the correct one. I don’t want to be concerned with technical details such as IP address or port numbers. By relying on the abstraction of both hostnames and DB names, I let the HANA client software and the TCP/IP stack to the work for me (probably better than I would do it) and keep my code simpler and more about what I want to achieve with it..
The fact that there are so many examples in the documentation that use the “port-number”-approach is probably showing that the examples and the habits of the authors have been first created when HANA didn’t support MDC. And we all know how hard it is to change a habit, don’t we?
I might do a little update to my now ancient blog HANA quick note – checking my connections and using them securely … at some point in time. But I’m pretty sure that I leave the infamously finicky SSO/SSL connection setup (possibly with ActiveDirectory) to someone who enjoys this topic more than I do 😉
Thanks again for this discussion. Cheers, Lars
Thanks for this informative article, Abdel.
Saved my day! I was struggling to find sql port for tenant DB and this post came in handy.
Thank Abdel. I should have remembered this after reading it last year. Now, I had to rediscover these ports once again more hard way 🙂