50 DB2 Nuggets #22 : Expert Advice - Controlling runstats during Admin Move Table

Body

Are you considering ‘Admin move table’ for reorganization work of your tables ? And are you finding the way to reduce time of last ‘SWAP’ of admin move table work ?

Nowadays, our database systems have so many number of tables and the tables are getting bigger. And as a database administrator, we are facing the reality that we need to do the time consuming work for maintaining huge tables and business departments usually do not permit enough time to work on those on weekends offline maintenance work time windows.

As many of you might know, manual admin move table work has 4 steps.
1. INIT
2. COPY
3. REPLAY – This step can be performed multiple times.
4. SWAP

In Admin move table work, the most important and scary time is ‘SWAP’ step in terms of service availability perspective. By default, ‘SWAP’ step internally performs the ‘runstats’ in this phase. Therefore, sometime it would be take more time than we expected.
But if you want to do the INIT, COPY and REPLAY phase during working days when service is available and do the ‘SWAP’ phase for multiple tables at once in short maintenance time, you can consider the following step.
The idea is that performing runstats in COPY phase and skipping in SWAP phase.

But just keep in mind that this strategy can be used in the enviroment that runstats is not necessary to be up to date in real time or daily basis.

1. Set runstats profile for the table. This command does not actually perform runstats.
 

4. Perform 'REPLAY' multiple times depending on transaction volumes for the table.
    I recommend to perform 'REPLAY' at least once just before the 'SWAP' phase.

For using this method, we need to take into consideration following things as well.

1. Actually using LOAD is faster than SQL trigger method in admin move table work.
    But it could be a bit slower than just pure LOAD job itself as admin move table has other internal work itself.

2. During 'COPY' phase of multiple admin move table work, we need to monitor system resources such as CPU.

3. LOAD method in admin move table use the 'NONRECOVERABLE' mode by default.
    Therefore we need to be careful when using in DB2 HADR environment.
    Of course, we can use COPY YES option as well like following.
 

db2 "call sysproc.admin_move_table('AMT','ORDER','TS1','TS1','TS1','','','','','COPY_USE_LOAD "MESSAGES ON SERVER COPY YES TO 
          /Users/db2inst1/load/copy"','COPY')”