DB2 Version 10.1 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
  • DBADM authority
  • SQLADM authority
In addition, to access snapshot monitor data, one of the following authorities is also required:
  • SYSMON
  • SYSCTRL
  • SYSMAINT
  • SYSADM

Default PUBLIC privilege

In a non-restrictive database, SELECT privilege is granted to PUBLIC when the view is automatically created.

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 is an example of 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 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:
  • 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
  • UOWWAI
  • 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 dbpartitionnum - Database partition number monitor element
MEMBER SMALLINT member - Database member monitor element