How to rebind packages in DB2?

by | Nov 8, 2006

Whenever you update statistics in your tables or modify any of following configuration parameters or bind options, you need to rebind the packages for updating the new access paths of the SQLs.

Configuration Parameters that affect execution of SQL queries

  • AVG_APPLS
  • SORTHEAP
  • LOCKLIST
  • MAXLOCKS
  • NUM_FREQVALUES
  • NUM_QUANTILES
  • LOCKS_AVAIL
  • DBHEAP
  • CPUSPEED
  • BUFFPAGE

Bind Options that affect execution of SQL queries

  • QUERYOPT
  • ISOLATION
  • BLOCK

Create this Stored Procedure in your database and execute this whenever you want to rebind your packages.

Note: The code for this stored procedure comes from this article.


--#SET TERMINATOR @
DROP SPECIFIC PROCEDURE REBINDPROCEDURES
@
CREATE PROCEDURE REBINDPROCEDURES(IN schema VARCHAR(128))
SPECIFIC REBINDPROCEDURES
MODIFIES SQL DATA NO EXTERNAL ACTION DETERMINISTIC
BEGIN
  FOR thisProc 
   AS SELECT specificname FROM SYSCAT.ROUTINES
       WHERE routineschema = schema
       AND   routinetype = 'P'
       AND   specificname != 'REBINDPROCEDURES'
       ORDER BY routinename
  DO  
    CALL REBIND_ROUTINE_PACKAGE('SP', schema || '.' || specificname,
                                'CONSERVATIVE');
  END FOR;
END
@