SNAPTBSP_PART administrative view and SNAP_GET_TBSP_PART table function - Retrieve tablespace_nodeinfo logical data group snapshot information

The SNAPTBSP_PART administrative view and the SNAP_GET_TBSP_PART table function return snapshot information from the tablespace_nodeinfo logical data group.

Important: The SNAPTBSP_PART administrative view and the SNAP_GET_TBSP_PART table function are deprecated and have been replaced by the MON_GET_TABLESPACE table function - Get table space metrics.

Depending on if you are using the administrative view or the table function, refer to one of the following sections:

SNAPTBSP_PART administrative view
SNAP_GET_TBSP_PART table function

SNAPTBSP_PART administrative view

This administrative view allows you to retrieve tablespace_nodeinfo logical data group snapshot information for the currently connected database.

Used in conjunction with the SNAPTBSP, SNAPTBSP_QUIESCER, SNAPTBSP_RANGE, SNAPCONTAINER administrative views, the SNAPTBSP_PART administrative view returns information equivalent to the GET SNAPSHOT FOR TABLESPACES ON database-alias CLP command.

The schema is SYSIBMADM.

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

Authorization

One of the following authorizations is required to use the view:

SELECT privilege on the SNAPTBSP_RANGE administrative view
CONTROL privilege on the SNAPTBSP_RANGE administrative view
DATAACCESS authority
DBADM authority
SQLADM authority

One of the following authorizations is required to use the table function:

EXECUTE privilege on the SNAP_GET_TBSP_PART table function
DATAACCESS authority
DBADM authority
SQLADM authority

In addition, to access snapshot monitor data, one of the following authorities is also required:

SYSMON
SYSCTRL
SYSMAINT
SYSADM

Default PUBLIC privilege

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

Example

Retrieve a list of table spaces and their state for all database partitions of the currently connected database.

SELECT SUBSTR(TBSP_NAME,1,30) AS TBSP_NAME, TBSP_ID, 
   SUBSTR(TBSP_STATE,1,30) AS TBSP_STATE, DBPARTITIONNUM 
   FROM SYSIBMADM.SNAPTBSP_PART

The following is an example of output from this query.

TBSP_NAME       TBSP_ID     TBSP_STATE     DBPARTITIONNUM
-----------...- -------...- ----------...- --------------
SYSCATSPACE               0 NORMAL                      0
TEMPSPACE1                1 NORMAL                      0
USERSPACE1                2 NORMAL                      0
TEMPSPACE1                1 NORMAL                      1
USERSPACE1                2 NORMAL                      1

   5 record(s) selected.

SNAP_GET_TBSP_PART table function

The SNAP_GET_TBSP_PART table function returns the same information as the SNAPTBSP_PART administrative view, but allows you to retrieve the information for a specific database on a specific database partition, aggregate of all database partitions or all database partitions.

Used in conjunction with the SNAP_GET_TBSP, SNAP_GET_TBSP_QUIESCER, SNAP_GET_TBSP_RANGE, SNAP_GET_CONTAINER table functions, the SNAP_GET_TBSP_PART table function returns information equivalent to the GET SNAPSHOT FOR TABLESPACES ON database-alias CLP command.

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

Syntax

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 member number. Specify -1 for the current member, or -2 for an aggregate of all active 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 members where the database is active.

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

Authorization

One of the following authorizations is required:

EXECUTE privilege on the SNAP_GET_TBSP_PART table function.
DATAACCESS authority

To access snapshot monitor data, one of the following authorities is also 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 partition 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(CAST(NULL AS VARCHAR(128)),-1)) AS T

The following is an example of 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 SNAPTBSP_PART administrative view and the SNAP_GET_TBSP_PART 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
DBPARTITIONNUM	SMALLINT	dbpartitionnum - Database partition number monitor element
TBSP_PATHS_DROPPED	SMALLINT	Indicates that the table space resides on one or more storage paths that have been dropped (`0` - No, `1` - Yes)