DB2 Version 9.7 for Linux, UNIX, and Windows

SNAP_GET_DB table function

Note: This table function has been deprecated and replaced by the SNAP_GET_DB_V91 table function - Retrieve snapshot information from the dbase logical group


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

The schema is SYSPROC.

The SNAP_GET_DB table function returns snapshot information from the database.

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 an aggregate of 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:

EXECUTE privilege on the SNAP_GET_DB table 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 SNAP_GET_DB table function
Column name	Data type	Description or corresponding monitor element
SNAPSHOT_TIMESTAMP	TIMESTAMP	snapshot_timestamp - Snapshot timestamp
DB_NAME	VARCHAR(128)	db_name - Database name
DB_PATH	VARCHAR(1024)	db_path - Database path
INPUT_DB_ALIAS	VARCHAR(128)	input_db_alias - Input database alias
DB_STATUS	BIGINT	db_status - Status of database
CATALOG_PARTITION	SMALLINT	catalog_node - Catalog node number
CATALOG_PARTITION_NAME	VARCHAR(128)	catalog_node_name - Catalog node network name
SERVER_PLATFORM	INTEGER	server_platform - Server operating system
DB_LOCATION	INTEGER	db_location - Database location
DB_CONN_TIME	TIMESTAMP	db_conn_time - Database activation timestamp
LAST_RESET	TIMESTAMP	last_reset - Last reset timestamp
LAST_BACKUP	TIMESTAMP	last_backup - Last backup timestamp
CONNECTIONS_TOP	BIGINT	connections_top - Maximum number of concurrent connections
TOTAL_CONS	BIGINT	total_cons - Connects since database activation
TOTAL_SEC_CONS	BIGINT	total_sec_cons - Secondary connections
APPLS_CUR_CONS	BIGINT	appls_cur_cons - Applications connected currently
APPLS_IN_DB2	BIGINT	appls_in_db2 - Applications executing in the database currently
NUM_ASSOC_AGENTS	BIGINT	num_assoc_agents - Number of associated agents
AGENTS_TOP	BIGINT	agents_top - Number of agents created
COORD_AGENTS_TOP	BIGINT	coord_agents_top - Maximum number of coordinating agents
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_LIST_IN_USE	BIGINT	lock_list_in_use - Total lock list memory in use
DEADLOCKS	BIGINT	deadlocks - Deadlocks detected
LOCK_ESCALS	BIGINT	lock_escals - Number of lock escalations
X_LOCK_ESCALS	BIGINT	x_lock_escals - Exclusive lock escalations
LOCKS_WAITING	BIGINT	locks_waiting - Current agents waiting on locks
LOCK_TIMEOUTS	BIGINT	lock_timeouts - Number of lock timeouts
NUM_INDOUBT_TRANS	BIGINT	num_indoubt_trans - Number of indoubt transactions
SORT_HEAP_ALLOCATED	BIGINT	sort_heap_allocated - Total sort heap allocated
SORT_SHRHEAP_ALLOCATED	BIGINT	sort_shrheap_allocated - Sort share heap currently allocated
SORT_SHRHEAP_TOP	BIGINT	sort_shrheap_top - Sort share heap high water mark
TOTAL_SORTS	BIGINT	total_sorts - Total sorts
TOTAL_SORT_TIME	BIGINT	total_sort_time - Total sort time
SORT_OVERFLOWS	BIGINT	sort_overflows - Sort overflows
ACTIVE_SORTS	BIGINT	active_sorts - Active sorts
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_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_ASYNC_DATA_READS	BIGINT	pool_async_data_reads - Buffer pool asynchronous data reads
POOL_DATA_WRITES	BIGINT	pool_data_writes - Buffer pool data writes
POOL_ASYNC_DATA_WRITES	BIGINT	pool_async_data_writes - Buffer pool asynchronous 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_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_INDEX_WRITES	BIGINT	pool_index_writes - Buffer pool index writes
POOL_ASYNC_INDEX_READS	BIGINT	pool_async_index_reads - Buffer pool asynchronous index reads
POOL_ASYNC_INDEX_WRITES	BIGINT	pool_async_index_writes - Buffer pool asynchronous index 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
POOL_ASYNC_READ_TIME	BIGINT	pool_async_read_time - Buffer pool asynchronous read time
POOL_ASYNC_WRITE_TIME	BIGINT	pool_async_write_time - Buffer pool asynchronous write time
POOL_ASYNC_DATA_ READ_REQS	BIGINT	pool_async_data_read_reqs - Buffer pool asynchronous read requests
POOL_ASYNC_INDEX_ READ_REQS	BIGINT	pool_async_index_read_reqs - Buffer pool asynchronous index read requests
POOL_NO_VICTIM_BUFFER	BIGINT	pool_no_victim_buffer - Buffer pool no victim buffers
POOL_LSN_GAP_CLNS	BIGINT	pool_lsn_gap_clns - Buffer pool log space cleaners triggered
POOL_DRTY_PG_STEAL_CLNS	BIGINT	pool_drty_pg_steal_clns - Buffer pool victim page cleaners triggered
POOL_DRTY_PG_THRSH_CLNS	BIGINT	pool_drty_pg_thrsh_clns - Buffer pool threshold cleaners triggered
PREFETCH_WAIT_TIME	BIGINT	prefetch_wait_time - Time waited for prefetch
UNREAD_PREFETCH_PAGES	BIGINT	unread_prefetch_pages - Unread prefetch pages
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
FILES_CLOSED	BIGINT	files_closed - Database files closed
POOL_DATA_TO_ESTORE	BIGINT	The pool_data_to_estore ESTORE monitor element is discontinued. A NULL value is returned for the discontinued monitor element.
POOL_INDEX_TO_ESTORE	BIGINT	The pool_index_to_estore ESTORE monitor element is discontinued. A NULL value is returned for the discontinued monitor element.
POOL_INDEX_FROM_ESTORE	BIGINT	The pool_index_from_estore ESTORE monitor element is discontinued. A NULL value is returned for the discontinued monitor element.
POOL_DATA_FROM_ESTORE	BIGINT	The pool_data_from_estore ESTORE monitor element is discontinued. A NULL value is returned for the discontinued monitor element.
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)*
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
UID_SQL_STMTS	BIGINT	uid_sql_stmts - UPDATE/INSERT/DELETE SQL statements executed
DDL_SQL_STMTS	BIGINT	ddl_sql_stmts - Data definition language (DDL) SQL statements
INT_AUTO_REBINDS	BIGINT	int_auto_rebinds - Internal automatic rebinds
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
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
ROWS_READ	BIGINT	rows_read - Rows read
BINDS_PRECOMPILES	BIGINT	binds_precompiles - Binds/precompiles attempted
TOTAL_LOG_AVAILABLE	BIGINT	total_log_available - Total log available
TOTAL_LOG_USED	BIGINT	total_log_used - Total log space used
SEC_LOG_USED_TOP	BIGINT	sec_log_used_top - Maximum secondary log space used
TOT_LOG_USED_TOP	BIGINT	tot_log_used_top - Maximum total log space used
SEC_LOGS_ALLOCATED	BIGINT	sec_logs_allocated - Secondary logs allocated currently
LOG_READS	BIGINT	log_reads - Number of log pages read
LOG_READ_TIME_S	BIGINT	log_read_time - Log read time (in seconds)†
LOG_READ_TIME_NS	BIGINT	log_read_time - Log read time (fractional, in nanoseconds)†
LOG_WRITES	BIGINT	log_writes - Number of log pages written
LOG_WRITE_TIME_S	BIGINT	log_write_time - Log write time (in seconds)†
LOG_WRITE_TIME_NS	BIGINT	log_write_time - Log write time (fractional, in nanoseconds)†
NUM_LOG_WRITE_IO	BIGINT	num_log_write_io - Number of log writes
NUM_LOG_READ_IO	BIGINT	num_log_read_io - Number of log reads
NUM_LOG_PART_PAGE_IO	BIGINT	num_log_part_page_io - Number of partial log page writes
NUM_LOG_BUFFER_FULL	BIGINT	num_log_buffer_full - Number of full log buffers
NUM_LOG_DATA_FOUND_ IN_BUFFER	BIGINT	num_log_data_found_in_buffer - Number of log data found in buffer
APPL_ID_OLDEST_XACT	BIGINT	appl_id_oldest_xact - Application with oldest transaction
LOG_TO_REDO_FOR_RECOVERY	BIGINT	log_to_redo_for_recovery - Amount of log to be redone for recovery
LOG_HELD_BY_DIRTY_PAGES	BIGINT	log_held_by_dirty_pages - Amount of log space accounted for by dirty pages
PKG_CACHE_LOOKUPS	BIGINT	pkg_cache_lookups - Package cache lookups
PKG_CACHE_INSERTS	BIGINT	pkg_cache_inserts - Package cache inserts
PKG_CACHE_NUM_ OVERFLOWS	BIGINT	pkg_cache_num_overflows - Package cache overflows
PKG_CACHE_SIZE_TOP	BIGINT	pkg_cache_size_top - Package cache high water mark
APPL_SECTION_LOOKUPS	BIGINT	appl_section_lookups - Section lookups
APPL_SECTION_INSERTS	BIGINT	appl_section_inserts - Section 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
CAT_CACHE_SIZE_TOP	BIGINT	cat_cache_size_top - Catalog cache high water mark
PRIV_WORKSPACE_SIZE_TOP	BIGINT	priv_workspace_size_top - Maximum private workspace size
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
SHR_WORKSPACE_SIZE_TOP	BIGINT	shr_workspace_size_top - Maximum shared 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
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
NUM_DB_STORAGE_PATHS	BIGINT	num_db_storage_paths - Number of automatic storage paths
DBPARTITIONNUM	SMALLINT	node_number - Node number
* 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. †To calculate the total elapsed time for this monitor element, 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,000 + `monitor-element-name`_MS) ÷ 1,000,000,000. For example, (LOG_READ_TIME_S × 1,000,000,000 + LOG_READ_TIME_MS) ÷ 1,000,000,000.