DB2 Version 9.7 for Linux, UNIX, and Windows

SNAPTAB_REORG administrative view and SNAP_GET_TAB_REORG table function - Retrieve table reorganization snapshot information

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.

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

One of the following authorizations is required:
  • SELECT privilege on the SNAPTAB_REORG administrative view
  • CONTROL privilege on the SNAPTAB_REORG administrative view
  • DATAACCESS authority
In addition, one of the following privileges or authorities is also required:
  • EXECUTE privilege on the SNAP_GET_TAB_REORG table function
  • DATAACCESS authority
In addition, to access snapshot monitor data, one of the following authorities is also required:
  • SYSMON
  • SYSCTRL
  • SYSMAINT
  • SYSADM

Example

Select details on reorganization operations for all database partitions on the currently connected database.
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
The following example is a sample output from this query.
TAB_NAME     TAB_SCHEMA     REORG_PHASE      ... 
--------...- ----------...- ---------------- ... 
EMPLOYEE     DBUSER         REPLACE          ... 
EMPLOYEE     DBUSER         REPLACE          ... 
EMPLOYEE     DBUSER         REPLACE          ... 
                                             ... 
3 record(s) selected.                            
Output from this query (continued).
... 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
Select all information about a reorganization operation to reclaim extents from a multidimensional clustering (MDC) table from the SNAPTAB_REORG administrative view..
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 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.

Syntax

Read syntax diagramSkip visual syntax diagram
>>-SNAP_GET_TAB_REORG--(--dbname--+------------------+--)------><
                                  '-, dbpartitionnum-'      

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.
dbpartitionnum
An optional input argument of type INTEGER that specifies a valid database partition number. Specify -1 for the current database partition, or -2 for an aggregate of all active database partitions. If dbname is not set to NULL and dbpartitionnum is set to NULL, -1 is set implicitly for dbpartitionnum. If this input option is not used, that is, only dbname is provided, data is returned from all active database partitions. An active database partition is a partition where the database is available for connection and use by applications.

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.

Authorization

One of the following authorizations is required:
  • EXECUTE privilege on the SNAP_GET_TAB_REORG table function
  • DATAACCESS authority
In addition, to access snapshot monitor data, one of the following authorities is also required:
  • SYSMON
  • SYSCTRL
  • SYSMAINT
  • SYSADM

Example

Select details on reorganization operations for database partition 1 on the currently connected database.
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
The following example is a sample output from this query.
TAB_NAME     TAB_SCHEMA     REORG_PHASE     REORG_TYPE           ...
--------...- ----------...- -----------...- -------------------- ...
EMPLOYEE     DBUSER         REPLACE         RECLAIM+OFFLINE+ALLO ...
                                                                 ...
  1 record(s) selected.                                          ...
Output from this query (continued).
... REORG_STATUS REORG_COMPLETION DBPARTITIONNUM 
... ------------ ---------------- -------------- 
... COMPLETED    SUCCESS                       1 
...                                              
Select all information about a reorganization operation to reclaim extents from a multidimensional clustering (MDC) table using the SNAP_GET_TAB_REORG table function.
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

Table 1. Information returned by the SNAPTAB_REORG administrative view and the SNAP_GET_TAB_REORG table function
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:
  • BUILD
  • DICT_SAMPLE
  • INDEX_RECREATE
  • REPLACE
  • SORT
  • SCAN
  • DRAIN
  • RELEASE
or SORT+DICT_SAMPLE.
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:

  • RECLAIM
  • RECLUSTER
  • RECLAIM_EXTS
and either:
  • +OFFLINE
  • +ONLINE
If access mode is specified, it is one of:
  • +ALLOW_NONE
  • +ALLOW_READ
  • +ALLOW_WRITE
If offline and RECLUSTER option, one of:
  • +INDEXSCAN
  • +TABLESCAN
If offline, one of:
  • +LONGLOB
  • +DATAONLY
If offline, and option is specified, any of:
  • +CHOOSE_TEMP
  • +KEEPDICTIONARY
  • +RESETDICTIONARY
If online, and option is specified:
  • +NOTRUNCATE
Example 1: If a REORG TABLE TEST.EMPLOYEE was run, the following would be displayed:
RECLAIM+OFFLINE+ALLOW_READ+DATAONLY
+KEEPDICTIONARY  
Example 2: If a REORG TABLE TEST.EMPLOYEE INDEX EMPIDX INDEXSCAN was run, then the following would be displayed:
RECLUSTER+OFFLINE+ALLOW_READ+INDEXSCAN
+DATAONLY+KEEPDICTIONARY
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:
  • COMPLETED
  • PAUSED
  • STARTED
  • STOPPED
  • TRUNCATE
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:
  • FAIL
  • SUCCESS
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_
   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