IBM Support

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

Technical Blog Post


Abstract

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.

image

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

$ db2 runstats on table ljsi97.car_order100 with distribution and indexes all set profile only
DB20000I  The RUNSTATS command completed successfully.
 
$ date
Fri Feb  3 05:41:35 KORST 2012

If you check STATS_TIME for the table, it has exiting statics at this time.
$ db2 "select substr(tabname,1,15) TABLE, STATS_TIME from syscat.tables where tabname like 'CAR_ORDER100%'"
 
TABLE           STATS_TIME                
--------------- --------------------------
CAR_ORDER100    2012-01-31-08.54.08.392207      <=== It's old time
 
2. Perform 'INIT' Phase. For this example, I used the "LOAD" for admin move table. 'TS1' is the tablespace name.

$ db2 "call sysproc.admin_move_table( 'LJSI97','CAR_ORDER100','TS1','TS1','TS1' ,'','','','',' COPY_USE_LOAD ','INIT')"

At this timing, only original table has statistics.
$ db2 "select substr(tabname,1,15) TABLE, STATS_TIME from syscat.tables where tabname like 'CAR_ORDER100%'"
 
TABLE           STATS_TIME                
--------------- --------------------------
CAR_ORDER100    2012-01-31-08.54.08.392207
CAR_ORDER100AC# -                         
CAR_ORDER100AC# -     

 
3. Perform 'COPY' Phase.  In this step , target table will have new statistics. 
 
$ db2 "call sysproc.admin_move_table( 'LJSI97','CAR_ORDER100','TS1','TS1','TS1' ,'','','','',' COPY_USE_LOAD ','COPY')"
 
.......< snippet > ........
 COPY_END                         2012-02-03-05.50.16.777612                                                                                                      
  COPY_OPTS                        LOAD,WITH_INDEXES,NON_CLUSTER                                                                                                   
  COPY_START                       2012-02-03-05.50.14.582311                                                                                                      
  COPY_TOTAL_ROWS                  447075                     
 
 
[ljsi97@rain:/home/ljsi97/AMT/junsu $] db2 "select substr(tabname,1,25) TABLE, STATS_TIME from syscat.tables where tabname like 'CAR_ORDER100%'"
 
TABLE                     STATS_TIME                
------------------------- --------------------------
CAR_ORDER100              2012-01-31-08.54.08.392207
CAR_ORDER100AC#NBws       -                         
CAR_ORDER100AC#NBwt       2012-02-03-05.50.16.357087    <=== Now, Target Table has new statistics
 
 

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.

 
$ db2 "call sysproc.admin_move_table( 'LJSI97','CAR_ORDER100','TS1','TS1','TS1' ,'','','','','','REPLAY')"

5. Perform 'SWAP' without runstats work.
 
$ db2 call sysproc.admin_move_table( 'LJSI97','CAR_ORDER100','TS1','TS1','TS1' ,'','','','',' NO_STATS, COPY_USE_LOAD ','SWAP')
 
......<snippet> ........
SWAP_END                         2012-02-03-06.05.51.338467                                                                                                      
  SWAP_RETRIES                     0                                                                                                                               
  SWAP_START                       2012-02-03-06.05.51.233262 
 

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')”

 

[{"Business Unit":{"code":"BU029","label":"Data and AI"}, "Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm11140058