DB2 10.5 for Linux, UNIX, and Windows

SNAP_GET_TBSP_PART_V97 table function - Retrieve tablespace_nodeinfo logical data group snapshot information

The SNAP_GET_TBSP_PART_V97 table function returns tablespace_nodeinfo logical data group snapshot information for a specific database on a specific database member, aggregate of all database members or all database members.

Important: The SNAP_GET_TBSP_PART_V97 table function is deprecated and has been replaced by the MON_GET_TABLESPACE table function - Get table space metrics.

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

Syntax


>>-SNAP_GET_TBSP_PART_V97--(--dbname--+----------+--)----------><
                                      '-, member-'

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 NULL or empty string to take the snapshot from the currently connected database.
member: An optional input argument of type INTEGER that specifies a valid database member number. Specify -1 for the current database member, or -2 for an aggregate of all active database members. If dbname is not set to NULL and member is set to NULL, -1 is set implicitly for member. If this input option is not used, that is, only dbname is provided, data is returned from all active database members. An active database member is a member where the database is available for connection and use by applications.

If both dbname and member 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_TBSP_PART_V97 table function takes a snapshot for the currently connected database and database member number.

Authorization

EXECUTE privilege on the SNAP_GET_TBSP_PART_V97 table function.
DATAACCESS authority

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

SYSMON authority
SYSCTRL authority
SYSMAINT authority
SYSADM authority

Default PUBLIC privilege

In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the function is automatically created.

Example

Retrieve a list of table spaces and their state for the connected database member of the connected database.

SELECT SUBSTR(TBSP_NAME,1,30) AS TBSP_NAME, TBSP_ID, SUBSTR(TBSP_STATE,1,30) AS TBSP_STATE FROM TABLE(SNAP_GET_TBSP_PART_V97(CAST(NULL AS VARCHAR(128)),-1)) AS T

The following output is an example of sample output from this query.

TBSP_NAME                      TBSP_ID              TBSP_STATE
------------------------------ -------------------- ------------...-
SYSCATSPACE                                       0 NORMAL
TEMPSPACE1                                        1 NORMAL
USERSPACE1                                        2 NORMAL
SYSTOOLSPACE                                      3 NORMAL
SYSTOOLSTMPSPACE                                  4 NORMAL

  5 record(s) selected.

Information returned

Table 1. Information returned by the SNAP_GET_TBSP_PART_V97 table function
Column name	Data type	Description or corresponding monitor element
SNAPSHOT_TIMESTAMP	TIMESTAMP	The date and time that the snapshot was taken.
TBSP_NAME	VARCHAR (128)	tablespace_name - Table space name
TBSP_ID	BIGINT	tablespace_id - Table space identification
TBSP_STATE	VARCHAR (256)	tablespace_state - Table space state . This interface returns a text identifier based on defines in sqlutil.h and is combination of the following separated by a '+' sign: `BACKUP_IN_PROGRESS` `BACKUP_PENDING` `DELETE_PENDING` `DISABLE_PENDING` `DROP_PENDING` `LOAD_IN_PROGRESS` `LOAD_PENDING` `NORMAL` `OFFLINE` `PSTAT_CREATION` `PSTAT_DELETION` `QUIESCED_EXCLUSIVE` `QUIESCED_SHARE` `QUIESCED_UPDATE` `REBAL_IN_PROGRESS` `REORG_IN_PROGRESS` `RESTORE_IN_PROGRESS` `RESTORE_PENDING` `ROLLFORWARD_IN_PROGRESS` `ROLLFORWARD_PENDING` `STORDEF_ALLOWED` `STORDEF_CHANGED` `STORDEF_FINAL_VERSION` `STORDEF_PENDING` `SUSPEND_WRITE`
TBSP_PREFETCH_SIZE	BIGINT	tablespace_prefetch_size - Table space prefetch size
TBSP_NUM_QUIESCERS	BIGINT	tablespace_num_quiescers - Number of quiescers
TBSP_STATE_CHANGE_OBJECT_ID	BIGINT	tablespace_state_change_object_id - State change object identification
TBSP_STATE_CHANGE_TBSP_ID	BIGINT	tablespace_state_change_ts_id - State change table space identification
TBSP_MIN_RECOVERY_TIME	TIMESTAMP	tablespace_min_recovery_time - Minimum recovery time for rollforward
TBSP_TOTAL_PAGES	BIGINT	tablespace_total_pages - Total pages in table space
TBSP_USABLE_PAGES	BIGINT	tablespace_usable_pages - Usable pages in table space
TBSP_USED_PAGES	BIGINT	tablespace_used_pages - Used pages in table space
TBSP_FREE_PAGES	BIGINT	tablespace_free_pages - Free pages in table space
TBSP_PENDING_FREE_PAGES	BIGINT	tablespace_pending_free_pages - Pending free pages in table space
TBSP_PAGE_TOP	BIGINT	tablespace_page_top - Table space high water mark
REBALANCER_MODE	VARCHAR (30)	tablespace_rebalancer_mode - Rebalancer mode . This interface returns a text identifier based on defines in sqlmon.h, and is one of: `FWD_REBAL` `NO_REBAL` `REV_REBAL` `FWD_REBAL_OF_2PASS` `REV_REBAL_OF_2PASS`
REBALANCER_EXTENTS_REMAINING	BIGINT	tablespace_rebalancer_extents_remaining - Total number of extents to be processed by the rebalancer
REBALANCER_EXTENTS_PROCESSED	BIGINT	tablespace_rebalancer_extents_processed - Number of extents the rebalancer has processed
REBALANCER_PRIORITY	BIGINT	tablespace_rebalancer_priority - Current rebalancer priority
REBALANCER_START_TIME	TIMESTAMP	tablespace_rebalancer_start_time - Rebalancer start time
REBALANCER_RESTART_TIME	TIMESTAMP	tablespace_rebalancer_restart_time - Rebalancer restart time
REBALANCER_LAST_EXTENT_MOVED	BIGINT	tablespace_rebalancer_last_extent_moved - Last extent moved by the rebalancer
TBSP_NUM_RANGES	BIGINT	tablespace_num_ranges - Number of ranges in the table space map
TBSP_NUM_CONTAINERS	BIGINT	tablespace_num_containers - Number of containers in table space
TBSP_INITIAL_SIZE	BIGINT	tablespace_initial_size - Initial table space size
TBSP_CURRENT_SIZE	BIGINT	tablespace_current_size - Current table space size
TBSP_MAX_SIZE	BIGINT	tablespace_max_size - Maximum table space size
TBSP_INCREASE_SIZE	BIGINT	tablespace_increase_size - Increase size in bytes
TBSP_INCREASE_SIZE_PERCENT	SMALLINT	tablespace_increase_size_percent - Increase size by percent
TBSP_LAST_RESIZE_TIME	TIMESTAMP	tablespace_last_resize_time - Time of last successful resize
TBSP_LAST_RESIZE_FAILED	SMALLINT	tablespace_last_resize_failed - Last resize attempt failed
TBSP_PATHS_DROPPED	SMALLINT	Indicates that the table space resides on one or more storage paths that have been dropped (`0` - No, `1` - Yes)
DBPARTITIONNUM	SMALLINT	dbpartitionnum - Database partition number monitor element