DB2 Version 9.7 for Linux, UNIX, and Windows

SNAP_GET_APPL table function - Retrieve appl logical data group snapshot information

Note: This table function has been deprecated and replaced by the SNAPAPPL administrative view and SNAP_GET_APPL_V95 table function - Retrieve appl logical data group snapshot information.

The SNAP_GET_APPL table function returns information about applications from an application snapshot, in particular, the appl logical data group.

Used with the SNAP_GET_AGENT, SNAP_GET_AGENT_MEMORY_POOL, SNAP_GET_APPL_INFO, SNAP_GET_STMT and SNAP_GET_SUBSECTION table functions, the SNAP_GET_APPL table function provides information equivalent to the GET SNAPSHOT FOR ALL APPLICATIONS CLP command, but retrieves data from all database partitions.

Refer to Table 1 for a complete list of information that can be returned.

Syntax


>>-SNAP_GET_APPL--(--dbname--+------------------+--)-----------><
                             '-, dbpartitionnum-'

The schema is SYSPROC.

Table function parameters

dbname: An input argument of type VARCHAR(128) that specifies a valid database name in the same instance as the currently connected database. Specify a database name that has a directory entry type of either "Indirect" or "Home", as returned by the LIST DATABASE DIRECTORY command. Specify an empty string to take the snapshot from the currently connected database. Specify a NULL value to take the snapshot from all databases within the same instance as the currently connected database.
dbpartitionnum: An optional input argument of type INTEGER that specifies a valid database partition number. Specify -1 for the current database partition, or -2 for an aggregate of all active database partitions. If dbname is not set to NULL and dbpartitionnum is set to NULL, -1 is set implicitly for dbpartitionnum. If this input option is not used, that is, only dbname is provided, data is returned from all active database partitions. An active database partition is a partition where the database is available for connection and use by applications.

If both dbname and dbpartitionnum are set to NULL, an attempt is made to read data from the file created by SNAP_WRITE_FILE procedure. Note that this file could have been created at any time, which means that the data might not be current. If a file with the corresponding snapshot API request type does not exist, then the SNAP_GET_APPL table function takes a snapshot for the currently connected database and database partition number.

Authorization

EXECUTE privilege on the SNAP_GET_APPL table function.
DATAACCESS authority

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

SYSMON authority
SYSCTRL authority
SYSMAINT authority
SYSADM authority

Example

Retrieve details on rows read and written for each application for all active databases.

SELECT SUBSTR(DB_NAME,1,8) AS DB_NAME, AGENT_ID, ROWS_READ, ROWS_WRITTEN 
   FROM TABLE (SNAP_GET_APPL(CAST(NULL AS VARCHAR(128)),-1)) AS T

The following example is a sample output from this query.

DB_NAME  AGENT_ID       ROWS_READ      ROWS_WRITTEN 
-------- --------...--- ---------...-- ------------...-
WSDB                679              0                0
WSDB                461              3                0
WSDB                460              4                0
TEST                680              4                0
TEST                455              6                0
TEST                454              0                0
TEST                453             50                0

Information returned

Table 1. Information returned by the SNAP_GET_APPL table function
Column name	Data type	Description or corresponding monitor element
SNAPSHOT_TIMESTAMP	TIMESTAMP	snapshot_timestamp - Snapshot timestamp monitor element
DB_NAME	VARCHAR(128)	db_name - Database name
AGENT_ID	BIGINT	agent_id - Application handle (agent ID)
UOW_LOG_SPACE_USED	BIGINT	uow_log_space_used - Unit of work log space used
ROWS_READ	BIGINT	rows_read - Rows read
ROWS_WRITTEN	BIGINT	rows_written - Rows written
INACT_STMTHIST_SZ	BIGINT	stmt_history_list_size - Statement history list size
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_DATA_WRITES	BIGINT	pool_data_writes - Buffer pool data writes
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_INDEX_WRITES	BIGINT	pool_index_writes - Buffer pool index writes
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_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
POOL_TEMP_XDA_L_READS	BIGINT	pool_temp_xda_l_reads - Buffer Pool Temporary XDA Data Logical Reads
POOL_TEMP_XDA_P_READS	BIGINT	pool_temp_xda_p_reads - Buffer Pool Temporary XDA Data Physical Reads monitor element
POOL_XDA_L_READS	BIGINT	pool_xda_l_reads - Buffer Pool XDA Data Logical Reads
POOL_XDA_P_READS	BIGINT	pool_xda_p_reads - Buffer Pool XDA Data Physical Reads
POOL_XDA_WRITES	BIGINT	pool_xda_writes - Buffer Pool XDA Data Writes
POOL_READ_TIME	BIGINT	pool_read_time - Total buffer pool physical read time
POOL_WRITE_TIME	BIGINT	pool_write_time - Total buffer pool physical write time
DIRECT_READS	BIGINT	direct_reads - Direct reads from database
DIRECT_WRITES	BIGINT	direct_writes - Direct writes to database
DIRECT_READ_REQS	BIGINT	direct_read_reqs - Direct read requests
DIRECT_WRITE_REQS	BIGINT	direct_write_reqs - Direct write requests
DIRECT_READ_TIME	BIGINT	direct_read_time - Direct read time
DIRECT_WRITE_TIME	BIGINT	direct_write_time - Direct write time
UNREAD_PREFETCH_PAGES	BIGINT	unread_prefetch_pages - Unread prefetch pages
LOCKS_HELD	BIGINT	locks_held - Locks held
LOCK_WAITS	BIGINT	lock_waits - Lock waits
LOCK_WAIT_TIME	BIGINT	lock_wait_time - Time waited on locks
LOCK_ESCALS	BIGINT	lock_escals - Number of lock escalations
X_LOCK_ESCALS	BIGINT	x_lock_escals - Exclusive lock escalations
DEADLOCKS	BIGINT	deadlocks - Deadlocks detected
TOTAL_SORTS	BIGINT	total_sorts - Total sorts
TOTAL_SORT_TIME	BIGINT	total_sort_time - Total sort time
SORT_OVERFLOWS	BIGINT	sort_overflows - Sort overflows
COMMIT_SQL_STMTS	BIGINT	commit_sql_stmts - Commit statements attempted
ROLLBACK_SQL_STMTS	BIGINT	rollback_sql_stmts - Rollback statements attempted
DYNAMIC_SQL_STMTS	BIGINT	dynamic_sql_stmts - Dynamic SQL statements attempted
STATIC_SQL_STMTS	BIGINT	static_sql_stmts - Static SQL statements attempted
FAILED_SQL_STMTS	BIGINT	failed_sql_stmts - Failed statement operations
SELECT_SQL_STMTS	BIGINT	select_sql_stmts - Select SQL statements executed
DDL_SQL_STMTS	BIGINT	ddl_sql_stmts - Data definition language (DDL) SQL statements
UID_SQL_STMTS	BIGINT	uid_sql_stmts - UPDATE/INSERT/DELETE SQL statements executed
INT_AUTO_REBINDS	BIGINT	int_auto_rebinds - Internal automatic rebinds
INT_ROWS_DELETED	BIGINT	int_rows_deleted - Internal rows deleted
INT_ROWS_UPDATED	BIGINT	int_rows_updated - Internal rows updated
INT_COMMITS	BIGINT	int_commits - Internal commits
INT_ROLLBACKS	BIGINT	int_rollbacks - Internal rollbacks
INT_DEADLOCK_ROLLBACKS	BIGINT	int_deadlock_rollbacks - Internal rollbacks due to deadlock
ROWS_DELETED	BIGINT	rows_deleted - Rows deleted
ROWS_INSERTED	BIGINT	rows_inserted - Rows inserted
ROWS_UPDATED	BIGINT	rows_updated - Rows updated
ROWS_SELECTED	BIGINT	rows_selected - Rows selected
BINDS_PRECOMPILES	BIGINT	binds_precompiles - Binds/precompiles attempted
OPEN_REM_CURS	BIGINT	open_rem_curs - Open remote cursors
OPEN_REM_CURS_BLK	BIGINT	open_rem_curs_blk - Open remote cursors with blocking
REJ_CURS_BLK	BIGINT	rej_curs_blk - Rejected block cursor requests
ACC_CURS_BLK	BIGINT	acc_curs_blk - Accepted block cursor requests
SQL_REQS_SINCE_COMMIT	BIGINT	sql_reqs_since_commit - SQL requests since last commit
LOCK_TIMEOUTS	BIGINT	lock_timeouts - Number of lock timeouts
INT_ROWS_INSERTED	BIGINT	int_rows_inserted - Internal rows inserted
OPEN_LOC_CURS	BIGINT	open_loc_curs - Open local cursors
OPEN_LOC_CURS_BLK	BIGINT	open_loc_curs_blk - Open local cursors with blocking
PKG_CACHE_LOOKUPS	BIGINT	pkg_cache_lookups - Package cache lookups
PKG_CACHE_INSERTS	BIGINT	pkg_cache_inserts - Package cache inserts
CAT_CACHE_LOOKUPS	BIGINT	cat_cache_lookups - Catalog cache lookups
CAT_CACHE_INSERTS	BIGINT	cat_cache_inserts - Catalog cache inserts
CAT_CACHE_OVERFLOWS	BIGINT	cat_cache_overflows - Catalog cache overflows
NUM_AGENTS	BIGINT	num_agents - Number of agents working on a statement
AGENTS_STOLEN	BIGINT	agents_stolen - Stolen agents
ASSOCIATED_AGENTS_TOP	BIGINT	associated_agents_top - Maximum number of associated agents
APPL_PRIORITY	BIGINT	appl_priority - Application agent priority
APPL_PRIORITY_TYPE	VARCHAR(16)	appl_priority_type - Application priority type . This interface returns a text identifier, based on defines in sqlmon.h, and is one of: DYNAMIC_PRIORITY FIXED_PRIORITY
PREFETCH_WAIT_TIME	BIGINT	prefetch_wait_time - Time waited for prefetch
APPL_SECTION_LOOKUPS	BIGINT	appl_section_lookups - Section lookups
APPL_SECTION_INSERTS	BIGINT	appl_section_inserts - Section inserts
LOCKS_WAITING	BIGINT	locks_waiting - Current agents waiting on locks
TOTAL_HASH_JOINS	BIGINT	total_hash_joins - Total hash joins
TOTAL_HASH_LOOPS	BIGINT	total_hash_loops - Total hash loops
HASH_JOIN_OVERFLOWS	BIGINT	hash_join_overflows - Hash join overflows
HASH_JOIN_SMALL_ OVERFLOWS	BIGINT	hash_join_small_overflows - Hash join small overflows
APPL_IDLE_TIME	BIGINT	appl_idle_time - Application idle time
UOW_LOCK_WAIT_TIME	BIGINT	uow_lock_wait_time - Total time unit of work waited on locks
UOW_COMP_STATUS	VARCHAR(14)	uow_comp_status - Unit of work completion status . This interface returns a text identifier, based on defines in sqlmon.h, and is one of: APPL_END UOWABEND UOWCOMMIT UOWDEADLOCK UOWLOCKTIMEOUT UOWROLLBACK UOWUNKNOWN
AGENT_USR_CPU_TIME_S	BIGINT	agent_usr_cpu_time - User CPU time used by agent (in seconds)*
AGENT_USR_CPU_TIME_MS	BIGINT	agent_usr_cpu_time - User CPU time used by agent (fractional, in microseconds)*
AGENT_SYS_CPU_TIME_S	BIGINT	agent_sys_cpu_time - System CPU time used by agent (in seconds)*
AGENT_SYS_CPU_TIME_MS	BIGINT	agent_sys_cpu_time - System CPU time used by agent (fractional, in microseconds)*
APPL_CON_TIME	TIMESTAMP	appl_con_time - Connection request start timestamp
CONN_COMPLETE_TIME	TIMESTAMP	conn_complete_time - Connection request completion timestamp
LAST_RESET	TIMESTAMP	last_reset - Last reset timestamp
UOW_START_TIME	TIMESTAMP	uow_start_time - Unit of work start timestamp
UOW_STOP_TIME	TIMESTAMP	uow_stop_time - Unit of work stop timestamp
PREV_UOW_STOP_TIME	TIMESTAMP	prev_uow_stop_time - Previous unit of work completion timestamp
UOW_ELAPSED_TIME_S	BIGINT	uow_elapsed_time - Most recent unit of work elapsed time (in seconds)*
UOW_ELAPSED_TIME_MS	BIGINT	uow_elapsed_time - Most recent unit of work elapsed time (fractional, in microseconds)*
ELAPSED_EXEC_TIME_S	BIGINT	elapsed_exec_time - Statement execution elapsed time (in seconds)*
ELAPSED_EXEC_TIME_MS	BIGINT	elapsed_exec_time - Statement execution elapsed time (fractional, in microseconds)*
INBOUND_COMM_ADDRESS	VARCHAR(32)	inbound_comm_address - Inbound communication address
LOCK_TIMEOUT_VAL	BIGINT	lock_timeout_val - Lock timeout (seconds)
PRIV_WORKSPACE_NUM_ OVERFLOWS	BIGINT	priv_workspace_num_overflows - Private workspace overflows
PRIV_WORKSPACE_SECTION_ INSERTS	BIGINT	priv_workspace_section_inserts - Private workspace section inserts
PRIV_WORKSPACE_SECTION_ LOOKUPS	BIGINT	priv_workspace_section_lookups - Private workspace section lookups
PRIV_WORKSPACE_SIZE_ TOP	BIGINT	priv_workspace_size_top - Maximum private workspace size
SHR_WORKSPACE_NUM_ OVERFLOWS	BIGINT	shr_workspace_num_overflows - Shared workspace overflows
SHR_WORKSPACE_SECTION_ INSERTS	BIGINT	shr_workspace_section_inserts - Shared workspace section inserts
SHR_WORKSPACE_SECTION_ LOOKUPS	BIGINT	shr_workspace_section_lookups - Shared workspace section lookups
SHR_WORKSPACE_SIZE_ TOP	BIGINT	shr_workspace_size_top - Maximum shared workspace size
DBPARTITIONNUM	SMALLINT	The database partition from which the data for the row was retrieved.
CAT_CACHE_SIZE_TOP	BIGINT	cat_cache_size_top - Catalog cache high water mark
* 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.