Category Archives: Move Table Partitions

DB2 Handle Table Partitioning

June 12, 2011 5:02 pm / Leave a Comment /

This is the final post in the series on handling DB2 range partitioned tables, attaching table partitions, detaching table partitions and moving table partitions. In my production environments I have a number of partitioned tables, that once a quater need new partitions adding, old ones detaching and middling partitions moving to slower / cheaper storage as they are less hot. I will discuss data retention policy in an other article.

To do this I use the code from my previous posts (can be found at the top), using all three it is possible to detach, move and add partitions, although obviously can be changed to your needs. The code I use can be found below:

CREATE PROCEDURE DB_MAIN.HANDLE_PARTITIONING  (IN TABLESCHEMA VARCHAR(255),
						IN TABLENAME VARCHAR(255),
						IN DETACHPARTITIONNUM INT,
						IN DETACHEXPORTDIR VARCHAR(255),
 						IN MOVEPARTITIONNO INTEGER,
						IN MOVEPARTITIONDIR VARCHAR(255),
						IN MOVEPARTITONTABLESPACE VARCHAR(20),
						IN NEWTABLESPACE VARCHAR(20))
LANGUAGE SQL
BEGIN
   --Move the partition to the archive schema
      CALL DB_MAIN.MOVE_PARTITION(TABLESCHEMA, TABLENAME, MOVEPARTITONNO, MOVEPARTITIONDIR, MOVEPARTITIONTABLESPACE);

   --Detach the partition
      CALL DB_MAIN.DETACH_PARTITION(TABLESCHEMA,TABLENAME,DETACHPARTITIONNUM,DETACHEXPORTDIR);

   --Attach the partition
      CALL DB_MAIN.ATTACH_PARTITION(TABLESCHEMA,TABLENAME,NEWTABLESPACE);
END

The moving partitions occurs first so as other wise it will throw off the partition numbers that you put in. This procedure will create a backup copy of your data while it is moving it in the directory you define, it will not remove it and so you will be able to back this up to permanent storage safe keeping before you get rid of it or just get rid of it. Second comes the detaching of the old data  that is no longer relevant to the business, again a file is created of the detached data so that you can back it up to permanent storage. Thirdly is adding the new partition for the new data that you will be adding in the future.

The thing that I like about this code is that it will enable you too will produce extracts to two different locations, so on the server I have two locations one for archive data and one for moving data, I can then back these up to CD, DVD or external HDD for safe keeping.

DISCLAIMER: As stated at the top of the blog use this code in your production systems at your own peril. I have tested and know it works on my systems, please test and check it works on yours properly as handling partitions can potentially dangerous. The file is a .doc only as that’s the only way I could get it uploaded onto wordpress, it should open fine like that, or knock the .doc off and it will open in your favourite text editor

FILE WITH CODE IN: DB2 Handle Table Partitions SPs

Posted in: Attach Partitions, DB2 Administration, DB2 Maintenance, Detach table Partition, IBM, Move table partitions

DB2 Move Table Partitions Automatically

June 4, 2011 2:08 pm / Leave a Comment /

One of the hot topics in databases at the moment is temperature based data. This is basically where you put the data that is most often access on fast disks, and data that is accessed least often is put on slower disks. To accomplish this is pretty easy as you can set the containers for an “archive” tablespace to a different disk / directory mount point. This does similar things to my two previous posts on attaching table partitions and detaching table partitions

The problem comes when you already have data in a table and the table partition already is part of a tablespace, you cant just alter the partition to the new tablespace. You have to identify the table partition to move, detach it recreate it in the new tablespace and then reload the data. You can do this manually, but who has time to do this, I have written code that will allow you to move the tablespace of the partition quite easily, and can be part of an automated process. The stored procedure call looks a little like this:

CALL DB_MAIN.MOVE_PARTITION('INSURANCE', 'TRANSACTIONS', 10, '/home/db2inst1/detach-archive/', 'INSURANCE_ARCH_TS')

The stored procedure that I have created deals with all of this and creates “safe” copies of the data, and records what it has done and where it currently is, although there is no functionality to stop and start the process. The stored procedure and the table it uses can be found in the file at the bottom of the page, I will go through briefly below what it does.

Given the parameters passed in then the procedure works out if there are any viable partitions to move. Once it has done that it will detach the partition in question and will store it away in the location given in the parameters. As the stored procedure moves faster than DB2 can detach the data you need to use SYSIBMADM.SNAPUTIL a view that will show the progress of the utility, the stored procedures can’t continue till this is done. Once the data is detached and reorg of the indexes will get rid of the last vestiges of the partition and you can run the command to attach the new one in the archive tablespace. Then it reloads the data that was extracted into the new partition in the new tablespace.

When this finishes then you are left with a row in the DB_MAIN.MOVE_PARTITION (assuming you choose to keep it in that schema) and an IXF of the extracted data, that you can write of somewhere as part of a backup strategy.

DISCLAIMER: As stated at the top of the blog use this code in your production systems at your own peril. I have tested and know it works on my systems, please test and check it works on yours properly as moving table data partitions can potentially dangerous. The file is a .doc only as that’s the only way I could get it uploaded onto wordpress, it should open fine like that, or knock the .doc off and it will open in your favourite text editor

FILE WITH CODE IN: DB2-move-data-partitions-sps-dcp

Posted in: DB2, DB2 Administration, DB2 built in tables, DB2 built in Views, DB2 Built-in Stored Procedures, DB2 Maintenance, IBM, Move table partitions, SYSIBM.SYSDATAPARTITIONS, SYSIBMADM.SNAPUTIL, SYSIBMADM.SNAPUTIL, SYSPROC.ADMIN_CMD