DB2 Detach Table Partitions automatically

To aid querying the large tables that DB2 will allow you to create or that you will be creating when you use ESE (Enterprise Server Edition) or one of the extensions for the lesser versions DB2 will enable you to create range partitioned tables. A full starter explanation and examples can be found here so no need to go into it in this article.

Although you can create a table with a number of partitions detaching an old partition is an entirely manual process. As this is not complicated process, but can be time consuming if you have several tables to do, and you need to do it in a safe way.

If you have any kind of data retention policy then eventually “old” data that is in your tables will need detaching at the end of the tables as it has now past its usefulness but may be required in the future to satisfy extraordinary queries. There is a table in DB2 that holds meta data on tables with partitions. This is table is called: SYSIBM.SYSDATAPARTITIONS. This can be used to determine if there are enough partitions to detach the old ones or not.

So to make this process easier to manage I have come up with a process that uses a two user stored procedures, the SYSPROC.ADMIN_CMD, and a table to record information in and so then can be called from either command line, batch script or SQL command editor.

This first stored procedure uses a variable passed in from the main script to determine if there is a partition that could be a candidate for detachment:

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

    --see if the ActualPart and the PartNum are equal or greater
        SET ActualPartNo = (SELECT COUNT(*)
                            FROM SYSIBM.SYSDATAPARTITIONS
                            WHERE LTRIM(RTRIM(TABNAME)) = TABLENAME
                                AND LTRIM(RTRIM(TABSCHEMA)) = TABLESCHEMA);

    --See if the number of partions on the table is the the same or greater than the
    --specified number
        IF(ActualPartNo > PARTITIONNUM) THEN
            SET PARTOBJID = (SELECT PARTITIONOBJECTID
                            FROM SYSIBM.SYSDATAPARTITIONS
                            WHERE LTRIM(RTRIM(TABNAME)) = TABLENAME
                                AND LTRIM(RTRIM(TABSCHEMA)) = TABLESCHEMA
                            ORDER BY DATAPARTITIONNAME ASC
                            FETCH FIRST ROW ONLY);
        END IF;           

    --Return the
        RETURN PARTOBJID;
END

This script returns the partition object ID if there is a partition that meets the criteria specified from the main procedure. If no partition meets the criteria, e.g. you pass in 10 and the table only has 8 partitions then a 0 will be returned and no ID will be passed to the outer procedure.

So onto the main procedure:

CREATE PROCEDURE DB_MAIN.DETACH_PARTITION(IN TABLESCHEMA VARCHAR(255), IN TABLENAME VARCHAR(255),IN PARTITIONNUM INT, IN EXPORTDIR VARCHAR(255))
LANGUAGE SQL
BEGIN
--Declare vars for use in SP
	DECLARE Partition_problem condition FOR SQLSTATE '99999';
	DECLARE DttActPartNo INT DEFAULT 0;
	DECLARE DttPartName varchar(150);
	DECLARE DttSQL Varchar(300) DEFAULT 'No Dont do it';
	DECLARE ReorgString VARCHAR(500);
	DECLARE ExportString VARCHAR(500);

--Find If there is a partition to detach
	CALL DB_MAIN.GET_MIN_PARTITION(TABLESCHEMA,TABLENAME,PARTITIONNUM,DttActPartNo);

	 IF(DttActPartNo <> 0) THEN
		--Get the name of the partition
		SET DttPartName = (SELECT DATAPARTITIONNAME
		       FROM SYSIBM.SYSDATAPARTITIONS
			   WHERE PARTITIONOBJECTID = DttActPartNo
			AND LTRIM(RTRIM(TABNAME)) = TABLENAME
			AND LTRIM(RTRIM(TABSCHEMA)) = TABLESCHEMA);

		--Build dynamic SQL to Detach and create a table of the partition
		SET DttSQL = 'ALTER TABLE ' || TABLESCHEMA || '.' || TABLENAME || ' DETACH PARTITION ';
		SET DttSQL = DttSQL || DttPartName || ' INTO ' || TABLESCHEMA || '.' || DttPartName;

		IF((DttSQL <> '') AND (DttSQL <> 'No Dont do it'))THEN
			--Write the table date Etc to Logging table
			INSERT INTO DB_MAIN.DETACHPARTITIONS(
				TABLESCHEMA,
				TABLENAME,
				DETACHDATE,
				DETACHTABLESCHEMA,
				DETACHTABLENAME,
				DETACHCODE
			)
			VALUES(
				TABLESCHEMA,
				TABLENAME,
				CURRENT DATE,
				TABLESCHEMA,
				DttPartName,
				DttSQL
			);			

	COMMIT; 

			--Run the code
			PREPARE S1 FROM DttSQL;
	EXECUTE S1;

	--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);   

	--Create the Export
	    --Create the string
	    SET ExportString = 'EXPORT TO ' || EXPORTDIR || '/' || TABLESCHEMA || '_' || DttPartName || '.tsv OF DEL MODIFIED BY CHARDEL"" COLDEL0x09 DATESISO SELECT * FROM ' || TABLESCHEMA || '.' || DttPartName;
	    --Run the command
	    CALL SYSPROC.ADMIN_CMD(ExportString);

		END IF;
ELSE
    INSERT INTO DB_MAIN.DETACHPARTITIONS(
	TABLESCHEMA,
	TABLENAME,
	DETACHDATE,
	ERRORTEXT
    )
    VALUES (TABLESCHEMA,
		TABLENAME,
		CURRENT DATE,
	    'This table does not have that many partitions. Attempted:' || CHAR(PARTITIONNUM)
	    );

END IF;
END

The stored procedure takes four variables, table schema (TABLESCHEMA), table name (TABLENAME), the number of partitions you wish the table to have (PARTITIONNUM) and the directory on the server where the IXF of the detached partition (EXPORTDIR). The procedure works out from the parameters if the table (TABLESCHEMA . TABLENAME ) has the same amount or more partitions then the PARTITIONNUM parameter, if it does then the partition will be detached and an IXF file of the partition will be created at the EXPORTDIR location.

There is one GOTCHA is that if you have called your partitions all the same things across different tables in the same schema, then you will need to edit this code slightly to take account of this and differentiate the both the tables that are created and IXF file that are exported. The reason I mention this is that when you create a partitioned table normally if you do not specify the names of the partitions then DB2 will create them like PART0, PART1, PART2. Using this code the schema and the name would be the same and lead to conflicts.

This allows the stored procedure to detach the partition that is needed to be archived and create an IXF and a detached partition table. No table or data is deleted automatically, this means you can make sure that the data you need in the IXF is archived in your chosen way before deleting the detached partition table. As you can see the solution also uses a table to record what has been done for auditing purposes. If there are not enough partitions the auditing table will record the fact and no detaching will take place. The reorg is needed as the indexes on the table that has just had the partition remove will not work properly till this is done.

The procedure then be called as per the code below, via your favourite method for automating tasks:

CALL DB_MAIN.DETACH_PARTITION('INSURANCE', 'TRANSACTIONS',10, '/home/db2inst1/detach-archive/')

Please note the trailing slash is needed in the directory path.

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 detaching 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_Detach_Partitions_Tables_Sps_DCP