DB2 Version 9.7 for Linux, UNIX, and Windows

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® for Linux, UNIX and Windows


>>-BIND--filename----------------------------------------------->

>--+-----------------------------------------------------------------------+-->
   '-ACTION--+-ADD-------------------------------------------------------+-'   
             | .-REPLACE-.                                               |     
             '-+---------+--+-----------------+--+---------------------+-'     
                            '-RETAIN--+-NO--+-'  '-REPLVER--version-id-'       
                                      '-YES-'                                  

>--+------------------+--+-----------------------+-------------->
   '-APREUSE--+-NO--+-'  '-BLOCKING--+-UNAMBIG-+-'   
              '-YES-'                +-ALL-----+     
                                     '-NO------'     

>--+----------------------+--+-------------------------+-------->
   '-CLIPKG--cli-packages-'  '-COLLECTION--schema-name-'   

>--+---------------------------------------------------------+-->
   '-CONCURRENTACCESSRESOLUTION--+-USECURRENTLYCOMMITTED---+-'   
                                 +-WAITFOROUTCOME----------+     
                                 +-USE CURRENTLY COMMITTED-+     
                                 '-WAIT FOR OUTCOME--------'     

>--+-------------------+---------------------------------------->
   '-DATETIME--+-DEF-+-'   
               +-EUR-+     
               +-ISO-+     
               +-JIS-+     
               +-LOC-+     
               '-USA-'     

>--+-----------------------------------+------------------------>
   '-DEGREE--+-1---------------------+-'   
             +-degree-of-parallelism-+     
             '-ANY-------------------'     

>--+------------------------------+--+--------------------+----->
   '-DYNAMICRULES--+-RUN--------+-'  '-EXPLAIN--+-NO----+-'   
                   +-BIND-------+               +-ALL---+     
                   +-INVOKERUN--+               +-REOPT-+     
                   +-INVOKEBIND-+               +-ONLY--+     
                   +-DEFINERUN--+               '-YES---'     
                   '-DEFINEBIND-'                             

>--+---------------------+--+----------------------------+------>
   '-EXPLSNAP--+-NO----+-'  |                    .-NO--. |   
               +-ALL---+    '-EXTENDEDINDICATOR--+-YES-+-'   
               +-REOPT-+                                     
               '-YES---'                                     

>--+--------------------+--------------------------------------->
   '-FEDERATED--+-NO--+-'   
                '-YES-'     

>--+------------------------------------------------------+----->
   '-FEDERATED_ASYNCHRONY--+-ANY------------------------+-'   
                           '-number_of_atqs_in_the_plan-'     

>--+---------------------------+--+-------------------+--------->
   |           .-,-----------. |  '-GENERIC--"string"-'   
   |           V             | |                          
   '-FUNCPATH----schema-name-+-'                          

>--+-------------------------+--+-----------------+------------->
   +-GRANT--+-authid-+-------+  |         .-DEF-. |   
   |        '-PUBLIC-'       |  '-INSERT--+-BUF-+-'   
   +-GRANT_GROUP--group-name-+                        
   +-GRANT_USER--user-name---+                        
   '-GRANT_ROLE--role-name---'                        

>--+-------------------+--+------------------------+------------>
   '-ISOLATION--+-CS-+-'  '-MESSAGES--message-file-'   
                +-RR-+                                 
                +-RS-+                                 
                '-UR-'                                 

>--+---------------------------------------+-------------------->
   '-OPTPROFILE--optimization-profile-name-'   

>--+-------------------------+--+---------------------------+--->
   '-OWNER--authorization-id-'  '-QUALIFIER--qualifier-name-'   

                                     .-REOPT NONE---.   
>--+------------------------------+--+--------------+----------->
   '-QUERYOPT--optimization-level-'  +-REOPT ONCE---+   
                                     '-REOPT ALWAYS-'   

>--+-------------------------+--+------------------+------------>
   '-SQLERROR--+-CHECK-----+-'  '-SQLWARN--+-NO--+-'   
               +-CONTINUE--+               '-YES-'     
               '-NOPACKAGE-'                           

>--+---------------------------------+-------------------------->
   |                 .-NO----------. |   
   '-STATICREADONLY--+-YES---------+-'   
                     '-INSENSITIVE-'     

>--+--------------------+--+----------------------------+------><
   '-VALIDATE--+-BIND-+-'  '-TRANSFORM GROUP--groupname-'   
               '-RUN--'

For DB2 on servers other than Linux, UNIX and Windows


>>-BIND--filename----------------------------------------------->

>--+-----------------------------------------------------------------------+-->
   '-ACTION--+-ADD-------------------------------------------------------+-'   
             | .-REPLACE-.                                               |     
             '-+---------+--+-----------------+--+---------------------+-'     
                            '-RETAIN--+-NO--+-'  '-REPLVER--version-id-'       
                                      '-YES-'                                  

>--+-----------------------+--+----------------------+---------->
   |           .-UNAMBIG-. |  '-CCSIDG--double-ccsid-'   
   '-BLOCKING--+-ALL-----+-'                             
               '-NO------'                               

>--+---------------------+--+--------------------+-------------->
   '-CCSIDM--mixed-ccsid-'  '-CCSIDS--sbcs-ccsid-'   

>--+----------------------+--+----------------------+----------->
   '-CHARSUB--+-DEFAULT-+-'  '-CLIPKG--cli-packages-'   
              +-BIT-----+                               
              +-MIXED---+                               
              '-SBCS----'                               

>--+-------------------+--+-------------------------+----------->
   '-CNULREQD--+-NO--+-'  '-COLLECTION--schema-name-'   
               '-YES-'                                  

>--+---------------------------------------------------------+-->
   '-CONCURRENTACCESSRESOLUTION--+-USECURRENTLYCOMMITTED---+-'   
                                 +-WAITFOROUTCOME----------+     
                                 +-USE CURRENTLY COMMITTED-+     
                                 '-WAIT FOR OUTCOME--------'     

>--+-------------------------+--+-------------------------+----->
   |  (1)                    |  '-DBPROTOCOL--+-DRDA----+-'   
   '-------DATETIME--+-DEF-+-'                '-PRIVATE-'     
                     +-EUR-+                                  
                     +-ISO-+                                  
                     +-JIS-+                                  
                     +-LOC-+                                  
                     '-USA-'                                  

>--+-------------+--+--------------------+---------------------->
   '-DEC--+-15-+-'  '-DECDEL--+-COMMA--+-'   
          '-31-'              '-PERIOD-'     

>--+-----------------------------------------+------------------>
   |  (2)                                    |   
   '-------DEGREE--+-1---------------------+-'   
                   +-degree-of-parallelism-+     
                   '-ANY-------------------'     

>--+------------------------------+----------------------------->
   |               .-RUN--------. |   
   '-DYNAMICRULES--+-BIND-------+-'   
                   +-INVOKERUN--+     
                   +-INVOKEBIND-+     
                   +-DEFINERUN--+     
                   '-DEFINEBIND-'     

>--+-----------------------+--+------------------------+-------->
   '-ENCODING--+-ASCII---+-'  |  (3)                   |   
               +-EBCDIC--+    '-------EXPLAIN--+-NO--+-'   
               +-UNICODE-+                     '-YES-'     
               '-CCSID---'                                 

>--+----------------------------+--+-------------------+-------->
   |                    .-NO--. |  '-GENERIC--"string"-'   
   '-EXTENDEDINDICATOR--+-YES-+-'                          

>--+-------------------+--+---------------------+--------------->
   '-GRANT--+-authid-+-'  '-IMMEDWRITE--+-NO--+-'   
            '-PUBLIC-'                  +-YES-+     
                                        '-PH1-'     

>--+-----------------+--+-------------------+------------------->
   '-INSERT--+-BUF-+-'  |            .-CS-. |   
             '-DEF-'    '-ISOLATION--+-NC-+-'   
                                     +-RR-+     
                                     +-RS-+     
                                     '-UR-'     

>--+----------------------+--+------------------------+--------->
   '-KEEPDYNAMIC--+-YES-+-'  '-MESSAGES--message-file-'   
                  '-NO -'                                 

>--+----------------------+--+-------------------------+-------->
   '-OPTHINT----hint-id---'  '-OS400NAMING--+-SYSTEM-+-'   
                                            '-SQL----'     

>--+-------------------------+--+-------------------+----------->
   '-OWNER--authorization-id-'  '-PATH--schema-name-'   

>--+---------------------------+--+-------------------------+--->
   '-QUALIFIER--qualifier-name-'  '-RELEASE--+-COMMIT-----+-'   
                                             '-DEALLOCATE-'     

   .-REOPT NONE---.                     
>--+--------------+--+--------------+--------------------------->
   +-REOPT ONCE---+  +-REOPT VARS---+   
   '-REOPT ALWAYS-'  '-NOREOPT VARS-'   

>--+---------------------+--+-------------------------+--------->
   '-SORTSEQ--+-JOBRUN-+-'  '-SQLERROR--+-CHECK-----+-'   
              '-HEX----'                +-CONTINUE--+     
                                        '-NOPACKAGE-'     

>--+--------------------+--+------------------------+----------->
   '-VALIDATE--+-BIND-+-'  '-STRDEL--+-APOSTROPHE-+-'   
               '-RUN--'              '-QUOTE------'     

>--+-------------+---------------------------------------------><
   '-TEXT--label-'

Notes:

If the server does not support the DATETIME DEF option, it is mapped to DATETIME ISO.
The DEGREE option is only supported by DRDA® Level 2 Application Servers.
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

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.

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 for Linux, UNIX, and Windows. The DRDA server will use a system defined 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 for Linux, UNIX, and Windows. The DRDA server will use a system defined 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 the server for DB2 for Linux, UNIX, and Windows. The DRDA server will use a system defined 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 for Linux, UNIX, and Windows.

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 system defined 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 for Linux, UNIX, and Windows.

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 to 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 OS/390® only. For a list of supported option values, refer to the documentation for DB2 for OS/390.

DEC

Specifies the maximum precision to be used in decimal arithmetic operations. This precompile/bind option is not supported by the server for DB2 for Linux, UNIX, and Windows. The DRDA server will use a system defined 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 for Linux, UNIX, and Windows. The DRDA server will use a system defined 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 standalone 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 standalone 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 standalone 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 standalone 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 new options that are supported by the target server, but not supported by DB2 for Linux, UNIX, and Windows as options for BIND PACKAGE or REBIND PACKAGE. Do not use this option to pass bind options that are defined in BIND or PRECOMPILE. The syntax is as follows:

   generic "option1 value1 option2 value2 ..."

Each option and value must be separated by one or more blank spaces. For example, one could use the following to bind each of the OPT1, OPT2, and OPT3 options:

   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.

For remote BIND commands, the BIND GENERIC clause can be used to flow new DB2 server bind options without requiring upgrade of the client. The client will pass the BIND command to the DB2 server without checking the syntax of this option. The DB2 server will check BIND command syntax and process.

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 for Linux, UNIX, and Windows.
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 pagesets 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

Specifies whether dynamic SQL statements are to be kept after commit points. Supported by DB2 for OS/390 only. For a list of supported option values, refer to the documentation for DB2 for OS/390.

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 System 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

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 desired 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 precompile/bind option is not supported by the server for DB2 for Linux, UNIX, and Windows. For DB2 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 System 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 for Linux, UNIX, and Windows. The DRDA server will use a system defined 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.

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 for Linux, UNIX, and Windows.

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 above 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:

The implicit or explicit value of the BIND option OWNER will be used for authorization checking of dynamic SQL statements.
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.
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.