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

DB2 Rebind Stored Procedures Automation

February 16, 2011 9:00 pm / 2 Comments / dangerousDBA

As part of the ongoing theme of automating the maintenance of my DB2 servers I needed some way of after Automatic Reorganisation of tables , Automatic Reorganisation of indexes and Automatically running statistics of rebinding the stored procedures to pick up potentially new execution plans.

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.

The following code I know I found here and it allows you to rebind the stored procedures. It uses REBIND_ROUTINE_PACKAGE to carry out the rebinding with a full IBM write up can be found here:

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

In my next post after my holiday I will go through how I combine this post and my previous three posts to do weekly maintenance.

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.

Posted in: DB2, DB2 Administration, DB2 Built-in Stored Procedures, DB2 Maintenance, IBM, Rebind Stored Procedure, SYSPROC.REBIND_ROUTINE_PACKAGE

2 Thoughts on “DB2 Rebind Stored Procedures Automation”

  1. Ремонт Acura on February 24, 2011 at 11:59 pm said:

    Really enjoyed this! Well done!

    Reply↓
  2. David on August 31, 2011 at 8:42 am said:

    Hi,

    It wouldn’t be the same if I execute…
    $ db2rbind dbname -l logfile all

    Otherwise, How I can execute that in cron?

    Thanks in advance.

    David.

    Reply↓

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 Amazon Bootcamp colum convert data types db2 DB2 DB2 Administration DB2 Development db2advis db2licm Decompose XML Exam EXPORT Google IBM IBM DB2 LUW idug information centre infosphere LOAD merry christmas and a happy new year Recursive Query Recursive SQL Redshift Reorganisation Reorganise Reorganise Indexes Reorganise Tables Runstats sqlcode 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