DB2 Rebind Stored Procedure – SYSPROC.REBIND_ROUTINE_PACKAGE

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!