DB2 10.5 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.

Important: The LONG_RUNNING_SQL administrative view is deprecated and has been replaced by the MON_CURRENT_SQL - Retrieve key metrics for all activities on all members.

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