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

Tag Archives: Sysproc.admin_cmd

Lazy RUNSTATS using SYSPROC.ADMIN_CMD

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

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: DB2, DB2 Administration, IBM DB2 LUW, Runstats, Stored Procedures, SYSPROC.ADMIN_CMD, Table, update stats, V10.1, V9.7

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

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 2021 - Dangerous DBA
Infinity Theme by DesignCoral / WordPress