MON_GET_TABLESPACE_RANGE table function - Get information about table space ranges

Authorization

Default PUBLIC privilege

None

Syntax

>>-MON_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

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