DB2 Handle Table Partitioning

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