idug.org - International DB2 User Group

Recently, I upgraded a fairly large system from DB2 V8.2 to 9.7, with one of the greatest expected benefits being the reduction in storage usage because of the compression (storage optimisation) feature.   This article describes how the new ADMIN_MOVE_TABLE() administrative stored procedure in DB2 9.7 made getting maximum benefit out of this feature easy.

The database had DMS tablespaces, but had been initially created prior to the introduction of automatic storage. DB2 9.7 allows an existing database to be converted to using automatic storage, using a combination of ALTER DATABASE and ALTER TABLESPACE commands.

First, we add automatic storage paths to the database -

ALTER DATABASE mydb ADD STORAGE ON '/db2db01/mydb', '/db2db02/mydb';

At this point, any new tablespaces created will use automatic storage. However, we have to convert the existing tablespaces -

ALTER TABLESPACE tabspace1 MANAGED BY AUTOMATIC STORAGE;

ALTER TABLESPACE tabspace1 REBALANCE;

The first of these commands sets up the new automatic storage paths for the tablespace; the second one rebalances the containers across the new automatic storage paths and removes the non-automatic files.

The next thing we want to do is turn on compression for each of the tables -

ALTER TABLE myschema.mytable COMPRESS YES;

To actually compress the data we need to reorg the table too -

REORG TABLE myschema.mytable USE tempspace1;

However at this point there is still more that can be done, and this is where it gets interesting. As documented at this page in the DB2 9.7 Information Center, there is a new command to allow reclamation of space by moving the data to the front of the tablespace and reducing the High Water Mark (something we've struggled with for a long time). However to use this functionality, the tablespaces had to be created at 9.7 level.

Thankfully, a new administrative stored procedure, SYSPROC.ADMIN_MOVE_TABLE() has been provided to make moving tables between tablespaces easier.

So I created new tablespaces for the tables -

CREATE TABLESPACE tabspace1_new MANAGED BY AUTOMATIC STORAGE;

I then used the administrative stored procedure to move the tables -

CALL SYSPROC.ADMIN_MOVE_TABLE(

'myschema','mytable',

'tabspace1_new','tabspace1_new','tabspace1_new','','','','','','MOVE');

The first and second parameters are the table's schema and name. The next three parameters are the standard, index and long tablespaces: I found that you needed to specify all three and if you want to use one tablespace for data and indexes you need to specify the same tablespace name three times. There are a variety of advanced options which can be specified in the next five parameters.  Three of these parameters allow addition of definitions for the various different types of DB2 partitioning (multi-dimensional clustering, DPF hash distribution and range partitioning) to the target table.   The fourth allows some column data type modifications, as long as the source and target data types are "compatible".  The fifth of these parameters is for control options. We simply passed through empty strings for these advanced options since we weren't using any of them. Note that if you do not want to specify these parameters then it must be an empty string, not a NULL (this has a different meaning for some of the parameters).  The last parameter specifies the action: "MOVE" means that we want to do the whole migration, but you can also specify different stages individually if you wish.

As the tables were moved only the space actually required was used. In our case this meant that we reclaimed between 10 and 15 percent more space than we achieved without moving to the newly created tablespaces.

Overall the space used by the database after migration to only 39% of the original size.

Not a bad day's work all told.

I found that everything worked very well. I was impressed with the new administrative stored procedure: it worked flawlessly. One thing I discovered is that any MQTs have to be dropped before using the stored procedure, and recreated afterwards. However even forgetting to do this wasn't a disaster : I simply dropped the offending MQT and then restarted the procedure at the appropriate point. There is a table (SYSTOOLS.ADMIN_MOVE_TABLE) that keeps track of the progress of any moves being done. By looking up the current status in this table we can restart the procedure at the appropriate point -

select * from systools.admin_move_table where key = 'STATUS';

In our case the VALUE of the STATUS was "REPLAY", and thus (according to the Information Center) we were able to rerun the stored procedure with the last parameter being set to "REPLAY" or "SWAP". This again worked flawlessly, and completed very quickly since all that it had left to do was swap the copy and live tables.

So now we've got a server which is once again in regular IBM support and with plenty of extra space available: something which I'm sure our developers will very quickly find a use for.

Closely related to the ADMIN_MOVE_TABLE() stored procedure is another stored procedure called ADMIN_MOVE_TABLE_UTIL(). This stored procedure allows you to change the default behaviour of the ADMIN_MOVE_TABLE stored procedure by updating parameters, in the form of key/value pairs.

As mentioned above, the last parameter in a call to ADMIN_MOVE_TABLE specifies the "operation" to be performed. There are a number of stages, and we chose to perform everything at once using the "MOVE" operation. This actually runs four operations one after the other : INIT, COPY, REPLAY and SWAP. There is also a VERIFY operation, which if required would be run between REPLAY and SWAP.

If we want to modify the operation of the MOVE process, we need to perform the move in stages. First we perform the INIT function on its own. Among the actions performed on initialisation is the creation of a table called SYSTOOLS.ADMIN_MOVE_TABLE, which has the following structure -

One point to note is that this table is created in the SYSTOOLSPACE tablespace. The ADMIN_MOVE_TABLE() stored procedure will fail if this tablespace does not exist.

After the INIT function completes we can then call the ADMIN_MOVE_TABLE_UTIL() stored procedure to make changes to the way to move is done. What can be done is listed in Table 2 at the foot of ”>this page in the Information Center.

For example, perhaps we want to change the commit frequency as the data is copied over to the target table from the default of every 10000 rows to every million rows -

CALL SYSPROC.ADMIN_MOVE_TABLE_UTIL(

'MYSCHEMA','MYTABLE',

'UPSERT','COMMIT_AFTER_N_ROWS','1000000');

To complete the process we now call ADMIN_MOVE_TABLE again three more times, with the same parameters as above apart from the operation which should be (in turn) 'COPY', 'REPLAY' and then 'SWAP'.

Note that the ADMIN_MOVE_TABLE function is an online operation. It creates a shadow copy of the table and uses triggers on the source table to capture changes into a staging table which are then replayed into the target table. The final “SWAP” operation briefly takes the table offline : using multiple steps rather than the “all-in-one” move operation allows you to have more control over exactly when this takes place. Using this online move facility improves availability, but requires additional storage, for the shadow and staging tables and log entries, and will cause a transactional overhead, due to the triggers, while the operation is in progress.

For additional safety it is possible to retain the source table under a different name after the move, by specify the KEEP option. This can then be manually checked and dropped after the operation is completed.

Up until the “SWAP” stage, the whole process can be cancelled using the “CANCEL” operation. This cleans up the target and staging tables and work continues on the source table as if the process was never started.

IBM recommends that you should not use this facility for tables without unique indexes as this can result in deadlocks and complex or expensive replay processing. There are a number of other restrictions and warnings in the Information Center which you should familiarise yourself with before using this facility. However our experience of the facility has been very positive and we would urge you to investigate it as a useful addition to your DBA toolbox.

About The Author

Philip Nelson is an Information Architect with Scottish Widows PLC, a UK financial services company. He is also a principal of ScotDB Limited, a database consultancy specializing in DB2 for LUW, particularly  delivering DB2 solutions on Linux for the SMB market. He has worked with DB2 since 1989 and with DB2 on distributed platforms since 1995. His expertise includes database design, performance tuning and using DB2 with new technologies (most recently focusing on XML and Ruby on Rails).  He has been involved with IDUG since 1998, and is currently Executive Editor of the IDUG Solutions Journal.  He can be reached at This e-mail address is being protected from spambots. You need JavaScript enabled to view it .

TrackBack URI for this entry

Comments (3)

Subscribe to this comment's feed

Great
written by siva kesava , December 01, 2009

Looks great, it would have been much better, if it is possible to move more than one table at a time in to new tablespaces.
Is there an option to do that ?
Transaction Log space is a constraint if the tables are too large, isn't it ? It may be best to use load if the tables are too huge !

Thanks a lot Philip, for clearly explaining the operation of the procedure and the utility.
Have a nice day.

Siva

• 
• +0
• 
• 

Re: Comments on Article
written by Philip Nelson, December 01, 2009

Siva,

Thanks for your kind comments.

The utility works on one table at a time (the hint is in the name ADMIN_MOVE_TABLE).

Your point about LOAD is valid, and thankfully the developers have thought of this. One of the control options you can specify is "COPY_USE_LOAD". You will need to also set the "FORCE" option before running the "SWAP" operation if you set this option. To allow recoverability you should run a backup of the target tablespace before running the "SWAP" operation (the reason why you must set the "FORCE" option before running "SWAP" is to remind you to do this according to the Information Center).

As you can see there is a lot more to this facility than I was able to describe in the article and it is well worth exploring even if you have very large objects.

HTH

Phil

• 
• +1
• 
• 

...
written by siva kesava, December 02, 2009

Hi Phil,

Thanks for your reply and answering my query.
I would love to try and use this ADMIN_MOVE_TABLE procedure in one of the test servers, when we decide to migrate to 9.7 and want to re-claim the unused space.

Thanks for sharing the useful information.

• 
• +0
• 
•