SNAPTAB_REORG administrative view and SNAP_GET_TAB_REORG table function - Retrieve table reorganization snapshot information
SNAPTAB_REORG administrative view
This administrative view allows you to retrieve table reorganization snapshot information for the currently connected database.
Used with the SNAPTAB administrative view, the SNAPTAB_REORG administrative view provides the data equivalent to the GET SNAPSHOT FOR TABLES 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 SNAPTAB_REORG administrative view
- CONTROL privilege on the SNAPTAB_REORG administrative view
- DATAACCESS authority
- DBADM authority
- SQLADM authority
- EXECUTE privilege on the SNAP_GET_TAB_REORG 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(TABNAME, 1, 15) AS TAB_NAME, SUBSTR(TABSCHEMA, 1, 15)
AS TAB_SCHEMA, REORG_PHASE, SUBSTR(REORG_TYPE, 1, 20) AS REORG_TYPE,
REORG_STATUS, REORG_COMPLETION, DBPARTITIONNUM
FROM SYSIBMADM.SNAPTAB_REORG ORDER BY DBPARTITIONNUM
TAB_NAME TAB_SCHEMA REORG_PHASE ...
--------...- ----------...- ---------------- ...
EMPLOYEE DBUSER REPLACE ...
EMPLOYEE DBUSER REPLACE ...
EMPLOYEE DBUSER REPLACE ...
...
3 record(s) selected.
... REORG_TYPE REORG_STATUS REORG_COMPLETION DBPARTITIONNUM
... -------------------- ------------ ---------------- --------------
... RECLAIM+OFFLINE+ALLO COMPLETED SUCCESS 0
... RECLAIM+OFFLINE+ALLO COMPLETED SUCCESS 1
... RECLAIM+OFFLINE+ALLO COMPLETED SUCCESS 2
db2 -v "select * from sysibmadm.snaptab_reorg"
TABNAME REORG_PHASE REORG_MAX_PHASE REORG_TYPE
-------- ----------------- ----------------- ---------------------------
T1 RELEASE 3 RECLAIM_EXTENTS+ALLOW_WRITE
REORG_STATUS REORG_COMPLETION REORG_START REORG_END
------------ ---------------- -------------------------- --------------------------
COMPLETED SUCCESS 2008-09-24-14.35.30.734741 2008-09-24-14.35.31.460674
SNAP_GET_TAB_REORG table function
The SNAP_GET_TAB_REORG table function returns the same information as the SNAPTAB_REORG 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_TAB table function, the SNAP_GET_TAB_REORG table function provides the data equivalent to the GET SNAPSHOT FOR TABLES 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_TAB_REORG table function takes a snapshot for the currently connected database and database member number.
Authorization
- EXECUTE privilege on the SNAP_GET_TAB_REORG 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(TABNAME, 1, 15) AS TAB_NAME, SUBSTR(TABSCHEMA, 1, 15)
AS TAB_SCHEMA, REORG_PHASE, SUBSTR(REORG_TYPE, 1, 20) AS REORG_TYPE,
REORG_STATUS, REORG_COMPLETION, DBPARTITIONNUM
FROM TABLE( SNAP_GET_TAB_REORG('', 1)) AS T
TAB_NAME TAB_SCHEMA REORG_PHASE REORG_TYPE ...
--------...- ----------...- -----------...- -------------------- ...
EMPLOYEE DBUSER REPLACE RECLAIM+OFFLINE+ALLO ...
...
1 record(s) selected. ...
... REORG_STATUS REORG_COMPLETION DBPARTITIONNUM
... ------------ ---------------- --------------
... COMPLETED SUCCESS 1
...
db2 -v "select * from table(snap_get_tab_reorg(''))"
TABNAME REORG_PHASE REORG_MAX_PHASE REORG_TYPE
-------- ----------------- ----------------- ---------------------------
T1 RELEASE 3 RECLAIM_EXTENTS+ALLOW_WRITE
REORG_STATUS REORG_COMPLETION REORG_START REORG_END
------------ ---------------- -------------------------- --------------------------
COMPLETED SUCCESS 2008-09-24-14.35.30.734741 2008-09-24-14.35.31.460674
Information returned
Column name | Data type | Description or corresponding monitor element |
---|---|---|
SNAPSHOT_TIMESTAMP | TIMESTAMP | The date and time that the snapshot was taken. |
TABNAME | VARCHAR (128) | table_name - Table name |
TABSCHEMA | VARCHAR (128) | table_schema - Table schema name |
PAGE_REORGS | BIGINT | page_reorgs - Page reorganizations |
REORG_PHASE | VARCHAR (16) | reorg_phase - Table reorganize phase . This interface
returns a text identifier based on defines in sqlmon.h and
is one of:
|
REORG_MAX_PHASE | INTEGER | reorg_max_phase - Maximum table reorganize phase |
REORG_CURRENT_COUNTER | BIGINT | reorg_current_counter - Table reorganize progress |
REORG_MAX_COUNTER | BIGINT | reorg_max_counter - Total amount of table reorganization |
REORG_TYPE | VARCHAR (128) | reorg_type - Table reorganize attributes . This
interface returns a text identifier using a combination of the following
identifiers separated by '+': Either:
Example 1: If a REORG TABLE TEST.EMPLOYEE was run, the following
would be displayed:
Example 2: If a REORG TABLE TEST.EMPLOYEE
INDEX EMPIDX INDEXSCAN was run, then the following would be displayed:
|
REORG_STATUS | VARCHAR (10) | reorg_status - Table reorganize status . This
interface returns a text identifier based on defines in sqlmon.h and
is one of:
|
REORG_COMPLETION | VARCHAR (10) | reorg_completion - Table reorganization
completion flag . This interface returns a text
identifier, based on defines in sqlmon.h and
is one of:
|
REORG_START | TIMESTAMP | reorg_start - Table reorganize start time |
REORG_END | TIMESTAMP | reorg_end - Table reorganize end time |
REORG_PHASE_START | TIMESTAMP | reorg_phase_start - Table reorganize phase start time |
REORG_INDEX_ID | BIGINT | reorg_index_id - Index used to reorganize the table |
REORG_TBSPC_ID | BIGINT | reorg_tbspc_id - Table space where table is reorganized |
DBPARTITIONNUM | SMALLINT | dbpartitionnum - Database partition number monitor element |
DATA_PARTITION_ID | INTEGER | data_partition_id - Data Partition identifier . For a non-partitioned table, this element will be NULL. |
REORG_ROWSCOMPRESSED | BIGINT | reorg_rows_compressed - Rows compressed |
REORG_ROWSREJECTED | BIGINT | reorg_rows_rejected_for_compression - Rows rejected for compression |
REORG_LONG_TBSPC_ID | BIGINT | reorg_long_tbspc_id - Table space where long objects are reorganized |
MEMBER | SMALLINT | member - Database member monitor element |