DB2 Version 9.7 for Linux, UNIX, and Windows

SNAP_GET_DYN_SQL table function

Note: This table function has been deprecated and replaced by the SNAP_GET_DYN_SQL_V91 table function - Retrieve dynsql logical group snapshot information


>>-SNAP_GET_DYN_SQL--(--dbname--,--dbpartitionnum--)-----------><

The schema is SYSPROC.

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

dbname: An input argument of type VARCHAR(255) that specifies a valid database name in the same instance as the currently connected database when calling this function. Specify a database name that has a directory entry type of either "Indirect" or "Home", as returned by the LIST DATABASE DIRECTORY command. Specify the null value to take the snapshot from the currently connected database.
dbpartitionnum: An input argument of type INTEGER that specifies a valid database partition number. Specify -1 for the current database partition, or -2 for all active database partitions. An active database partition is a partition where the database is available for connection and use by applications.
If the null value is specified, -1 is set implicitly.

If both parameters are set to NULL, the snapshot will be taken only if a file has not previously been created by the SNAPSHOT_FILEW stored procedure for the corresponding snapshot API request type.

Authorization

One of the following authorities is required:

EXECUTE privilege on the SNAP_GET_DYN_SQL table function
DATAACCESS authority

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

SYSMON authority
SYSCTRL authority
SYSMAINT authority
SYSADM authority

The function returns a table as shown in the following section.

Table 1. Information returned by the SNAP_GET_DYN_SQL table function
Column name	Data type	Description or corresponding monitor element
SNAPSHOT_TIMESTAMP	TIMESTAMP	snapshot_timestamp - Snapshot timestamp
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
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_TIME	BIGINT	total_usr_cpu_time - Total user CPU for a statement (in seconds)*
TOTAL_USR_TIME_MS	BIGINT	total_usr_cpu_time - Total user CPU for a statement (fractional, in microseconds)*
TOTAL_SYS_TIME	BIGINT	total_sys_cpu_time - Total system CPU for a statement (in seconds)*
TOTAL_SYS_TIME_MS	BIGINT	total_sys_cpu_time - Total system CPU for a statement (fractional, in microseconds)*
STMT_TEXT	CLOB	stmt_text - SQL statement text
* 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.