MON_GET_TABLESPACE_QUIESCER table function - Get information about quiesced table spaces
The MON_GET_TABLESPACE_QUIESCER table function returns information about quiesced table spaces. A single row is returned for each member.
Authorization
One of the following authorizations
is required:
- EXECUTE privilege on the routine
- DATAACCESS authority
- SQLADM authority
- DBADM authority
Default PUBLIC privilege
None
Syntax
The schema is SYSPROC.
Routine parameters
- member
- An optional input argument of type SMALLINT that specifies a valid member in the same instance as the currently connected database when you call this function. Specify -1 for the current database member or -2 for all active database members. If the null value is specified, -1 is set.
Information returned
Column name | Data type | Description |
---|---|---|
TBSP_NAME | VARCHAR(128) | tablespace_name - Table space name monitor element |
QUIESCER_TS_ID | BIGINT | quiescer_ts_id - Quiescer table space identification monitor element |
QUIESCER_OBJ_ID | BIGINT | quiescer_obj_id - Quiescer object identification monitor element |
QUIESCER_AUTH_ID | VARCHAR(128) | quiescer_auth_id - Quiescer user authorization identification monitor element |
QUIESCER_APPLICATION_HANDLE | BIGINT | quiescer_application_handle - Quiescer application handle monitor element |
QUIESCER_STATE | VARCHAR(14) | quiescer_state - Quiescer state monitor element |
DBPARTITIONNUM | SMALLINT | dbpartitionnum - Database partition number monitor element |
MEMBER | SMALLINT | member - Database member monitor element |
Usage notes
Use the MON_GET_TABLESPACE_QUIESCER table function in place of the SNAP_GET_TBSP_QUIESCER table function and SNAPTBSP_QUIESCER view, which have been deprecated.
Examples
- Retrieve information about the quiesced table spaces for all database
members for the currently connected database.
Sample output from this query is as follows:SELECT SUBSTR(TBSP_NAME, 1, 10) AS TBSP_NAME, QUIESCER_TS_ID, QUIESCER_OBJ_ID, QUIESCER_AUTH_ID, QUIESCER_APPLICATION_HANDLE, QUIESCER_STATE, DBPARTITIONNUM FROM TABLE(MON_GET_TABLESPACE_QUIESCER(-2)) ORDER BY DBPARTITIONNUM
Output from this query (continued):TBSP_NAME QUIESCER_TS_ID QUIESCER_OBJ_ID QUIESCER_AUTH_ID ... ---------- --------------- ---------------- ----------------- ... USERSPACE1 2 5 SWALKTY ... USERSPACE1 2 5 SWALKTY ... 2 record(s) selected.
... QUIESCER_APPLICATION_HANDLE QUIESCER_STATE DBPARTITIONNUM ... --------------------------- -------------- -------------- ... 0 EXCLUSIVE 0 ... 65983 EXCLUSIVE 1
- Retrieve information about the quiesced table spaces for database
member 1 for the currently connected database.
The following is sample output from this query:SELECT SUBSTR(TBSP_NAME, 1, 10) AS TBSP_NAME, QUIESCER_TS_ID, QUIESCER_OBJ_ID, QUIESCER_AUTH_ID, QUIESCER_APPLICATION_HANDLE, QUIESCER_STATE, DBPARTITIONNUM FROM TABLE( MON_GET_TABLESPACE_QUIESCER(1)) AS T
Output from this query (continued):TBSP_NAME QUIESCER_TS_ID QUIESCER_OBJ_ID QUIESCER_AUTH_ID ... ---------- --------------- ---------------- ----------------- ... USERSPACE1 2 5 SWALKTY ... 1 record(s) selected.
... QUIESCER_APPLICATION_HANDLE QUIESCER_STATE DBPARTITIONNUM ... --------------------------- -------------- -------------- ... 65983 EXCLUSIVE 1
- Determine the table name for a quiesced table in a partitioned
database.If the table is partitioned and kept in quiesced state, the values for table space ID and table ID are different from the values that are stored in SYSCAT.TABLES. The values for these IDs are stored with the unsigned short representation. The first step in finding the table name of the quiesced table is to find the signed short representation. Do this by calculating the table space ID by subtracting 65536 (the maximum value) from QUIESCER_TS_ID. Next, use this table space ID to locate the quiesced tables. The actual table space ID can be found in SYSCAT.DATAPARTITIONS for each data partition in the table.
Sample output from this query is as follows: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 TABLE(MON_GET_TABLESPACE_QUIESCER(-2)) ORDER BY DBPARTITIONNUM
Use the given TBSPACEID and TABLEID provided in the output from the previous query to find the table schema and name from SYSCAT.TABLES:TBSP_NAME TBSPACEID TABLEID ------------- ------------ -------- TABDATA -6 -32768 DATAMART -6 -32765 SMALL 5 17 3 record(s) selected.
- For the table spaces TABDATA and DATAMART, run the following query:
Sample output from this query is as follows:SELECT CHAR(tabschema, 10)tabschema, CHAR(tabname,15)tabname FROM SYSCAT.TABLES WHERE tbspaceid = -6 AND tableid in (-32768,-32765)
TABSCHEMA TABNAME ------------ -------- SAMP ORDERS_RP SAMP ORDERS_DMART 2 record(s) selected.
- For the table space SMALL, run the following query:
Sample output from this query is as follows:SELECT CHAR(tabschema, 10)tabschema, CHAR(tabname,15)tabname FROM SYSCAT.TABLES WHERE tbspaceid = 5 AND tableid = 17
TABSCHEMA TABNAME ------------ -------- SAMP NATION 1 record(s) selected.
- For the table spaces TABDATA and DATAMART, run the following query: