(This article is part of our Db2 Guide. Use the right-hand menu to navigate.)
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 [email protected]. Db2 is a registered trademark of the IBM Corporation.