DB2 Rebind Stored Procedures Automation

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.