SNAPTBSP_RANGE administrative view and SNAP_GET_TBSP_RANGE table function - Retrieve range snapshot information
The SNAPTBSP_RANGE administrative view and the SNAP_GET_TBSP_RANGE table function return information from a range snapshot.
SNAPTBSP_RANGE administrative view
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.
Authorization
- SELECT privilege on the SNAPTBSP_RANGE administrative view
- CONTROL privilege on the SNAPTBSP_RANGE administrative view
- DATAACCESS authority
- DBADM authority
- SQLADM authority
- EXECUTE privilege on the SNAP_GET_TBSP_RANGE table function
- DATAACCESS authority
- DBADM authority
- SQLADM authority
- SYSMON
- SYSCTRL
- SYSMAINT
- SYSADM
Default PUBLIC privilege
In a non-restrictive database, SELECT privilege is granted to PUBLIC when the view is automatically created.
Example
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
SNAP_GET_TBSP_RANGE table function
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.
Syntax
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_RANGE table function takes a snapshot for the currently connected database and database member number.
Authorization
- EXECUTE privilege on the SNAP_GET_TBSP_RANGE table function
- DATAACCESS authority
- SYSMON
- SYSCTRL
- SYSMAINT
- SYSADM
Default PUBLIC privilege
In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the function is automatically created.
Examples
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
Information returned
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 |