Db2 Luw 10.5 - How To Force Db2 Stored Procedures To Use Latest Stats For Most Optimized Plan

We see an issue occasionally. Stored procedure running a SQL runs very slow. Same SQL when run from command line runs very fast. It seems stored procedure uses a different path.

Solution 1:

You probably don't want to recompile plans every time you call your procedure, as you lose the performance benefit of having the procedure in the first place.

When you do need to recompile it, which shouldn't happen too frequently in a stable environment, you can use the REBIND_ROUTINE_PACKAGE system stored procedure:

call SYSPROC.REBIND_ROUTINE_PACKAGE('P', 'YOUR_SP', '')

If you do decide that you want the plan to be recreated each time the procedure is called, you can set the REOPT ALWAYS bind option when you create the procedure, using the many ways described in the manual, for example by executing call SYSPROC.SET_ROUTINE_OPTS('REOPT ALWAYS) before creating the procedure