List all Connected Applications to SAP HANA

You can use below SQL query to list all application connections which are connected to SAP HANA system from SQL editor.

 List all Connected Applications to SAP HANA

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 
	(SELECT CT.LOGICAL_CONNECTION_ID, 
	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;

Information Link ;

http://www.bestsaphanatraining.com/

 

 

Leave a Reply


× six = 12

Blogroll