Command Reference

PRECOMPILE PROGRAM

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 node in db2nodes.cfg. It updates the database catalogs on the catalog node. Its effects are visible to all nodes.

Authorization

One of the following:

sysadm or dbadm authority
BINDADD privilege if a package does not exist, and one of:
- IMPLICIT_SCHEMA authority on the database if the schema name of the package does not exist
- CREATEIN privilege on the schema if the schema name of the package exists
ALTERIN privilege on the schema if the package exists
BIND privilege on the package if it exists.

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. If the user has sysadm authority, but not explicit privileges to complete the bind, the database manager grants explicit dbadm authority automatically.

Required Connection

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

Command Syntax

For DB2

>>-+-PRECOMPILE-+--filename------------------------------------->
   '-PREP-------'
 
>-----+----------------------------------+---------------------->
      '-BINDFILE--+-------------------+--'
                  '-USING--bind-file--'
 
>-----+-------------------------+------------------------------->
      |           .-UNAMBIG--.  |
      '-BLOCKING--+-ALL------+--'
                  '-NO-------'
 
>-----+--------------------------+---+------------------+------->
      '-COLLECTION--schema-name--'   |          .-1--.  |
                                     '-CONNECT--+-2--+--'
 
>-----+---------------------+---+----------------------------+-->
      |           .-DEF--.  |   |                   .-NO--.  |
      '-DATETIME--+-EUR--+--'   '-DEFERRED_PREPARE--+-ALL-+--'
                  +-ISO--+                          '-YES-'
                  +-JIS--+
                  +-LOC--+
                  '-USA--'
 
>-----+------------------------------------+-------------------->
      |         .-1---------------------.  |
      '-DEGREE--+-degree-of-parallelism-+--'
                '-ANY-------------------'
 
>-----+------------------------------+-------------------------->
      |             .-EXPLICIT----.  |
      '-DISCONNECT--+-AUTOMATIC---+--'
                    '-CONDITIONAL-'
 
>-----+-------------------------+---+-------------------+------->
      |               .-RUN--.  |   |          .-NO--.  |
      '-DYNAMICRULES--+-BIND-+--'   '-EXPLAIN--+-ALL-+--'
                                               '-YES-'
 
>-----+--------------------+------------------------------------>
      |           .-NO--.  |
      '-EXPLSNAP--+-ALL-+--'
                  '-YES-'
 
>-----+-------------------------------+---+-------------------+->
      |           .-,--------------.  |   |         .-DEF--.  |
      |           V                |  |   '-INSERT--+-BUF--+--'
      '-FUNCPATH-----schema-name---+--'
 
>-----+---------------------+---+------------------------+------>
      |            .-CS--.  |   |            .-SAA1---.  |
      '-ISOLATION--+-RR--+--'   '-LANGLEVEL--+-MIA----+--'
                   +-RS--+                   '-SQL92E-'
                   '-UR--'
 
>-----+-------------------------+---+--------------+------------>
      '-MESSAGES--message-file--'   '-NOLINEMACRO--'
 
>-----+-------------------+---+-------------------+------------->
      |           .-0--.  |   '-OUTPUT--filename--'
      '-OPTLEVEL--+-1--+--'
 
>-----+--------------------------+------------------------------>
      '-OWNER--authorization-id--'
 
>-----+------------------------------------+-------------------->
      '-PACKAGE--+----------------------+--'
                 '-USING--package-name--'
 
>-----+-------------------------------------------+------------->
      '-PREPROCESSOR--+-"preprocessor-command"-+--'
                      '-'preprocessor-command'-'
 
>-----+----------------------------+---------------------------->
      '-QUALIFIER--qualifier-name--'
 
>-----+-------------------------------+---+------------------+-->
      '-QUERYOPT--optimization-level--'   |        .-SAA--.  |
                                          '-SQLCA--+-NONE-+--'
 
>-----+----------------------------------+---------------------->
      |  (1)             .-NOPACKAGE--.  |
      '--------SQLERROR--+-CHECK------+--'
 
>-----+----------------------------------+---------------------->
      '-SQLFLAG--+-SQL92E----+---SYNTAX--'
                 +-MVSDB2V23-+
                 +-MVSDB2V31-+
                 '-MVSDB2V41-'
 
>-----+---------------------+---+--------------------+---------->
      |           .-DB2--.  |   |          .-YES--.  |
      '-SQLRULES--+-STD--+--'   '-SQLWARN--+-NO---+--'
 
>-----+---------------------------+---+---------+--------------->
      |            .-ONEPHASE--.  |   '-SYNTAX--'
      '-SYNCPOINT--+-NONE------+--'
                   '-TWOPHASE--'
 
>-----+--------------------------------+------------------------>
      |         .-IBMCOB------------.  |
      '-TARGET--+-MFCOB-------------+--'
                +-MFCOB16-----------+
                +-ANSI_COBOL--------+
                +-C-----------------+
                +-CPLUSPLUS---------+
                +-FORTRAN-----------+
                +-BORLAND_C---------+
                '-BORLAND_CPLUSPLUS-'
 
>-----+----------------------------+---------------------------><
      |            .-NOCONVERT--.  |
      '-WCHARTYPE--+-CONVERT----+--'

Notes:

SYNTAX is a synonym for SQLERROR(CHECK).

For DRDA

>>-+-PRECOMPILE-+--filename------------------------------------->
   '-PREP-------'
 
>-----+----------------------------------------------------------------------------+>
      '-ACTION--+-ADD-----------------------------------------------------------+--'
                | .-REPLACE-.                                                   |
                '-+---------+--+-------------------+---+----------------------+-'
                               |         .-YES--.  |   '-REPLVER--version-id--'
                               '-RETAIN--+-NO---+--'
 
>-----+----------------------------------+---------------------->
      '-BINDFILE--+-------------------+--'
                  '-USING--bind-file--'
 
>-----+-------------------------+---+-----------------------+--->
      |           .-UNAMBIG--.  |   '-CCSIDG--double-ccsid--'
      '-BLOCKING--+-ALL------+--'
                  '-NO-------'
 
>-----+----------------------+---+---------------------+-------->
      '-CCSIDM--mixed-ccsid--'   '-CCSIDS--sbcs-ccsid--'
 
>-----+------------------------+---+---------------------+------>
      |          .-DEFAULT--.  |   |           .-YES--.  |
      '-CHARSUB--+-BIT------+--'   '-CNULREQD--+-NO---+--'
                 +-MIXED----+
                 '-SBCS-----'
 
>-----+--------------------------+---+------------------+------->
      '-COLLECTION--schema-name--'   |          .-1--.  |
                                     '-CONNECT--+-2--+--'
 
>-----+----------------------------+---+--------------+--------->
      |  (1)             .-DEF--.  |   '-DEC--+-15-+--'
      '--------DATETIME--+-EUR--+--'          '-31-'
                         +-ISO--+
                         +-JIS--+
                         +-LOC--+
                         '-USA--'
 
>-----+----------------------+---+----------------------------+->
      |         .-PERIOD--.  |   |                   .-NO--.  |
      '-DECDEL--+-COMMA---+--'   '-DEFERRED_PREPARE--+-ALL-+--'
                                                     '-YES-'
 
>-----+-------------------------------------------+------------->
      |  (2)           .-1---------------------.  |
      '--------DEGREE--+-degree-of-parallelism-+--'
                       '-ANY-------------------'
 
>-----+------------------------------+-------------------------->
      |             .-EXPLICIT----.  |
      '-DISCONNECT--+-AUTOMATIC---+--'
                    '-CONDITIONAL-'
 
>-----+---------------------------+---+-------------------+----->
      |               .-RUN----.  |   |          .-NO--.  |
      '-DYNAMICRULES--+-BIND---+--'   '-EXPLAIN--+-YES-+--'
                      +-DEFINE-+
                      '-INVOKE-'
 
>-----+------------------+---+---------------------+------------>
      '-GENERIC--string--'   |            .-CS--.  |
                             '-ISOLATION--+-NC--+--'
                                          +-RR--+
                                          +-RS--+
                                          '-UR--'
 
>-----+---------------------------+----------------------------->
      '-LEVEL--consistency-token--'
 
>-----+-------------------------+---+--------------+------------>
      '-MESSAGES--message-file--'   '-NOLINEMACRO--'
 
>-----+-------------------+---+--------------------------+------>
      |           .-0--.  |   '-OWNER--authorization-id--'
      '-OPTLEVEL--+-1--+--'
 
>-----+------------------------------------+-------------------->
      '-PACKAGE--+----------------------+--'
                 '-USING--package-name--'
 
>-----+-------------------------------------------+------------->
      '-PREPROCESSOR--+-"preprocessor-command"-+--'
                      '-'preprocessor-command'-'
 
>-----+----------------------------+---------------------------->
      '-QUALIFIER--qualifier-name--'
 
>-----+--------------------------+------------------------------>
      |          .-COMMIT-----.  |
      '-RELEASE--+-DEALLOCATE-+--'
 
>-----+----------------------------------+---------------------->
      |  (3)             .-NOPACKAGE--.  |
      '--------SQLERROR--+-CHECK------+--'
                         '-CONTINUE---'
 
>-----+----------------------------------+---------------------->
      '-SQLFLAG--+-SQL92E----+---SYNTAX--'
                 +-MVSDB2V23-+
                 +-MVSDB2V31-+
                 '-MVSDB2V41-'
 
>-----+---------------------+---+--------------------------+---->
      |           .-DB2--.  |   |         .-APOSTROPHE--.  |
      '-SQLRULES--+-STD--+--'   '-STRDEL--+-QUOTE-------+--'
 
>-----+---------------------------+---+---------+--------------->
      |            .-ONEPHASE--.  |   '-SYNTAX--'
      '-SYNCPOINT--+-NONE------+--'
                   '-TWOPHASE--'
 
>-----+--------------------------------+---+--------------+----->
      |         .-IBMCOB------------.  |   '-TEXT--label--'
      '-TARGET--+-MFCOB-------------+--'
                +-MFCOB16-----------+
                +-ANSI_COBOL--------+
                +-C-----------------+
                +-CPLUSPLUS---------+
                +-FORTRAN-----------+
                +-BORLAND_C---------+
                '-BORLAND_CPLUSPLUS-'
 
>-----+---------------------+---+----------------------+-------->
      |           .-RUN--.  |   '-VERSION--version-id--'
      '-VALIDATE--+-BIND-+--'
 
>-----+----------------------------+---------------------------><
      |            .-NOCONVERT--.  |
      '-WCHARTYPE--+-CONVERT----+--'

Notes:

The DATETIME DEF option is not supported by DRDA, and is mapped to ISO when going through DB2 Connect.
The DEGREE option is only supported by DRDA Level 2 Application Servers.
SYNTAX is a synonym for SQLERROR(CHECK).

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 (OS/2 or the Windows operating system), or .sqC (UNIX based systems) must be specified for C++ applications (generates a .cxx file on OS/2 or the Windows operating system, or a .C file on UNIX based systems)
.sqb must be specified for COBOL applications (generates a .cbl file)
.sqf must be specified for FORTRAN applications (generates a .for file on OS/2 or the Windows operating system, or a .f file on UNIX based systems).

The preferred extension for C++ applications containing embedded SQL on UNIX based systems is sqC; however, the sqx convention, which was invented for systems that are not case sensitive, is tolerated by UNIX based systems.

ACTION

Indicates whether the package can be added or replaced. This DRDA precompile/bind option is not supported by DB2.

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 old package is to be replaced by a new one with the same location, collection, and package name.

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.
YES: Preserves EXECUTE authorities when a package is replaced.

REPLVER version-id

Replaces a specific version of a package. The version identifier specifies which version of the package is to be replaced. Maximum length is 254 characters.

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

For information about row blocking, see the Administration Guide or the Application Development Guide.

ALL

Specifies to block for:

Read-only cursors
Cursors not specified as FOR UPDATE OF

Ambiguous cursors are treated as read-only.

NO

Specifies not to block any cursors. Ambiguous cursors are treated as updatable.

UNAMBIG

Specifies to block for:

Read-only cursors
Cursors not specified as FOR UPDATE OF

Ambiguous cursors are treated as updatable.

CCSIDG double-ccsid

An integer specifying the coded character set identifier (CCSID) to be used for double byte characters in character column definitions (without a specific CCSID clause) in CREATE and ALTER TABLE SQL statements. This DRDA precompile/bind option is not supported by DB2. 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 DRDA precompile/bind option is not supported by DB2. 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 DRDA precompile/bind option is not supported by DB2. 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 DRDA precompile/bind option is not supported by DB2.

BIT: Use the FOR BIT DATA SQL character sub-type in all new character columns for which an explicit sub-type is not specified.
DEFAULT: Use the target 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, which is not supported by DRDA. It is valid only if the bind file is created from a C or a C++ application. This DRDA bind option is not supported by DB2.

NO: The application was coded on the basis of the langlevel SAA1 precompile option with respect to the null terminator in C string host variables.
YES: The application was coded on the basis of the langlevel MIA precompile option with respect to the null terminator in C string host variables.

COLLECTION schema-name

Specifies an 8-character collection identifier for the package. If not specified, the authorization identifier for the user processing the package is used.

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. For more information about date and time formats, see the SQL Reference.

DEF: Use a date and time format associated with the country 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 country code of the database.
USA: Use the IBM standard for U.S. date and time format.

DEC

Specifies the maximum precision to be used in decimal arithmetic operations. This DRDA precompile/bind option is not supported by DB2. 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 DRDA precompile/bind option is not supported by DB2. 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. 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.

When binding a package with this option, the binder of the package should not have any authorities that the user of the package should not receive, because dynamic SQL statements will be using the authorization ID of the package owner. The following dynamically prepared SQL statements cannot be used within a package that has been bound with this option: GRANT, REVOKE, ALTER, CREATE, DROP, COMMENT ON, RENAME, SET CONSTRAINTS, and SET EVENT MONITOR STATE.

DEFINE

Indicates that the authorization identifier used for the execution of dynamic SQL statements in a UDF or stored procedure is the definer of the UDF or stored procedure. This option is not supported by DB2.

INVOKE

Indicates that the authorization identifier used for the execution of dynamic SQL statements in a UDF or stored procedure is the invoker of the UDF or stored procedure. This option is not supported by DB2.

EXPLAIN

Stores information in the Explain tables about the access plans chosen for each SQL statement in the package. DRDA does not support the ALL value for this option.

NO

Explain information will not be captured.

YES

Explain tables will be populated with information about the chosen access plan.

ALL

Explain information for each eligible static SQL statement will be placed in the Explain tables. In addition, Explain information will be gathered for eligible dynamic SQL statements at run time, even if the CURRENT EXPLAIN SNAPSHOT register is set to NO. For more information about special registers, see the SQL Reference.

Note:

This value for EXPLAIN is not supported by DRDA.

EXPLSNAP

Stores Explain Snapshot information in the Explain tables. This DB2 precompile/bind option is not supported by DRDA.

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.
ALL: An Explain Snapshot for each eligible static SQL statement will be placed in the Explain tables. In addition, Explain Snapshot information will be gathered for eligible dynamic SQL statements at run time, even if the CURRENT EXPLAIN SNAPSHOT register is set to NO. For more information about special registers, see the SQL Reference.

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. This DB2 precompile/bind option is not supported by DRDA.

schema-name: A short 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 number of schemas that can be specified is limited by the length of the resulting function path, which cannot exceed 254 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. For more information, see the SQL Reference.

INSERT

Allows a program being precompiled or bound against a DB2 Universal Database Extended Enterprise 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

Provides a means of passing new bind options to a target DRDA database. Supports the binding of new options that are defined in the target database, but that are not known to the local command. Do not use this option to pass bind options that are defined in BIND or PRECOMPILE PROGRAM. This option can substantially improve dynamic SQL performance. 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, if the target DRDA database is DB2 MVS Version 5, one could use:

   generic "keepdynamic yes"

to bind the new keepdynamic YES option, which is not defined locally on the PRECOMPILE PROGRAM or the BIND command.

The maximum length of the string is 1023 bytes. This DRDA bind option is currently only supported by DB2 MVS Version 5; it is not supported by DB2.

ISOLATION

Determines how far a program bound to this package can be isolated from the effect of other executing programs. For more information about isolation levels, see the SQL Reference.

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. This option is not supported by DB2 Connect. For more information about this option, see the Application Development Guide.

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 may 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).

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 DRDA precompile option is not supported by DB2.

Note:

This option is not recommended for general use.

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.

Note:

This precompile option is used for the C/C⁺⁺ programming languages only.

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:

pointer host variables, as in the following example:

exec sql begin declare section;
char (*name)[20];
short *id;
exec sql end declare section;

C++ data members directly in SQL statements.

For more information, see the Application Development Guide.

OUTPUT filename

Overrides the default name of the modified source file produced by the compiler. It can include a path.

OWNER authorization-id

Designates an 8-character 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 SYSADM or 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.

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 8 characters.

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 8-character 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. For the complete range of optimization levels available, see the SET CURRENT QUERY OPTIMIZATION statement in the SQL Reference. This DB2 precompile/bind option is not supported by DRDA.

RELEASE

Indicates whether resources are released at each COMMIT point, or when the application terminates. This DRDA precompile/bind option is not supported by DB2.

COMMIT: Release resources at each COMMIT point. Used for dynamic SQL statements.
DEALLOCATE: Release resources only when the application terminates.

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: A package or a bind file is created even when SQL errors are encountered. This option is not supported by DB2.
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.

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 a user or application can specify the format of LOB answer set columns.

DB2

Permits the SQL CONNECT statement to switch the current connection to another established (dormant) connection.
The user or application can specify the format of a LOB column only during the first fetch request.

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 user or application can change the format of a LOB column with each fetch request.

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). This DB2 precompile/bind option is not supported by DRDA.

NO: Warnings will not be returned from the SQL compiler.
YES: Warnings will be returned from the SQL compiler.

Note: SQLCODE +238 is an exception. It is returned regardless of the sqlwarn option value.

STRDEL

Designates whether an apostrophe (') or double quotation marks (") will be used as the string delimiter within SQL statements. This DRDA precompile/bind option is not supported by DB2. 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.

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. On OS/2, code is generated for the IBM VisualAge for COBOL compiler.
MFCOB: Code is generated for the Micro Focus COBOL compiler. On OS/2 this refers to the 32-bit Micro Focus COBOL compiler. This is the default if a target value is not specified with the COBOL precompiler on all UNIX platforms and Windows NT.
MFCOB16: Code is generated for the 16-bit Micro Focus COBOL compiler. This value is only valid on OS/2, and is the default if a target value is not specified with the COBOL precompiler.
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.
BORLAND_C: C code is generated for the Borland C/C++ compiler. This value is only valid on OS/2.
BORLAND_CPLUSPLUS: C++ code is generated for the Borland C/C++ compiler. This value is only valid on OS/2.
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 DRDA precompile/bind option is not supported by DB2.

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. This DRDA precompile/bind option is not supported by DB2.

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 may be redone at execution time.

VERSION version-id

Defines the version identifier for a package. The version identifier is any alphanumeric value, $, #, @, _, -, or ., up to 254 characters in length. This DRDA precompile option is not supported by DB2.

WCHARTYPE

For details and restrictions on the use and applicability of wchartype, see the Application Development Guide.

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.

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.

If a package is bound with dynamicrules bind, the implicit or explicit value of the bind option owner is used for authorization checking of dynamic SQL statements, and the implicit or explicit value of the bind option qualifier is used as the implicit qualifier of unqualified objects within dynamic SQL statements. If multiple packages are referenced during a single connection, dynamic SQL statements prepared by a specific package will behave according to the bind options for that package. The value of the special register CURRENT SCHEMA has no effect on qualification in a package bound with dynamicrules bind.

See Also

BIND.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]