DB2 Version 9.7 for Linux, UNIX, and Windows

SNAPSHOT_TBREORG table function

Note: This table function has been deprecated and replaced by the SNAPTAB_REORG administrative view and SNAP_GET_TAB_REORG table function - Retrieve table reorganization snapshot information.


>>-SNAPSHOT_TBREORG--(--dbname--,--dbpartitionnum--)-----------><

The schema is SYSPROC.

The SNAPSHOT_TBREORG function returns table reorganization information in the form of a result set. If no tables have been reorganized, 0 rows are returned. To obtain real-time snapshot information, the user must have SYSADM, SYSCTRL, or SYSMAINT authority.

Table function parameters

dbname: An input argument of type VARCHAR(255) that specifies a valid database name in the same instance as the currently connected database when calling this function. Specify a database name that has a directory entry type of either "Indirect" or "Home", as returned by the LIST DATABASE DIRECTORY command. Specify the null value to take the snapshot from the currently connected database.
dbpartitionnum: An input argument of type INTEGER that specifies a valid database partition number. Specify -1 for the current database partition, or -2 for all active database partitions. An active database partition is a partition where the database is available for connection and use by applications.
If the null value is specified, -1 is set implicitly.

If both parameters are set to NULL, the snapshot will be taken only if a file has not previously been created by the SNAPSHOT_FILEW stored procedure for the corresponding snapshot API request type.

Authorization

One of the following authorities is required to execute the function:

EXECUTE privilege on the function
DATAACCESS authority

To access snapshot monitor data, one of the following authorities is required:

SYSMON authority
SYSCTRL authority
SYSMAINT authority
SYSADM authority

The function returns a table as shown in the following section.

Table 1. Information returned by the SNAPSHOT_TBREORG table function
Column name	Data type	Description or corresponding monitor element
SNAPSHOT_TIMESTAMP	TIMESTAMP	snapshot_timestamp - Snapshot timestamp
TABLE_NAME	VARCHAR(128)	table_name - Table name
TABLE_SCHEMA	VARCHAR(128)	table_schema - Table schema name
PAGE_REORGS	BIGINT	page_reorgs - Page reorganizations
REORG_PHASE	BIGINT	reorg_phase - Table reorganize phase
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	INTEGER	reorg_type - Table reorganize attributes
REORG_STATUS	SMALLINT	reorg_status - Table reorganize status
REORG_COMPLETION	INTEGER	reorg_completion - Table reorganization completion flag
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
PARTITION_NUMBER	SMALLINT	node_number - Node number