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