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