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
  • ACCESSCTRL authority
  • SECADM 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