DB2 10.5 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 for the partition is returned.

Depending on if you are using the administrative view or the table function, refer to one of the following sections:

SNAPTAB_REORG administrative view
SNAP_GET_TAB_REORG table function

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 to use the view:

SELECT privilege on the SNAPTAB_REORG administrative view
CONTROL privilege on the SNAPTAB_REORG administrative view
DATAACCESS authority
DBADM authority
SQLADM authority

One of the following is required to use the table function:

EXECUTE privilege on the SNAP_GET_TAB_REORG table function
DATAACCESS authority
DBADM authority
SQLADM authority

In addition, to access snapshot monitor data, one of the following authorities is also required:

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 details on reorganization operations for all database members 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 is an example of 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) or insert time clustering (ITC) 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 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


>>-SNAP_GET_TAB_REORG--(--dbname--+----------+--)--------------><
                                  '-, member-'

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

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

Default PUBLIC privilege

In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the function is automatically created.

Example

Select details on reorganization operations for database member 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 is an example of 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) or insert time clustering (ITC) 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	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: `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	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