DB2 Version 9.7 for Linux, UNIX, and Windows

SNAPSHOT_DATABASE table function

Returns information from a database snapshot.

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


>>-SNAPSHOT_DATABASE--(--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 will be 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_DATABASE table function
Column name	Data type	Description or corresponding monitor element
SNAPSHOT_TIMESTAMP	TIMESTAMP	snapshot_timestamp - Snapshot timestamp
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
TOTAL_LOG_USED	BIGINT	total_log_used - Total log space used
TOTAL_LOG_AVAILABLE	BIGINT	total_log_available - Total log available
ROWS_READ	BIGINT	rows_read - Rows read
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_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_INDEX_READS	BIGINT	pool_async_index_reads - Buffer pool asynchronous index reads
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.
POOL_ASYNC_DATA_READS	BIGINT	pool_async_data_reads - Buffer pool asynchronous data reads
POOL_ASYNC_DATA_WRITES	BIGINT	pool_async_data_writes - Buffer pool asynchronous data writes
POOL_ASYNC_INDEX_WRITES	BIGINT	pool_async_index_writes - Buffer pool asynchronous index writes
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
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
FILES_CLOSED	BIGINT	files_closed - Database files closed
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
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 - agents waiting on locks
SORT_HEAP_ALLOCATED	BIGINT	sort_heap_allocated - Total sort heap allocated
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
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
TOTAL_CONS	BIGINT	total_cons - Connects since database activation
APPLS_CUR_CONS	BIGINT	appls_cur_cons - Applications connected currently
APPLS_IN_DB2	BIGINT	appls_in_db2 - Applications executing in the database currently
SEC_LOGS_ALLOCATED	BIGINT	sec_logs_allocated - Secondary logs allocated currently
DB_STATUS	BIGINT	db_status - Status of database
LOCK_TIMEOUTS	BIGINT	lock_timeouts - Number of lock timeouts
CONNECTIONS_TOP	BIGINT	connections_top - Maximum number of concurrent connections
DB_HEAP_TOP	BIGINT	db_heap_top - Maximum database heap allocated
INT_ROWS_INSERTED	BIGINT	int_rows_inserted - Internal rows inserted
LOG_READS	BIGINT	log_reads - Number of log pages read
LOG_WRITES	BIGINT	log_writes - Number of log pages written
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
CAT_CACHE_HEAP_FULL	BIGINT	cat_cache_overflows - Catalog cache overflows
CATALOG_PARTITION	SMALLINT	catalog_node - Catalog node number
TOTAL_SEC_CONS	BIGINT	total_sec_cons - Secondary connections
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
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
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
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
DB_CONN_TIME	TIMESTAMP	db_conn_time - Database activation timestamp
SQLM_ELM_LAST_RESET	TIMESTAMP	last_reset - Last reset timestamp
SQLM_ELM_LAST_BACKUP	TIMESTAMP	last_backup - Last backup timestamp
APPL_CON_TIME	TIMESTAMP	appl_con_time - Connection request start timestamp
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)*
DB_LOCATION	INTEGER	db_location - Database location
SERVER_PLATFORM	INTEGER	server_platform - Server operating system
APPL_ID_OLDEST_XACT	BIGINT	appl_id_oldest_xact - Application with oldest transaction
CATALOG_PARTITION_NAME	VARCHAR(128)	catalog_node_name - Catalog node network name
INPUT_DB_ALIAS	VARCHAR(128)	input_db_alias - Input database alias
DB_NAME	VARCHAR(128)	db_name - Database name
DB_PATH	VARCHAR(1024)	db_path - Database path
* 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.