The SNAPTAB_REORG administrative view and the SNAP_GET_TAB_REORG table function return table reorganization information. If no tables have been reorganized, 0 rows are returned. When a data partitioned table is reorganized, one record for each data partition is returned. If only a specific data partition of a data partitioned table is reorganized, only a record the for the partition is returned.
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.
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
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 partition, aggregate of all database partitions or all database partitions.
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.
>>-SNAP_GET_TAB_REORG--(--dbname--+------------------+--)------>< '-, dbpartitionnum-'
The schema is SYSPROC.
If both dbname and dbpartitionnum 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 partition number.
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
Column name | Data type | Description or corresponding monitor element |
---|---|---|
SNAPSHOT_TIMESTAMP | TIMESTAMP | snapshot_timestamp - Snapshot timestamp monitor element |
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_ |
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 | The database partition from which the data was retrieved for this row. |
DATA_PARTITION_ID | INTEGER | data_partition_id - Data Partition identifier . For a non-partitioned table, this element will be NULL. |
REORG_ |
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 |