• About Dangerous DBA
  • Table of Contents
Dangerous DBA A blog for those DBA's who live on the edge

DB2 Attach Table Partitions Automatically

May 30, 2011 2:04 pm / Leave a Comment / dangerousDBA

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

Posted in: Attach Partitions, DB2, DB2 Administration, DB2 built in tables, DB2 Built-in Stored Procedures, DB2 Maintenance, IBM, SYSIBM.SYSDATAPARTITIONS, SYSPROC.ADMIN_CMD

Leave a Reply Cancel reply

Post Navigation

← Previous Post
Next Post →

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 757 other subscribers

Recent Posts

  • Self generating Simple SQL procedures – MySQL
  • Google Cloud Management – My Idea – My White Whale?
  • Position Tracker – The Stub – Pandas:
  • Position Tracker – The Stub
  • Position Tracker – In the beginning
  • Whats been going on in the world of the Dangerous DBA:
  • QCon London Day 1
  • Testing Amazon Redshift: Distribution keys and styles
  • Back to dangerous blogging
  • DB2 10.1 LUW Certification 611 notes 1 : Physical Design

Dangerous Topics

added functionality ADMIN_EST_INLINE_LENGTH Amazon Bootcamp colum convert data types db2 DB2 DB2 Administration DB2 Development db2advis db2licm Decompose XML Exam EXPORT Google IBM IBM DB2 LUW idug information centre infosphere LOAD merry christmas and a happy new year Recursive Query Recursive SQL Redshift Reorganisation Reorganise Reorganise Indexes Reorganise Tables Runstats sqlcode sql statement Stored Procedures SYSPROC.ADMIN_CMD Time UDF User Defined Functions V9.7 V10.1 Varchar XML XML PATH XMLTABLE

DangerousDBA Links

  • DB2 for WebSphere Commerce
  • My Personal Blog

Disclaimer:

The posts here represent my personal views and not those of my employer. Any technical advice or instructions are based on my own personal knowledge and experience, and should only be followed by an expert after a careful analysis. Please test any actions before performing them in a critical or nonrecoverable environment. Any actions taken based on my experiences should be done with extreme caution. I am not responsible for any adverse results. DB2 is a trademark of IBM. I am not an employee or representative of IBM.

Advertising

© Copyright 2021 - Dangerous DBA
Infinity Theme by DesignCoral / WordPress