BIND PACKAGE subcommand (DSN)

The DSN subcommand BIND PACKAGE builds an application package. Db2 records the description of the package in the catalog tables and saves the prepared package in the directory. Start of change BIND PACKAGE also deletes phased-out package copies. End of change

Environment for BIND PACKAGE

You can use BIND PACKAGE from DB2I, or from a DSN session under TSO that runs in either the foreground or background.

Data sharing scope: Group

Authorization for BIND PACKAGE

The package owner must have authorization to execute all statements embedded in the package for BIND PACKAGE to build a package without producing error messages. The SYSADM authority and the DATAACCESS authority includes this authorization.

For VALIDATE(BIND), Db2 verifies the authorization at bind time, with the exception of the LOCK TABLE statement, and some CREATE, ALTER, and DROP statements. For those SQL statements, Db2 verifies the authorization at run time.

For VALIDATE(RUN), Db2 verifies the authorization initially at bind time, but if the authorization check fails, Db2 rechecks it at run time.

Start of change The required authorization to add a new package or a new version of an existing package depends on the value of subsystem parameter BINDNV. The default value is BINDADD. End of change

The package owner must be a role to execute BIND PACKAGE in a trusted context with role ownership. If performing a bind in a trusted context that has a role-as-object owner, then the owner of the package will be a role. If OWNER is specified, then it is assumed to be a role. If it is not specified, then the role of the binder becomes the owner.

To specify the option SQLERROR(CHECK), the binder must have the BIND, BINDAGENT, or EXPLAIN privilege.

To specify the option EXPLAIN(ONLY), the binder must have the EXPLAIN privilege.

The following table summarizes the required authorization to run BIND PACKAGE, depending on the bind options that you specify, and in the case of the ADD option, the value of installation panel field BIND NEW PACKAGE.

Table 1. Summary of privileges needed for BIND PACKAGE options
Bind option	Installation panel field BIND NEW PACKAGE (BINDNV subsystem parameter)	Authorization required to run BIND PACKAGE
ADD, using the default owner or primary authorization ID	BINDADD	The primary authorization ID or role must have one of the following to add a new package or new version of an existing package to a collection: The BINDADD system privilege and either the CREATE IN privilege or PACKADM authority on the collection or on all collections SYSADM, SYSCTRL, or system DBADM authority
ADD, using the default owner or primary authorization ID	BIND	The primary authorization ID or role must have one of the following to add a new package or a new version of an existing package to a collection: The BINDADD system privilege and either the CREATE IN privilege or PACKADM authority on the collection or on all collections SYSADM, SYSCTRL, or system DBADM authority PACKADM authority on the collection or on all collections The BIND package privilege (can only add a new version of an existing package)
ADD, specifying an OWNER other than the primary authorization ID¹	BINDADD	If any of the authorization IDs or roles of the process has SYSADM authority, SYSCTRL authority, or system DBADM authority, OWNER authorization-id can be any value, when subsystem parameter SEPARATE_SECURITY is set to NO. If any of the authorization IDs has the BINDAGENT privilege granted from the owner, authorization-id can specify the grantor as OWNER. Otherwise, the OWNER authorization-id must be one of the primary or secondary authorization IDs of the binder. If you specify OWNER authorization-id , Db2 first checks the OWNER and then the binder for the necessary bind privilege. If the binder does not have SYSADM, SYSCTRL, or system DBADM authority, the authorization ID or role of the OWNER must have one of the following to add a new package or new version of an existing package to a collection: The BINDADD system privilege and either the CREATE IN privilege or PACKADM authority on the collection or on all collections SYSADM, SYSCTRL, or system DBADM authority
ADD, specifying an OWNER other than the primary authorization ID¹	BIND	If any of the authorization IDs or roles of the process has SYSADM authority, SYSCTRL authority, or system DBADM authority, OWNER authorization-id can be any value, when subsystem parameter SEPARATE_SECURITY is set to NO. If any of the authorization IDs has the BINDAGENT privilege granted from the owner, authorization-id can specify the grantor as OWNER. Otherwise, the OWNER authorization-id must be one of the primary or secondary authorization IDs of the binder. If you specify OWNER authorization-id , Db2 first checks the OWNER and then the binder for the necessary bind privilege. If the binder does not have SYSADM, SYSCTRL, or system DBADM authority, the authorization ID or role of the OWNER must have one of the following to add a new package or new version of an existing package to a collection: The BINDADD system privilege and either the CREATE IN privilege or PACKADM authority on the collection or on all collections SYSADM, SYSCTRL, or system DBADM authority PACKADM authority on the collection or on all collections The BIND package privilege (can only add a new version of an existing package)
REPLACE, using the default owner or primary authorization ID	BINDADD or BIND	Primary authorization ID or role must have one of the following: Ownership of the package BIND privilege on the package. If the package does not exist, see the authorization that is required for "ADD, using the default owner or primary authorization ID." PACKADM authority on the collection or on all collections SYSADM, SYSCTRL, or system DBADM authority
REPLACE, specifying an OWNER other than the primary authorization ID¹	BINDADD or BIND	If any of the authorization IDs or roles of the process has SYSADM authority, SYSCTRL authority, or system DBADM authority, OWNER authorization-id can be any value, when subsystem parameter SEPARATE_SECURITY is set to NO. If any of the authorization IDs has the BINDAGENT privilege granted from the owner, authorization-id can specify the grantor as OWNER. Otherwise, the OWNER authorization-id must be one of the primary or secondary authorization IDs of the binder. If you specify OWNER authorization-id , Db2 first checks the OWNER and then the binder for the necessary bind privilege. If the binder does not have SYSADM or SYSCTRL or system DBADM authority, the authorization ID or role of the OWNER must have one of the following: BIND privilege on the package. If the package does not exist, the authorization that is required is the same as for ADD, when an OWNER other than the primary authorization ID is specified. BINDAGENT privilege from the current owner of the package. PACKADM authority on the collection or on all collections SYSADM, SYSCTRL, or system DBADM authority
COPY	BINDADD or BIND	The primary or secondary authorization ID or role of the binder or OWNER must have one of the following on the package being copied: Ownership of the package COPY privilege on the package BINDAGENT privilege from the owner of the package PACKADM authority on the collection or on all collections SYSADM, SYSCTRL, or system DBADM authority
Note: If both the OWNER and the binder do not have the necessary bind privilege and the IFCID 140 trace is active, a trace record is written with details about the authorization failure.

Syntax

Notes:

¹ The default for a local package is the plan value. The default for a remote package is CS.

member-block

enable-block

acceleration-block

dynamicrules-block

encoding-block

reopt-block

Notes:

¹ NOREOPT(VARS) can be specified as a synonym of REOPT(NONE)
² REOPT(VARS) can be specified as a synonym of REOPT(ALWAYS)

Descriptions for BIND PACKAGE

The following options identify the package to bind. You can identify the location and collection ID. The DBRM supplies the package ID and version ID if you use the MEMBER option. See MEMBER bind option. Otherwise, these IDs come from the COPY option. See COPY bind option

PACKAGE(location-name)

The location of the DBMS where the package binds and where the description of the package resides. The location name must be defined in catalog table SYSIBM.LOCATIONS. If that table does not exist or if the DBMS is not in it, you receive an error message. See LOCATIONS catalog table.

The default is the local DBMS.

PACKAGE(collection-id)

Specifies the collection to contain the package to bind. There is no default value.

collection-id can be an undelimited or delimited identifier. The delimiter for collection-id is double quotation marks ("). If collection-id is delimited, Db2 does not convert the value to uppercase.

Other options for BIND PACKAGE

For descriptions of the other options in the syntax diagram, see BIND and REBIND options for packages, plans, and services.

Usage notes for BIND PACKAGE

Trace information for data sharing members: When this command with group scope is issued in a Db2 data sharing member, it also runs on all other active members. IFICID 090 trace records for other group members can show that the same command was issued by the SYSOPR authorization ID from the 016.TLPKN5F correlation ID, in addition to the trace records from the member where the original command was issued. See Command scope in Db2 data sharing.

Examples for BIND PACKAGE

Example: Replacing a version of a package

The following command replaces version APRIL_VERSION of package TEST.DSN8BC12 at local location USIBMSTODB22 with another version of the package. The new version (or it could be the same) is in the DBRM DSN8BC12. If the DBRM contains no version ID, the version ID of the package defaults to the empty string. The package runs only from the TSO BATCH environment, and from the CICS® environment if the connection ID is CON1. The name PRODUCTN qualifies all unqualified table, view, alias and index names.

BIND PACKAGE (USIBMSTODB22.TEST) -
  MEMBER (DSN8BC12) -
  ACTION (REPLACE) REPLVER (APRIL_VERSION) -
  QUALIFIER (PRODUCTN) -
  ENABLE (BATCH, CICS) CICS (CON1)

Example: Binding the SPUFI package with ISOLATION(UR)

UR isolation acquires almost no locks. It is fast and causes little contention, but it reads uncommitted data. Do not use ISOLATION(UR) unless you are sure that your applications and end users can accept the logically inconsistent data that can occur, such as in the case of this example.

Assume that a supervisor routinely executes SQL statements using SPUFI to check the status of parts as they go through the assembly process and to update a table with the results of her inspection. She does not need to know the exact status of the parts; a small margin of error is acceptable.

The supervisor queries the status of the parts from a production table called ASSEMBLY-STATUS and makes the updates in a non-production table called REPORTS. She uses the SPUFI option AUTOCOMMIT NO and has the habit of leaving data on the screen while she performs other tasks.

If the supervisor executes a version of SPUFI that is bound with ISOLATION(UR), the query for the status of the parts executes without acquiring locks using UR isolation level and the update executes using CS isolation level. Thus, the query does not inadvertently hold locks in the production table, which interferes with the production jobs, and the supervisor has data good enough for her purposes.

The SPUFI application is bound as follows:

BIND PACKAGE(DSNESPUR) -
  COPY(DSNESPCS.DSNESM68) -
  ACTION(ADD) -
  ISOLATION(UR)

Example: Binding a package for a native SQL procedure

The following command creates a native SQL procedure named CHICAGO.PRODUCTION.MYPROC from the current location procedure TEST.MYPROC.

Deprecated function: The DEPLOY bind option is deprecated. For best results, deploy compiled SQL functions and native SQL procedures to multiple environments by issuing the same CREATE or ALTER statements separately in each Db2 environment.

Both native SQL procedures have the same version ABC. The package for native SQL procedure CHICAGO.PRODUCTION.MYPROC.(ABC) has XYZ as QUALIFIER.

CREATE PROCEDURE TEST.MYPROC LANGUAGE SQL VERSION ABC ...

BEGIN
...
END

BIND PACKAGE(CHICAGO.PRODUCTION) DEPLOY(TEST.MYPROC) COPYVER(ABC)
                  ACTION(ADD) QUALIFIER(XYZ)

The following command then replaces the native SQL procedure CHICAGO.PRODUCTION.MYPROC version ABC, using the current location native SQL procedure TEST.MYPROC version ABC.

BIND PACKAGE(CHICAGO.PRODUCTION) DEPLOY(TEST.MYPROC) COPYVER(ABC)
                  ACTION(REPLACE) REPLVER(ABC)