Category Archives: Stored Procedures

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

DB2 Rebind Stored Procedure – SYSPROC.REBIND_ROUTINE_PACKAGE

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

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