| Option | Valid values | Plan | Pckg | Trigr | Qry |
| ACQUIRE | USE, ALLOCATE | X | |||
| Whether to acquire resources specified in the DBRM at first access or allocation | |||||
| ACTION | REPLACE, ADD | X, BO | X, BO | ||
| REPLACE(RPLVER) | X, BO | ||||
| REPLACE(RETAIN) | X,BO | ||||
| Whether object (plan or package) replaces an existing object with same name or is new | |||||
| ACOMPARE | NO, NONE, WARN, ERROR | X | X | X | |
| Determines whether the new access paths are different from the older access paths | |||||
| APPLCOMPAT | V10R1, V11R1 | X | X | ||
| Specifies the package compatibility level behavior for statis SQL | |||||
| APRETAINUP | YES, NO | RO | X | ||
| Whether or not DB2 retains an old package copy when access paths of the old copy are identical to the incoming copy. Applies to PLANMGMT(BASIC) or PLANMGMT(EXTENDED) | |||||
| APREUSE | NO, NONE, ERROR, WARN | X | X | ||
| Specifies whether DB2 tries to reuse previous access paths for SQL statements in a package | |||||
| APPREUSE SOURCE | CURRENT, PREVIOUS, ORIGINAL | RO | X | ||
| Specifies whether DB2 tries to reuse previous access paths for SQL statements in a package | |||||
| ARCHIVE SENSITIVE | YES, NO | X | X | ||
| Whether references to archive-enabled tables are affected by SYSIBMADM.GET_ARCHIVE | |||||
| BUSTIME SENSITIVE | YES, NO | X | X | ||
| Whether references to application-period temporal tables are affected by CURRENT TEMPORAL BUSINESS_TIME special register | |||||
| CACHESIZE | Value of PLAN AUTH CACHE; Decimal | X | |||
| Size (in bytes) of the authorization cache acquired in the EDM pool for the plan | |||||
| COPY | Collection-id, package-id, COPYVER | X, BO | |||
| Determines that you are copying an existing package and names the package | |||||
| CONCENTRATE STMT | NO, YES | X | |||
| Whether to enforce statement concentration at the package level | |||||
| CONCURRENT ACCESS RESOLUTION | WAITFOROUTCOME USECURRENTLYCOMMITTED Default depends on SKIPUNCI setting | X | X | X | |
| Determines which concurrent access resolution option to use for statements in a package | |||||
| CURRENTDATA | YES, NO | X | X | X | |
| Whether to require data currency for RO and ambiguous cursors when isolation level is CS | |||||
| CURRENT SERVER | Location-name | X | |||
| Determines the location to connect to before running the plan | |||||
| DBPROTOCOL | DRDA, DRDACBF (package only) | X | X | ||
| Protocol to use when connecting to a remote site that is identified by a three-part name | |||||
| DESCSTAT | NO, YES | X | X | ||
| Whether DB2 builds a DESCRIBE SQL descriptor when binding statis SQL statements | |||||
| DEFER | DEFER(PREPARE) NODEFER(PREPARE) DEFER(INHERITFROMPLAN) | X | X | ||
| Whether to defer preparation of dynamic SQL statements that refer to remote objects or to prepare them immediately. DEFER(PREPARE) is assumed for REOPT(AUTO, ALWAYS and ONCE) | |||||
| DEGREE | 1, ANY | X | X | ||
| Whether to attempt to run a query using parallel processing to maximise performance | |||||
| DEPLOY | (collection-id.package-id), COPYVER(version-id) | X | |||
| Deploys a native SQL procedure | |||||
| DISCONNECT | EXPLICIT, AUTOMATIC, CONDITIONAL | X | |||
| Determines which remote connections to destroy during commit operations | |||||
| DYNAMICRULES | RUN, BIND, DEFINEBIND(PKG ONLY), DEFINERUN(PKG ONLY), INVOKEBIND(PKG ONLY), INVOKERUN(PKG ONLY) | X | X | ||
| DEFINEBIND, DEFINERUN, INVOKEBIND, INVOKE RUN | X | ||||
| Option | Valid values | Plan | Pckg | Trigr | Qry |
| Determines which values apply at runtime for dynamic SQL attributes | |||||
| ENABLE/ DISABLE | BATCH, CICS, DB2CALL, DLIBATCH, IMS, IMSBMP, IMSMPP, RRSAF, * | X | X | ||
| REMOTE | X | ||||
| Determines which connections can use the plan or package | |||||
| ENCODING | ASCII, EBCDIC, UNICODE, ccsid | X | X | ||
| Application encoding for all static statements in the plan or package (defaults to installed selection) | |||||
| EXPLAIN | NO, YES, ONLY | X | X | X | |
| Whether to populate the PLAN_TABLE with information about the SQL statements | |||||
| EXTENDED INDICATOR | NO, YES | X | |||
| Determines if DB2 recognizes extended indicator variables when associated package is run | |||||
| FILTER | ‘filter-name’ | FQ | |||
| Allows you to delete a set of queries in the SYSIBM.SYSQUERY table under a {tag} value specified by the SYSQUERY.USERFILTER column. Also works with FREE QUERY | |||||
| FLAG | I, W, E, C | X | X | X | |
| Determines what messages to display | |||||
| GENERIC | ‘string’ | X | X | ||
| Specifies one or more bind options that are supported by the target server, but are not supported by DB2 for z/OS as options for BIND PACKAGE or REBIND PACKAGE | |||||
| GETACCEL ARCHIVE | NO, YES | X | X | ||
| Whether a static SQL query bound for acceleration retrieves archive data | |||||
| IMMEDIATE | NO, YES, INHERITFROMPLAN | X | X | ||
| Whether immediate writes will be done for updates made to GBP-dependent page sets/partitions | |||||
| ISOLATION | RR, RS, CS, UR, NC | X | X | X | |
| Determines how far to isolate an application from the effects of other running applications | |||||
| KEEPDYNAMIC | NO, YES | X | X | ||
| Determines whether DB2 keeps dynamic SQL statements after commit points | |||||
| LIBRARY | Dbrm-pds-name | X, BO | |||
| Determines which partitioned data set to search for DBRMs listed in the member option | |||||
| LOOKUP | NO, YES | BQ | |||
| Determines whether a query has matching access plan hint information in the SYSIBM.SYSQUERYPLAN table | |||||
| MEMBER | Dbrm-member-name | X, BO | |||
| Determines what DBRMs to include in the package | |||||
| OPTHINT | Hint-id | X | X | ||
| Controls whether query optimization hints are used for static SQL | |||||
| OPTIONS | COMPOSITE, COMMAND | X, BC | |||
| Specifies which bind options to use for the new package | |||||
| OWNER | Authorization-id | X | X | ||
| Determines the authorization ID or the owner of the object (plan or package) | |||||
| PACKAGE | Location-name.collection-id. package-id(version-id) | X | |||
| (*) – Rebind Only | X,RO | ||||
| Determines which package or packages to bind or rebind | |||||
| PATH | Schema-name, USER, (schema-name, (USER)…) | X | X | ||
| SQL path that DB2 uses to resolve unqualified UDTs, functions and stored procedure names | |||||
| PATHDEFAULT | Mutually exclusive with PATH | X | X | ||
| Resets PATH for package or plan to “SYSIBM”, “SYSFUN”, “SYSPROC”, or plan/package qualifier | |||||
| PKLIST or NOPKLIST | (Location-name.collection-id.package-id…) PKLIST only | X | |||
| Determines which package to include for the package list in the plan | |||||
| PROGAUTH | DISABLE, ENABLE | X | |||
| Whether DB2 performs authorization checking to determine whether DB2 can execute a plan | |||||
| PLANMGMT | OFF, BASIC, EXTENDED | X | X | ||
| Retains, during a rebind operation, all relevant package information (metadata, query text, dependencies, authorizations, access paths, and so on) in catalog tables and in the directory | |||||
| QUALIFIER | Qualifier-name | X | X | ||
| Determines the implicit qualifier for unqualified names of objects in the plan or package | |||||
| QUERY ACCELERATION | NONE, ENABLE, ALL ENABLEWITHFALLBACK, ELIGIBLE | X | X | ||
| Option | Valid values | Plan | Pckg | Trigr | Qry |
| Whether a static SQL query is bound for acceleration | |||||
| QUERYID | ‘number’ ALL | FQ | |||
| Frees entries from SYSIBM.SYSQUERY with same value (or ALL), and corresponding entries in SYSIBM.SYSQUERYPLAN table or SYSIBM.SYSQUERYOPTS table | |||||
| RECORD TEMPORAL HISTORY | YES, NO | X | |||
| Whether changes to data in a system-period temporal table that are made by static or dynamic SQL statements cause changes to corresponding history table of the system-period temporal table | |||||
| RELEASE | COMMIT, DEALLOCATE, INHERITFROMPLAN | X | X | X | |
| Determines when to release resources that the program uses, either at commit or at termination | |||||
| REOPT | ONCE, ALWAYS, AUTO, NONE | X | X | ||
| If access path is determined at runtime (host variables, parameter markers, special registers) | |||||
| ROUNDING | CEILING, DOWN, FLOOR HALFDOWN, HALFEVEN, HALFUP, UP | X | X | ||
| Specifies the rounding mode at bind time | |||||
| SQLERROR | NOPACKAGE, CONTINUE, CHECK | X | |||
| Whether to create a package if the package contains an SQL error | |||||
| SQLRULES | DB2, STD | X | |||
| Whether a Type 2 connection can bemade according to DB2 rules for an existing connection | |||||
| SWITCH | PREVIOUS, ORIGINAL | X | X | ||
| Restores all previous or original package information in the catalog tables and directory to that of the specified package copy | |||||
| SYSTIME SENSITIVE | YES, NO | X | X | ||
| Whether references to system-period temporal tables are affected by value of CURRENT TEMPORAL TEMPORAL SYSTEM_TIME | |||||
| VALIDATE | RUN, BIND | X | X | ||
| Whether to recheck at runtime ‘not found’ and ‘not authorized’ errors found at bind time | |||||
BO = BIND only, BC = BIND copy, RO = REBIND only, FQ = FREE QUERY, BQ = BIND QUERY
BOLD/UNDERSCORE = default
Disclaimer: This Db2® 12 for z/OS Reference Guide was developed to help users in their daily activities in administrating and programming in Db2 for z/OS. There are no guarantees expressed or implied with the contents in this guide. We want to provide a quality and useful reference for users. Please notify us of any mistakes or errors in this reference guide at blogs@bmc.com. Db2 is a registered trademark of the IBM Corporation.
