DB2 10.5 for Linux, UNIX, and Windows

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.

Important: This routine is available starting with DB2® Cancun Release 10.5.0.4. If your database was created before you applied DB2 Cancun Release 10.5.0.4, then this routine becomes available after you apply DB2 Cancun Release 10.5.0.4 then issue the db2updv105 command. If your database was created before Version 10.5, upgrade to DB2 Cancun Release 10.5.0.4 or later pack to have this routine available.

Authorization

One of the following authorizations is required:

EXECUTE privilege on the routine
DATAACCESS authority
SQLADM authority
DBADM authority

Default PUBLIC privilege

None

Syntax


>>-MON_GET_TABLESPACE_QUIESCER--(--member--)-------------------><

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

Table 1. Information returned by MON_GET_TABLESPACE_QUIESCER
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.

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

Sample output from this query is as follows:

TBSP_NAME  QUIESCER_TS_ID  QUIESCER_OBJ_ID  QUIESCER_AUTH_ID  ...
---------- --------------- ---------------- ----------------- ...
USERSPACE1               2                5 SWALKTY           ...
USERSPACE1               2                5 SWALKTY           ...

  2 record(s) selected.

Output from this query (continued):

... 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.

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

The following is sample output from this query:

TBSP_NAME  QUIESCER_TS_ID  QUIESCER_OBJ_ID  QUIESCER_AUTH_ID  ...
---------- --------------- ---------------- ----------------- ...
USERSPACE1               2                5 SWALKTY           ...

  1 record(s) selected.

Output from this query (continued):

... 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.
```
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
```
Sample output from this query is as follows:
```
TBSP_NAME     TBSPACEID    TABLEID
------------- ------------ --------
TABDATA       -6           -32768
DATAMART      -6           -32765
SMALL         5            17

  3 record(s) selected.
```
Use the given TBSPACEID and TABLEID provided in the output from the previous query to find the table schema and name from SYSCAT.TABLES:
- For the table spaces TABDATA and DATAMART, run the following query:
```
SELECT CHAR(tabschema, 10)tabschema, CHAR(tabname,15)tabname 
  FROM SYSCAT.TABLES 
  WHERE tbspaceid = -6 AND tableid in (-32768,-32765)
```
  Sample output from this query is as follows:
```
TABSCHEMA    TABNAME
------------ --------
SAMP         ORDERS_RP
SAMP         ORDERS_DMART

  2 record(s) selected.
```
- For the table space SMALL, run the following query:
```
SELECT CHAR(tabschema, 10)tabschema, CHAR(tabname,15)tabname 
  FROM SYSCAT.TABLES 
  WHERE tbspaceid = 5 AND tableid = 17
```
  Sample output from this query is as follows:
```
TABSCHEMA    TABNAME
------------ --------
SAMP         NATION

  1 record(s) selected.
```