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.
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.