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.
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.
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.
Database. If implicit connect is enabled, a connection to the default database is established.
>>-BIND--filename-----------------------------------------------> >--+-----------------------------------------------------------------------+--> '-ACTION--+-ADD-------------------------------------------------------+-' | .-REPLACE-. | '-+---------+--+-----------------+--+---------------------+-' '-RETAIN--+-NO--+-' '-REPLVER--version-id-' '-YES-' >--+------------------+--+-----------------------+--------------> '-APREUSE--+-NO--+-' '-BLOCKING--+-UNAMBIG-+-' '-YES-' +-ALL-----+ '-NO------' >--+---------------------------+--+----------------------+------> '-BUSTIMESENSITIVE--+-YES-+-' '-CLIPKG--cli-packages-' '-NO--' >--+-------------------------+----------------------------------> '-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-+-' '-KEEPDYNAMIC--+-YES-+-' +-RR-+ '-NO -' +-RS-+ '-UR-' >--+------------------------+-----------------------------------> '-MESSAGES--message-file-' >--+---------------------------------------+--------------------> '-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-' >--+---------------------------------+--------------------------> '-STATICREADONLY--+-NO----------+-' +-YES---------+ '-INSENSITIVE-' >--+-------------------------------+----------------------------> '-STRING_UNITS--+-SYSTEM------+-' '-CODEUNITS32-' >--+---------------------------+--------------------------------> '-SYSTIMESENSITIVE--+-YES-+-' '-NO--' >--+----------------------------+--+--------------------+------>< '-TRANSFORM GROUP--groupname-' '-VALIDATE--+-BIND-+-' '-RUN--'
>>-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-'
/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
Ambiguous cursors are treated as read-only.
For the definition of a read-only cursor and an ambiguous cursor, refer to DECLARE CURSOR statement.
Ambiguous cursors are treated as updatable.
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.
If the package is used as a stand-alone application, dynamic SQL statements are processed as if the package were bound with DYNAMICRULES RUN.
If the package is used as a stand-alone application, dynamic SQL statements are processed as if the package were bound with DYNAMICRULES BIND.
If the package is used as a stand-alone application, dynamic SQL statements are processed as if the package were bound with DYNAMICRULES RUN.
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.
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).
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).
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).
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).
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).
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).
generic "option1 value1 option2 value2 ..."
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 for Linux, UNIX, and Windows clients".
For a list of GENERIC options that are supported by DB2 for 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?".
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 documentation in the DB2 for z/OS Information Center.
Starting with DB2 for Linux, UNIX, and Windows 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.
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.
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.
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.
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.
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.
db2 bind myapp.bnd
Any messages resulting from the bind process are sent to standard output.
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.
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.