The LONG_RUNNING_SQL administrative view returns SQL statements executed in the currently connected database. This view can be used to identify long-running SQL statements in the database.
The schema is SYSIBMADM.
In a non-restrictive database, SELECT privilege is granted to PUBLIC when the view is automatically created.
SELECT SUBSTR(STMT_TEXT, 1, 50) AS STMT_TEXT, AGENT_ID,
ELAPSED_TIME_MIN, APPL_STATUS, DBPARTITIONNUM
FROM SYSIBMADM.LONG_RUNNING_SQL ORDER BY DBPARTITIONNUM
STMT_TEXT AGENT_ID ...
-----------------------------...- --------...- ...
select * from dbuser.employee 228 ...
select * from dbuser.employee 228 ...
select * from dbuser.employee 228 ...
...
3 record(s) selected. ...
... ELAPSED_TIME_MIN APPL_STATUS DBPARTITIONNUM
... ---------------- -----------...- --------------
... 2 UOWWAIT 0
... 0 CONNECTED 1
... 0 CONNECTED 2
This view can be used to identify long-running SQL statements in the database. You can look at the currently running queries to see which statements are the longest running and the current status of the query. Further investigation can be done of the application containing the SQL statement, using agent ID as the unique identifier. If executing a long time and waiting on a lock, you might want to dig deeper using the LOCKWAITS or LOCKS_HELD administrative views. If "waiting on User", this means that the DB2® server is not doing anything but rather is waiting for the application to do something (like issue the next fetch or submit the next SQL statement).
Column name | Data type | Description or corresponding monitor element |
---|---|---|
SNAPSHOT_TIMESTAMP | TIMESTAMP | Time the report was generated. |
ELAPSED_TIME_MIN | INTEGER | Elapsed time of the statement in minutes. |
AGENT_ID | BIGINT | agent_id - Application handle (agent ID) |
APPL_NAME | VARCHAR(256) | appl_name - Application name |
APPL_STATUS | VARCHAR(22) | appl_status - Application status . This
interface returns a text identifier based on the defines in sqlmon.h, and is one of:
|
AUTHID | VARCHAR(128) | auth_id - Authorization ID |
INBOUND_COMM_ADDRESS | VARCHAR(32) | inbound_comm_address - Inbound communication address |
STMT_TEXT | CLOB(16 M) | stmt_text - SQL statement text |
DBPARTITIONNUM | SMALLINT | dbpartitionnum - Database partition number monitor element |
MEMBER | SMALLINT | member - Database member monitor element |