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

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

One Thought on “SYSPROC.ADMIN_CMD – RUNSTATS”

  1. Pingback: How to run administrative commands using Stored Procedures (using database connection to send CLP commands) | How to rule the DB2!

Leave a Reply Cancel reply

Post Navigation

← Previous Post
Next Post →

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