DB2 Version 9.7 for Linux, UNIX, and Windows

SNAP_GET_TBSP table function

Note: This table function has been deprecated and replaced by the SNAPTBSP administrative view and SNAP_GET_TBSP_V91 table function - Retrieve table space logical data group snapshot information


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

The schema is SYSPROC.

The SNAP_GET_TBSP table function returns snapshot information from the table space logical data group.

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. 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:

EXECUTE privilege on the SNAP_GET_TBSP 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_TBSP table function
Column name	Data type	Description or corresponding monitor element
SNAPSHOT_TIMESTAMP	TIMESTAMP	snapshot_timestamp - Snapshot timestamp
TBSP_NAME	VARCHAR(128)	tablespace_name - Table space name
TBSP_ID	BIGINT	tablespace_id - Table space identification
TBSP_TYPE	SMALLINT	tablespace_type - Table space type
TBSP_CONTENT_TYPE	SMALLINT	tablespace_content_type - Table space content type
TBSP_PAGE_SIZE	BIGINT	tablespace_page_size - Table space page size
TBSP_EXTENT_SIZE	BIGINT	tablespace_extent_size - Table space extent size
TBSP_PREFETCH_SIZE	BIGINT	tablespace_prefetch_size - Table space prefetch size
TBSP_CUR_POOL_ID	BIGINT	tablespace_cur_pool_id - Buffer pool currently being used
TBSP_NEXT_POOL_ID	BIGINT	tablespace_next_pool_id - Buffer pool that will be used at next startup
FS_CACHING¹	SMALLINT	fs_caching - File system caching
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_ASYNC_INDEX_READS	BIGINT	pool_async_index_reads - Buffer pool asynchronous index reads
POOL_INDEX_WRITES	BIGINT	pool_index_writes - Buffer pool index writes
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
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
UNREAD_PREFETCH_PAGES	BIGINT	unread_prefetch_pages - Unread prefetch pages
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.
TBSP_REBALANCER_MODE	BIGINT	tablespace_rebalancer_mode - Rebalancer mode
TBSP_USING_AUTO_STORAGE	SMALLINT	tablespace_using_auto_storage - Table space enabled for automatic storage
TBSP_AUTO_RESIZE_ENABLED	SMALLINT	tablespace_auto_resize_enabled - Table space automatic resizing enabled
¹ If FS_CACHING is 0, file system caching is enabled, and if FS_CACHING is 1, file system caching is disabled.