BIND PLAN (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

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

The plan owner must be a role to execute BIND PLAN in a trusted context with role ownership.

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

Specifying the OWNER for ADD and REPLACE: If any of the authorization IDs of the process has SYSADM authority, SYSCTRL authority, or system DBADM authority, OWNER authorization-id can be any value. 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.

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

Read syntax diagramSkip visual syntax diagramBINDPLAN1( plan-name)OWNER( authorization-id)QUALIFIER( qualifier-name)enable-blockpklist-block NODEFER(PREPARE)DEFER(PREPARE)ACQUIRE(USEALLOCATE)ACTION(REPLACE)RETAIN(ADD)CACHESIZE( decimal-value)CURRENTDATA(NOYES)CURRENTSERVER( location-name)DBPROTOCOL(DRDA)DEGREE(1ANY)DISCONNECT(EXPLICITAUTOMATICCONDITIONAL)DYNAMICRULES(RUNBIND)ENCODING(ASCIIEBCDICUNICODEccsid)EXPLAIN(NOYES)FLAG(IWEC)IMMEDWRITE(NOYES)ISOLATION(CSRRRSUR)KEEPDYNAMIC(NOYES)REOPT(NONE2ALWAYS3ONCEAUTO)OPTHINT(' hint-id' )PATH(,schema-nameUSER)RELEASE(COMMITDEALLOCATE)ROUNDING(CEILINGDOWNFLOORHALFDOWNHALFEVENHALFUPUP)SQLRULES(DB2STD)VALIDATE(RUNBIND)CONCURRENTACCESSRESOLUTION(USECURRENTLYCOMMITTEDWAITFOROUTCOME)PROGAUTH(DISABLEENABLE)
Notes:
  • 1 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.
  • 2 NOREOPT(VARS) can be specified as a synonym of REOPT(NONE)
  • 3 REOPT(VARS) can be specified as a synonym of REOPT(ALWAYS)

enable-block

Read syntax diagramSkip visual syntax diagramENABLEDISABLE(,BATCHDLIBATCHDB2CALLCICSIMSIMSBMPIMSMPPRRSAF)ENABLE( *)DLIBATCH(,connection-name)CICS(,applid)IMSBMP(,imsid)IMSMPP(,imsid)

pklist-block

Read syntax diagramSkip visual syntax diagramPKLIST(,location-name*.collection-id*.package-id*)

Option descriptions

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

Usage notes

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.

Examples

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 DSN8IC12.

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(DSN8IC12.*) -
  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(DSN8IC12.*) -
  ACTION(ADD) -
  ISOLATION(CS) -
  OWNER(DEPTM12) -
  QUALIFIER(TESTSYS) -
  CACHESIZE(0) -
  ENABLE(CICS) CICS(CON1)