Making copies of a package for a native SQL procedure

When you create a native SQL procedure, a package is implicitly bound with the options that you specified on the CREATE PROCEDURE statement. If the native SQL procedure performs certain actions, you need to explicitly make copies of that package.

About this task

If the native SQL procedure performs one or more of the following actions, you need to create copies of the package for that procedure:

Uses a CONNECT statement to connect to a database server.
Refers to a table with a three part name that includes a location other than the current server or refers to an alias that resolves to such a name.
Sets the CURRENT PACKAGESET special register to control which package is invoked for that version of the procedure.
Sets the CURRENT PACKAGE PATH special register to control which package is invoked for that version of the procedure.

The package for a version of a procedure has the following name: location.collection-id.package-id.version-id where these variables have the following values:

location: Value of the CURRENT SERVER special register
collection-id: Schema qualifier of the procedure
package-id: Procedure name
version-id: Version identifier

To make copies of a package for a native SQL procedure, specify the BIND PACKAGE command with the COPY option. For copies that are created on the current server, specify a different schema qualifier, which is the collection ID. For the first copy that is created on a remote server, you can specify the same schema qualifier. For other copies on that remote server, specify a different schema qualifier.

If you later change the native SQL procedure, you might need to explicitly rebind any local or remote copies of the package that exist for that version of the procedure.

Examples

Example

Because the following native SQL procedure contains a CONNECT statement, you must create a copy of the package at the target server, which in this case is at location SAN_JOSE. The subsequent BIND command creates a copy of the package for version ABC of the procedure TEST.MYPROC. This package is created at location SAN_JOSE and is used by Db2 when this procedure is executed.

CREATE PROCEDURE TEST.MYPROC VERSION ABC LANGUAGE SQL ...
BEGIN
 ...
 CONNECT TO SAN_JOSE
 ...
END

BIND PACKAGE (SAN_JOSE.TEST) COPY(TEST.MYPROC) COPYVER(ABC) ACTION(ADD)

Example

The following native SQL procedure sets the CURRENT PACKAGESET special register to ensure that Db2 uses the package with the collection ID COLL2 for this version of the procedure. Consequently, you must create such a package. The subsequent BIND command creates this package with collection ID COLL2. This package is a copy of the package for version ABC of the procedure TEST.MYPROC. Db2 uses this package to process the SQL statements in this procedure.

CREATE PROCEDURE TEST.MYPROC VERSION ABC LANGUAGE SQL ...
BEGIN
 ...
 SET CURRENT PACKAGESET = 'COLL2'
 ...
END

BIND PACKAGE(COLL2) COPY(TEST.MYPROC) COPYVER(ABC)
                    ACTION(ADD) QUALIFIER(XYZ)