MON_GET_EXTENT_MOVEMENT_STATUS - get extent movement progress

The MON_GET_EXTENT_MOVEMENT_STATUS table function returns the status of the extent movement operation.

Syntax

The schema is SYSPROC.

Table function parameters

tbsp_name: An input argument of type VARCHAR(128) that specifies the table space to query. If the argument value is null, the function returns information for all table spaces.
member: An input argument of type INTEGER that specifies a valid member inside the same instance as the currently connected database. Specify -1 for the current database member, or -2 for all active database members. If the argument value is null, -1 is set implicitly.

Authorization

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

EXECUTE privilege on the routine
DATAACCESS authority
DBADM authority
SQLADM authority

Default PUBLIC privilege

None

Example

Retrieve all information about the current extent progress for all table spaces:

   SELECT * FROM TABLE(SYSPROC.MON_GET_EXTENT_MOVEMENT_STATUS('', -1))

Here is an example of the output from the preceding query:

TBSP_NAME      TBSP_ID  MEMBER CURRENT_EXTENT LAST_EXTENT NUM_EXTENTS_MOVED 
-------------- -------- ------ -------------- ----------- ----------------- 
SYSCATSPACE           0      0             -1          -1                -1 
TEMPSPACE1            1      0             -1          -1                -1 
USERSPACE1            2      0             -1          -1                -1 
TS_01A                3      0             -1          -1                -1 
SYSTOOLSPACE          4      0             -1          -1                -1 
                                                                            
  5 record(s) selected.

Output from the query continued:

...NUM_EXTENTS_LEFT TOTAL_MOVE_TIME   PHYSICAL_MOVE_TIME   LBP_UPDATE_TIME
...---------------- ----------------- -------------------- ----------------- 
...              -1                -1                   -1                -1
...              -1                -1                   -1                -1
...              -1                -1                   -1                -1
...              -1                -1                   -1                -1
...              -1                -1                   -1                -1

Output from the query continued:

...REMOTE_UPDATE_TIME   GBP_UPDATE_TIME   TOTAL_METADATA_UPDATE_TIME EHL_LOCKWAIT_TIME   
...-------------------- ----------------- -------------------------- --------------------
...                  -1                -1                         -1                   -1
...                  -1                -1                         -1                   -1
...                  -1                -1                         -1                   -1
...                  -1                -1                         -1                   -1
...                  -1                -1                         -1                   -1

Information returned

Table 1. Information returned for MON_GET_EXTENT_MOVEMENT_STATUS
Column Name	Data Type	Description or corresponding monitor element
TBSP_NAME	VARCHAR(128)	tablespace_name - Table space name
TBSP_ID	BIGINT	tablespace_id - Table space identifier
MEMBER	SMALLINT	member - Member from which this information was collected.
CURRENT_EXTENT	INTEGER	current_extent - Current extent being moved
LAST_EXTENT	INTEGER	last_extent - Last extent moved
NUM_EXTENTS_MOVED	INTEGER	num_extents_moved - Number of extents moved so far during this extent movement operation
NUM_EXTENTS_LEFT	INTEGER	num_extents_left - Number of extents left to move during this extent movement operation
TOTAL_MOVE_TIME	BIGINT	total_move_time - Total move time for all extents moved (in milliseconds)
PHYSICAL_MOVE_TIME	BIGINT	physical_move_time - Physical extent move time
LBP_UPDATE_TIME	BIGINT	lbp_update_time - Local buffer pool update time
REMOTE_UPDATE_TIME	BIGINT	remote_update_time - Remote member update time
GBP_UPDATE_TIME	BIGINT	gbp_update_time - Group puffer pool update time
TOTAL_METADATA_UPDATE_TIME	BIGINT	total_metadata_update_time - Total time for metadata update for extent movement
EHL_LOCKWAIT_TIME	BIGINT	ehl_lockwait_time - Total time for EHL object lock wait