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

Category Archives: Sysproc.admin_cmd

SYSPROC.ADMIN_CMD – REORG

July 9, 2012 2:03 pm / Leave a Comment / dangerousDBA

I have covered a basic introduction to the SYPROC.ADMIN_CMD and looked at using it for running a RUNSTAT operation on a table and an index, now I am going to look at it for running a REORG command.

The code below will show you an example of a ONLINE table REGORG and an OFFLINE table REORG. There are several GOTCHA’s here at a high level, others exist and can be found here:

GOTCHA’s

1. OFFLINE CLASSIC REORG is quicker, does both Table and Index reorganization, but you cant access your table
2. Once on OFFLINE CLASSIC REORG is started it can’t be stopped. An ONLINE REORG can be stopped and paused, but the job is not done.
3. ONLINE REORG’s do not reorganize your index’s as well that has to be done separately!
4. ONLINE REORG’s can’t take place on a partitioned table, but there are ways and means around that.

SYSPROC.ADMIN_CMD – REORG Code

Much in the same way that the runstats worked you can get the ONLINE REORG to work in a stored procedure like the example below:


CREATE PROCEDURE DB_MAIN.AUTOMATED_REORG_TABLE_ONLINE(IN IN_TABLE_SCHEMA VARCHAR(255),
IN IN_TABLE_NAME VARCHAR(255))
LANGUAGE SQL
BEGIN
DECLARE REORG_STRING VARCHAR(1000);

SET REORG_STRING = 'REORG TABLE ' || TRIM(IN_TABLE_SCHEMA) || '.' || IN_TABLE_NAME || ' INPLACE ALLOW WRITE ACCESS START';

CALL SYSPROC.ADMIN_CMD(REORG_STRING);
END

The above will start an online reorganisation of the table and this can be monitored through a command like db2pd:

db2pd -d {Database Name} -reorg

So in one of the GOTCHA’s above I hinted that you can’t run a REORG on a partitioned table online and it has to be done offline which is true but you can get away with only doing one partition at a time with something like:


CREATE PROCEDURE DB_MAIN.AUTOMATED_REORG_TABLE_PARTITION_OFFLINE(IN IN_TABLE_SCHEMA VARCHAR(255),
IN IN_TABLE_NAME VARCHAR(255),
IN IN_PARTITION_NAME VARCHAR(255))
LANGUAGE SQL
BEGIN
DECLARE REORG_STRING VARCHAR(1000);

SET REORG_STRING = 'REORG TABLE ' || TRIM(IN_TABLE_SCHEMA) || '.' || IN_TABLE_NAME || ' ALLOW NO ACCESS ON DATA PARTITION ' || IN_PARTITION_NAME;

CALL SYSPROC.ADMIN_CMD(REORG_STRING);
END

The data residing inside the partition will still be unavailable but, at least most of the table will still be there and it should be quicker to do just one partition offline than it is to do the whole table. The issue that you have with an offline reorg is that once it is started then you can’t pause it or stop it so make sure your maintenance window is large enough.

Future Post

The examples above should give you some idea on how to do this for your self in you database, but how do you know it needs doing?



Posted in: DB2 Administration, DB2 Built-in Stored Procedures, DB2 Development, IBM DB2 LUW, Reorganisation, Reorganise Indexes, Reorganise Tables, SYSPROC.ADMIN_CMD / Tagged: DB2, DB2 Administration, DB2 Development, IBM DB2 LUW, Reorganisation, Reorganise, Reorganise Indexes, Reorganise Tables, Stored Procedures, SYSPROC.ADMIN_CMD

SYSPROC.ADMIN_CMD – RUNSTATS

July 6, 2012 9:24 pm / 1 Comment / dangerousDBA

As I mentioned last post you can use SYSPROC.ADMIN_CMD to carry out runstats. I originally developed this Stored procedure using SYSPROC.ADMIN_CMD when I had a junior DBA that was new to the administration side of the database game, and therefore they could RUNSTATS without too many issues. Calling a RUNSTATS this way can be very useful in large stored procedure calls in a data-warehouse without having to break up a controlling Stored Procedure.

SYSPROC.ADMIN_CMD – RUNSTATS Code

So you can create a dynamic string to pass into the SYSPROC.ADMIN_CMD using parameters from the stored procedure call that are passed in something like:

RUNSTATS ON TABLE ' || IN_TABLESCHEMA || '.' || IN_TABLENAME || ' ON ALL COLUMNS AND INDEXES ALL ALLOW WRITE ACCESS

Where IN_TABLESCHEMA and IN_TABLENAME are passed into the stored procedure by the user. You can create a stored procedure like:

CREATE PROCEDURE DB_MAIN.RUNSTATS  (IN IN_TABLESCHEMA VARCHAR(100), IN IN_TABLENAME VARCHAR(100))
LANGUAGE SQL
BEGIN
   DECLARE RSSTRING VARCHAR(255);

   SET RSSTRING = 'RUNSTATS ON TABLE ' || IN_TABLESCHEMA || '.' || IN_TABLENAME || ' ON ALL COLUMNS AND INDEXES ALL ALLOW WRITE ACCESS';

   CALL SYSPROC.ADMIN_CMD(RSSTRING);

END

As I said in my last post I have a load of stored procedures in a DB_MAIN schema that allows a database based “installable” schema that can carry out many maintenance tasks. The one above will do a general runstats against everything, I also created a more specific one for RUNSTAT’ing an index only:

CREATE PROCEDURE DB_MAIN.RUNSTATS_INDEX (IN IN_TABLESCHEMA VARCHAR(100), 
                                               IN IN_TABLENAME VARCHAR(100), 
                                               IN IN_INDEX_NAME VARCHAR(255))
LANGUAGE SQL
BEGIN
DECLARE RSSTRING VARCHAR(1000);

SET RSSTRING = 'RUNSTATS ON TABLE ' || IN_TABLESCHEMA || '.' || IN_TABLENAME || ' ON ALL COLUMNS AND INDEXES ' || IN_TABLESCHEMA || '.' || IN_INDEX_NAME || ' ALLOW WRITE ACCESS';


CALL SYSPROC.ADMIN_CMD(RSSTRING);
END

This is obviously superseded by the first example but it will be quicker.

Future Posts

I will look at combining this with other SYSPROC.ADMIN_CMD run commands to perform the 4 R’s of DB2. The 4 R’s are:

  1. Reorganise Tables
  2. Reorganise Index’s
  3. Runstat’s Table / Index’s
  4. Rebind



Posted in: DB2 Administration, DB2 Built-in Stored Procedures, DB2 Development, IBM DB2 LUW, Reorganisation, Reorganise Indexes, Reorganise Tables, Runstats, SYSPROC.ADMIN_CMD, Uncategorized / Tagged: DB2, DB2 Administration, DB2 Development, IBM DB2 LUW, Reorganisation, Reorganise, Reorganise Indexes, Reorganise Tables, Runstats, Stored Procedures, SYSPROC.ADMIN_CMD

SYSPROC.ADMIN_CMD – Start of an adventure

July 4, 2012 2:01 pm / Leave a Comment / dangerousDBA

On my old site I did an post on the SYSPROC.ADMIN_CMD and how you can get it too carry out a runstats of a table. The full list of everything it can do can be found in the IBM DB2 LUW Info center and as the title of this page suggests “ADMIN_CMD procedure – Run administrative commands” it allows you to run administrative tasks that you would normally have to type in manually or use the GUI (Data Studio) for.

Use of SYSPROC.ADMIN_CMD

The command is quite useful in it takes upto a 2Mb string in as a parameter and executes it, it is unuseful in the fact that it has a limited list of commands it will take, e.g. it will not take any of the SIUD (SELECT, INSERT, UPDATE, DELETE) statements which should be eveident from the “administrative commands” part of the description. But equally I can think of many occasions that this would be more useful then PREPARE and EXECUTE for running dynamic commands.

The real power of this command in my opionion is that SYSPROC.ADMIN_CMD allows you to create Stored Procedures in DB2 where you would normally create a bash script. This makes your database administration scritpts a lot more protable and give you the ability to create an “Database Maintenace” (DB_MAIN) Schema that is installable as part of a standard set up.

Standard useage is along the lines of:

CALL SYSPROC.ADMIN_CMD('{Command}')

The SP can be called by anyone who has permissions on it, but the commands issued through the SP by the user, the user has to be of an appropriate level to run them therefore you cant just have any joe (or joanne) running a backup of the database.

Typical of how I use it is along the lines of

CALL SYSPROC.ADMIN_CMD('RUNSTATS ON TABLE {Schema}.{Table Name} ON ALL COLUMNS AND INDEXES ALL ALLOW WRITE ACCESS')

But as you will notice this still requires me to type in a command that would in essence still work at the command line. This brings me onto …..

Future posts

I use it to run things like Runstats, Re-organisations of table and indexes, Exporting and Loading data. In later blog posts I will be looking at how I use this command and how you might be able to as well to run standard, but dynamic administrative commands.



Posted in: DB2 Administration, DB2 Built-in Stored Procedures, DB2 Development, EXPORT, IBM DB2 LUW, LOAD, Reorganisation, Reorganise Indexes, Reorganise Tables, Runstats, SYSPROC.ADMIN_CMD / Tagged: DB2, DB2 Administration, DB2 Development, EXPORT, IBM DB2 LUW, LOAD, Reorganisation, Reorganise, Reorganise Indexes, Reorganise Tables, Runstats, Stored Procedures, SYSPROC.ADMIN_CMD

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