DB2 Version 9.7 for Linux, UNIX, and Windows

SQLCACHE_SNAPSHOT table function

The SQLCACHE_SNAPSHOT function returns the results of a snapshot of the DB2® dynamic SQL statement cache.

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


>>-SQLCACHE_SNAPSHOT--(--)-------------------------------------><

The schema is SYSFUN.

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 does not take any arguments. It returns the following table.

Table 1. Information returned by SQLCACHE_SNAPSHOT table function
Column name	Data type	Description or corresponding monitor element
NUM_EXECUTIONS	INTEGER	num_executions - Statement executions
NUM_COMPILATIONS	INTEGER	num_compilations - Statement compilations
PREP_TIME_WORST	INTEGER	prep_time_worst - Statement worst preparation time
PREP_TIME_BEST	INTEGER	prep_time_best - Statement best preparation time
INT_ROWS_DELETED	INTEGER	int_rows_deleted - Internal rows deleted
INT_ROWS_INSERTED	INTEGER	int_rows_inserted - Internal rows inserted
ROWS_READ	INTEGER	rows_read - Rows read
INT_ROWS_UPDATED	INTEGER	int_rows_updated - Internal rows updated
ROWS_WRITTEN	INTEGER	rows_written - Rows written
STMT_SORTS	INTEGER	stmt_sorts - Statement sorts
TOTAL_EXEC_TIME_S	INTEGER	total_exec_time - Elapsed statement execution time (in seconds)*
TOTAL_EXEC_TIME_MS	INTEGER	total_exec_time - Elapsed statement execution time (fractional, in microseconds)*
TOT_U_CPU_TIME_S	INTEGER	total_usr_cpu_time - Total user CPU for a statement (in seconds)*
TOT_U_CPU_TIME_MS	INTEGER	total_usr_cpu_time - Total user CPU for a statement (fractional, in microseconds)*
TOT_S_CPU_TIME_S	INTEGER	total_sys_cpu_time - Total system CPU for a statement (in seconds)*
TOT_S_CPU_TIME_MS	INTEGER	total_sys_cpu_time - Total system CPU for a statement (fractional, in microseconds)*
DB_NAME	VARCHAR(128)	db_name - Database name
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. * 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.