Category Archives: Runstats

Lazy RUNSTATS using SYSPROC.ADMIN_CMD

November 3, 2012 12:00 pm / Leave a Comment /

So if you follow my Twitter @dangerousDBA will know that I will do anything for an easy life, and where I work thee range of DB2 skills is very varied and so making things as simple as possible is always needed. To this end using the SYSPROC.ADMIN_CMD it is possible to make this as simple as possible without knowing all the ins and outs of of the actual command.

This first one then is just a simple runstats that will runstats on all indexes and columns.


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

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

CALL SYSPROC.ADMIN_CMD(RUNSTATSTMT);

END

As you can probably guess DB_MAIN is the schema that I keep all the stored procedures and tables in for maintaining the DB2 databases in. So this is easy for anyone who wants now do a total runstats on any table in the database. The second one that I created is a little more fine grained. This one runstats on all columns, but also only on an index specified so will run a little quicker.

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 RUNSTATSTMT VARCHAR(1000);
SET RUNSTATSTMT = 'RUNSTATS ON TABLE ' || IN_TABLESCHEMA || '.' || IN_TABLENAME || ' ON ALL COLUMNS AND INDEXES ' || IN_TABLESCHEMA || '.' || IN_INDEX_NAME || ' ALLOW WRITE ACCESS';
CALL SYSPROC.ADMIN_CMD(RUNSTATSTMT);
END

There is not a great need to run the statistics on the columns when you are after just the index, but when in Rome. Obviously you can change these to suit your needs and take out the column stats on the index SP.



Posted in: DB2 Administration, DB2 Built in commands, DB2 built in functions, DB2 Built-in Stored Procedures, IBM DB2 LUW, Runstats, Stored Procedures, SYSPROC.ADMIN_CMD, V10 / Tagged: , , , , , , , , ,

db2advis a beginners guide

July 9, 2012 9:51 pm / 1 Comment /

This article was prompted by the fact that the new Express-C version of DB2 uninstalls the much maligned Control Center and installs the IBM Data Studio that so far, after much reading of the reading materials, I struggle to get to run on a VM running 4 CPU’s of an 2GHz i7 Processor and 5Gb of RAM. So after realising that when you try and run the tuning part of of the application unless you have the appropriate licences then it only ever recommends runstats. Unless someone out there know why?

Therefore I needed a different way of running the design advisor to see what DB2 thinks it can do to optimise the query, which left me with my not so favourite environment the command line! So all you need to know about design advisor command (db2advis) with all the different options can be found here at the IBM Infocenter, what I am going to do is take you through what I run most often.

db2advis – The query optimisation command

The command is:

db2advis

At the minimum you need to run

db2advis -d {Database name} -s "{SQL Statement}"

This will then output to the terminal window like this:

Using user id as default schema name. Use -n option to specify schema
execution started at timestamp 2012-06-19-17.31.31.666368
Recommending indexes…
0 indexes in current solution
[974.0000] timerons (without recommendations)
[974.0000] timerons (with current solution)
[0.00%] improvement
—
—
— LIST OF RECOMMENDED INDEXES
— ===========================
— no indexes are recommended for this workload.
—
—
— RECOMMENDED EXISTING INDEXES
— ============================
—
—
— UNUSED EXISTING INDEXES
— ============================
— ===========================
—
— ====ADVISOR DETAILED XML OUTPUT=============
— ==(Benefits do not include clustering recommendations)==
—
–<?xml version=”1.0″?>
–<design-advisor>
–<statement>
–<statementnum>1</statementnum>
–<statementtext>
— SELECT * FROM <SCHEMA>.<TABLE NAME>
–</statementtext>
–<objects>
–<identifier>
–<name><TABLE NAME></name>
–<schema><SCHEMA> </schema>
–</identifier>
–</objects>
–<benefit>0.000000</benefit>
–<frequency>1</frequency>
–</statement>
–</design-advisor>
— ====ADVISOR DETAILED XML OUTPUT=============
—
0 solutions were evaluated by the advisor
DB2 Workload Performance Advisor tool is finished.

On a query that did need optimising then handily the tool will give you an expected improvement and a list of actions to carry out to hopefully achieve this in terms of extra indexes and runstats.

GOTCHA Alerts:

1. The section under “Unused Existing Indexes” normally contains a whole list of drop indexes as it did not use them in the query, do not blindly do this! Please consider what it is suggesting and the obvious ramifications before carrying out these actions.

2. The indexes it suggests for creation under “List of Recommend Indexes” does not take into account for existing indexes that may have say three out of the four columns needed, so rather than create another index increasing your storage footprint, increasing insert and update times, etc then please consider extending existing indexes to the fourth column, rather than creating another index.

3. The names of the indexes suggested for creation can be changed, and I do as they come out with a name that is unintelligible to man nor beast at a later date on what columns might be in there.

4. The Schema of the index recommended to be created will be in whatever scema that is defined what running the command. The assumed default is the user that you are running as, which might not be where you want it.

So now you have run it for the first time then you want to get a little more complicated and you have a really long multi lined statement that you want to tune, well you can do that easily by passing the parameters for the command. You always have to pass the database (-d) parameter, you can then follow it with the -i parameter which tells it that it is taking an input file this can be called anything. This file that you are passing as an input needs to have a ; at the end of the statement. The other thing you can do is output what is normally returned to the terminal to a file using the normal Linux channels. You can limit the time DB2 considers what it is going to recommend by the -t parameter that, this is in minutes. I usually run something like this:

db2advis -d {DATABASE NAME} -i {INPUT FILENAME} -t {TIME (Mins)} > {OUTPUT FILENAME}

GOTCHAS:

1. Make sure if you want to see the improvements any changes you have carried out have made you need to make sure you take a copy of the output file, or call the file something else.

2. The advice that is given out is only for indexes, if you want other advice like MQT’s or MDC’s then you have to specify a further option of -m

db2advis – Extended command

As stated above if you want other advice then you need the -m option then a series of letters. Indexes is assumed but this is I, M recommends new MQT’s that could be used in optimising the query. C will look to see if a MDC or clustering index, finally P will recommend a different field to partition your table over. These can be used like -m IMCP, one or none can be used so your command would look a little like

db2advis -d {DATABASE NAME} -i {INPUT FILENAME} -t {TIME (Mins)} -m {Options M,C,P,I} > {OUTPUT FILENAME}

Some of you may be limited for disk space so the -loption can be quite useful to keep the size down, especially if you have specified the -m M (consider advising MQT’s). Therefore an extended, and most of the options that I usually use would be:

db2advis -d {DATABASE NAME} -i {INPUT FILENAME} -t {TIME (Mins)} -m {Options M,C,P,I} -l {Size in Mb} > {OUTPUT FILENAME}

Or something like I would run

db2advis -d SAMPLE -i TROUBLE_QUERY_IN -t 5 -m ICM -l 1000 > TROUBLE_QUERY_OUT_1

I hope this has been informative and there are loads more options that can be used, full list here.



Posted in: DB2 Administration, db2advis, IBM DB2 LUW, Reorganisation, Reorganise Indexes, Reorganise Tables, Runstats, Uncategorized / Tagged: , , , , , ,

SYSPROC.ADMIN_CMD – RUNSTATS

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

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

SYSPROC.ADMIN_CMD – Start of an adventure

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

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

Automated DB2 Reorganisation, Runstats and Rebinds – Version 2

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

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

Post Navigation

← Older Posts