The SNAPTBSP_RANGE administrative view and the SNAP_GET_TBSP_RANGE table function return information from a range snapshot.
This administrative view allows you to retrieve range snapshot information for the currently connected database.
Used with the SNAPTBSP, SNAPTBSP_PART, SNAPTBSP_QUIESCER and SNAPCONTAINER administrative views, the SNAPTBSP_RANGE administrative view provides 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.
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 SYSIBMADM.SNAPTBSP_RANGE
ORDER BY DBPARTITIONNUM
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.
... RANGE_OFFSET RANGE_MAX_PAGE RANGE_MAX_EXTENT ...
... ------------...- -------------------- -------------------- ...
... 0 11515 2878 ...
... 0 479 14 ...
... 0 251 62 ...
... 0 479 14 ...
... 0 479 14 ...
... RANGE_START_STRIPE RANGE_END_STRIPE RANGE_ADJUSTMENT ...
... -------------------- -------------------- -------------------- ...
... 0 2878 0 ...
... 0 14 0 ...
... 0 62 0 ...
... 0 14 0 ...
... 0 14 0 ...
... RANGE_NUM_CONTAINER RANGE_CONTAINER_ID DBPARTITIONNUM
... -------------------- -------------------- --------------
... 1 0 0
... 1 0 0
... 1 0 0
... 1 0 1
... 1 0 2
The SNAP_GET_TBSP_RANGE table function returns the same information as the SNAPTBSP_RANGE 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 with the SNAP_GET_TBSP_V91, SNAP_GET_TBSP_PART_V91, SNAP_GET_TBSP_QUIESCER and SNAP_GET_CONTAINER_V91 table functions, the SNAP_GET_TBSP_RANGE table function provides 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.
>>-SNAP_GET_TBSP_RANGE--(--dbname--+------------------+--)----->< '-, dbpartitionnum-'
The schema is SYSPROC.
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_RANGE table function takes a snapshot for the currently connected database and database partition number.
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(SNAP_GET_TBSP_RANGE('',-1)) AS T WHERE TBSP_ID = 2
TBSP_ID TBSP_NAME RANGE_NUMBER ...
-------...- --------------- ------------...- ...
2 USERSPACE1 0 ...
1 record(s) selected.
... RANGE_STRIPE_SET_NUMBER RANGE_OFFSET RANGE_MAX_PAGE ...
... ----------------------- ------------...- --------------...---- ...
... 0 0 3967 ...
... RANGE_MAX_EXTENT RANGE_START_STRIPE RANGE_END_STRIPE ...
... -------------------- -------------------- -------------------- ...
... 123 0 123 ...
... RANGE_ADJUSTMENT RANGE_NUM_CONTAINER RANGE_CONTAINER_ID
... -------------------- -------------------- --------------------
... 0 1 0
Column name | Data type | Description or corresponding monitor element |
---|---|---|
SNAPSHOT_TIMESTAMP | TIMESTAMP | snapshot_timestamp - Snapshot timestamp monitor element |
TBSP_ID | BIGINT | tablespace_id - Table space identification |
TBSP_NAME | VARCHAR(128) | tablespace_name - Table space name |
RANGE_NUMBER | BIGINT | range_number - Range number |
RANGE_STRIPE_SET_NUMBER | BIGINT | range_stripe_set_number - Stripe set number |
RANGE_OFFSET | BIGINT | range_offset - Range offset |
RANGE_MAX_PAGE | BIGINT | range_max_page_number - Maximum page in range |
RANGE_MAX_EXTENT | BIGINT | range_max_extent - Maximum extent in range |
RANGE_START_STRIPE | BIGINT | range_start_stripe - Start stripe |
RANGE_END_STRIPE | BIGINT | range_end_stripe - End stripe |
RANGE_ADJUSTMENT | BIGINT | range_adjustment - Range adjustment |
RANGE_NUM_CONTAINER | BIGINT | range_num_containers - Number of containers in range |
RANGE_CONTAINER_ID | BIGINT | range_container_id - Range container |
DBPARTITIONNUM | SMALLINT | The database partition from which the data was retrieved for this row. |