How to Rebind a Package Inside a DB2 Stored Procedure

db2stored-procedures

I have a few Stored Procedures for my application (DB2 V 9.7.3). I need to rebind the packages corresponding to the Stored Procedures. I wish to get having them recompiled inside a Stored Procedure by retrieving the package names via a Cursor and rebind the packages one by one.

The command to do is

REBIND PACKAGE <schema>.<pkgName>

To execute this command, I tried the following options but both of them failed.

EXECUTE IMMEDIATE <stmt>;

CALL SYSPROC.ADMIN_CMD(<stmt>);

Is there a way to get this accomplished? Any help would be highly appreciated.

Best Answer

There is a way to rebind the packages via the following routine

db2 "CALL SYSPROC.REBIND_ROUTINE_PACKAGE('P', 'SCHEMA1.PROC1', 'ANY')"
  • P -> stands for Stored Procedure
  • SCHEMA1.PROC1 -> stored procedure name with the schema

  • ANY -> an optional input argument supported for backward compatability. You can use ANY or CONSERVATIVE.

    Please refer these links for more information.

http://www-01.ibm.com/support/docview.wss?uid=swg21250633

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0009863.html

Note: Explicitly rebinding the associated package does not revalidate the routine. Revalidate an invalid routine by using automatic revalidation or explicitly by using the ADMIN_REVALIDATE_DB_OBJECTS procedure. Routine revalidation automatically rebinds the dependent package.

Related Question