DB2 Version 9.7 for Linux, UNIX, and Windows

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

Note: This table function has been deprecated and replaced by SNAPTBSP_PART administrative view and SNAP_GET_TBSP_PART_V97 table function - Retrieve tablespace_nodeinfo logical data group snapshot information.

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

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:

SELECT privilege on the SNAPTBSP_PART administrative view
CONTROL privilege on the SNAPTBSP_PART administrative view
DATAACCESS authority

In addition, one of the following privileges or authorities is also required:

EXECUTE privilege on the SNAP_GET_TBSP_PART_V91 table function
DATAACCESS authority

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

SYSMON
SYSCTRL
SYSMAINT
SYSADM

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 example is a sample 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_V91 table function

The SNAP_GET_TBSP_PART_V91 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_V91, SNAP_GET_TBSP_QUIESCER, SNAP_GET_TBSP_RANGE, SNAP_GET_CONTAINER_V91 table functions, the SNAP_GET_TBSP_PART_V91 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


>>-SNAP_GET_TBSP_PART_V91--(--dbname--+------------------+--)--><
                                      '-, dbpartitionnum-'

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.
dbpartitionnum: An optional 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. If dbname is not set to NULL and dbpartitionnum is set to NULL, -1 is set implicitly for dbpartitionnum. If this input option is not used, that is, only dbname is provided, data is returned from all active database partitions. An active database partition is a partition where the database is available for connection and use by applications.

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

Authorization

One of the following authorizations is required:

EXECUTE privilege on the SNAP_GET_TBSP_PART_V91 table function
DATAACCESS authority

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

SYSMON
SYSCTRL
SYSMAINT
SYSADM

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_V91(CAST(NULL AS VARCHAR(128)),-1)) AS T

The following example is a 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 SNAPTBSP_PART administrative view and the SNAP_GET_TBSP_PART_V91 table function
Column name	Data type	Description or corresponding monitor element
SNAPSHOT_TIMESTAMP	TIMESTAMP	snapshot_timestamp - Snapshot timestamp monitor element
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 (10)	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
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	The database partition from which the data was retrieved for this row.