SNAPDYN_SQL administrative view and SNAP_GET_DYN_SQL table function - Retrieve dynsql logical group snapshot information

The SNAPDYN_SQL administrative view and the SNAP_GET_DYN_SQL table function return snapshot information from the dynsql logical data group.

The MON_GET_PKG_CACHE_STMT table function - Get package cache statement metrics returns a point-in-time view of both static and dynamic SQL statements in the database package cache. If you use this table function, you can take advantage of the monitoring infrastructure introduced in Version 9.7.

Depending on if you are using the administrative view or the table function, refer to one of the following sections:

SNAPDYN_SQL administrative view

This administrative view allows you to retrieve dynsql logical group snapshot information for the currently connected database.

This view returns information equivalent to the GET SNAPSHOT FOR DYNAMIC SQL ON database-alias CLP command.

The schema is SYSIBMADM.

Refer to Table 1 for a complete list of information that can be returned.

Authorization

One of the following authorizations is required to use the view:
  • SELECT privilege on the SNAPDYN_SQL administrative view
  • CONTROL privilege on the SNAPDYN_SQL administrative view
  • DATAACCESS authority
  • DBADM authority
  • SQLADM authority
  • ACCESSCTRL authority
  • SECADM authority
One of the following authorizations is required to use the table function:
  • EXECUTE privilege on the SNAP_GET_DYN_SQL table function
  • 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 list of dynamic SQL run on the currently connected database, ordered by the number of rows read. 
SELECT PREP_TIME_WORST, NUM_COMPILATIONS, SUBSTR(STMT_TEXT, 1, 60) 
   AS STMT_TEXT, DBPARTITIONNUM 
   FROM SYSIBMADM.SNAPDYN_SQL ORDER BY ROWS_READ
The following is an example of output from this query. 
PREP_TIME_WORST      NUM_COMPILATIONS     ...
-------------------- -------------------- ...
                  98                    1 ...
                   9                    1 ...
                   0                    0 ...
                   0                    1 ...
                   0                    1 ...
                   0                    1 ...
                   0                    1 ...
                   0                    1 ...
                  40                    1 ...

  9 record(s) selected.
Output from this query (continued). 
... STMT_TEXT                                                    ...
... ------------------------------------------------------------ ...
... select prep_time_worst, num_compilations, substr(stmt_text,  ...
... select * from dbuser.employee                                ...
... SET CURRENT LOCALE LC_CTYPE = 'en_US'                        ...
... select prep_time_worst, num_compilations, substr(stmt_text,  ...
... select prep_time_worst, num_compilations, substr(stmt_text,  ...
... select * from dbuser.employee                                ...
... insert into dbuser.employee values(1)                        ...
... select * from dbuser.employee                                ...
... insert into dbuser.employee values(1)                        ...
Output from this query (continued). 
... DBPARTITIONNUM
... --------------
...              0
...              0
...              0
...              2
...              1
...              2
...              2
...              1
...              0

SNAP_GET_DYN_SQL table function

The SNAP_GET_DYN_SQL table function returns the same information as the SNAPDYN_SQL administrative view, but allows you to retrieve the information for a specific database on a specific database member, aggregate of all database members or all database members.

This table function returns information equivalent to the GET SNAPSHOT FOR DYNAMIC SQL ON database-alias CLP command.

Refer to Table 1 for a complete list of information that can be returned.

Syntax

Read syntax diagramSkip visual syntax diagramSNAP_GET_DYN_SQL(dbname , member)

The schema is SYSPROC.

Table function parameters

dbname
An input argument of type VARCHAR(128) that specifies a valid database name in the same instance as the currently connected database. Specify a database name that has a directory entry type of either "Indirect" or "Home", as returned by the LIST DATABASE DIRECTORY command. Specify NULL or empty string to take the snapshot from the currently connected database.
member
An optional input argument of type INTEGER that specifies a valid database member number. Specify -1 for the current member, or -2 for an aggregate of all active members. If dbname is not set to NULL and member is set to NULL, -1 is set implicitly for member. If this input option is not used, that is, only dbname is provided, data is returned from members where the database is active.

If both dbname and member are set to NULL, an attempt is made to read data from the file created by SNAP_WRITE_FILE procedure. Note that this file could have been created at any time, which means that the data might not be current. If a file with the corresponding snapshot API request type does not exist, then the SNAP_GET_DYN_SQL table function takes a snapshot for the currently connected database and database member number.

Important: The SYSPROC.SNAP_WRITE_FILE procedure is deprecated and might be removed in a future release. For more information, see SNAP_WRITE_FILE procedure.

Authorization

One of the following authorizations is required:
  • EXECUTE privilege on the SNAP_GET_DYN_SQL table function
  • DATAACCESS 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, EXECUTE privilege is granted to PUBLIC when the function is automatically created.

Example

Retrieve a list of dynamic SQL run on the currently connected database, ordered by the number of rows read. 
SELECT PREP_TIME_WORST, NUM_COMPILATIONS, SUBSTR(STMT_TEXT, 1, 60) 
   AS STMT_TEXT FROM TABLE(SNAP_GET_DYN_SQL('',-1)) as T
    ORDER BY ROWS_READ
The following is an example of output from this query. 
PREP_TIME_WORST      NUM_COMPILATIONS      ...
-------------------- ---------------------...
                   0                    0 ...
                  49                    1 ...
                   0                    0 ...
                  46                    1 ...
                   0                    0 ...
                   0                    0 ...
                   0                    0 ...
                  29                    1 ...
                   0                    0 ...
                   0                    0 ...
                  10                    1 ...
                   0                    0 ...
                   4                    0 ...
                  53                    0 ...
                   0                    0 ...
                   6                    1 ...
                 334                    0 ...
                   0                    0 ...
                   5                    0 ...
                  10                    0 ...
                 599                    0 ...
                  15                    1 ...
                   7                    0 ...

  23 record(s) selected.
Output from this query (continued). 
... STMT_TEXT
... -----------------------------------------------------------
... SET :HV00017  :HI00017  = RPAD(VARCHAR(:HV00035  :HI00035 ),
... SELECT COLNAME, TYPENAME FROM  SYSCAT.COLUMNS WHERE TABNAME=
... DECLARE RES CURSOR WITH RETURN TO CALLER FOR SELECT R.TEXT F
... SELECT PREP_TIME_WORST, NUM_COMPILATIONS, SUBSTR(STMT_TEXT, 
... VALUES (:HV00026  :HI00026  + 1, :HV00024  :HI00024  + 1) IN
... VALUES (:HV00035  :HI00035  + 1, :HV00024  :HI00024  + 1) IN
... VALUES (1) INTO :HV00035  :HI00035                          
... SELECT TRIGNAME FROM  SYSCAT.TRIGGERS WHERE TABNAME='POLICY'
... VALUES (:HV00024  :HI00024 +1, :HV00022  :HI00022 +1) INTO :
... VALUES (1, CARDINALITY(CAST(:HV00040  :HI00040  AS "SYSIBMAD
... CALL SYSPROC.SYSINSTALLOBJECTS('POLICY','V','','')          
... SET :HV00017  :HI00017  = RPAD(VARCHAR(:HV00035  :HI00035 ),
... drop event monitor act                                      
... SELECT TABSCHEMA, TABNAME, TYPE, STATUS, TBSPACEID, PROPERTY
... CALL SAVE_EXEC_INFO (CAST(:HV00040  :HI00040  AS "SYSIBMADM"
... SET CURRENT LOCK TIMEOUT 5                                  
... SELECT TABNAME FROM SYSCAT.PERIODS WHERE PERIODNAME = 'SYSTE
... SELECT ARRAY_AGG(P.EXECUTABLE_ID ORDER BY M.IO_WAIT_TIME DES
... SET CURRENT ISOLATION RESET                                 
... CALL monreport.pkgcache()                                   
... SELECT A.SPECIFICNAME FROM SYSCAT.ROUTINES A WHERE (A.FENCED
... SELECT POLICY FROM SYSTOOLS.POLICY WHERE MED='DB2CommonMED' 
... VALUES 0                                                    

  23 record(s) selected.
After running a workload, user can use the following query with the table function. 
SELECT STATS_FABRICATE_TIME,SYNC_RUNSTATS_TIME 
   FROM TABLE (SNAP_GET_DYN_SQL('mytestdb', -1)) 
   AS SNAPDB

STATS_FABRICATE_TIME   SYNC_RUNSTATS_TIME
---------------------- ------------------
                     2                 12
                     1                 30
For the view based on this table function:
SELECT STATS_FABRICATE_TIME,SYNC_RUNSTATS_TIME 
   FROM SYSIBMADM.SNAPDYN_SQL  

STATS_FABRICATE_TIME   SYNC_RUNSTATS_TIME 
---------------------- ------------------ 
                     5                 10 
                     3                 20 
  2 record(s) selected.

Information returned

Table 1. Information returned by the SNAPDYN_SQL administrative view and the SNAP_GET_DYN_SQL table function
Column name Data type Description or corresponding monitor element
SNAPSHOT_TIMESTAMP TIMESTAMP The date and time that the snapshot was taken.
NUM_EXECUTIONS BIGINT num_executions - Statement executions
NUM_COMPILATIONS BIGINT num_compilations - Statement compilations
PREP_TIME_WORST BIGINT prep_time_worst - Statement worst preparation time
PREP_TIME_BEST BIGINT prep_time_best - Statement best preparation time
INT_ROWS_DELETED BIGINT int_rows_deleted - Internal rows deleted
INT_ROWS_INSERTED BIGINT int_rows_inserted - Internal rows inserted
INT_ROWS_UPDATED BIGINT int_rows_updated - Internal rows updated
ROWS_READ BIGINT rows_read - Rows read
ROWS_WRITTEN BIGINT rows_written - Rows written
STMT_SORTS BIGINT stmt_sorts - Statement sorts
SORT_OVERFLOWS BIGINT sort_overflows - Sort overflows
TOTAL_SORT_TIME BIGINT total_sort_time - Total sort time
POOL_DATA_L_READS BIGINT pool_data_l_reads - Buffer pool data logical reads
POOL_DATA_P_READS BIGINT pool_data_p_reads - Buffer pool data physical reads
POOL_TEMP_DATA_L_READS BIGINT pool_temp_data_l_reads - Buffer pool temporary data logical reads
POOL_TEMP_DATA_P_READS BIGINT pool_temp_data_p_reads - Buffer pool temporary data physical reads
POOL_INDEX_L_READS BIGINT pool_index_l_reads - Buffer pool index logical reads
POOL_INDEX_P_READS BIGINT pool_index_p_reads - Buffer pool index physical reads
POOL_TEMP_INDEX_L_READS BIGINT pool_temp_index_l_reads - Buffer pool temporary index logical reads
POOL_TEMP_INDEX_P_READS BIGINT pool_temp_index_p_reads - Buffer pool temporary index physical reads
POOL_XDA_L_READS BIGINT pool_xda_l_reads - Buffer Pool XDA Data Logical Reads
POOL_XDA_P_READS BIGINT pool_xda_p_reads - Buffer Pool XDA Data Physical Reads
POOL_TEMP_XDA_L_READS BIGINT pool_temp_xda_l_reads - Buffer Pool Temporary XDA Data Logical Reads
POOL_TEMP_XDA_P_READS BIGINT pool_temp_xda_p_reads - Buffer Pool Temporary XDA Data Physical Reads monitor element
TOTAL_EXEC_TIME BIGINT total_exec_time - Elapsed statement execution time
TOTAL_EXEC_TIME_MS BIGINT total_exec_time - Elapsed statement execution time
TOTAL_USR_CPU_TIME BIGINT total_usr_cpu_time - Total user CPU for a statement
TOTAL_USR_CPU_TIME_MS BIGINT total_usr_cpu_time - Total user CPU for a statement
TOTAL_SYS_CPU_TIME BIGINT total_sys_cpu_time - Total system CPU for a statement
TOTAL_SYS_CPU_TIME_MS BIGINT total_sys_cpu_time - Total system CPU for a statement
STMT_TEXT CLOB(2 M) stmt_text - SQL statement text
DBPARTITIONNUM SMALLINT dbpartitionnum - Database partition number monitor element
STATS_FABRICATE_TIME BIGINT The total time (in milliseconds) spent by system to create needed statistics without table or index scan during query compilation for a dynamic statement.
SYNC_RUNSTATS_TIME BIGINT The total time (in milliseconds) spent on synchronous statistics-collect activities during query compilation for a dynamic statement.
MEMBER SMALLINT member - Database member monitor element
* To calculate the total time spent for the monitor element that this column is based on, you must add the full seconds reported in the column for this monitor element that ends with _S to the fractional seconds reported in the column for this monitor element that ends with _MS, using the following formula: (monitor-element-name_S × 1,000,000 + monitor-element-name_MS) ÷ 1,000,000. For example, (ELAPSED_EXEC_TIME_S × 1,000,000 + ELAPSED_EXEC_TIME_MS) ÷ 1,000,000.