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

The queries returned by TOP_DYNAMIC_SQL administrative view 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.

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

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