DB2 Version 9.7 for Linux, UNIX, and Windows

SNAPSHOT_STATEMENT table function

Returns information about statements from an application snapshot.

Note: This table function has been deprecated and replaced by the SNAPSTMT administrative view and SNAP_GET_STMT table function - Retrieve statement snapshot information.


>>-SNAPSHOT_STATEMENT--(--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 all databases under the database instance.
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 is 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_STATEMENT table function
Column name	Data type	Description or corresponding monitor element
SNAPSHOT_TIMESTAMP	TIMESTAMP	snapshot_timestamp - Snapshot timestamp
AGENT_ID	BIGINT	agent_id - Application handle (agent ID)
ROWS_READ	BIGINT	rows_read - Rows read
ROWS_WRITTEN	BIGINT	rows_written - Rows written
NUM_AGENTS	BIGINT	num_agents - Number of agents working on a statement
AGENTS_TOP	BIGINT	agents_top - Number of agents created
STMT_TYPE	BIGINT	stmt_type - Statement type
STMT_OPERATION	BIGINT	stmt_operation/operation - Statement operation
SECTION_NUMBER	BIGINT	section_number - Section number
QUERY_COST_ESTIMATE	BIGINT	query_cost_estimate - Query cost estimate
QUERY_CARD_ESTIMATE	BIGINT	query_card_estimate - Query number of rows estimate
DEGREE_PARALLELISM	BIGINT	degree_parallelism - Degree of parallelism
STMT_SORTS	BIGINT	stmt_sorts - Statement sorts
TOTAL_SORT_TIME	BIGINT	total_sort_time - Total sort time
SORT_OVERFLOWS	BIGINT	sort_overflows - Sort overflows
INT_ROWS_DELETED	BIGINT	int_rows_deleted - Internal rows deleted
INT_ROWS_UPDATED	BIGINT	int_rows_updated - Internal rows updated
INT_ROWS_INSERTED	BIGINT	int_rows_inserted - Internal rows inserted
FETCH_COUNT	BIGINT	fetch_count - Number of successful fetches
STMT_START	TIMESTAMP	stmt_start - Statement operation start timestamp
STMT_STOP	TIMESTAMP	stmt_stop - Statement operation stop timestamp
STMT_USR_CPU_TIME_S	BIGINT	stmt_usr_cpu_time - User CPU time used by statement (in seconds)*
STMT_USR_CPU_TIME_MS	BIGINT	stmt_usr_cpu_time - User CPU time used by statement (fractional, in microseconds)*
STMT_SYS_CPU_TIME_S	BIGINT	stmt_sys_cpu_time - System CPU time used by statement (in seconds)*
STMT_SYS_CPU_TIME_MS	BIGINT	stmt_sys_cpu_time - System CPU time used by statement (fractional, in microseconds)*
STMT_ELAPSED_TIME_S	BIGINT	stmt_elapsed_time - Most recent statement elapsed time (in seconds)*
STMT_ELAPSED_TIME_MS	BIGINT	stmt_elapsed_time - Most recent statement elapsed time (fractional, in microseconds)*
BLOCKING_CURSOR	SMALLINT	blocking_cursor - Blocking cursor
STMT_PARTITION_NUMBER	SMALLINT	stmt_node_number - Statement node
CURSOR_NAME	VARCHAR(128)	cursor_name - Cursor name
CREATOR	VARCHAR(128)	creator - Application creator
PACKAGE_NAME	VARCHAR(128)	package_name - Package 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.