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'