Monitoring Extent Movement Progress

Since DB2 9.7, you have been able to reduce the size of tablespaces using the statement:

    ALTER TABLESPACE USERSPACE1 REDUCE MAX

Using this functionality, however, requires that the tablespace uses automatic storage and that it was created with the reclaimable storage attribute. Unfortunately, tablespaces created in DB2 9.5 or earlier will not have the reclaimable storage attribute, and it’s not possible to convert an existing tablespace. If you are in this situation, you’ll have to use other (more painful) methods to release space.

When you issue the ALTER TABLESPACE ... REDUCE MAX statement, this starts an asynchronous process where DB2 relocates extents within the tablespace – this is similar to the process of defragmenting your hard drive that we used to use in the bad old days.

During this movement, the tablespace will not be in normal state, as you can see by querying the MON_GET_TABLESPACE() table function:

    SELECT 
       char(tbsp_name,30)  as tbsp_name
      ,char(tbsp_state,20) as tbsp_state 
    FROM 
       TABLE(mon_get_tablespace('',-2)) as t
    
    
    TBSP_NAME                      TBSP_STATE
    ------------------------------ --------------------
    SYSCATSPACE                    NORMAL
    TEMPSPACE1                     NORMAL
    SYSTOOLSPACE                   NORMAL
    SYSTOOLSTMPSPACE               NORMAL
    USERSPACE1                     MOVE_IN_PROGRESS
    
      5 record(s) selected.

In this example, you can see that USERSPACE1 is in MOVE_IN_PROGRESS state.

To monitor the status of tables in this state, IBM provides another handy table function that you can use: MON_GET_EXTENT_MOVEMENT_STATUS(). As per the documentation, this table returns the following information:

    Column Name       Description
    ----------------- --------------------------------------------------------------------
    TBSP_NAME         Table space name
    TBSP_ID           Table space identifier
    MEMBER            Member from which this information was collected.
    CURRENT_EXTENT    Current extent being moved
    LAST_EXTENT       Last extent moved
    NUM_EXTENTS_MOVED Number of extents moved so far during this extent movement operation
    NUM_EXTENTS_LEFT  Number of extents left to move during this extent movement operation
    TOTAL_MOVE_TIME   Total move time for all extents moved (in milliseconds)

We can write a query using table function to get the current status:

    SELECT
       char(TBSP_NAME,30) TBSP_NAME,
       NUM_EXTENTS_MOVED,
       NUM_EXTENTS_LEFT,
       TOTAL_MOVE_TIME
    FROM
       TABLE(SYSPROC.MON_GET_EXTENT_MOVEMENT_STATUS('', -1));
    
    
    TBSP_NAME                      NUM_EXTENTS_MOVED NUM_EXTENTS_LEFT TOTAL_MOVE_TIME
    ------------------------------ ----------------- ---------------- --------------------
    SYSCATSPACE                                   -1               -1                   -1
    TEMPSPACE1                                    -1               -1                   -1
    SYSTOOLSPACE                                  -1               -1                   -1
    SYSTOOLSTMPSPACE                              -1               -1                   -1
    USERSPACE1                                  9107            44278              3416946
    
      5 record(s) selected.

This shows you the current progress of the extent movement, but if you’d like to get a little more useful information, like when extent movement may complete, we can calculate the average time required to move each extent and then extrapolate to determine an estimated completion time:

    SELECT
        char(TBSP_NAME,30) TBSP_NAME,
        decimal(NUM_EXTENTS_MOVED*100.0/(NUM_EXTENTS_MOVED+NUM_EXTENTS_LEFT),5,2) as percent_complete,
        decimal(TOTAL_MOVE_TIME*1.0/NUM_EXTENTS_MOVED,6,1) as ms_per_extent,
        CURRENT TIMESTAMP + ((TOTAL_MOVE_TIME*1.0/NUM_EXTENTS_MOVED/1000)*NUM_EXTENTS_LEFT) seconds as est_completion_ts
    FROM
        TABLE(SYSPROC.MON_GET_EXTENT_MOVEMENT_STATUS('', -1))
    WHERE
        NUM_EXTENTS_LEFT <> -1;
    
    
    TBSP_NAME                     PCT_COMPLETE MS_PER_EXTENT EST_COMPLETION_TS
    ----------------------------- ------------ ------------- --------------------------
    USERSPACE1                           17.06         375.1 2016-06-26-13.02.51.304264
    
      1 record(s) selected.

Note that I filtered results to get only tablespaces where movement is in progress, by specifying that NUM_EXTENTS_LEFT can’t be -1 (as it is for tablespaces that are not in MOVE_IN_PROGRESS state).

I find that this information is far more useful than the raw numbers that come out of MON_GET_EXTENT_MOVEMENT_STATUS(). Hopefully you will, too.

 
Ian Bjorhovde is the principal consultant for DataProxy LLC in Phoenix, Arizona. He has worked in the IT industry for over 18 years and has spent the last 15 years as a consultant developing and supporting large DB2 LUW databases with clients in many different industries. He is an IBM Gold Consultant and IBM Champion, produces two DB2-focused podcasts called The Whole Package Cache and Create Database and has presented at many RUG, IDUG and IBM conferences.