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_QUIESCER administrative view and the MON_GET_TABLESPACE and the MON_GET_CONTAINER table functions, 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.
In a non-restrictive database, SELECT privilege is granted to PUBLIC when the view is automatically created.
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 member, aggregate of all database members or all database members.
Used with the SNAP_GET_TBSP, SNAP_GET_TBSP_PART, SNAP_GET_TBSP_QUIESCER and SNAP_GET_CONTAINER 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--+----------+--)------------->< '-, member-'
The schema is SYSPROC.
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_RANGE table function takes a snapshot for the currently connected database and database member number.
In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the function is automatically created.
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 | The date and time that the snapshot was taken. |
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_container - Number of containers in range |
RANGE_CONTAINER_ID | BIGINT | range_container_id - Range container |
DBPARTITIONNUM | SMALLINT | dbpartitionnum - Database partition number monitor element |