BIND command

The BIND command invokes the bind utility, which prepares SQL statements stored in the bind file generated by the precompiler, and creates a package that is stored in the database.

Scope

This command can be issued from any database partition in db2nodes.cfg. It updates the database catalogs on the catalog database partition. Its effects are visible to all database partitions.

Authorization

One of the following authorizations:
  • DBADM authority
  • If EXPLAIN ONLY is specified, EXPLAIN authority or an authority that implicitly includes EXPLAIN is sufficient.
  • If a package does not exist, BINDADD authority and:
    • If the schema name of the package does not exist, IMPLICIT_SCHEMA authority on the database.
    • If the schema name of the package does exist, CREATEIN privilege on the schema.
  • If the package exists, one of the following privileges:
    • ALTERIN privilege on the schema
    • BIND privilege on the package
In addition, if capturing explain information using the EXPLAIN or the EXPLSNAP clause, one of the following authorizations is required:
  • INSERT privilege on the explain tables
  • DATAACCESS authority

The user also needs all privileges required to compile any static SQL statements in the application. Privileges granted to groups are not used for authorization checking of static statements.

Required connection

Database. If implicit connect is enabled, a connection to the default database is established.

Command syntax

For Db2®
Read syntax diagramSkip visual syntax diagramBINDfilenameACTIONADDREPLACERETAINNOYESREPLVERversion-idAPREUSENOYESBLOCKINGUNAMBIGALLNOBUSTIMESENSITIVEYESNOCLIPKGcli-packagesCOLLECTIONschema-nameCONCURRENTACCESSRESOLUTIONUSECURRENTLYCOMMITTEDWAITFOROUTCOMEUSE CURRENTLY COMMITTEDWAIT FOR OUTCOMEDATETIMEDEFEURISOJISLOCUSADEGREE1degree-of-parallelismANYDYNAMICRULESRUNBINDINVOKERUNINVOKEBINDDEFINERUNDEFINEBINDEXPLAINNOALLREOPTONLYYESEXPLSNAPNOALLREOPTYESEXTENDEDINDICATORNOYESFEDERATEDNOYESFEDERATED_ASYNCHRONYANYnumber_of_atqs_in_the_planFUNCPATH,schema-nameGENERIC" string"GRANTauthidPUBLICGRANT_GROUPgroup-nameGRANT_USERuser-nameGRANT_ROLErole-nameINSERTDEFBUFISOLATIONCSRRRSURKEEPDYNAMICYESNO MESSAGESmessage-fileOPTPROFILEoptimization-profile-nameOWNERauthorization-idQUALIFIERqualifier-nameQUERYOPToptimization-levelREOPT NONEREOPT ONCEREOPT ALWAYSSQLERRORCHECKCONTINUENOPACKAGESQLWARNNOYESSTATICREADONLYNOYESINSENSITIVESTRING_UNITSSYSTEMCODEUNITS32SYSTIMESENSITIVEYESNOTRANSFORM GROUPgroupnameVALIDATEBINDRUN
For Db2 on servers other than Linux®, Windows and UNIX
Read syntax diagramSkip visual syntax diagramBINDfilenameACTIONADDREPLACERETAINNOYESREPLVERversion-idBLOCKINGUNAMBIGALLNOCCSIDGdouble-ccsidCCSIDMmixed-ccsidCCSIDSsbcs-ccsidCHARSUBDEFAULTBITMIXEDSBCSCLIPKGcli-packagesCNULREQDNOYESCOLLECTIONschema-nameCONCURRENTACCESSRESOLUTIONUSECURRENTLYCOMMITTEDWAITFOROUTCOMEUSE CURRENTLY COMMITTEDWAIT FOR OUTCOME1DATETIMEDEFEURISOJISLOCUSADBPROTOCOLDRDAPRIVATEDEC1531DECDELCOMMAPERIOD2DEGREE1degree-of-parallelismANYDYNAMICRULESRUNBINDINVOKERUNINVOKEBINDDEFINERUNDEFINEBINDENCODINGASCIIEBCDICUNICODECCSID3EXPLAINNOYESEXTENDEDINDICATORNOYESGENERIC" string"GRANTauthidPUBLICIMMEDWRITENOYESPH1INSERTBUFDEFISOLATIONCSNCRRRSURKEEPDYNAMICYESNO MESSAGESmessage-fileOPTHINThint-idOS400NAMINGSYSTEMSQLOWNERauthorization-idPATHschema-nameQUALIFIERqualifier-nameRELEASECOMMITDEALLOCATEREOPT NONEREOPT ONCEREOPT ALWAYSREOPT VARSNOREOPT VARSSORTSEQJOBRUNHEXSQLERRORCHECKCONTINUENOPACKAGEVALIDATEBINDRUNSTRDELAPOSTROPHEQUOTETEXTlabel
Notes:
  • 1 If the server does not support the DATETIME DEF option, it is mapped to DATETIME ISO.
  • 2 The DEGREE option is only supported by DRDA Level 2 Application Servers.
  • 3 DRDA defines the EXPLAIN option to have the value YES or NO. If the server does not support the EXPLAIN YES option, the value is mapped to EXPLAIN ALL.

Command parameters

filename
Specifies the name of the bind file that was generated when the application program was precompiled, or a list file containing the names of several bind files. Bind files have the extension .bnd. The full path name can be specified.
If a list file is specified, the @ character must be the first character of the list file name. The list file can contain several lines of bind file names. Bind files listed on the same line must be separated by plus (+) characters, but a + cannot appear in front of the first file listed on each line, or after the last bind file listed. For example,
   /u/smith/sqllib/bnd/@all.lst
is a list file that contains the following bind files:
   mybind1.bnd+mybind.bnd2+mybind3.bnd+
         mybind4.bnd+mybind5.bnd+
         mybind6.bnd+
         mybind7.bnd

When just the bind file name without any path is specified, the file would be first searched for in the current directory. If the file is found, it would be picked up and the package would be created. If the file is not found in the current directory, then the file would be automatically picked up from the instance or install path.

ACTION
Indicates whether the package can be added or replaced.
ADD
Indicates that the named package does not exist, and that a new package is to be created. If the package already exists, execution stops, and a diagnostic error message is returned.
REPLACE
Indicates that the existing package is to be replaced by a new one with the same package name and creator. This is the default value for the ACTION option.
RETAIN
Indicates whether BIND and EXECUTE authorities are to be preserved when a package is replaced. If ownership of the package changes, the new owner grants the BIND and EXECUTE authority to the previous package owner.
NO
Does not preserve BIND and EXECUTE authorities when a package is replaced. This value is not supported by Db2.
YES
Preserves BIND and EXECUTE authorities when a package is replaced. This is the default value.
REPLVER version-id
Replaces a specific version of a package. The version identifier specifies which version of the package is to be replaced. If the specified version does not exist, an error is returned. If the REPLVER option of REPLACE is not specified, and a package already exists that matches the package name, creator, and version of the package being bound, that package will be replaced; if not, a new package will be added.
APREUSE
Specifies whether static SQL access plans are to be reused. When this option is enabled, the query compiler will attempt to reuse the access plans for the statement in any existing packages during the bind and during future implicit and explicit rebinds.
YES
The query compiler will attempt to reuse the access plans for the statements in the package. If there is an existing package, the query compiler will attempt to reuse the access plan for every statement that can be matched with a statement in the new bind file. For a statement to match, the statement text must be identical and the section number for the statement in the existing package must match what the section number will be for the statement in the new package.
NO
The query compiler will not attempt to reuse access plans for the statements in the package. This is the default setting.
BLOCKING
Specifies the type of row blocking for cursors. The blocking of row data that contains references to LOB column data types is also supported in partitioned database environments.
ALL
For cursors that are specified with the FOR READ ONLY clause or cursors not specified as FOR UPDATE, blocking occurs.

Ambiguous cursors are treated as read-only.

NO
Blocking does not occur for any cursor.

For the definition of a read-only cursor and an ambiguous cursor, refer to DECLARE CURSOR statement.

Ambiguous cursors are treated as updatable.

UNAMBIG
For cursors that are specified with the FOR READ ONLY clause, blocking occurs.

Cursors that are not declared with the FOR READ ONLY or FOR UPDATE clause which are not ambiguous and are read-only will be blocked. Ambiguous cursors will not be blocked.

Ambiguous cursors are treated as updatable.

BUSTIMESENSITIVE
Indicates whether references to application-period temporal tables in static and dynamic SQL statements are affected by the value of the CURRENT TEMPORAL BUSINESS_TIME special register.
YES
References to application-period temporal tables are affected by the value of the CURRENT TEMPORAL BUSINESS_TIME special register. This value is the default value.
NO
References to application-period temporal tables are not affected by the value of the CURRENT TEMPORAL BUSINESS_TIME special register.
CCSIDG double-ccsid
An integer specifying the coded character set identifier (CCSID) to be used for double byte characters in character column definitions (without a specific CCSID clause) in CREATE and ALTER TABLE SQL statements. This option is not supported by the server for Db2. The DRDA server will use a built-in default value if this option is not specified.
CCSIDM mixed-ccsid
An integer specifying the coded character set identifier (CCSID) to be used for mixed byte characters in character column definitions (without a specific CCSID clause) in CREATE and ALTER TABLE SQL statements. This option is not supported by the server for Db2. The DRDA server will use a built-in default value if this option is not specified.
CCSIDS sbcs-ccsid
An integer specifying the coded character set identifier (CCSID) to be used for single byte characters in character column definitions (without a specific CCSID clause) in CREATE and ALTER TABLE SQL statements. This option is not supported by Db2. The DRDA server will use a built-in default value if this option is not specified.
CHARSUB
Designates the default character sub-type that is to be used for column definitions in CREATE and ALTER TABLE SQL statements. This precompile/bind option is not supported by the server for Db2.
BIT
Use the FOR BIT DATA SQL character sub-type in all new character columns for which an explicit sub-type is not specified.
DEFAULT
Use the target built-in default in all new character columns for which an explicit sub-type is not specified.
MIXED
Use the FOR MIXED DATA SQL character sub-type in all new character columns for which an explicit sub-type is not specified.
SBCS
Use the FOR SBCS DATA SQL character sub-type in all new character columns for which an explicit sub-type is not specified.
CLIPKG cli-packages
An integer between 3 and 30 specifying the number of CLI large packages to be created when binding CLI bind files against a database.
CNULREQD
This option is related to the LANGLEVEL precompile option. It is valid only if the bind file is created from a C or a C++ application. This bind option is not supported by the server for Db2.
NO
The application was coded on the basis of the LANGLEVEL SAA1 precompile option with respect to the null terminator in C string host variables.
YES
The application was coded on the basis of the LANGLEVEL MIA precompile option with respect to the null terminator in C string host variables.
COLLECTION schema-name
Specifies a 128-byte collection identifier for the package. If not specified, the authorization identifier for the user processing the package is used.
CONCURRENTACCESSRESOLUTION
Specifies the concurrent access resolution to use for statements in the package.
USE CURRENTLY COMMITTED
Specifies that the database manager can use the currently committed version of the data for applicable scans when it is in the process of being updated or deleted. Rows in the process of being inserted can be skipped. This clause applies when the isolation level in effect is Cursor Stability or Read Stability (for Read Stability it skips uncommited inserts only) and is ignored otherwise. Applicable scans include read-only scans that can be part of a read-only statement as well as a non read-only statement. The settings for the registry variables DB2_EVALUNCOMMITTED, DB2_SKIPDELETED, and DB2_SKIPINSERTED do not apply to scans using currently committed. However, the settings for these registry variables still apply to scans that do not use currently committed.
WAIT FOR OUTCOME
Specifies Cursor Stability and higher scans wait for the commit or rollback when encountering data in the process of being updated or deleted. Rows in the process of being inserted are not skipped. The settings for the registry variables DB2_EVALUNCOMMITTED, DB2_SKIPDELETED, and DB2_SKIPINSERTED no longer apply.
DATETIME
Specifies the date and time format to be used.
DEF
Use a date and time format associated with the territory code of the database.
EUR
Use the IBM standard for Europe date and time format.
ISO
Use the date and time format of the International Standards Organization.
JIS
Use the date and time format of the Japanese Industrial Standard.
LOC
Use the date and time format in local form associated with the territory code of the database.
USA
Use the IBM standard for U.S. date and time format.
DBPROTOCOL
Specifies what protocol to use when connecting to a remote site that is identified by a three-part name statement. Supported by Db2 for z/OS® only. For a list of supported option values, refer to the documentation for Db2 for z/OS.
DEC
Specifies the maximum precision to be used in decimal arithmetic operations. This precompile/bind option is not supported by the server for Db2. The DRDA server will use a built-in default value if this option is not specified.
15
15-digit precision is used in decimal arithmetic operations.
31
31-digit precision is used in decimal arithmetic operations.
DECDEL
Designates whether a period (.) or a comma (,) will be used as the decimal point indicator in decimal and floating point literals. This precompile/bind option is not supported by the server for Db2. The DRDA server will use a built-in default value if this option is not specified.
COMMA
Use a comma (,) as the decimal point indicator.
PERIOD
Use a period (.) as the decimal point indicator.
DEGREE
Specifies the degree of parallelism for the execution of static SQL statements in an SMP system. This option does not affect CREATE INDEX parallelism.
1
The execution of the statement will not use parallelism.
degree-of-parallelism
Specifies the degree of parallelism with which the statement can be executed, a value between 2 and 32 767 (inclusive).
ANY
Specifies that the execution of the statement can involve parallelism using a degree determined by the database manager.
DYNAMICRULES
Defines which rules apply to dynamic SQL at run time for the initial setting of the values used for authorization ID and for the implicit qualification of unqualified object references.
RUN
Specifies that the authorization ID of the user executing the package is to be used for authorization checking of dynamic SQL statements. The authorization ID will also be used as the default package qualifier for implicit qualification of unqualified object references within dynamic SQL statements. This is the default value.
BIND
Specifies that all of the rules that apply to static SQL for authorization and qualification are to be used at run time. That is, the authorization ID of the package owner is to be used for authorization checking of dynamic SQL statements, and the default package qualifier is to be used for implicit qualification of unqualified object references within dynamic SQL statements.
DEFINERUN
If the package is used within a routine context, the authorization ID of the routine definer is to be used for authorization checking and for implicit qualification of unqualified object references within dynamic SQL statements within the routine.

If the package is used as a stand-alone application, dynamic SQL statements are processed as if the package were bound with DYNAMICRULES RUN.

DEFINEBIND
If the package is used within a routine context, the authorization ID of the routine definer is to be used for authorization checking and for implicit qualification of unqualified object references within dynamic SQL statements within the routine.

If the package is used as a stand-alone application, dynamic SQL statements are processed as if the package were bound with DYNAMICRULES BIND.

INVOKERUN
If the package is used within a routine context, the current statement authorization ID in effect when the routine is invoked is to be used for authorization checking of dynamic SQL statements and for implicit qualification of unqualified object references within dynamic SQL statements within that routine.

If the package is used as a stand-alone application, dynamic SQL statements are processed as if the package were bound with DYNAMICRULES RUN.

INVOKEBIND
If the package is used within a routine context, the current statement authorization ID in effect when the routine is invoked is to be used for authorization checking of dynamic SQL statements and for implicit qualification of unqualified object references within dynamic SQL statements within that routine.

If the package is used as a stand-alone application, dynamic SQL statements are processed as if the package were bound with DYNAMICRULES BIND.

Because dynamic SQL statements will be using the authorization ID of the package owner in a package exhibiting bind behavior, the binder of the package should not have any authorities granted to them that the user of the package should not receive. Similarly, when defining a routine that will exhibit define behavior, the definer of the routine should not have any authorities granted to them that the user of the package should not receive since a dynamic statement will be using the authorization ID of the routine's definer.

The following dynamically prepared SQL statements cannot be used within a package that was not bound with DYNAMICRULES RUN: GRANT, REVOKE, ALTER, CREATE, DROP, COMMENT ON, RENAME, SET INTEGRITY, and SET EVENT MONITOR STATE.

ENCODING
Specifies the encoding for all host variables in static statements in the plan or package. Supported by Db2 for OS/390® only. For a list of supported option values, refer to the documentation for Db2 for OS/390.
EXPLAIN
Stores information in the Explain tables about the access plans chosen for each SQL statement in the package.
NO
Explain information will not be captured.
YES
Explain tables will be populated with information about the chosen access plan at prep/bind time for static statements and at run time for incremental bind statements.

If the package is to be used for a routine and the package contains incremental bind statements, then the routine must be defined as MODIFIES SQL DATA. If this is not done, incremental bind statements in the package will cause a run time error (SQLSTATE 42985).

REOPT
Explain information for each reoptimizable incremental bind SQL statement is placed in the explain tables at run time. In addition, explain information is gathered for reoptimizable dynamic SQL statements at run time, even if the CURRENT EXPLAIN MODE register is set to NO.

If the package is to be used for a routine, the routine must be defined as MODIFIES SQL DATA, otherwise incremental bind and dynamic statements in the package will cause a run time error (SQLSTATE 42985).

ONLY
The ONLY option allows you to explain statements without having the privilege to execute them. The explain tables are populated but no persistent package is created. If an existing package with the same name and version is encountered during the bind process, the existing package is neither dropped nor replaced even if you specified ACTION REPLACE. If an error occurs during population of the explain tables, explain information is not added for the statement that returned the error and for any statements that follow it.
ALL
Explain information for each eligible static SQL statement will be placed in the Explain tables at prep/bind time. Explain information for each eligible incremental bind SQL statement will be placed in the explain tables at run time. In addition, explain information will be gathered for eligible dynamic SQL statements at run time, even if the CURRENT EXPLAIN MODE register is set to NO.

If the package is to be used for a routine, the routine must be defined as MODIFIES SQL DATA, otherwise incremental bind and dynamic statements in the package will cause a run time error (SQLSTATE 42985).

EXPLSNAP
Stores Explain Snapshot information in the Explain tables.
NO
An Explain Snapshot will not be captured.
YES
An Explain Snapshot for each eligible static SQL statement will be placed in the Explain tables at prep/bind time for static statements and at run time for incremental bind statements.

If the package is to be used for a routine and the package contains incremental bind statements, then the routine must be defined as MODIFIES SQL DATA or incremental bind statements in the package will cause a run time error (SQLSTATE 42985).

REOPT
Explain snapshot information for each reoptimizable incremental bind SQL statement is placed in the explain tables at run time. In addition, explain snapshot information is gathered for reoptimizable dynamic SQL statements at run time, even if the CURRENT EXPLAIN SNAPSHOT register is set to NO.

If the package is to be used for a routine, the routine must be defined as MODIFIES SQL DATA, otherwise incremental bind and dynamic statements in the package will cause a run time error (SQLSTATE 42985).

ALL
An Explain Snapshot for each eligible static SQL statement will be placed in the Explain tables at prep/bind time. Explain snapshot information for each eligible incremental bind SQL statement will be placed in the Explain tables at run time. In addition, explain snapshot information will be gathered for eligible dynamic SQL statements at run time, even if the CURRENT EXPLAIN SNAPSHOT register is set to NO.

If the package is to be used for a routine, then the routine must be defined as MODIFIES SQL DATA, otherwise incremental bind and dynamic statements in the package will cause a run time error (SQLSTATE 42985).

EXTENDEDINDICATOR
Enables the recognition of extended indicator variable values during the execution of the associated plan or package.
NO
Extended indicator variable values are not recognized. Indicator variables are normal indicator variables; negative indicator variable values imply null, and positive or zero values imply non-null. This is the default condition.
YES
Extended indicator variable values are recognized. Using any non-recognized indicator variable values, or using the default or unassigned indicator variable-based values in a non-supported location will cause Db2 database manager to generate an error message during execution of the bound statement.
FEDERATED
Specifies whether a static SQL statement in a package references a nickname or a federated view. If this option is not specified and a static SQL statement in the package references a nickname or a federated view, a warning is returned and the package is created.
NO
A nickname or federated view is not referenced in the static SQL statements of the package. If a nickname or federated view is encountered in a static SQL statement during the prepare or bind phase of this package, an error is returned and the package is not created.
YES
A nickname or federated view can be referenced in the static SQL statements of the package. If no nicknames or federated views are encountered in static SQL statements during the prepare or bind of the package, no errors or warnings are returned and the package is created.
FEDERATED_ASYNCHRONY
Specifies the maximum number of asynchrony table queues (ATQs) that the federated server supports in the access plan for programs that use embedded SQL.
ANY
The optimizer determines the number of ATQs for the access plan. The optimizer assigns an ATQ to all eligible SHIP or remote pushdown operators in the plan. The value that is specified for DB2_MAX_ASYNC_REQUESTS_PER_QUERY server option limits the number of asynchronous requests.
number_of_atqs_in_the_plan
The number of ATQs in the plan. You specify a number in the range 0 to 32767.
FUNCPATH
Specifies the function path to be used in resolving user-defined distinct types and functions in static SQL. If this option is not specified, the default function path is "SYSIBM","SYSFUN",USER where USER is the value of the USER special register.
schema-name
An SQL identifier, either ordinary or delimited, which identifies a schema that exists at the application server. No validation that the schema exists is made at precompile or at bind time. The same schema cannot appear more than once in the function path. The schema name SYSPUBLIC cannot be specified for the function path. The number of schemas that can be specified is limited by the length of the resulting function path, which cannot exceed 2048 bytes. The schema SYSIBM does not need to be explicitly specified; it is implicitly assumed to be the first schema if it is not included in the function path.
GENERIC "string"
Supports the binding of packages with bind options that are supported by the target server, but that are not predefined in the BIND PACKAGE or REBIND PACKAGE command. Do not use the GENERIC bind option to specify bind options that are predefined in the BIND or PRECOMPILE command syntax. For example, do not specify predefined bind option such as KEEPDYNAMIC in the GENERIC bind option. The syntax for the GENERIC bind option is as follows:
   generic "option1 value1 option2 value2 ..."
Each option and value must be separated by one or more blank spaces. For example, you can use the following GENERIC bind option syntax to bind a package with OPT1, OPT2, and OPT3 options values:
   generic "opt1 value1 opt2 value2 opt3 value3"

The maximum length of the string is 4096 bytes. The maximum length of each option name in the string is 255 bytes.

The client passes the BIND command to the Db2 server without any client validation. The Db2 server processes the BIND command and returns an appropriate message.

In Version 10.5 Fix Pack 2 and later, you can specify the application compatibility special register setting (APPLCOMPAT) with the GENERIC option of the BIND command when you are connecting to Db2 for z/OS Version 11 and later servers.

For a list of GENERIC options that are supported by Db2 for z/OS servers, see Specification of Db2 for z/OS bind options from Db2 clients.

For a list of GENERIC options that are supported by Db2 for IBM® i servers, see Table 1 in How can unlike clients override package options such as NLSS sort sequences, system naming and separate date/time formats?.

GRANT
Note: If more than one of the GRANT, GRANT_GROUP, GRANT_USER, and GRANT_ROLE options are specified, only the last option specified is executed.
authid
Grants EXECUTE and BIND privileges to a specified user name, role name or group ID. The SQL GRANT statement and its rules are used to determine the type of authid when none of USER, GROUP, or ROLE is provided to specify the type of the grantee on a GRANT statement. For the rules, see GRANT (Role) statement.
PUBLIC
Grants EXECUTE and BIND privileges to PUBLIC.
GRANT_GROUP group-name
Grants EXECUTE and BIND privileges to a specified group name.
GRANT_USER user-name
Grants EXECUTE and BIND privileges to a specified user name.
GRANT_ROLE role-name
Grants EXECUTE and BIND privileges to a specified role name.
INSERT
Allows a program being precompiled or bound against a Db2 Enterprise Server Edition server to request that data inserts be buffered to increase performance.
BUF
Specifies that inserts from an application should be buffered.
DEF
Specifies that inserts from an application should not be buffered.
ISOLATION
Determines how far a program bound to this package can be isolated from the effect of other executing programs.
CS
Specifies Cursor Stability as the isolation level.
NC
No Commit. Specifies that commitment control is not to be used. This isolation level is not supported by Db2.
RR
Specifies Repeatable Read as the isolation level.
RS
Specifies Read Stability as the isolation level. Read Stability ensures that the execution of SQL statements in the package is isolated from other application processes for rows read and changed by the application.
UR
Specifies Uncommitted Read as the isolation level.
IMMEDWRITE
Indicates whether immediate writes will be done for updates made to group buffer pool dependent page sets or database partitions. Supported by Db2 for OS/390 only. For a list of supported option values, refer to the documentation for Db2 for OS/390.
KEEPDYNAMIC

This parameter specifies whether dynamic SQL statements are to be kept after commit points.

For a list of supported option values supported for Db2 for z/OS, see the Db2 for z/OS documentation.

Starting with Db2 Version 9.8 Fix Pack 2, you can modify the value of the KEEPDYNAMIC bind option for a package without requiring a fresh bind operation, thereby avoiding unnecessary recompilation until the next bind operation occurs. The KEEPDYNAMIC bind option controls how long the statement text and section associated with a prepared statement are kept in the SQL context, specifying whether dynamic SQL statements are kept after a COMMIT or ROLLBACK.

YES

Instructs the SQL context to keep the statement text and section associated with prepared statements indefinitely. Dynamic SQL statements are kept across transactions. All packages bound with KEEPDYNAMIC YES are by default compatible with the existing package cache behavior.

No

Instructs the SQL context to remove the statement text and section associated with prepared statements at the end of each unit of work. Inactive dynamic SQL statements prepared in a package bound with KEEPDYNAMIC NO are removed from the SQL context during a COMMIT or ROLLBACK operation. The statements must be prepared again in a new transaction. The client, driver, or application needs to prepare any dynamic SQL statement it wants to reuse in a new unit of work again. If the package is executed by a remote application, executable versions for prepared statements are disassociated from the application SQL context when the transaction ends.

Active dynamic SQL statements must be kept until the next COMMIT or ROLLBACK operation where they are inactive. Here are some situations where dynamic SQL statements can be active at transaction boundaries:
  • Cursors declared using the WITH HOLD option are open at a commit point.
  • A dynamic SQL statement is executing a COMMIT or ROLLBACK operation.
  • A dynamic SQL statement invokes a stored procedure or a user defined function that is executing COMMIT or ROLLBACK operation.
MESSAGES message-file
Specifies the destination for warning, error, and completion status messages. A message file is created whether the bind is successful or not. If a message file name is not specified, the messages are written to standard output. If the complete path to the file is not specified, the current directory is used. If the name of an existing file is specified, the contents of the file are overwritten.
OPTHINT
Controls whether query optimization hints are used for static SQL. Supported by Db2 for OS/390 only. For a list of supported option values, refer to the documentation for Db2 for OS/390.
OPTPROFILE optimization-profile-name
Specifies the name of an existing optimization profile to be used for all static statements in the package. The default value of the option is an empty string. The value also applies as the default for dynamic preparation of DML statements for which the CURRENT OPTIMIZATION PROFILE special register is null. If the specified name is unqualified, it is an SQL identifier, which is implicitly qualified by the QUALIFIER bind option.

The BIND command does not process the optimization file, but only validates that the name is syntactically valid. Therefore if the optimization profile does not exist or is invalid, an SQL0437W warning with reason code 13 will not occur until a DML statement is optimized using that optimization profile.

OS400NAMING
Specifies which naming option is to be used when accessing Db2 for System i® data. Supported by Db2 for System i only. For a list of supported option values, refer to the documentation for Db2 for IBM i.
Because of the slashes used as separators, a Db2 utility can still report a syntax error at execution time on certain SQL statements which use the System i system naming convention, even though the utility might have been precompiled or bound with the OS400NAMING SYSTEM option. For example, the Command Line Processor will report a syntax error on an SQL CALL statement if the System i system naming convention is used, whether or not it has been precompiled or bound using the OS400NAMING SYSTEM option.
OWNER authorization-id
Designates a 128-byte authorization identifier for the package owner. The owner must have the privileges required to execute the SQL statements contained in the package. Only a user with DBADM authority can specify an authorization identifier other than the user ID. The default value is the authorization ID of the invoker of the precompile/bind process. SYSIBM, SYSCAT, and SYSSTAT are not valid values for this option. The authorization-id must be a user. A role or a group cannot be specified using the OWNER option.
PATH
Specifies the function path to be used in resolving user-defined distinct types and functions in static SQL. If this option is not specified, the default function path is "SYSIBM","SYSFUN",USER where USER is the value of the USER special register.
schema-name
An SQL identifier, either ordinary or delimited, which identifies a schema that exists at the application server. No validation that the schema exists is made at precompile or at bind time.
QUALIFIER qualifier-name
Provides a 128-byte implicit qualifier for unqualified objects contained in the package. The default is the owner's authorization ID, whether or not OWNER is explicitly specified.
QUERYOPT optimization-level
Indicates the required level of optimization for all static SQL statements contained in the package. The default value is 5. The SET CURRENT QUERY OPTIMIZATION statement describes the complete range of optimization levels available.
RELEASE
Indicates whether resources are released at each COMMIT point, or when the application terminates. This DRDA precompile/bind option is not supported by Db2. For Db2 for z/OS Version 10 servers or later, the default value is DEALLOCATE.
COMMIT
Release resources at each COMMIT point. Used for dynamic SQL statements.
DEALLOCATE
Release resources only when the application terminates.
SORTSEQ
Specifies which sort sequence table to use on System i. Supported by Db2 for IBM i only. For a list of supported option values, refer to the documentation for Db2 for System i.
SQLERROR
Indicates whether to create a package or a bind file if an error is encountered.
CHECK
Specifies that the target system performs all syntax and semantic checks on the SQL statements being bound. A package will not be created as part of this process. If, while binding, an existing package with the same name and version is encountered, the existing package is neither dropped nor replaced even if ACTION REPLACE was specified.
CONTINUE
Creates a package, even if errors occur when binding SQL statements. Those statements that failed to bind for authorization or existence reasons can be incrementally bound at execution time if VALIDATE RUN is also specified. Any attempt to execute them at run time generates an error (SQLCODE -525, SQLSTATE 51015).
NOPACKAGE
A package or a bind file is not created if an error is encountered.
REOPT
Specifies whether to have Db2 determine an access path at run time using values for host variables, parameter markers, global variables, and special registers. Valid values are:
NONE
The access path for a given SQL statement containing host variables, parameter markers, global variables, or special registers will not be optimized using real values. The default estimates for the these variables is used, and the plan is cached and will be used subsequently. This is the default value.
ONCE
The access path for a given SQL statement will be optimized using the real values of the host variables, parameter markers, global variables, or special registers when the query is first executed. This plan is cached and used subsequently.
ALWAYS
The access path for a given SQL statement will always be compiled and reoptimized using the values of the host variables, parameter markers, global variables, or special registers that are known each time the query is executed.
REOPT | NOREOPT VARS
These options have been replaced by REOPT ALWAYS and REOPT NONE; however, they are still supported for previous compatibility. Specifies whether to have Db2 determine an access path at run time using values for host variables, global variables, parameter markers, and special registers. Supported by Db2 for OS/390 only. For a list of supported option values, refer to the documentation for Db2 for OS/390.
SQLWARN
Indicates whether warnings will be returned from the compilation of dynamic SQL statements (via PREPARE or EXECUTE IMMEDIATE), or from describe processing (via PREPARE...INTO or DESCRIBE).
NO
Warnings will not be returned from the SQL compiler.
YES
Warnings will be returned from the SQL compiler.
SQLCODE +236, +237 and +238 are exceptions. They are returned regardless of the SQLWARN option value.
STATICREADONLY
Determines whether static cursors will be treated as being READ ONLY or INSENSITIVE.
NO
All static cursors will take on the attributes as would normally be generated given the statement text and the setting of the LANGLEVEL precompile option. This is the default value.
YES
Any static cursor that does not contain the FOR UPDATE or FOR READ ONLY clause will be considered READ ONLY.
INSENSITIVE
Any static cursor that does not contain the FOR UPDATE clause will be considered READ ONLY and INSENSITIVE.
STRDEL
Designates whether an apostrophe (') or double quotation marks (") will be used as the string delimiter within SQL statements. This precompile/bind option is not supported by the server for Db2. The DRDA server will use a built-in default value if this option is not specified.
APOSTROPHE
Use an apostrophe (') as the string delimiter.
QUOTE
Use double quotation marks (") as the string delimiter.
STRING_UNITS
Specifies the string units when character and graphic data types are used without explicit string units in static SQL statements. The default is based on the string_units database configuration parameter setting for the database.
SYSTEM
In a Unicode database, the setting has the following effect:
  • CHAR, VARCHAR, and CLOB data types defined without specifying the CODEUNITS32 keyword will default to OCTETS.
  • GRAPHIC, VARGRAPHIC, and DBCLOB data types defined without specifying the CODEUNITS32 keyword will default to CODEUNITS16.
In a non-Unicode database, the setting has the following effect:
  • CHAR, VARCHAR, and CLOB data types defined without specifying the CODEUNITS32 keyword will default to OCTETS.
  • GRAPHIC, VARGRAPHIC, and DBCLOB data types have implicit string units of double bytes.
CODEUNITS32
This setting is only valid for a Unicode database and has the following effect:
  • CHAR, VARCHAR, and CLOB data types defined without specifying the BYTE or OCTETS keywords will default to CODEUNITS32.
  • GRAPHIC, VARGRAPHIC, and DBCLOB data types defined without specifying the CODEUNITS16 keyword will default to CODEUNITS32.
SYSTIMESENSITIVE
Indicates whether references to system-period temporal tables in static and dynamic SQL statements are affected by the value of the CURRENT TEMPORAL SYSTEM_TIME special register.
YES
References to system-period temporal tables are affected by the value of the CURRENT TEMPORAL SYSTEM_TIME special register. This value is the default value.
NO
References to system-period temporal tables are not affected by the value of the CURRENT TEMPORAL SYSTEM_TIME special register.
TEXT label
The description of a package. Maximum length is 255 characters. The default value is blanks. This precompile/bind option is not supported by the server for Db2.
TRANSFORM GROUP
Specifies the transform group name to be used by static SQL statements for exchanging user-defined structured type values with host programs. This transform group is not used for dynamic SQL statements or for the exchange of parameters and results with external functions or methods.
groupname
An SQL identifier of up to 18 bytes in length. A group name cannot include a qualifier prefix and cannot begin with the prefix SYS since this is reserved for database use. In a static SQL statement that interacts with host variables, the name of the transform group to be used for exchanging values of a structured type is as follows:
  • The group name in the TRANSFORM GROUP bind option, if any
  • The group name in the TRANSFORM GROUP prep option as specified at the original precompilation time, if any
  • The DB2_PROGRAM group, if a transform exists for the given type whose group name is DB2_PROGRAM
  • No transform group is used if none of the previously listed conditions exist.
The following errors are possible during the bind of a static SQL statement:
  • SQLCODE yyyyy, SQLSTATE xxxxx: A transform is needed, but no static transform group has been selected.
  • SQLCODE yyyyy, SQLSTATE xxxxx: The selected transform group does not include a necessary transform (TO SQL for input variables, FROM SQL for output variables) for the data type that needs to be exchanged.
  • SQLCODE yyyyy, SQLSTATE xxxxx: The result type of the FROM SQL transform is not compatible with the type of the output variable, or the parameter type of the TO SQL transform is not compatible with the type of the input variable.
In these error messages, yyyyy is replaced by the SQL error code, and xxxxx by the SQL state code.
VALIDATE
Determines when the database manager checks for authorization errors and object not found errors. The package owner authorization ID is used for validity checking.
BIND
Validation is performed at precompile/bind time. If all objects do not exist, or all authority is not held, error messages are produced. If SQLERROR CONTINUE is specified, a package/bind file is produced despite the error message, but the statements in error are not executable.
RUN
Validation is attempted at bind time. If all objects exist, and all authority is held, no further checking is performed at execution time.

If all objects do not exist, or all authority is not held at precompile/bind time, warning messages are produced, and the package is successfully bound, regardless of the SQLERROR CONTINUE option setting. However, authority checking and existence checking for SQL statements that failed these checks during the precompile/bind process can be redone at execution time.

Examples

The following example binds myapp.bnd (the bind file generated when the myapp.sqc program was precompiled) to the database to which a connection has been established:
   db2 bind myapp.bnd

Any messages resulting from the bind process are sent to standard output.

Usage notes

Binding a package using the REOPT option with the ONCE or ALWAYS value specified might change the static and dynamic statement compilation and performance.

Binding can be done as part of the precompile process for an application program source file, or as a separate step at a later time. Use BIND when binding is performed as a separate process.

The name used to create the package is stored in the bind file, and is based on the source file name from which it was generated (existing paths or extensions are discarded). For example, a precompiled source file called myapp.sql generates a default bind file called myapp.bnd and a default package name of MYAPP. However, the bind file name and the package name can be overridden at precompile time by using the BINDFILE and the PACKAGE options.

Binding a package with a schema name that does not already exist results in the implicit creation of that schema. The schema owner is SYSIBM. The CREATEIN privilege on the schema is granted to PUBLIC.

BIND executes under the transaction that was started. After performing the bind, BIND issues a COMMIT or a ROLLBACK to terminate the current transaction and start another one.

Binding stops if a fatal error or more than 100 errors occur. If a fatal error occurs, the utility stops binding, attempts to close all files, and discards the package.

When a package exhibits bind behavior, the following will be true:
  1. The implicit or explicit value of the BIND option OWNER will be used for authorization checking of dynamic SQL statements.
  2. The implicit or explicit value of the BIND option QUALIFIER will be used as the implicit qualifier for qualification of unqualified objects within dynamic SQL statements.
  3. The value of the special register CURRENT SCHEMA has no effect on qualification.

In the event that multiple packages are referenced during a single connection, all dynamic SQL statements prepared by those packages will exhibit the behavior as specified by the DYNAMICRULES option for that specific package and the environment they are used in.

Parameters displayed in the SQL0020W message are correctly noted as errors, and will be ignored as indicated by the message.

If an SQL statement is found to be in error and the BIND option SQLERROR CONTINUE was specified, the statement will be marked as invalid. In order to change the state of the SQL statement, another BIND must be issued . Implicit and explicit rebind will not change the state of an invalid statement. In a package bound with VALIDATE RUN, a statement can change from static to incremental bind or incremental bind to static across implicit and explicit rebinds depending on whether or not object existence or authority problems exist during the rebind.

The privileges from the roles granted to the authorization identifier used to bind the package (the value of the OWNER bind option) or to PUBLIC, are taken into account when binding a package. Roles acquired through groups, in which the authorization identifier used to bind the package is a member, will not be used.

For an embedded SQL program, if the bind option is not explicitly specified the static statements in the package are bound using the federated_async configuration parameter. If the FEDERATED_ASYNCHRONY bind option is specified explicitly, that value is used for binding the packages and is also the initial value of the special register. Otherwise, the value of the database manager configuration parameter is used as the initial value of the special register. The FEDERATED_ASYNCHRONY bind option influences dynamic SQL only when it is explicitly set.

The value of the FEDERATED_ASYNCHRONY bind option is recorded in the FEDERATED_ASYNCHRONY column in the SYSCAT.PACKAGES catalog table. When the bind option is not explicitly specified, the value of federated_async configuration parameter is used and the catalog shows a value of -2 for the FEDERATED_ASYNCHRONY column.

If the FEDERATED_ASYNCHRONY bind option is not explicitly specified when a package is bound, and if this package is implicitly or explicitly rebound, the package is rebound using the current value of the federated_async configuration parameter.