BIND PLAN subcommand (DSN)

The DSN subcommand BIND PLAN builds an application plan. All Db2 programs require an application plan to allocate Db2 resources and support SQL requests made at run time.

Environment for BIND PLAN

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

Data sharing scope: Group

Authorization for BIND PLAN

The plan owner must be a role to execute BIND PLAN in a trusted context with role ownership. If performing a bind in a trusted context that has a role-as-object owner attribute and the OWNERTYPE BIND option is not specified, the default owner is determined as follows:

If the OWNER option is not specified, the role that is associated with the binder becomes the owner.
If the OWNER option is specified, the role that is specified in the OWNER option becomes the owner. In a trusted context, the default owner must be a role. For the bind to succeed, the binder needs BINDAGENT privilege from the role that is specified in the OWNER option. The binder also receives BINDAGENT privilege if the role that is associated with the binder has BINDAGENT privilege.

The following table explains the authorization required to run BIND PLAN, depending on the options specified.

Table 1. Summary of privileges needed for BIND PLAN options
Option	Authorization required to run BIND PLAN
ADD, using the default owner or primary authorization ID	Primary authorization ID must have one of the following privileges: BINDADD privilege SYSADM, SYSCTRL, or system DBADM authority
ADD, specifying an OWNER other than the primary authorization ID	If the binder does not have SYSADM or SYSCTRL or system DBADM authority, the authorization ID of the new OWNER must have one of the following privileges: BINDADD privilege SYSADM, SYSCTRL, or system DBADM authority
REPLACE, using the default owner or primary authorization ID	Primary authorization ID of the process must have one of the following privileges: Ownership of the plan BIND privilege on the plan. If the plan does not exist, the authorization is the same as for ADD, using the default owner or primary authorization ID. SYSADM, SYSCTRL, or system DBADM authority
REPLACE, specifying an OWNER other than the primary authorization ID	If the binder does not have SYSADM or SYSCTRL or system DBADM authority, the authorization ID of the OWNER must have one of the following privileges: Ownership of the plan BIND privilege on the plan. If the plan does not exist, the authorization is the same as for ADD, specifying an OWNER other than the primary authorization ID. BINDAGENT privilege from the current owner of the plan. SYSADM, SYSCTRL, or system DBADM authority
PKLIST, specifying individual packages	Authorization ID of the process must include one of the following privileges: EXECUTE authority on each package specified in the PKLIST PACKADM authority on specific collections that contain the packages or on all collections SYSADM or DATAACCESS authority
PKLIST, specifying (*), indicating all packages in the collection	Authorization ID of the process must include one of the following privileges: EXECUTE authority on collection-id .* PACKADM authority on specific collections that contain the packages or on all collections SYSADM or DATAACCESS authority

Start of change Specifying the OWNER for ADD and REPLACE other than the primary authorization ID: 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 the SEPERATE_SECURITY subsystem parameter 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, OWNER authorization-id must be one of the primary or secondary authorization IDs of the binder. End of change

If you specify OWNER authorization-id , Db2 first checks the OWNER and then the binder for the necessary bind privilege. If both the OWNER and the binder do not have the necessary bind privilege and the IFCID 140 trace is active, the trace record is written.

Syntax

Notes:

¹ If PLAN is not specified, all bind functions will be performed, including error diagnostics, without producing an application plan and without inserting rows into PLAN_TABLE for the option EXPLAIN.
² NOREOPT(VARS) can be specified as a synonym of REOPT(NONE)
³ REOPT(VARS) can be specified as a synonym of REOPT(ALWAYS)

enable-block

pklist-block

Descriptions for BIND PLAN

PLAN(plan-name)

Specifies the name of the application plan.

The Db2 catalog record for the plan-name value is the NAME column in SYSPLAN catalog table.

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

Usage notes for BIND PLAN

The MEMBER option is deprecated: Although the BIND PLAN command accepts the MEMBER option, the MEMBER option is deprecated. Use it only when you cannot run BIND PACKAGE to bind DBRMs into packages explicitly. When you specify MEMBER, Db2 binds the DBRMs into packages, and includes those packages in a package list for the specified plan.
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 PLAN

Example: Binding a plan with ISOLATION(CS) for maximum concurrency

This subcommand creates a new plan called IMSONLY. The SQL statements for the plan are in the package DSN8IC13.

An ISOLATION level of cursor stability (CS) provides maximum concurrency when you run the plan, and protects database values only while the program uses them. DEPTM92 owns the plan, but PRODUCTN qualifies any unqualified table, view, index, and alias names that are referenced in the package.

A cache size of 0 indicates that users will not run the plan repeatedly. Caching the names of users authorized to run the plan helps only when the same user runs the plan repeatedly while it is in the EDM pool. Because this is not the case with this plan, there is no need to reserve space in the EDM pool for a cache that the plan does not use.

The option ENABLE(IMS) runs the plan only from an IMS environment (DLI Batch, BMP and MPP). If you attempt to run the plan from another environment, such as TSO Batch, the plan allocation fails.

BIND PLAN(IMSONLY) -
  PKLIST(DSN8IC13.*) -
  ACTION(ADD) -
  ISOLATION(CS) -
  OWNER(DEPTM92) -
  QUALIFIER(PRODUCTN) -
  CACHESIZE -
  ENABLE(IMS)

Example: Binding a plan for a CICS® environment only

The following subcommand creates a new plan called CICSONLY. The plan specifies an isolation level of cursor stability (CS). DEPTM12 owns the plan, but TESTSYS qualifies any unqualified table, view, index, and alias names referenced in the package.

The option ENABLE(CICS) CICS(CON1) runs the plan only from CICS VTAM® node CON1 which is specified in the APPLID parameter of the CICS SIT table. If you attempt to run the plan from another environment or from another CICS VTAM note, the run attempt fails. A cache size of 0 indicates that users will not run the plan repeatedly.

BIND PLAN(CICSONLY) -
  PKLIST(DSN8IC13.*) -
  ACTION(ADD) -
  ISOLATION(CS) -
  OWNER(DEPTM12) -
  QUALIFIER(TESTSYS) -
  CACHESIZE(0) -
  ENABLE(CICS) CICS(CON1)