DB2 Version 9.7 for Linux, UNIX, and Windows

LONG_RUNNING_SQL administrative view

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.

Authorization

One of the following authorizations is required:

SELECT privilege on the LONG_RUNNING_SQL administrative view
CONTROL privilege on the LONG_RUNNING_SQL administrative view
DATAACCESS authority

In addition, to access snapshot monitor data, one of the following authorities is also required:

SYSMON
SYSCTRL
SYSMAINT
SYSADM

Example

Retrieve a report on long running SQL statements in the currently connected database.

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

The following example is a sample output for this query.

STMT_TEXT                         AGENT_ID     ...  
-----------------------------...- --------...- ...  
select * from dbuser.employee              228 ...  
select * from dbuser.employee              228 ...  
select * from dbuser.employee              228 ...  
                                               ...  
3 record(s) selected.                          ...

Output for this query (continued).

... ELAPSED_TIME_MIN APPL_STATUS     DBPARTITIONNUM 
... ---------------- -----------...- -------------- 
...                2 UOWWAIT                      0 
...                0 CONNECTED                    1 
...                0 CONNECTED                    2

Usage note

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).

Information returned

Table 1. Information returned by the LONG_RUNNING_SQL administrative view
Column name	Data type	Description or corresponding monitor element
SNAPSHOT_TIMESTAMP	TIMESTAMP	snapshot_timestamp - Snapshot timestamp monitor element
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: BACKUP COMMIT_ACT COMP CONNECTED CONNECTPEND CREATE_DB DECOUPLED DISCONNECTPEND INTR IOERROR_WAIT LOAD LOCKWAIT QUIESCE_TABLESPACE RECOMP REMOTE_RQST RESTART RESTORE ROLLBACK_ACT ROLLBACK_TO_SAVEPOINT TEND THABRT THCOMT TPREP UNLOAD UOWEXEC UOWWAIT WAITFOR_REMOTE
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	The database partition from which the data was retrieved for this row.