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

Category Archives: Stored Procedures

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

DB2 Rebind Stored Procedure – SYSPROC.REBIND_ROUTINE_PACKAGE

July 17, 2012 12:00 pm / Leave a Comment / dangerousDBA

Rebinding DB2 stored procedures in a stored procedure it is possible using SYSPROC.REBIND_ROUTINE_PACKAGE. The following code I originally found at here and it allows you to rebind the stored procedures, I have changed it a little bit to suit my needs. I use this stored procedure as part of a bigger suit of SP’s that I will cover in a future post as I do not trust DB2 automated maintenance and it also does not have the flexibility that I need.

Why rebind Stored procedures ?

When you first run a stored procedure it picks up an execution plan in its package, after that it will not be reassesed until the stored procedure is rebound, dropped and recreated or there is a configuration parameter that can be set (SET CURRENT QUERY OPTIMIZATION). If you have reorganised and runstat’ed your tables then it is all for nothing if your stored procedures will not pick up on the fact that things have changed. Dynamic SQL will obviously pick up on the change.

SYSPROC.REBIND_ROUTINE_PACKAGE

It uses SYSPROC.REBIND_ROUTINE_PACKAGE to carry out the rebinding with a full IBM write up can be found here

The Code

Below is the stored procedure, it takes in a schema name and uses that in a looping query to get all the specific names of the procedures that you wish to rebind, and passes it too SYSPROC.REBIND_ROUTINE_PACKAGE. Error outputs are in the SP failing, but I find it happens rarely, only ever really if something has gone missing (other DB2 objects) in your database that the SP it is trying to rebind needs.


CREATE PROCEDURE DB_MAIN.REBIND_PROCEDURES(IN IN_SCHEMA VARCHAR(128))
MODIFIES SQL DATA NO EXTERNAL ACTION DETERMINISTIC
BEGIN  
FOR thisProc    AS
 SELECT SPECIFICNAME
 FROM SYSCAT.ROUTINES
 WHERE ROUTINESCHEMA = IN_SCHEMA
       AND   ROUTINETYPE = 'P'
       AND   SPECIFICNAME != 'REBIND_PROCEDURES'
       ORDER BY ROUTINENAME  
DO      
CALL SYSPROC.REBIND_ROUTINE_PACKAGE('SP', IN_SCHEMA || '.' || SPECIFICNAME,'CONSERVATIVE');  
END FOR;
END

The alternative

There is an alternative that can be called from the command line: db2rbind. The full IBM Infocenter entry can be found here. It has the ability to rebind whole databases at a time and write a log file about it, but it is not callable from the db itself, unlike an SP. An example of the code you could run is:

db2rbind {dbname} -l {logfile} all

Future Posts

I will look at the pros and cons of using DB2’s inbuilt table maintenance compared too building you own!



Posted in: DB2 Administration, DB2 Built-in Stored Procedures, DB2 Development, IBM DB2 LUW, Rebind, Stored Procedures, SYSPROC.REBIND_ROUTINE_PACKAGE / Tagged: DB2, DB2 Administration, IBM DB2 LUW, Stored Procedures, SYSPROC.REBIND_ROUTINE_PACKAGE

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