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

Category Archives: Sysibm.sysdatapartitions

Automated DB2 Reorganisation, Runstats and Rebinds – Version 2

December 11, 2011 8:56 pm / 1 Comment / dangerousDBA

A while back I did the first version of this code (can be found here). Over time I have been running this code on our production servers, it started out by working fine but sometimes it would over run and interfere with the morning batch, so a different solution was needed. In a previous article I discussed if it was better to let the included automated DB2 functionality take care of the maintenance of tables etc, or to create your own process that uses included stored procedures to identify the tables that need reorganising.

So this new version of the script will only work between certain times and only do offline reorganisations, but is still possible to just reorganise a single partition of a range partitioned table. The reason for the time restriction is to take a leaf from the included automated scripts having an offline maintenance window, and to stop the scripts that I have created before overrunning into the morning batch. The previous version of the reorganisation script attempted to be to “clever” and do an online reorg of non partitioned tables and an offline reorg of the partitions of the range partitioned tables. The problem with this is that capturing when the online reorgs have finished (as they are asynchronous), so that the table can have it statistics run so that it is not identified again by the SYSPROC.REORGCHK_TB_STATS stored procedure. Equally another issue is that you would have to reorganise the index’s on the tables that you have on-line reorganised as they would not have been done, where as an offline reorganisation also does the indexes at the same time.

So I made the decision to do all the reorganisations offline, followed by a runstats and a rebind. The main controlling stored procedure looks like:

CREATE PROCEDURE DB_MAIN.RUN_ALL_AUTOMATED_MAINTENANCE(IN MAINT_SCHEMA VARCHAR(255), IN REORG_FINISH_TIME TIME, IN RUNSTATS_FINISH_TIME TIME, IN DAY_TO_REMOVE INTEGER)
LANGUAGE SQL
BEGIN
 ----------------------------------------------------------------------------
 ----------------------------------------------------------------------------
 --This procedure is the wrapper for all the rest to tidy it up a little bit.
 --It will only run the reorgs tille the time specified, then will just finish the one
 --that it is on once the time has expired.
 --Similar thing for the runstats so that it does not impact on the running of the
 --morning loads.
 --Rebind the procedures so that they get new packages based on the updated statistics
 --from the reorg and runstats.
 --All Reorg done off line as this is what DB2 does.
 --MAINT_SCHEMA = The schema you wish to be looked at
 --REORG_FINISH_TIME = The time you wish the reorgs to run until
 --RUNSTATS_FINISH_TIME = The time you wish runstats to run till
 --DAY_TO_REMOVE = The number of day back you wish staging tables to be emptied from
 ----------------------------------------------------------------------------
 ----------------------------------------------------------------------------

 ----------------------------------------------------------------------------
 ----------------------------------------------------------------------------
 --Reorg the tables
 CALL DB_MAIN.RUN_AUTOMATED_TABLE_REORG(MAINT_SCHEMA, REORG_FINISH_TIME, DAY_TO_REMOVE);
----------------------------------------------------------------------------
 ----------------------------------------------------------------------------
 --Runstat the tables that have been reorged
 CALL DB_MAIN.RUN_AUTOMATED_TABLE_RUNSTATS(MAINT_SCHEMA, RUNSTATS_FINISH_TIME,DAY_TO_REMOVE);
----------------------------------------------------------------------------
 ----------------------------------------------------------------------------
 --Rebind the stored procedures to take advantage of the potentially new plans
 CALL DB_MAIN.RUN_AUTOMATED_REBIND_PROCEDURES(MAINT_SCHEMA);

END

This is now a three stage operation, the first two stages have time limits and so they will carry out new operations until this time limit is breached. What you have to realise here is that if the end time is 18:00:00 then it will start work right up until 17:59:59, this means if it picks up a particularly large reorganisation task at this last second then it will run till it has finished.

Some of the code especially the runstats stuff is quite a lot like the previous version just with a change for the time. As I cant upload a single .zip file as apparently it will be a security risk, and apparently a .sql file is also a risk please find a number of .doc files a the bottom of the article. Please just change the file extension and then you will be able to access them. I would very interested in having feedback from anyone who uses this code to see how you get on with it.

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 reorganising tables can potentially dangerous.

FILES WITH CODE IN:

OverallRunnerStoredProcedure

ReorganiseTablesStoredProcedures

ReorganiseTableTables

ReorganiseTableViews

RunstatsTableTables

RunstatsTableViews

RunstatsTableStoredProcedures

RebindSchemaStoredProcedure

Posted in: DB2, DB2 Administration, DB2 built in tables, DB2 built in Views, DB2 Built-in Stored Procedures, DB2 Maintenance, IBM, Rebind Stored Procedure, Reorg Index, Reorg Table, Reorganise Index, Runstats, SYSIBM.SYSDATAPARTITIONS, SYSIBM.SYSTABLES, SYSIBMADM.SNAPUTIL, SYSPROC.ADMIN_CMD, SYSPROC.ADMIN_CMD, SYSPROC.REBIND_ROUTINE_PACKAGE, SYSPROC.REORGCHK_IX_STATS, SYSPROC.REORGCHK_TB_STATS

DB2 Move Table Partitions Automatically

June 4, 2011 2:08 pm / Leave a Comment / dangerousDBA

One of the hot topics in databases at the moment is temperature based data. This is basically where you put the data that is most often access on fast disks, and data that is accessed least often is put on slower disks. To accomplish this is pretty easy as you can set the containers for an “archive” tablespace to a different disk / directory mount point. This does similar things to my two previous posts on attaching table partitions and detaching table partitions

The problem comes when you already have data in a table and the table partition already is part of a tablespace, you cant just alter the partition to the new tablespace. You have to identify the table partition to move, detach it recreate it in the new tablespace and then reload the data. You can do this manually, but who has time to do this, I have written code that will allow you to move the tablespace of the partition quite easily, and can be part of an automated process. The stored procedure call looks a little like this:

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

The stored procedure that I have created deals with all of this and creates “safe” copies of the data, and records what it has done and where it currently is, although there is no functionality to stop and start the process. The stored procedure and the table it uses can be found in the file at the bottom of the page, I will go through briefly below what it does.

Given the parameters passed in then the procedure works out if there are any viable partitions to move. Once it has done that it will detach the partition in question and will store it away in the location given in the parameters. As the stored procedure moves faster than DB2 can detach the data you need to use SYSIBMADM.SNAPUTIL a view that will show the progress of the utility, the stored procedures can’t continue till this is done. Once the data is detached and reorg of the indexes will get rid of the last vestiges of the partition and you can run the command to attach the new one in the archive tablespace. Then it reloads the data that was extracted into the new partition in the new tablespace.

When this finishes then you are left with a row in the DB_MAIN.MOVE_PARTITION (assuming you choose to keep it in that schema) and an IXF of the extracted data, that you can write of somewhere as part of a backup strategy.

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 moving table data 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-move-data-partitions-sps-dcp

Posted in: DB2, DB2 Administration, DB2 built in tables, DB2 built in Views, DB2 Built-in Stored Procedures, DB2 Maintenance, IBM, Move table partitions, SYSIBM.SYSDATAPARTITIONS, SYSIBMADM.SNAPUTIL, SYSIBMADM.SNAPUTIL, SYSPROC.ADMIN_CMD

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

DB2 Detach Table Partitions automatically

May 15, 2011 5:36 pm / Leave a Comment / dangerousDBA

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

Posted in: DB2, DB2 Administration, DB2 built in tables, DB2 Built-in Stored Procedures, Detach table Partition, Reorg Index, SYSIBM.SYSDATAPARTITIONS, SYSPROC.ADMIN_CMD, SYSPROC.ADMIN_CMD

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 Bootcamp colum convert data types DB2 db2 DB2 Administration DB2 Development db2advis db2licm Decompose XML EXPORT GCP Google IBM IBM DB2 LUW idug information centre infosphere IOT LOAD merry christmas and a happy new year Position Tracking python Recursive Query Recursive SQL Reorganisation Reorganise Reorganise Indexes Reorganise Tables Runstats 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 2022 - Dangerous DBA
Infinity Theme by DesignCoral / WordPress