The SNAPTBSP_QUIESCER administrative view and the SNAP_GET_TBSP_QUIESCER table function return information about quiescers from a table space snapshot.
This administrative view allows you to retrieve quiescer table space snapshot information for the currently connected database.
Used with the SNAPTBSP, SNAPTBSP_PART, SNAPTBSP_RANGE, SNAPCONTAINER administrative views, 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.
In a non-restrictive database, SELECT privilege is granted to PUBLIC when the view is automatically created.
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
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.
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.
>>-SNAP_GET_TBSP_QUIESCER--(--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_QUIESCER 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 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
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 |