DB2 Version 9.7 for Linux, UNIX, and Windows

SNAPSHOT_DYN_SQL table function

Returns information from a dynamic SQL snapshot. It replaces the SQLCACHE_SNAPSHOT function, which is still available for compatibility reasons.

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


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

The schema is SYSPROC.

Table function parameters

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 to execute the function:

EXECUTE privilege on the 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 SNAPSHOT_DYN_SQL table function
Column name	Data type	Description or corresponding monitor element
SNAPSHOT_TIMESTAMP	TIMESTAMP	snapshot_timestamp - Snapshot timestamp
ROWS_READ	BIGINT	rows_read - Rows read
ROWS_WRITTEN	BIGINT	rows_written - Rows written
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
STMT_SORTS	BIGINT	stmt_sorts - Statement sorts
TOTAL_EXEC_TIME	BIGINT	total_exec_time - Elapsed statement execution time
TOTAL_SYS_CPU_TIME	BIGINT	total_sys_cpu_time - Total system CPU for a statement
TOTAL_USR_CPU_TIME	BIGINT	total_usr_cpu_time - Total user CPU for a statement
STMT_TEXT	CLOB(16M)¹	stmt_text - SQL statement text
¹ STMT_TEXT is defined as CLOB(16M) to allow for future expansion only. Actual output of the statement text is truncated at 64K.