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

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

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