Monitoring Extent Movement Progress


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


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:

       char(tbsp_name,30)  as tbsp_name
      ,char(tbsp_state,20) as tbsp_state 
       TABLE(mon_get_tablespace('',-2)) as t
    TBSP_NAME                      TBSP_STATE
    ------------------------------ --------------------
    SYSCATSPACE                    NORMAL
    TEMPSPACE1                     NORMAL
    SYSTOOLSPACE                   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:

       char(TBSP_NAME,30) TBSP_NAME,
    ------------------------------ ----------------- ---------------- --------------------
    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:

        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
        NUM_EXTENTS_LEFT <> -1;
    ----------------------------- ------------ ------------- --------------------------
    USERSPACE1                           17.06         375.1 2016-06-26-
      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 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.


Ian Bjorhovde
Ian Bjorhovde

Ian Bjorhovde is the Lead Db2 DBA and Delivery Manager for XTIVIA, and is based in Phoenix, Arizona. He has spent over 20 years as a consultant developing and supporting large and small Db2 databases for clients in many different industries. Ian is an IBM Gold Consultant and IBM Champion. He volunteers for the International DB2 Users Group and holds numerous DB2 certifications, and has presented at many RUG, IDUG and IBM conferences around the world.

Articles: 8


  1. Hi Mike,

    I’d like to share my experience: I have ran +5 “alter tablespace… reduce max” simultaneously without issues (i.e. slow performance, etc.) of course during low transactionality… one thing I’ve noticed is that if a backup (online) kicks off, it will pause the extent movement for the tablespace(s)… then you need to run the ALTER again after backup is complete.


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.