DB2 Version 9.7 for Linux, UNIX, and Windows

SNAPSHOT_SUBSECT table function

Returns information about subsections of access plans from an application snapshot.

Note: This table function has been deprecated and replaced by the SNAPSUBSECTION administrative view and SNAP_GET_SUBSECTION table function - Retrieve subsection logical monitor group snapshot information.


>>-SNAPSHOT_SUBSECT--(--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_SUBSECT table function
Column name	Data type	Description or corresponding monitor element
SNAPSHOT_TIMESTAMP	TIMESTAMP	snapshot_timestamp - Snapshot timestamp
STMT_TEXT	CLOB(16M)¹	stmt_text - SQL statement text
SS_EXEC_TIME	BIGINT	ss_exec_time - Subsection execution elapsed time
TQ_TOT_SEND_SPILLS	BIGINT	tq_tot_send_spills - Total number of table queue buffers overflowed
TQ_CUR_SEND_SPILLS	BIGINT	tq_cur_send_spills - Current number of table queue buffers overflowed
TQ_MAX_SEND_SPILLS	BIGINT	tq_max_send_spills - Maximum number of table queue buffers overflows
TQ_ROWS_READ	BIGINT	tq_rows_read - Number of rows read from table queues
TQ_ROWS_WRITTEN	BIGINT	tq_rows_written - Number of rows written to table queues
ROWS_READ	BIGINT	rows_read - Rows read
ROWS_WRITTEN	BIGINT	rows_written - Rows written
SS_USR_CPU_TIME	BIGINT	ss_usr_cpu_time - User CPU time used by subsection
SS_SYS_CPU_TIME	BIGINT	ss_sys_cpu_time - System CPU time used by subsection
SS_NUMBER	INTEGER	ss_number - Subsection number
SS_STATUS	INTEGER	ss_status - Subsection status
SS_PARTITION_NUMBER	SMALLINT	ss_node_number - Subsection node number
TQ_PARTITION_WAITED_FOR	SMALLINT	tq_node_waited_for - Waited for node on a table queue
TQ_WAIT_FOR_ANY	INTEGER	tq_wait_for_any - Waiting for any node to send on a table queue
TQ_ID_WAITING_ON	INTEGER	tq_id_waiting_on - Waited on node on a table queue
¹ STMT_TEXT is defined as CLOB(16M) to allow for future expansion only. Actual output of the statement text is truncated at 64K.