MON_GET_TABLESPACE_RANGE table function - Get information about table space ranges

The MON_GET_TABLESPACE_RANGE table function returns information about table space ranges. A single row is returned for each member.

Authorization

One of the following authorizations is required:
  • EXECUTE privilege on the routine
  • DATAACCESS authority
  • SQLADM authority
  • DBADM authority

Default PUBLIC privilege

None

Syntax

Read syntax diagramSkip visual syntax diagramMON_GET_TABLESPACE_RANGE(dbpartitionnum )

The schema is SYSPROC.

Routine parameters

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. An active database partition is a partition where the database is available for connection and use by applications. If the null value is specified, -1 is set implicitly.

Information returned

Table 1. Information returned by MON_GET_TABLESPACE_RANGE
Column name Data type Description
TBSP_ID BIGINT tablespace_id - Table space identification monitor element
TBSP_NAME VARCHAR(128) tablespace_name - Table space name monitor element
RANGE_NUMBER BIGINT range_number - Range Number monitor element
RANGE_STRIPE_SET_NUMBER BIGINT range_stripe_set_number - Stripe set number monitor element
RANGE_OFFSET BIGINT range_offset - Range offset monitor element
RANGE_MAX_PAGE BIGINT range_max_page_number - Maximum page in range monitor element
RANGE_MAX_EXTENT BIGINT range_max_extent - Maximum extent in range monitor element
RANGE_START_STRIPE BIGINT range_start_stripe - Start stripe monitor element
RANGE_END_STRIPE BIGINT range_end_stripe - End stripe monitor element
RANGE_ADJUSTMENT BIGINT range_adjustment - Range adjustment monitor element
RANGE_NUM_CONTAINER BIGINT range_num_container - Number of containers in range monitor element
RANGE_CONTAINER_ID BIGINT range_container_id - Range container monitor element
DBPARTITIONNUM SMALLINT dbpartitionnum - Database partition number monitor element

Usage notes

Use the MON_GET_TABLESPACE_RANGE table function in place of the SNAP_GET_TBSP_RANGE table function and SNAPTBSP_RANGE view, which have been deprecated.

Examples

  • Select information about table space ranges for all database partitions for the currently connected database. 
    SELECT TBSP_ID, SUBSTR(TBSP_NAME, 1, 15) AS TBSP_NAME, RANGE_NUMBER, 
   RANGE_STRIPE_SET_NUMBER, RANGE_OFFSET, RANGE_MAX_PAGE, 
   RANGE_MAX_EXTENT, RANGE_START_STRIPE, RANGE_END_STRIPE, 
   RANGE_ADJUSTMENT, RANGE_NUM_CONTAINER, RANGE_CONTAINER_ID, 
   DBPARTITIONNUM FROM TABLE(MON_GET_TABLESPACE_RANGE(-2))
   ORDER BY DBPARTITIONNUM
    The following is sample output from this query:
    TBSP_ID  TBSP_NAME       RANGE_NUMBER  RANGE_STRIPE_SET_NUMBER ...
-------- --------------- ------------- ----------------------- ...
       0 SYSCATSPACE                 0                       0 ...
       2 USERSPACE1                  0                       0 ...
       3 SYSTOOLSPACE                0                       0 ...
       2 USERSPACE1                  0                       0 ...
       2 USERSPACE1                  0                       0 ...

5 record(s) selected.
    Output from this query (continued):
    ... RANGE_OFFSET  RANGE_MAX_PAGE       RANGE_MAX_EXTENT     ... 
... ------------- -------------------- -------------------- ... 
...             0                11515                 2878 ... 
...             0                  479                   14 ... 
...             0                  251                   62 ... 
...             0                  479                   14 ... 
...             0                  479                   14 ...
    Output from this query (continued):
    ... RANGE_START_STRIPE   RANGE_END_STRIPE     RANGE_ADJUSTMENT     ...
... -------------------- -------------------- -------------------- ...
...                    0                 2878                    0 ...
...                    0                   14                    0 ...
...                    0                   62                    0 ...
...                    0                   14                    0 ...
...                    0                   14                    0 ...
    Output from this query (continued):
    ... RANGE_NUM_CONTAINER  RANGE_CONTAINER_ID   DBPARTITIONNUM  
... -------------------- -------------------- --------------  
...                    1                    0              0  
...                    1                    0              0  
...                    1                    0              0  
...                    1                    0              1  
...                    1                    0              2
  • Select information about the table space range for the table space whose TBSP_ID value is 2 on the current database partition. 
    SELECT TBSP_ID, SUBSTR(TBSP_NAME, 1, 15) AS TBSP_NAME, RANGE_NUMBER, 
   RANGE_STRIPE_SET_NUMBER, RANGE_OFFSET, RANGE_MAX_PAGE, RANGE_MAX_EXTENT, 
   RANGE_START_STRIPE, RANGE_END_STRIPE, RANGE_ADJUSTMENT, 
   RANGE_NUM_CONTAINER, RANGE_CONTAINER_ID 
   FROM TABLE(MON_GET_TABLESPACE_RANGE(-1)) AS T WHERE TBSP_ID = 2
    The following is sample output from this query: 
    TBSP_ID  TBSP_NAME       RANGE_NUMBER  ...      
-------- --------------- ------------- ...      
       2 USERSPACE1                  0 ...      

1 record(s) selected.
    Output from this query (continued):
    ... RANGE_STRIPE_SET_NUMBER RANGE_OFFSET  RANGE_MAX_PAGE     ...
... ----------------------- ------------- ------------------ ...
...                       0             0               3967 ...
    Output from this query (continued):
    ... RANGE_MAX_EXTENT     RANGE_START_STRIPE   RANGE_END_STRIPE     ...
... -------------------- -------------------- -------------------- ...
...                  123                    0                  123 ...
    Output from this query (continued):
    ... RANGE_ADJUSTMENT     RANGE_NUM_CONTAINER  RANGE_CONTAINER_ID    
... -------------------- -------------------- -------------------- 
...                    0                    1                    0