DB2 Version 10.1 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
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

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 is an example of 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	Timestamp for the report.
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	dbpartitionnum - Database partition number monitor element
MEMBER	SMALLINT	member - Database member monitor element