Restrictions when calling other programs from an external stored procedure

An external procedure can consist of more than one program, each with its own package. Your stored procedure can call other programs, stored procedures, or user-defined functions. Use the facilities of your programming language to call other programs.

If the stored procedure calls other programs that contain SQL statements, each of those called programs must have a Db2 package. The owner of the package or plan that contains the CALL statement must have EXECUTE authority for all packages that the other programs use.

When a stored procedure calls another program, Db2 determines which collection the package of the called program belongs to in one of the following ways:
  • If the stored procedure definition contains PACKAGE PATH with a specified list of collection IDs, Db2 uses those collection IDs. If you also specify COLLID, Db2 ignores that clause.
  • If the stored procedure definition contains COLLID collection-id, Db2 uses collection-id.
  • If the stored procedure executes SET CURRENT PACKAGE PATH and contains the NO COLLID option, Db2 uses the CURRENT PACKAGE PATH special register. The package of the called program comes from the list of collections in the CURRENT PACKAGE PATH special register. For example, assume that CURRENT PACKAGE PATH contains the list COLL1, COLL2, COLL3, COLL4. Db2 searches for the first package (in the order of the list) that exists in these collections.
  • If the stored procedure does not execute SET CURRENT PACKAGE PATH and instead executes SET CURRENT PACKAGESET, Db2 uses the CURRENT PACKAGESET special register. The package of the called program comes from the collection that is specified in the CURRENT PACKAGESET special register.
  • If both of the following conditions are true, Db2 uses the collection ID of the package that contains the SQL statement CALL:
    • the stored procedure does not execute SET CURRENT PACKAGE PATH or SET CURRENT PACKAGESET
    • the stored procedure definition contains the NO COLLID option

    When control returns from the stored procedure, the value of the CURRENT PACKAGESET special register is reset.Db2 restores the value of the CURRENT PACKAGESET special register to the value that it contained before the client program executed the SQL statement CALL.