SNAPTBSP_QUIESCER administrative view and SNAP_GET_TBSP_QUIESCER table function - Retrieve quiescer table space snapshot information
The SNAPTBSP_QUIESCER administrative view and the SNAP_GET_TBSP_QUIESCER table function return information about quiescers from a table space snapshot.
SNAPTBSP_QUIESCER administrative view
This administrative view allows you to retrieve quiescer table space snapshot information for the currently connected database.
Used with the SNAPTBSP_RANGE administrative view and the MON_GET_TABLESPACE and MON_GET_CONTAINER table functions, the SNAPTBSP_QUIESCER 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_QUIESCER administrative view
- CONTROL privilege on the SNAPTBSP_QUIESCER administrative view
- DATAACCESS authority
- DBADM authority
- SQLADM authority
- ACCESSCTRL authority
- SECADM authority
- EXECUTE privilege on the SNAP_GET_TBSP_QUIESCER 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 SUBSTR(TBSP_NAME, 1, 10) AS TBSP_NAME, QUIESCER_TS_ID,
QUIESCER_OBJ_ID, QUIESCER_AUTH_ID, QUIESCER_AGENT_ID,
QUIESCER_STATE, DBPARTITIONNUM
FROM SYSIBMADM.SNAPTBSP_QUIESCER ORDER BY DBPARTITIONNUM
TBSP_NAME QUIESCER_TS_ID QUIESCER_OBJ_ID QUIESCER_AUTH_ID ..
---------- --------------...- ---------------...- ----------------...- ..
USERSPACE1 2 5 SWALKTY ..
USERSPACE1 2 5 SWALKTY ..
2 record(s) selected.
... QUIESCER_AGENT_ID QUIESCER_STATE DBPARTITIONNUM
... -------------------- -------------- --------------
... 0 EXCLUSIVE 0
... 65983 EXCLUSIVE 1
Example: Determine the partitioned table names
SELECT SUBSTR(TBSP_NAME, 1, 10) AS TBSP_NAME,
CASE WHEN QUIESCER_TS_ID = 65530
THEN QUIESCER_TS_ID - 65536
ELSE QUIESCER_TS_ID END as tbspaceid,
CASE WHEN QUIESCER_TS_ID = 65530
THEN QUIESCER_OBJ_ID - 65536
ELSE QUIESCER_OBJ_ID END as tableid
FROM SYSIBMADM.SNAPTBSP_QUIESCER
ORDER BY DBPARTITIONNUM
TBSP_NAME TBSPACEID TABLEID
------------- ------------ --------
TABDATA -6 -32768
DATAMART -6 -32765
SMALL 5 17
3 record(s) selected.
SELECT CHAR(tabschema, 10)tabschema, CHAR(tabname,15)tabname
FROM SYSCAT.TABLES
WHERE tbspaceid = -6 AND tableid in (-32768,-32765)
The
following is an example of output from this query. TABSCHEMA TABNAME
------------ --------
SAMP ORDERS_RP
SAMP ORDERS_DMART
2 record(s) selected.
SELECT CHAR(tabschema, 10)tabschema, CHAR(tabname,15)tabname
FROM SYSCAT.TABLES
WHERE tbspaceid = 5 AND tableid = 17
The following is
an example of output from this query.TABSCHEMA TABNAME
------------ --------
SAMP NATION
1 record(s) selected.
SNAP_GET_TBSP_QUIESCER table function
The SNAP_GET_TBSP_QUIESCER table function returns the same information as the SNAPTBSP_QUIESCER 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_RANGE, SNAP_GET_CONTAINER table functions, the SNAP_GET_TBSP_QUIESCER 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_QUIESCER table function takes a snapshot for the currently connected database and database member number.
Authorization
- EXECUTE privilege on the SNAP_GET_TBSP_QUIESCER 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.
Example
SELECT SUBSTR(TBSP_NAME, 1, 10) AS TBSP_NAME, QUIESCER_TS_ID,
QUIESCER_OBJ_ID, QUIESCER_AUTH_ID, QUIESCER_AGENT_ID,
QUIESCER_STATE, DBPARTITIONNUM
FROM TABLE( SYSPROC.SNAP_GET_TBSP_QUIESCER( '', 1)) AS T
TBSP_NAME QUIESCER_TS_ID QUIESCER_OBJ_ID QUIESCER_AUTH_ID ...
---------- --------------...- ---------------...- ----------------...- ...
USERSPACE1 2 5 SWALKTY ...
1 record(s) selected.
... QUIESCER_AGENT_ID QUIESCER_STATE DBPARTITIONNUM
... -------------------- -------------- --------------
... 65983 EXCLUSIVE 1
Information returned
Column name | Data type | Description or corresponding monitor element |
---|---|---|
SNAPSHOT_TIMESTAMP | TIMESTAMP | The date and time that the snapshot was taken. |
TBSP_NAME | VARCHAR(128) | tablespace_name - Table space name |
QUIESCER_TS_ID | BIGINT | quiescer_ts_id - Quiescer table space identification |
QUIESCER_OBJ_ID | BIGINT | quiescer_obj_id - Quiescer object identification |
QUIESCER_AUTH_ID | VARCHAR(128) | quiescer_auth_id - Quiescer user authorization identification |
QUIESCER_AGENT_ID | BIGINT | quiescer_agent_id - Quiescer agent identification |
QUIESCER_STATE | VARCHAR(14) | quiescer_state - Quiescer state . This interface
returns a text identifier based on defines in sqlutil.h and
is one of:
|
DBPARTITIONNUM | SMALLINT | dbpartitionnum - Database partition number monitor element |
MEMBER | SMALLINT | member - Database member monitor element |