Find activate HANA port via SQL

If you want to list “Active HANA Ports” on OS or HANA Admin Tool via SQL, then you can try to use below SQL queries to do it easily ;

Architecture of HANA Ports ;

- We can list active “SQL Port” which is belongs to “SYSTEMDB” , to do this; Go on SYSTEMDB and execute following SQL query;

SELECT DATABASE_NAME, SQL_PORT FROM SYS_DATABASES.M_SERVICES WHERE DATABASE_NAME=’SYSTEMDB’ 
and SERVICE_NAME=’nameserver’  and COORDINATOR_TYPE= ‘MASTER’;

- We can list all the ports from SYS_DATABASES.M_SERVICES ,  to do this; Go on SYSTEMDB and execute following SQL query;

SELECT * FROM SYS_DATABASES.M_SERVICES;

Tenant DB Port number rule is ;

Example for an instance 02

- On Tenant DB , we can get “IndexServer” and “XSengine” Ports ;

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’));

 

- Checks all ports for a specific Tenant DB (example DB name is : H3) ;

SELECT DATABASE_NAME, SERVICE_NAME, PORT, SQL_PORT, (PORT + 2) HTTP_PORT FROM SYS_DATABASES.M_SERVICES WHERE DATABASE_NAME=’H3′and ((SERVICE_NAME=’indexserver’ and COORDINATOR_TYPE= ‘MASTER’) or (SERVICE_NAME=’xsengine’));

- Shows all Tenant DB’s all Port Numbers ;

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’));