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 syntax
For DB2® for Linux, UNIX, and Windows
>>-+-PRECOMPILE-+--filename------------------------------------->
'-PREP-------'
>--+-----------------------------------------------------------------------+-->
'-ACTION--+-ADD-------------------------------------------------------+-'
| .-REPLACE-. |
'-+---------+--+-----------------+--+---------------------+-'
'-RETAIN--+-NO--+-' '-REPLVER--version-id-'
'-YES-'
>--+------------------+--+--------------------------------+----->
'-APREUSE--+-NO--+-' '-BINDFILE--+------------------+-'
'-YES-' '-USING--bind-file-'
>--+-----------------------+--+-------------------------+------->
'-BLOCKING--+-UNAMBIG-+-' '-COLLECTION--schema-name-'
+-ALL-----+
'-NO------'
>--+--------------------------------+--------------------------->
'-CALL_RESOLUTION--+-IMMEDIATE-+-'
'-DEFERRED--'
>--+---------------------------------------------------------+-->
'-CONCURRENTACCESSRESOLUTION--+-USE CURRENTLY COMMITTED-+-'
'-WAIT FOR OUTCOME--------'
>--+----------------+--+-------------------+-------------------->
'-CONNECT--+-1-+-' '-DATETIME--+-DEF-+-'
'-2-' +-EUR-+
+-ISO-+
+-JIS-+
+-LOC-+
'-USA-'
>--+---------------------------+-------------------------------->
'-DEFERRED_PREPARE--+-NO--+-'
+-ALL-+
'-YES-'
>--+-----------------------------------+------------------------>
'-DEGREE--+-1---------------------+-'
+-degree-of-parallelism-+
'-ANY-------------------'
>--+-----------------------------+------------------------------>
'-DISCONNECT--+-EXPLICIT----+-'
+-AUTOMATIC---+
'-CONDITIONAL-'
>--+------------------------------+--+--------------------+----->
'-DYNAMICRULES--+-RUN--------+-' '-EXPLAIN--+-NO----+-'
+-BIND-------+ +-ALL---+
+-INVOKERUN--+ +-ONLY--+
+-INVOKEBIND-+ +-REOPT-+
+-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-+-'
>--+-----------------+--+-------------------+------------------->
'-INSERT--+-DEF-+-' '-ISOLATION--+-CS-+-'
'-BUF-' +-RR-+
+-RS-+
'-UR-'
>--+-----------------------+--+--------------------------+------>
'-LANGLEVEL--+-SAA1---+-' '-LEVEL--consistency token-'
+-MIA----+
'-SQL92E-'
>--+--------------------------+--+------------------------+----->
| (1) | '-MESSAGES--message-file-'
'-------LONGERROR--+-NO--+-'
'-YES-'
>--+-------------+--+-----------------+------------------------->
'-NOLINEMACRO-' '-OPTLEVEL--+-0-+-'
'-1-'
>--+---------------------------------------+-------------------->
'-OPTPROFILE--optimization-profile-name-'
>--+------------------+--+-------------------------+------------>
'-OUTPUT--filename-' '-OWNER--authorization-id-'
>--+----------------------------------+------------------------->
'-PACKAGE--+---------------------+-'
'-USING--package-name-'
>--+------------------------------------------+----------------->
'-PREPROCESSOR--+-"preprocessor-command"-+-'
'-'preprocessor-command'-'
>--+---------------------------+-------------------------------->
'-QUALIFIER--qualifier-name-'
.-REOPT NONE---.
>--+------------------------------+--+--------------+----------->
'-QUERYOPT--optimization-level-' +-REOPT ONCE---+
'-REOPT ALWAYS-'
>--+-----------------+--+-------------------------------+------->
'-SQLCA--+-NONE-+-' | (2) |
'-SAA--' '-------SQLERROR--+-NOPACKAGE-+-'
+-CHECK-----+
'-CONTINUE--'
>--+--------------------------------+--+-------------------+---->
'-SQLFLAG--+-SQL92E----+--SYNTAX-' '-SQLRULES--+-DB2-+-'
+-MVSDB2V23-+ '-STD-'
+-MVSDB2V31-+
'-MVSDB2V41-'
>--+------------------+--+---------------------------------+---->
'-SQLWARN--+-NO--+-' | .-NO----------. |
'-YES-' '-STATICREADONLY--+-YES---------+-'
'-INSENSITIVE-'
>--+-------------------------+--+--------+---------------------->
'-SYNCPOINT--+-ONEPHASE-+-' '-SYNTAX-'
+-NONE-----+
'-TWOPHASE-'
>--+------------------------+--+----------------------------+--->
'-TARGET--+-IBMCOB-----+-' '-TRANSFORM GROUP--groupname-'
+-MFCOB------+
+-ANSI_COBOL-+
+-C----------+
+-CPLUSPLUS--+
'-FORTRAN----'
>--+--------------------+--+--------------------------+--------->
'-VALIDATE--+-BIND-+-' '-WCHARTYPE--+-NOCONVERT-+-'
'-RUN--' '-CONVERT---'
>--+-------------------------+---------------------------------><
'-VERSION--+-version-id-+-'
'-AUTO-------'
Notes:
- NO 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. YES is
the default for 64 bit UNIX systems.
- SYNTAX is a synonym for SQLERROR(CHECK).
For DB2 Database
on servers other than Linux, UNIX and Windows
>>-+-PRECOMPILE-+--filename------------------------------------->
'-PREP-------'
>--+-----------------------------------------------------------------------+-->
'-ACTION--+-ADD-------------------------------------------------------+-'
| .-REPLACE-. |
'-+---------+--+-----------------+--+---------------------+-'
| .-YES-. | '-REPLVER--version-id-'
'-RETAIN--+-NO--+-'
>--+--------------------------------+--------------------------->
'-BINDFILE--+------------------+-'
'-USING--bind-file-'
>--+-----------------------+------------------------------------>
| .-UNAMBIG-. |
'-BLOCKING--+-ALL-----+-'
'-NO------'
>--+--------------------------------+--------------------------->
'-CALL_RESOLUTION--+-IMMEDIATE-+-'
'-DEFERRED--'
>--+----------------------+--+---------------------+------------>
'-CCSIDG--double-ccsid-' '-CCSIDM--mixed-ccsid-'
>--+--------------------+--+----------------------+------------->
'-CCSIDS--sbcs-ccsid-' | .-DEFAULT-. |
'-CHARSUB--+-BIT-----+-'
+-MIXED---+
'-SBCS----'
>--+-------------------+--+-------------------------+----------->
| .-YES-. | '-COLLECTION--schema-name-'
'-CNULREQD--+-NO--+-'
>--+-COMPILE----+----------------------------------------------->
'-PRECOMPILE-'
>--+---------------------------------------------------------+-->
'-CONCURRENTACCESSRESOLUTION--+-USE CURRENTLY COMMITTED-+-'
'-WAIT FOR OUTCOME--------'
>--+----------------+--+-------------------------+-------------->
| .-1-. | | (1) |
'-CONNECT--+-2-+-' '-------DATETIME--+-DEF-+-'
+-EUR-+
+-ISO-+
+-JIS-+
+-LOC-+
'-USA-'
>--+-------------------------+--+-------------+----------------->
'-DBPROTOCOL--+-DRDA----+-' '-DEC--+-15-+-'
'-PRIVATE-' '-31-'
>--+--------------------+--+---------------------------+-------->
| .-PERIOD-. | | .-NO--. |
'-DECDEL--+-COMMA--+-' '-DEFERRED_PREPARE--+-ALL-+-'
'-YES-'
>--+-----------------------------------------+------------------>
| (2) .-1---------------------. |
'-------DEGREE--+-degree-of-parallelism-+-'
'-ANY-------------------'
>--+-----------------------------+------------------------------>
| .-EXPLICIT----. |
'-DISCONNECT--+-AUTOMATIC---+-'
'-CONDITIONAL-'
>--+------------------------------+----------------------------->
| .-RUN--------. |
'-DYNAMICRULES--+-BIND-------+-'
+-INVOKERUN--+
+-INVOKEBIND-+
+-DEFINERUN--+
'-DEFINEBIND-'
>--+-----------------------+--+------------------+-------------->
'-ENCODING--+-ASCII---+-' | .-NO--. |
+-EBCDIC--+ '-EXPLAIN--+-YES-+-'
+-UNICODE-+
'-CCSID---'
>--+----------------------------+--+-------------------+-------->
| .-NO--. | '-GENERIC--"string"-'
'-EXTENDEDINDICATOR--+-YES-+-'
>--+---------------------+--+-------------------+--------------->
'-IMMEDWRITE--+-NO--+-' | .-CS-. |
+-YES-+ '-ISOLATION--+-NC-+-'
'-PH1-' +-RR-+
+-RS-+
'-UR-'
>--+----------------------+--+--------------------------+------->
'-KEEPDYNAMIC--+-YES-+-' '-LEVEL--consistency-token-'
'-NO -'
>--+--------------------------+--+------------------------+----->
| (3) .-NO--. | '-MESSAGES--message-file-'
'-------LONGERROR--+-YES-+-'
>--+-------------+--+----------------------+-------------------->
'-NOLINEMACRO-' '-OPTHINT----hint-id---'
>--+-----------------+--+-------------------------+------------->
| .-0-. | '-OS400NAMING--+-SYSTEM-+-'
'-OPTLEVEL--+-1-+-' '-SQL----'
>--+-------------------------+---------------------------------->
'-OWNER--authorization-id-'
>--+------------------------------------------+----------------->
'-PREPROCESSOR--+-"preprocessor-command"-+-'
'-'preprocessor-command'-'
>--+---------------------------+--+-------------------------+--->
'-QUALIFIER--qualifier-name-' | .-COMMIT-----. |
'-RELEASE--+-DEALLOCATE-+-'
.-REOPT NONE---.
>--+--------------+--+--------------+--------------------------->
+-REOPT ONCE---+ +-REOPT VARS---+
'-REOPT ALWAYS-' '-NOREOPT VARS-'
>--+--------------------------------+--------------------------->
'-SQLFLAG--+-SQL92E----+--SYNTAX-'
+-MVSDB2V23-+
+-MVSDB2V31-+
'-MVSDB2V41-'
>--+---------------------+--+-------------------+--------------->
'-SORTSEQ--+-JOBRUN-+-' | .-DB2-. |
'-HEX----' '-SQLRULES--+-STD-+-'
>--+-------------------------+--+------------------------+------>
| .-NOPACKAGE-. | | .-APOSTROPHE-. |
'-SQLERROR--+-CHECK-----+-' '-STRDEL--+-QUOTE------+-'
'-CONTINUE--'
>--+-------------------------+--+--------+---------------------->
| .-ONEPHASE-. | '-SYNTAX-'
'-SYNCPOINT--+-NONE-----+-'
'-TWOPHASE-'
>--+-------------------------------+--+-------------+----------->
| .-IBMCOB------------. | '-TEXT--label-'
'-TARGET--+-MFCOB-------------+-'
+-ANSI_COBOL--------+
+-C-----------------+
+-CPLUSPLUS---------+
+-FORTRAN-----------+
+-BORLAND_C---------+
'-BORLAND_CPLUSPLUS-'
>--+-------------------------+--+--------------------+---------->
'-VERSION--+-version-id-+-' '-VALIDATE--+-BIND-+-'
'-AUTO-------' '-RUN--'
>--+--------------------------+--------------------------------><
| .-NOCONVERT-. |
'-WCHARTYPE--+-CONVERT---+-'
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.
- NO 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. YES is
the default for 64 bit UNIX systems.
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
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.
- 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.
- 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/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.
- 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 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 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/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).
- 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.
- 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.
- 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.
- 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 hand 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 hand 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).
- 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.
- 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 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.
- 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 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.
- 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 above 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.
- 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.
- 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).
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.