Processes an application program source file containing
embedded SQL statements. A modified source file is produced, containing
host language calls for the SQL statements and, by default, a package
is created in the database.
Scope
This command can be issued from any
database partition in db2nodes.cfg. In a partitioned
database environment, it can be issued from any database partition
server defined in the db2nodes.cfg file. 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 parameters
- filename
- Specifies the source file to be precompiled. An extension of:
- .sqc must be specified for C applications (generates
a .c file)
- .sqx (Windows operating
systems), or .sqC (UNIX and Linux operating systems) must be
specified for C++ applications (generates a .cxx file
on Windows operating systems,
or a .C file on UNIX and Linux operating systems)
- .sqb must be specified for COBOL applications
(generates a .cbl file)
- .sqf must be specified for FORTRAN applications
(generates a .for file on Windows operating systems, or a .f file
on UNIX and Linux operating systems).
The preferred extension for C++ applications containing embedded
SQL on UNIX and Linux operating systems is sqC;
however, the sqx convention, which was invented for
systems that are not case sensitive, is tolerated by UNIX and Linux operating
systems.
- 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 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 EXECUTE authorities when a package is replaced.
This value is not supported by DB2.
- YES
- Preserves 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 and version of the package being
precompiled, 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.
- BINDFILE
- Results in the creation of a bind file. A package is not created
unless the package option is also specified. If a bind file is requested,
but no package is to be created, as in the following example:
db2 prep sample.sqc bindfile
Object
existence and authorization SQLCODEs will be treated as warnings instead
of errors. This will allow a bind file to be successfully created,
even if the database being used for precompilation does not have all
of the objects referred to in static SQL statements within the application.
The bind file can be successfully bound, creating a package, once
the required objects have been created.
- USING bind-file
- The name of the bind file that is to be generated by the precompiler.
The file name must have an extension of .bnd. If
a file name is not entered, the precompiler uses the name of the program
(entered as the filename parameter), and adds the .bnd extension.
If a path is not provided, the bind file is created in the current
directory.
- 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.
- CALL_RESOLUTION
- If set, the CALL_RESOLUTION DEFERRED option
indicates that the CALL statement will be executed as an invocation
of the deprecated sqleproc() API. If not set
or if IMMEDIATE is set, the CALL statement will be
executed as a normal SQL statement. SQL0204 will
be issued if the precompiler fails to resolve the procedure on a CALL
statement with CALL_RESOLUTION IMMEDIATE.
- 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
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 for Linux, UNIX, and Windows.
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 the server for DB2 for Linux, UNIX, and Windows.
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 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 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.
- 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.
- COMPATIBILITY_MODE
- ORA
- The DB2 database manager
provides features that facilitate the migration of embedded SQL C
applications from other database systems. You can enable these compatibility
features by setting the COMPATIBILITY_MODE to ORA.
For example, the following command enables the compatibility features
when you compile the tbsel.sqc file:
$ db2 PRECOMPILE tbsel.sqc BINDFILE COMPATIBILITY_MODE ORA
- 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 no longer apply.
- 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.
- CONNECT
-
- 1
- Specifies that a CONNECT statement is to be processed as a type
1 CONNECT.
- 2
- Specifies that a CONNECT statement is to be processed as a type
2 CONNECT.
- 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 or bind option is not supported by the
server for DB2 for Linux, UNIX, and Windows.
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 for Linux, UNIX, and Windows.
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.
- DEFERRED_PREPARE
- Provides a performance enhancement when accessing DB2 common
server databases or DRDA databases.
This option combines the SQL PREPARE statement flow with the associated
OPEN, DESCRIBE, or EXECUTE statement flow to minimize inter-process
or network flow.
- NO
- The PREPARE statement will be executed at the time it is issued.
- YES
- Execution of the PREPARE statement will be deferred until the
corresponding OPEN, DESCRIBE, or EXECUTE statement is issued.
The
PREPARE statement will not be deferred if it uses the INTO clause,
which requires an SQLDA to be returned immediately. However, if the
PREPARE INTO statement is issued for a cursor that does not use
any parameter markers, the processing will be optimized by pre-OPENing
the cursor when the PREPARE is executed.
- ALL
- Same as YES, except that a PREPARE INTO statement is also deferred.
If the PREPARE statement uses the INTO clause to return an SQLDA,
the application must not reference the content of this SQLDA until
the OPEN, DESCRIBE, or EXECUTE statement is issued and returned.
- 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.
- DISCONNECT
-
- AUTOMATIC
- Specifies that all database connections are to be disconnected
at commit.
- CONDITIONAL
- Specifies that the database connections that have been marked
RELEASE or have no open WITH HOLD cursors are to be disconnected at
commit.
- EXPLICIT
- Specifies that only database connections that have been explicitly
marked for release by the RELEASE statement are to be disconnected
at commit.
- 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 will be placed in the Explain tables at run time. In addition,
Explain information will be gathered for reoptimizable dynamic SQL
statements at run time, even if the CURRENT EXPLAIN MODE special
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).
- 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 special 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).
- 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 will be placed in the Explain tables at run time.
In addition, Explain Snapshot information will be gathered for reoptimizable
dynamic SQL statements at run time, even if the CURRENT EXPLAIN SNAPSHOT
special 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).
- ALL
- An Explain Snapshot for each eligible static SQL statement will
be placed in the Explain tables at prep or 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 special 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, or incremental
bind and dynamic statements in the package will cause a run time
error (SQLSTATE 42985).
- 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.
- INSERT
- Allows a program being precompiled or bound against a 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.
- GENERIC "string"
- Supports the binding of new options that are defined in the target
database. 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.
- 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.
- 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.
- KEEPDYNAMIC
This parameter specifies whether dynamic SQL statements are
to be kept across transactions.
For details about the DB2 for z/OS® supported option, see documentation in
the DB2 for z/OS Information Center.
Starting
with DB2 for Linux, UNIX,
and Windows Version 9.8 Fix
Pack 2, dynamic SQL statements prepared in a package bound with the KEEPDYNAMIC YES option
are kept in the SQL context after a COMMIT or ROLLBACK operation.
This is the default behavior.
- 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.
Dynamic SQL statements can
remain active beyond the end of a transaction under the following
circumstances:
- 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.
- LANGLEVEL
- Specifies the SQL rules that apply for both the syntax and the
semantics for both static and dynamic SQL in the application.
- MIA
- Select the ISO/ANS SQL92 rules as follows:
- To support error SQLCODE or SQLSTATE checking, an SQLCA must be
declared in the application code.
- C null-terminated strings are padded with blanks and always include
a null-terminating character, even if truncation occurs.
- The FOR UPDATE clause is optional for all columns to be updated
in a positioned UPDATE.
- A searched UPDATE or DELETE requires SELECT privilege on the object
table of the UPDATE or DELETE statement if a column of the object
table is referenced in the search condition or on the right side of
the assignment clause.
- A column function that can be resolved using an index (for example
MIN or MAX) will also check for nulls and return warning SQLSTATE
01003 if there were any nulls.
- An error is returned when a duplicate unique constraint is included
in a CREATE or ALTER TABLE statement.
- An error is returned when no privilege is granted and the grantor
has no privileges on the object (otherwise a warning is returned).
- SAA1
- Select the common IBM® DB2 rules as follows:
- To support error SQLCODE or SQLSTATE checking, an SQLCA must be
declared in the application code.
- C null-terminated strings are not terminated with a null character
if truncation occurs.
- The FOR UPDATE clause is required for all columns to be updated
in a positioned UPDATE.
- A searched UPDATE or DELETE will not require SELECT privilege
on the object table of the UPDATE or DELETE statement unless a fullselect
in the statement references the object table.
- A column function that can be resolved using an index (for example
MIN or MAX) will not check for nulls and warning SQLSTATE 01003 is
not returned.
- A warning is returned and the duplicate unique constraint is ignored.
- An error is returned when no privilege is granted.
- SQL92E
- Defines the ISO/ANS SQL92 rules as follows:
- To support checking of SQLCODE or SQLSTATE values, variables by
this name can be declared in the host variable declare section (if
neither is declared, SQLCODE is assumed during precompilation).
- C null-terminated strings are padded with blanks and always include
a null-terminating character, even if truncation occurs.
- The FOR UPDATE clause is optional for all columns to be updated
in a positioned UPDATE.
- A searched UPDATE or DELETE requires SELECT privilege on the object
table of the UPDATE or DELETE statement if a column of the object
table is referenced in the search condition or on the right side of
the assignment clause.
- A column function that can be resolved using an index (for example
MIN or MAX) will also check for nulls and return warning SQLSTATE
01003 if there were any nulls.
- An error is returned when a duplicate unique constraint is included
in a CREATE or ALTER TABLE statement.
- An error is returned when no privilege is granted and the grantor
has no privileges on the object (otherwise a warning is returned).
- LEVEL consistency-token
- Defines the level of a module using the consistency token. The
consistency token is any alphanumeric value up to 8 characters in
length. The RDB package consistency token verifies that the requester's
application and the relational database package are synchronized.
This option is not recommended for general use.
- LONGERROR
- Indicates whether long host variable declarations will be treated
as an error. For portability, sqlint32 can be used as a declaration
for an INTEGER column in precompiled C and C++ code.
- NO
- Does not generate errors for the use of long host variable declarations.
This is the default for 32 bit systems and for 64 bit Windows systems where long host variables
can be used as declarations for INTEGER columns. The use of this
option on 64 bit UNIX platforms
will allow long host variables to be used as declarations for BIGINT
columns.
- YES
- Generates errors for the use of long host variable declarations.
This is the default for 64 bit UNIX systems.
- 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.
- NOLINEMACRO
- Suppresses the generation of the #line macros in the output .c file.
Useful when the file is used with development tools which require
source line information such as profiles, cross-reference utilities,
and debuggers. This precompile option is used for the C/C++ programming
languages only.
- 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.
- OPTLEVEL
- Indicates whether the C/C++ precompiler is to optimize initialization
of internal SQLDAs when host variables are used in SQL statements.
Such optimization can increase performance when a single SQL statement
(such as FETCH) is used inside a tight loop.
- 0
- Instructs the precompiler not to optimize SQLDA initialization.
- 1
- Instructs the precompiler to optimize SQLDA initialization. This
value should not be specified if the application uses:
- 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.
- OUTPUT filename
- Overrides the default name of the modified source file produced
by the compiler. It can include a path.
- 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 primary authorization ID
of the precompile/bind process. SYSIBM, SYSCAT, and SYSSTAT are not
valid values for this option. The authorization-id can
only be a user (cannot be a role or a group).
- PACKAGE
- Creates a package. If neither PACKAGE, BINDFILE,
nor SYNTAX is specified, a package is created
in the database by default.
- USING package-name
- The name of the package that is to be generated by the precompiler.
If a name is not entered, the name of the application program source
file (minus extension and folded to uppercase) is used. Maximum length
is 128 bytes.
- PREPROCESSOR "preprocessor-command"
-
Specifies the preprocessor command that can be executed by
the precompiler before it processes embedded SQL statements. The preprocessor
command string (maximum length 1024 bytes) must be enclosed either
by double or by single quotation marks.
This option enables
the use of macros within the declare section. A valid preprocessor
command is one that can be issued from the command line to invoke
the preprocessor without specifying a source file. For example,
xlc -P -DMYMACRO=0
- QUALIFIER qualifier-name
- Provides an 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 precompile/bind option is
not supported by the server for DB2 for Linux, UNIX, and Windows.
- COMMIT
- Release resources at each COMMIT point. Used for dynamic SQL
statements.
- DEALLOCATE
- Release resources only when the application terminates.
- REOPT
- Specifies whether to have DB2 optimize
an access path 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 for these variables. The default estimates
for the these variables will be used instead, and this plan is cached
and used subsequently. This is the default behavior.
- 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 known at each execution
time.
- REOPT | NOREOPT VARS
- These options have been replaced by REOPT ALWAYS and REOPT NONE;
however, they are still supported for compatibility with previous
releases. 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.
- SQLCA
- For FORTRAN applications only. This option is ignored if it is
used with other languages.
- NONE
- Specifies that the modified source code is not consistent with
the SAA definition.
- SAA
- Specifies that the modified source code is consistent with the SAA definition.
- 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.
- SQLFLAG
- Identifies and reports on deviations from the SQL language syntax
specified in this option.
A bind file or a package is created
only if the BINDFILE or the PACKAGE option
is specified, in addition to the SQLFLAG option.
Local
syntax checking is performed only if one of the following options
is specified:
- BINDFILE
- PACKAGE
- SQLERROR CHECK
- SYNTAX
If
SQLFLAG is not specified, the
flagger function is not invoked, and the bind file or the package
is not affected.
- SQL92E SYNTAX
- The SQL statements will be checked against ANSI or ISO SQL92 Entry
level SQL language format and syntax with the exception of syntax
rules that would require access to the database catalog. Any deviation
is reported in the precompiler listing.
- MVSDB2V23 SYNTAX
- The SQL statements will be checked against MVS™ DB2 Version
2.3 SQL language syntax. Any deviation from the syntax is reported
in the precompiler listing.
- MVSDB2V31 SYNTAX
- The SQL statements will be checked against MVS DB2 Version
3.1 SQL language syntax. Any deviation from the syntax is reported
in the precompiler listing.
- MVSDB2V41 SYNTAX
- The SQL statements will be checked against MVS DB2 Version
4.1 SQL language syntax. Any deviation from the syntax is reported
in the precompiler listing.
- SORTSEQ
- Specifies which sort sequence table to use on the System i system.
Supported by DB2 for System i only.
For a list of supported option values, refer to the documentation
for DB2 for System i.
- SQLRULES
- Specifies:
- Whether type 2 CONNECTs are to be processed according to the DB2 rules
or the Standard (STD) rules based on ISO/ANS SQL92.
- How an application specifies the format of LOB columns in the
result set.
- DB2
-
- Permits the SQL CONNECT statement to switch the current connection
to another established (dormant) connection.
- This default setting allows an application to specify whether
LOB values or LOB locators are retrieved only during the first fetch
request. Subsequent fetch requests must use the same format for the
LOB columns.
- STD
-
- Permits the SQL CONNECT statement to establish a new connection
only. The SQL SET CONNECTION statement must be used to switch to a
dormant connection.
- The application can change between retrieving LOB values and LOB
locators with each fetch request. This means that cursors with one
or more LOB columns cannot be blocked, regardless of the BLOCKING
bind option setting.
- 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 +238 is an exception. It is returned regardless
of the SQLWARN option value.
- STATICREADONLY
- Determines whether static cursors will be treated as being READ
ONLY.
- 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.
- 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
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.
- SYNCPOINT
- Specifies how commits or rollbacks are to be coordinated among
multiple database connections. This command parameter is ignored and
is only included here for backward compatibility.
- NONE
- Specifies that no Transaction Manager (TM) is to be used to perform
a two-phase commit, and does not enforce single updater, multiple
reader. A COMMIT is sent to each participating database. The application
is responsible for recovery if any of the commits fail.
- ONEPHASE
- Specifies that no TM is to be used to perform a two-phase commit.
A one-phase commit is to be used to commit the work done by each database
in multiple database transactions.
- TWOPHASE
- Specifies that the TM is required to coordinate two-phase commits
among those databases that support this protocol.
- SYNTAX
- Suppresses the creation of a package or a bind file during precompilation.
This option can be used to check the validity of the source file without
modifying or altering existing packages or bind files. SYNTAX is
a synonym for SQLERROR CHECK.
If SYNTAX is used together with the PACKAGE option, PACKAGE is
ignored.
- TARGET
- Instructs the precompiler to produce modified code tailored to
one of the supported compilers on the current platform.
- IBMCOB
- On AIX®,
code is generated for the IBM COBOL
Set for AIX compiler.
- MFCOB
- Code is generated for the Micro Focus COBOL compiler. This is
the default if a TARGET value is not specified
with the COBOL precompiler on all Linux, UNIX and Windows operating systems.
- ANSI_COBOL
- Code compatible with the ANS X3.23-1985 standard is generated.
- C
- Code compatible with the C compilers supported by DB2 on the current platform is generated.
- CPLUSPLUS
- Code compatible with the C++ compilers supported by DB2 on the current platform is generated.
- FORTRAN
- Code compatible with the FORTRAN compilers supported by DB2 on the current platform is generated.
- 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 128 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 yyy, SQLSTATE xxxxx:
A transform is needed, but no static transform group has been selected.
- SQLCODE yyy, 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 yyy, 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.
- UNSAFENULL
- Starting in DB2 V10.1 Fix
Pack 2 and later, PRECOMPILE option UNSAFENULL YES with
combination of COMPATIBILITY_MODEORA provides
compatibility to suppress unspecified indicator variable error (generated
when NULL value exists while the application has not specified NULL
indicator in the program) while migrating to the DB2 applications.
- NO
- By default UNSAFENULL functionality will
be set to NO.
- If
the data type can handle nulls, the application must provide a null
indicator. If a null indicator is not used and while fetching the
result-set, it is found that one or more of the column data is NULL,
unspecified indicator variable error will be returned to the application.
- YES
- Provides compatibility to suppress unspecified indicator variable
error (generated when NULL value exists while the application has
not specified NULL indicator in the program) while running DB2 applications which are primarily
migrated from other database vendors.
- 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 or 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 or 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.
- VERSION
- Defines the version identifier for a package. If this option is
not specified, the package version will be "" (the empty string).
- version-id
- Specifies a version identifier that is any alphanumeric value,
$, #, @, _, -, or ., up to 64 characters in length.
- AUTO
- The version identifier will be generated from the consistency
token. If the consistency token is a timestamp (it will be if the LEVEL option
is not specified), the timestamp is converted into ISO character format
and is used as the version identifier.
- WCHARTYPE
- Specifies the format for graphic data.
- CONVERT
- Host variables declared using the wchar_t base type will be treated
as containing data in wchar_t format. Since this format is not directly
compatible with the format of graphic data stored in the database
(DBCS format), input data in wchar_t host variables is implicitly
converted to DBCS format on behalf of the application, using the ANSI
C function wcstombs(). Similarly, output DBCS data
is implicitly converted to wchar_t format, using mbstowcs(),
before being stored in host variables.
- NOCONVERT
- Host variables declared using the wchar_t base type will be treated
as containing data in DBCS format. This is the format used within
the database for graphic data; it is, however, different from the
native wchar_t format implemented in the C language. Using NOCONVERT means
that graphic data will not undergo conversion between the application
and the database, which can improve efficiency. The application is,
however, responsible for ensuring that data in wchar_t format is not
passed to the database
manager.
When this option is used, wchar_t host variables should not be manipulated
with the C wide character string functions, and should not be initialized
with wide character literals (L-literals).
- WITH / WITHOUT TEMPORAL HISTORY
- Specifies whether changes to data in system-period temporal tables
made by static or dynamic SQL statements causes changes to the corresponding
history table.
- WITH
- Specifies that changes to data in system-period temporal tables
causes changes to the corresponding history table.
This is the
default option.
- WITHOUT
- Specifies that changes to data in system-period temporal tables
do not cause changes to the corresponding history table. The database
manager can provide values to override the row-begin, row-end, and
transaction-start-ID columns even though they are defined as GENERATED
ALWAYS.
DBADM authority is required for this option.
Usage notes
A modified source file is produced,
which contains host language equivalents to the SQL statements. By
default, a package is created in the database to which a connection
has been established. The name of the package is the same as the file
name (minus the extension and folded to uppercase), up to a maximum
of 8 characters. Although the maximum length of a package name is
128 bytes, unless the PACKAGE USING option is
specified, only the first 8 characters of the file name are used to
maintain compatibility with previous versions of DB2.
Following connection to a database, PREP executes
under the transaction that was started. PREP then
issues a COMMIT or a ROLLBACK to terminate the current transaction
and start another one.
Creating 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.
During precompilation, an Explain
Snapshot is not taken unless a package is created and EXPLSNAP has
been specified. The snapshot is put into the Explain tables of the
user creating the package. Similarly, Explain table information is
only captured when EXPLAIN is specified, and a
package is created.
Precompiling stops if a fatal error or more
than 100 errors occur. If a fatal error occurs, the utility stops
precompiling, 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.
If
an SQL statement was found to be in error and the PRECOMPILE option SQLERROR CONTINUE was
specified, the statement will be marked as invalid and another PRECOMPILE must
be issued in order to change the state of the SQL statement. 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.
Binding
a package with REOPT ONCE or REOPT ALWAYS might
change static and dynamic statement compilation and performance.
For
an embedded SQL program, if the FEDERATED_ASYNCHRONY precompile
option is not explicitly specified the static statements in the package
are bound using the federated_async configuration
parameter. If the FEDERATED_ASYNCHRONY 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 precompile
option influences dynamic SQL only when it is explicitly set.