I am back from holiday and I have been reviewing the code that I have put in place for automating the table maintenance and have come up with the first adjustment. As I said before the DB2 system that I manage is a little bit more open than most of you probably have to deal with and so sometimes the stored procedures become invalid for no real “reason”.  So the first adjustment is to only rebind those that are still valid with a “AND VALID = ‘Y’” so it is now:

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'
       AND VALID = 'Y'
       ORDER BY ROUTINENAME  
DO
      CALL SYSPROC.REBIND_ROUTINE_PACKAGE('SP', IN_SCHEMA || '.' || SPECIFICNAME,'CONSERVATIVE');  
END FOR;
END

So this improves on the code from DB2 Rebind Stored Procedures Automation. I did not expect this to be an issue in the first version of the code. Equally I have created myself a little script based around this SQL to find me all the invalid stored procedures and notify me.

SELECT *
FROM SYSCAT.ROUTINES
WHERE VALID = 'N'