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 have authorization to execute all SQL statements embedded in the plan for BIND PLAN to build a plan without producing error messages. This excludes statements included in DBRMs that are bound to packages included in the package list of the plan. The SYSADM 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.
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.
| Option | Authorization required to run BIND PLAN |
|---|---|
| ADD, using the default owner or primary authorization ID | Primary authorization
ID (default owner) must have one of the following privileges:
|
| 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:
|
| REPLACE, using the default owner or primary authorization ID | Primary authorization
ID of the process must have one of the following privileges:
|
| 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:
|
| PKLIST, specifying individual packages | Authorization ID of the
process must include one of the following privileges:
|
| PKLIST, specifying (*), indicating all packages in the collection | Authorization ID of the
process must include one of the following privileges:
|
| Note:
If any of the authorization IDs of the process has SYSADM, SYSCTRL, or
system DBADM authority, OWNER authorization-id can be any value, when the system parameter, SEPARATE SECURITY, is set to NO. If any
of the authorization IDs has the BINDAGENT privilege granted from the owner, then 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. |
|
Specifying the OWNER for ADD and REPLACE: If any of the authorization IDs of the process has SYSADM authority or SYSCTRL 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
(1) >>-BIND--PLAN----(plan-name)--+-------------------------+-------> '-OWNER(authorization-id)-' >--+---------------------------+--------------------------------> '-QUALIFIER(qualifier-name)-' .-NODEFER(PREPARE)-. >--| enable-block |--| pklist-block |--+------------------+-----> '-DEFER(PREPARE)---' >--+-------------------------+----------------------------------> | .-USE------. | '-ACQUIRE(-+-ALLOCATE-+-)-' >--+----------------------------------+-------------------------> | .-(REPLACE)-+--------+-. | | | '-RETAIN-' | | '-ACTION--+-(ADD)----------------+-' >--+--------------------------+--+------------------------+-----> '-CACHESIZE(decimal-value)-' | .-NO--. | '-CURRENTDATA(-+-YES-+-)-' >--+------------------------------+-----------------------------> '-CURRENTSERVER(location-name)-' >--+------------------------+--+-------------------+------------> '-DBPROTOCOL(---DRDA---)-' | .-1---. | '-DEGREE(-+-ANY-+-)-' >--+-------------------------------+----------------------------> | .-EXPLICIT----. | '-DISCONNECT(-+-AUTOMATIC---+-)-' '-CONDITIONAL-' >--+--------------------------+--+-------------------------+----> | .-RUN--. | '-ENCODING(-+-ASCII---+-)-' '-DYNAMICRULES(-+-BIND-+-)-' +-EBCDIC--+ +-UNICODE-+ '-ccsid---' >--+--------------------+--+---------------+--------------------> | .-NO--. | | .-I-. | '-EXPLAIN(-+-YES-+-)-' '-FLAG(-+-W-+-)-' +-E-+ '-C-' >--+-----------------------+--+---------------------+-----------> '-IMMEDWRITE(-+-NO--+-)-' | .-CS-. | '-YES-' '-ISOLATION(-+-RR-+-)-' +-RS-+ '-UR-' >--+------------------------+--+-------------------------+------> | .-NO--. | | (2) | '-KEEPDYNAMIC(-+-YES-+-)-' | .-NONE-------. | | | (3) | | '-REOPT(-+-ALWAYS-----+-)-' +-ONCE-------+ '-AUTO-------' >--+----------------------+--+-----------------------------+----> '-OPTHINT(-'hint-id'-)-' | .-,---------------. | | V | | '-PATH(---+-schema-name-+-+-)-' '-USER--------' >--+---------------------------+--------------------------------> | .-COMMIT-----. | '-RELEASE(-+-DEALLOCATE-+-)-' >--+--------------------------+--+---------------------+--------> '-ROUNDING(-+-CEILING--+-)-' | .-DB2-. | +-DOWN-----+ '-SQLRULES(-+-STD-+-)-' +-FLOOR----+ +-HALFDOWN-+ +-HALFEVEN-+ +-HALFUP---+ '-UP-------' >--+----------------------+-------------------------------------> | .-RUN--. | '-VALIDATE(-+-BIND-+-)-' >--+---------------------------------------------------------+->< '-CONCURRENTACCESSRESOLUTION(-+-USECURRENTLYCOMMITTED-+-)-' '-WAITFOROUTCOME--------'
- 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 >>-+--------------------------------------------------------------------------------+->< | .-,------------. .-------------------------------------. | | V | V | | '-+-+-ENABLE--+--(---+-BATCH----+-+-)-+----+---------------------------------+-+-' | '-DISABLE-' +-DLIBATCH-+ | | .-,---------------. | | +-DB2CALL--+ | | V | | | +-CICS-----+ | +-DLIBATCH(---connection-name-+-)-+ | +-IMS------+ | | .-,------. | | +-IMSBMP---+ | | V | | | +-IMSMPP---+ | +-CICS(---applid-+-)--------------+ | '-RRSAF----' | | .-,-----. | '-ENABLE(*)-------------------------' | V | | +-IMSBMP(---imsid-+-)-------------+ | .-,-----. | | V | | '-IMSMPP(---imsid-+-)-------------'
pklist-block .-,----------------------------------------------------------. V | >>-PKLIST(---+---------------+--.--+-collection-id-+--.--+-package-id-+-+-)->< +-location-name-+ '-*-------------' '-*----------' '-*-------------'
Option descriptions
For descriptions of the options shown in the syntax diagram, see BIND and REBIND options for packages and plans.
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 binds the packages to 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 DSN8IC10.
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(DSN8IC10.*) - 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(DSN8IC10.*) - ACTION(ADD) - ISOLATION(CS) - OWNER(DEPTM12) - QUALIFIER(TESTSYS) - CACHESIZE(0) - ENABLE(CICS) CICS(CON1)