DB2 Version 9.7 for Linux, UNIX, and Windows

TOP_DYNAMIC_SQL administrative view - Retrieve information about the top dynamic SQL statements

The TOP_DYNAMIC_SQL administrative view returns the top dynamic SQL statements sortable by number of executions, average execution time, number of sorts, or sorts per statement. These are the queries that should get focus to ensure they are well tuned.

The schema is SYSIBMADM.

Authorization

One of the following authorizations is required:

SELECT privilege on the TOP_DYNAMIC_SQL administrative view
CONTROL privilege on the TOP_DYNAMIC_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

Identify the top 5 most frequently run SQL.

SELECT NUM_EXECUTIONS, AVERAGE_EXECUTION_TIME_S, STMT_SORTS, 
   SORTS_PER_EXECUTION, SUBSTR(STMT_TEXT,1,60) AS STMT_TEXT 
   FROM SYSIBMADM.TOP_DYNAMIC_SQL 
   ORDER BY NUM_EXECUTIONS DESC FETCH FIRST 5 ROWS ONLY

The following example is a sample output for this query.

NUM_EXECUTIONS       AVERAGE_EXECUTION_TIME_S STMT_SORTS           ...  
-------------------- ------------------------ -------------------- ...  
                 148                        0                    0 ...  
                 123                        0                    0 ...  
                   2                        0                    0 ...  
                   1                        0                    0 ...  
                   1                        0                    0 ...  
                                                                        
  5 record(s) selected.

Output for this query (continued).

... SORTS_PER_EXECUTION  ...
... -------------------- ...
...                    0 ...
...                    0 ...
...                    0 ...
...                    0 ...
...                    0 ...

Output for this query (continued).

... STMT_TEXT                                                   
... ------------------------------------------------------------
... SELECT A.ID, B.EMPNO, B.FIRSTNME, B.LASTNAME, A.DEPT FROM E 
... SELECT A.EMPNO, A.FIRSTNME, A.LASTNAME, B.LOCATION, B.MGRNO 
... SELECT A.EMPNO, A.FIRSTNME, A.LASTNAME, B.DEPTNAME FROM EMP 
... SELECT ATM.SCHEMA, ATM.NAME, ATM.CREATE_TIME, ATM.LAST_WAIT,
... SELECT * FROM JESSICAE.EMP_RESUME

Information returned

Table 1. Information returned by the TOP_DYNAMIC_SQL administrative view
Column name	Data type	Description or corresponding monitor element
SNAPSHOT_TIMESTAMP	TIMESTAMP	snapshot_timestamp - Snapshot timestamp monitor element
NUM_EXECUTIONS	BIGINT	num_executions - Statement executions
AVERAGE_EXECUTION_TIME_S	BIGINT	Average execution time, in seconds.
STMT_SORTS	BIGINT	stmt_sorts - Statement sorts
SORTS_PER_EXECUTION	BIGINT	Number of sorts per statement execution.
STMT_TEXT	CLOB(2 M)	stmt_text - SQL statement text
DBPARTITIONNUM	SMALLINT	The database partition from which the data was retrieved for this row.