Category Archives: Attach Partitions

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

DB2 Attach Table Partitions Automatically

This follows on from my last post on detaching DB2 table partitions that can be found here. After you have detached a partition, more than likely you are going to need to attach one. Using the code here you can do this automatically for range partitioned tables that work on ranges based on dates, but it could easily be adapted to work on any number of different data type ranges.

Attaching a partition like detaching is an entirely manual process after the initial creation of the table. The code I have created allows you too just call a simple command below and it will add the next partition for you without having to look at what the next range is. In this case it is the next 3 months.

CALL DB_MAIN.ATTACH_PARTITION('INSURANCE','TRANSACTIONS','INSURANCE_TS')

So the code is pretty simple it is just a wrapper around the commands that you would normally have to run to add a partition, plus with the added bonus it works out what the next range is and records the work that it has done.

First it works out what is the maximum partition so that it can create the new one:

CREATE PROCEDURE DB_MAIN.GET_MAX_PARTITION(IN TABLESCHEMA VARCHAR(255), IN TABLENAME VARCHAR(255), OUT PARTOBJID INT)
LANGUAGE SQL
BEGIN
    --Decalre Vars for use
        DECLARE Task_problem condition FOR SQLSTATE '99999';

            SET PARTOBJID = (SELECT PARTITIONOBJECTID
                            FROM SYSIBM.SYSDATAPARTITIONS
                            WHERE LTRIM(RTRIM(TABNAME)) = TABLENAME
                                AND LTRIM(RTRIM(TABSCHEMA)) = TABLESCHEMA
                            ORDER BY DATAPARTITIONNAME DESC
                            FETCH FIRST ROW ONLY);                    

    --Return the
        RETURN PARTOBJID;
END

This is then used to work out what the next partition values for high and low should be. Next is the main procedure itself, it uses a calendar table the standard attach command with inclusive start and end values and an index reorganisation at the end. The reorganisation is needed so that the new partition is recognised by the indexes. All the code and definition for the calendar table are in the file available at the bottom of this post.

CREATE PROCEDURE DB_MAIN.ATTACH_PARTITION(IN TABLESCHEMA VARCHAR(255), IN TABLENAME VARCHAR(255), IN NEWTABLESPACE VARCHAR(20))
LANGUAGE SQL
BEGIN
--Declare vars for use in SP
DECLARE Task_problem condition FOR SQLSTATE '99999';
DECLARE AttActPartNo INT;
DECLARE AttCurHighVal DATE;
DECLARE AttQuarterVal CHAR;
DECLARE AttLastDateQu DATE;
DECLARE AttTableSpace varchar(20);
DECLARE AttSQL Varchar(500) DEFAULT 'No Dont do it';
DECLARE YearAttLastDateQu VARCHAR(4);
DECLARE ReorgString VARCHAR(500);

--Get the Latest Partition in Existance for the table
	CALL DB_MAIN.GET_MAX_PARTITION(TABLESCHEMA,TABLENAME,AttActPartNo);

--Create the new Lower value for the Partition and the Quarter
--Get the current High value
	SET AttCurHighVal = (SELECT DATE(SUBSTR(HIGHVALUE,10,2) || '/' || SUBSTR(HIGHVALUE,7,2) || '/' || SUBSTR(HIGHVALUE,2,4)) + 1 DAY
		             FROM SYSIBM.SYSDATAPARTITIONS
		             WHERE PARTITIONOBJECTID = AttActPartNo
                                AND LTRIM(RTRIM(TABNAME)) = TABLENAME
                                AND LTRIM(RTRIM(TABSCHEMA)) = TABLESCHEMA);

--Get the Quarter val
        SET AttQuarterVal = (SELECT CHAR(Quarter_of_year)
                             FROM GLOBAL.CALENDAR
                             WHERE CALENDAR_DATE = AttCurHighVal);

--Get the last date in month
	SET AttLastDateQu = (SELECT Last_Date_of_Quarter
                             FROM GLOBAL.CALENDAR
                             WHERE CALENDAR_DATE = AttCurHighVal);

--Set the year for the partition
	SET YearAttLastDateQu = CHAR(YEAR(AttLastDateQu));

--Build the SQL to attach a new section
	SET AttSQL = 'ALTER TABLE ' || TABLESCHEMA || '.' || TABLENAME || ' ADD PARTITION ';
        SET AttSQL = AttSQL || TABLENAME || '_' || YearAttLastDateQu || '_Q' || AttQuarterVal;
	SET AttSQL = AttSQL || ' STARTING FROM (''' || CAST(AttCurHighVal as varchar(10)) || ''')';
        SET AttSQL = AttSQL || ' ENDING AT (''' ||  CAST(AttLastDateQu as varchar(10)) || ''')';
        SET AttSQL = AttSQL || ' IN ' || NEWTABLESPACE; 

	IF(AttSQL <> '')THEN
	--Insert into the logging table
		INSERT INTO DB_MAIN.ATTACHPARTITIONS(
			TABLESCHEMA,
			TABLENAME,
			ATTACHDATE,
			ATTACHTABLESCHEMA,
			ATTACHTABLENAME,
			ATTACHCODE
		)
		VALUES(
			TABLESCHEMA,
			TABLENAME,
			CURRENT DATE,
			TABLESCHEMA,
			TABLENAME,
			AttSQL
		);

        COMMIT;

--Execute the code
	PREPARE S2 FROM AttSQL;
        EXECUTE S2;
--Reorg the table
  --Create the string
        SET ReorgString = 'REORG INDEXES ALL FOR TABLE '  || TABLESCHEMA || '.' || TABLENAME || ' ALLOW NO ACCESS CLEANUP ONLY';

  --Run the command
        CALL SYSPROC.ADMIN_CMD(ReorgString);
END IF;
END
GO

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 attaching 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 Attach Table Partitions Tables SPs