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.
In Db2® 12.1.1 and later, TBSPACEADM authority on any storage groups.

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