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

Read syntax diagramSkip visual syntax diagramSNAP_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