I am reposting my question from SAP SCN, where it was not blessed with an answer.
I am trying to write a query or script in HANA that shall provice detailed information about the current locks in the system, including the SQL statement that has acquired the lock. Here is what I have so far:
SELECT BT.BLOCKED_CONNECTION_ID AS "Blocked session", TR.CONNECTION_ID AS "Blocking session", BT.BLOCKED_TIME AS "Start", SECONDS_BETWEEN(BT.BLOCKED_TIME, NOW()) AS "Length", CN.CURRENT_SCHEMA_NAME AS "Schema", CN.CLIENT_HOST || ' ' || CAST(CN.CLIENT_PID AS VARCHAR(5)) AS "Host/PID", SC_AP.VALUE AS "Program", SC_AS.VALUE AS "Program source", BT.TABLE_NAME AS "Lock object", BT.LOCK_OWNER_UPDATE_TRANSACTION_ID, PS.STATEMENT_STRING AS "Locking statement" FROM SYS.M_BLOCKED_TRANSACTIONS_ BT JOIN SYS.M_TRANSACTIONS_ TR ON TR.HOST = BT.HOST AND TR.PORT = BT.PORT AND TR.UPDATE_TRANSACTION_ID = BT.LOCK_OWNER_UPDATE_TRANSACTION_ID JOIN SYS.M_CONNECTIONS CN ON CN.HOST = TR.HOST AND CN.PORT = TR.PORT AND CN.CONNECTION_ID = TR.CONNECTION_ID JOIN M_SESSION_CONTEXT SC_AP ON SC_AP.HOST = TR.HOST AND SC_AP.PORT = TR.PORT AND SC_AP.CONNECTION_ID = TR.CONNECTION_ID AND SC_AP.KEY = 'APPLICATION' JOIN M_SESSION_CONTEXT SC_AS ON SC_AS.HOST = TR.HOST AND SC_AS.PORT = TR.PORT AND SC_AS.CONNECTION_ID = TR.CONNECTION_ID AND SC_AS.KEY = 'APPLICATIONSOURCE' LEFT JOIN M_PREPARED_STATEMENTS PS ON PS.CONNECTION_ID = TR.CONNECTION_ID AND -- Doesn't work, because TR.CURRENT_STATEMENT_ID IS NULL: PS.STATEMENT_ID = TR.CURRENT_STATEMENT_ID
This query displays everyting correctly except the most important field—the locking statement, which I cannot retrieve because
M_TRANSACTIONS_.CURRENT_STATEMENT_ID IS NULL
for the transaction holding the lock. How can I extract the text of the statement that has acquired the lock? The version of our HANA is 1.00.122.05.
In connexion with this question, I have another, about HANA documentation. The only documentation for these system views and tables that I have found is SAP HANA SQL and System Views Reference, but it only lists the fields of views and tables with nary an attempt to describe them and their interrelations. Where is the actual documentation?