DB2 Version 9.7 for Linux, UNIX, and Windows

SNAP_GET_DYN_SQL_V91 table function - Retrieve dynsql logical group snapshot information

Note: This table function has been deprecated and replaced by the SNAPDYN_SQL administrative view and SNAP_GET_DYN_SQL_V95 table function - Retrieve dynsql logical group snapshot information.

The SNAP_GET_DYN_SQL_V91 table function returns snapshot information from the dynsql logical data group.

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


>>-SNAP_GET_DYN_SQL_V91--(--dbname--+------------------+--)----><
                                    '-, dbpartitionnum-'

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.
dbpartitionnum: An optional input argument of type INTEGER that specifies a valid database partition number. Specify -1 for the current database partition, or -2 for an aggregate of all active database partitions. If dbname is not set to NULL and dbpartitionnum is set to NULL, -1 is set implicitly for dbpartitionnum. If this input option is not used, that is, only dbname is provided, data is returned from all active database partitions. An active database partition is a partition where the database is available for connection and use by applications.

If both dbname and dbpartitionnum 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_V91 table function takes a snapshot for the currently connected database and database partition number.

Authorization

One of the following authorities is required to execute the function:

EXECUTE privilege on the SNAP_GET_DYN_SQL_V91 table function
DATAACCESS authority

To access snapshot monitor data, one of the following authorities is required:

SYSMON authority
SYSCTRL authority
SYSMAINT authority
SYSADM authority

Example

Retrieve a list of dynamic SQL run on the currently connected database partition of 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_V91('',-1)) as T
    ORDER BY ROWS_READ

The following example is a sample output from this query.

PREP_TIME_WORST      ...
-------------------- ...
                   0 ...
                   3 ...
                     ...
                   4 ...
                     ...
                   4 ...
                     ...
                   4 ...
                     ...
                   3 ...
                     ...
                   4 ...
                     ...

Output from this query (continued).

... NUM_COMPILATIONS     STMT_TEXT                                  
... -------------------- ---------------------------------------...-
...                    0 SET CURRENT LOCALE LC_CTYPE = 'en_US'      
...                    1 select rows_read, rows_written,            
...                         substr(stmt_text, 1, 40) as             
...                    1 select * from table                        
...                         (snap_get_dyn_sqlv9('',-1)) as t        
...                    1 select * from table                        
...                         (snap_getdetaillog9('',-1)) as t        
...                    1 select * from table                        
...                         (snap_get_hadr('',-1)) as t             
...                    1 select prep_time_worst, num_compilations,  
...                         substr(stmt_text,                       
...                    1 select prep_time_worst, num_compilations,  
...                         substr(stmt_text,

Information returned

Table 1. Information returned by the SNAP_GET_DYN_SQL_V91 table function
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
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 (in seconds)*
TOTAL_EXEC_TIME_MS	BIGINT	total_exec_time - Elapsed statement execution time (fractional, in microseconds)*
TOTAL_USR_CPU_TIME	BIGINT	total_usr_cpu_time - Total user CPU for a statement (in seconds)*
TOTAL_USR_CPU_TIME_MS	BIGINT	total_usr_cpu_time - Total user CPU for a statement (fractional, in microseconds)*
TOTAL_SYS_CPU_TIME	BIGINT	total_sys_cpu_time - Total system CPU for a statement (in seconds)*
TOTAL_SYS_CPU_TIME_MS	BIGINT	total_sys_cpu_time - Total system CPU for a statement (fractional, in microseconds)*
STMT_TEXT	CLOB(2 M)	stmt_text - SQL statement text
DBPARTITIONNUM	SMALLINT	The database partition from which the data was retrieved for this row.
* 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.