Discovering who is connected could be very useful detail in many situations. As deadlocks are not managed automatically within sap hana, it is important to identify who is connected in order to kill one of the locked sessions. A user query or a batch query may be running for a long periode of time. Sap hana performance may be suffering from it. Looking for the troublemaker and asking him to stop his query may be better than killing the query itself, but in an emergency situation you will have to kill the query yourself.
how to know who is connected to sap hana?
Keep in mind, that SystemDb and all its tenant databases are independant as regard connections. You will have to inquire specific database or each one of them to retrieve who is connected.
Client Applications can connect to sap hana tenant databases using a various type of client library such as (ODBC, JDBC, SQLDBC, DBSL, ODBO etc ...). It is important to understand at which level is the connection established.
how to list all current connections to sap hana?
Current connections can be active ( running ) or inactive ( idle ). Check the starting date to find out how long they have be running or inactive.
You can execute the SQL script either from hana studio or hdbsql. Hana studio will be preferable if other columns are added to the query.
how to list all sap hana client users who have been connected?
When trouble starts to show up, it is time to investigate. You may find out a hana user session interfering with another user session, a user logged in for more than 24 hours which could indicate a session running a very long query.
how to list all applications connected to sap hana?
You may want to know who is connected but most of all what is being used to connect to sap hana. On one hand, it is possible that a user is connected at the operating system level using hdbsql when he is not supposed to have direct access to the server, one the other hand you may discover a user connected with an unknow application. Most common situation would be a user connected too many times with an application and its sessions using ressources.
hdbsql=> \c -i 00 -d systemdb -u system -p AREalPass01
Connected to HAQ@linu.ard.usa-fgh.intra:30013
SELECT C.HOST AS "Host",
C.PORT AS "Port",
C.LOGICAL_CONNECTION_ID AS "Connection",
SUBSTRING(C.start_time,1,19) as "start",
SECONDS_BETWEEN(STAT.LAST_EXECUTED_TIME, CURRENT_TIMESTAMP) AS "Start Snd",
IFNULL(CR.CONNECTION_STATUS, 'IDLE') AS "Connection",
IFNULL(TX_STATUS.TRANSACTION_STATUS, 'INACTIVE') AS "Transaction",
C.AUTO_COMMIT AS "Auto commit",
SC1.VALUE AS "Appli ",
SC4.VALUE AS "Appli User",
C.USER_NAME AS "Db User",
C.CLIENT_IP AS "Client IP",
--PS.STATEMENT_STRING AS "SQL Statement", -- commented in order to fit result with hdbsql
FROM M_CONNECTIONS C LEFT OUTER JOIN M_SESSION_CONTEXT SC1 ON SC1.HOST = C.HOST
AND SC1.PORT = C.PORT
AND SC1.CONNECTION_ID = C.CONNECTION_ID
AND SC1.KEY = 'APPLICATION'
LEFT OUTER JOIN M_SESSION_CONTEXT SC2 ON SC2.HOST = C.HOST AND SC2.PORT = C.PORT AND SC2.CONNECTION_ID = C.CONNECTION_ID AND SC2.KEY = 'APPLICATIONSOURCE'
LEFT OUTER JOIN M_SESSION_CONTEXT SC3 ON SC3.HOST = C.HOST AND SC3.PORT = C.PORT AND SC3.CONNECTION_ID = C.CONNECTION_ID AND SC3.KEY = 'APPLICATIONVERSION'
LEFT OUTER JOIN M_SESSION_CONTEXT SC4 ON SC4.HOST = C.HOST AND SC4.PORT = C.PORT AND SC4.CONNECTION_ID = C.CONNECTION_ID AND SC4.KEY = 'APPLICATIONUSER'
LEFT OUTER JOIN M_CONNECTIONS CR ON CR.LOGICAL_CONNECTION_ID = C.LOGICAL_CONNECTION_ID AND CR.CONNECTION_STATUS != 'IDLE'
LEFT OUTER JOIN M_ACTIVE_STATEMENTS PS ON CR.HOST = PS.HOST AND CR.PORT = PS.PORT AND CR.CONNECTION_ID = PS.CONNECTION_ID AND CR.CURRENT_STATEMENT_ID = PS.STATEMENT_ID
LEFT OUTER JOIN M_SQL_PLAN_CACHE SPC ON SPC.HOST = PS.HOST AND SPC.PORT = PS.PORT AND SPC.PLAN_ID = PS.PLAN_ID
JOIN (SELECT C1.LOGICAL_CONNECTION_ID,
MAX(ST.LAST_EXECUTED_TIME) AS LAST_EXECUTED_TIME
FROM M_CONNECTIONS C1, M_CONNECTION_STATISTICS ST
WHERE C1.HOST = ST.HOST
AND C1.PORT = ST.PORT
AND C1.CONNECTION_ID = ST.CONNECTION_ID
GROUP BY C1.LOGICAL_CONNECTION_ID ) STAT
ON STAT.LOGICAL_CONNECTION_ID = C.LOGICAL_CONNECTION_ID
LEFT OUTER JOIN
MAX(TR.TRANSACTION_STATUS) AS TRANSACTION_STATUS
FROM M_TRANSACTIONS TR, M_CONNECTIONS CT WHERE TR.HOST = CT.HOST AND TR.PORT = CT.PORT AND TR.CONNECTION_ID = CT.CONNECTION_ID AND TR.TRANSACTION_STATUS != 'INACTIVE'
GROUP BY CT.LOGICAL_CONNECTION_ID ) TX_STATUS
ON TX_STATUS.LOGICAL_CONNECTION_ID = C.LOGICAL_CONNECTION_ID
WHERE C.CONNECTION_TYPE = 'Remote' AND C.LOGICAL_CONNECTION_ID = C.CONNECTION_ID ORDER BY 7,6 DESC, 3;
| Host | Port | Connection | start | Start Snd | Connect | Transact | Auto | Appli | Appli Us | Db Use | Client IP | ' |
| ------ | ----------- | ----------- | ------------------- | --------- | ------- | -------- | ----- | --------- | ------------ | ------ | -------------- | - |
| linux7 | 30258 | 217080 | 2017-07-28 10:29:03 | 20 | RUNNING | ACTIVE | TRUE | ? | ? | SYSTEM | 184.108.40.206 | - |
| linux7 | 30258 | 200050 | 2017-07-18 13:46:15 | 7062 | IDLE | ACTIVE | FALSE | ABAP:BHR | SAPSYS | SAPBRH | 220.127.116.11 | - |
| linux7 | 30258 | 200051 | 2017-07-18 13:46:16 | 1182 | IDLE | ACTIVE | FALSE | ABAP:BHR | SAPSYS | SAPBRH | 18.104.22.168 | - |
| linux7 | 30258 | 200057 | 2017-07-18 13:46:16 | 48 | IDLE | ACTIVE | FALSE | ABAP:BHR | ADMINSAP | SAPBRH | 22.214.171.124 | - |
| linux7 | 30258 | 200060 | 2017-07-18 13:46:17 | 288 | IDLE | ACTIVE | FALSE | ABAP:BHR | SAPSYS | SAPBRH | 126.96.36.199 | - |
| linux7 | 30258 | 200064 | 2017-07-18 13:46:17 | 42 | IDLE | ACTIVE | FALSE | ABAP:BHR | SAPSYS | SAPBRH | 188.8.131.52 | - |
| linux7 | 30258 | 200056 | 2017-07-18 13:46:16 | 1356 | IDLE | INACTIVE | FALSE | ABAP:BHR | ADMINSAP | SAPBRH | 184.108.40.206 | - |
| linux7 | 30258 | 216987 | 2017-07-28 10:12:42 | 997 | IDLE | INACTIVE | FALSE | ABAP:BHR | ADMINSAP | SAPBRH | 220.127.116.11 | - |
| linux7 | 30258 | 216989 | 2017-07-28 10:12:45 | 997 | IDLE | INACTIVE | FALSE | ABAP:BHR | ADMINSAP | SAPBRH | 18.104.22.168 | - |
| linux7 | 30258 | 217000 | 2017-07-28 10:14:40 | 882 | IDLE | INACTIVE | FALSE | ABAP:BHR | ADMINSAP | SAPBRH | 22.214.171.124 | - |
| linux7 | 30258 | 217062 | 2017-07-28 10:26:06 | 73 | IDLE | INACTIVE | FALSE | HDBStudio | ID_ADMIN_USR | ADMUSR | 126.96.36.199 | - |