PRECOMPILE command

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

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®

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

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. The DRDA server will use a built-in default value if this option is not specified.

CCSIDM mixed-ccsid

An integer specifying the coded character set identifier (CCSID) to be used for mixed byte characters in character column definitions (without a specific CCSID clause) in CREATE and ALTER TABLE SQL statements. This option is not supported by the server for Db2. The DRDA server will use a built-in default value if this option is not specified.

CCSIDS sbcs-ccsid

An integer specifying the coded character set identifier (CCSID) to be used for single byte characters in character column definitions (without a specific CCSID clause) in CREATE and ALTER TABLE SQL statements. This option is not supported by the server for Db2. The DRDA server will use a built-in default value if this option is not specified.

CHARSUB

Designates the default character sub-type that is to be used for column definitions in CREATE and ALTER TABLE SQL statements. This precompile/bind option is not supported by the server for Db2.

BIT

Use the FOR BIT DATA SQL character sub-type in all new character columns for which an explicit sub-type is not specified.

DEFAULT

Use the target built-in default in all new character columns for which an explicit sub-type is not specified.

MIXED

Use the FOR MIXED DATA SQL character sub-type in all new character columns for which an explicit sub-type is not specified.

SBCS

Use the FOR SBCS DATA SQL character sub-type in all new character columns for which an explicit sub-type is not specified.

CNULREQD

This option is related to the LANGLEVEL precompile option. It is valid only if the bind file is created from a C or a C++ application. This bind option is not supported by the server for Db2.

NO

The application was coded on the basis of the LANGLEVEL SAA1 precompile option with respect to the null terminator in C string host variables.

YES

The application was coded on the basis of the LANGLEVEL MIA precompile option with respect to the null terminator in C string host variables.

COLLECTION schema-name

Specifies a 128-byte collection identifier for the package. If not specified, the authorization identifier for the user processing the package is used.

COMPATIBILITY_MODE

ORA

The Db2 database manager provides features that facilitate the migration of embedded SQL C applications from other database systems. You can enable these compatibility features by setting the COMPATIBILITY_MODE to ORA. For example, the following command enables the compatibility features when you compile the tbsel.sqc file:

$ db2 PRECOMPILE tbsel.sqc BINDFILE COMPATIBILITY_MODE ORA

CONCURRENTACCESSRESOLUTION

Specifies the concurrent access resolution to use for statements in the package.

USE CURRENTLY COMMITTED

Specifies that the database manager can use the currently committed version of the data for applicable scans when it is in the process of being updated or deleted. Rows in the process of being inserted can be skipped. This clause applies when the isolation level in effect is Cursor Stability or Read Stability (for Read Stability it skips uncommited inserts only) and is ignored otherwise. Applicable scans include read-only scans that can be part of a read-only statement as well as a non read-only statement. The settings for the registry variables DB2_EVALUNCOMMITTED, DB2_SKIPDELETED, and DB2_SKIPINSERTED no longer apply.

WAIT FOR OUTCOME

Specifies Cursor Stability and higher scans wait for the commit or rollback when encountering data in the process of being updated or deleted. Rows in the process of being inserted are not skipped. The settings for the registry variables DB2_EVALUNCOMMITTED, DB2_SKIPDELETED, and DB2_SKIPINSERTED no longer apply.

CONNECT

1

Specifies that a CONNECT statement is to be processed as a type 1 CONNECT.

2

Specifies that a CONNECT statement is to be processed as a type 2 CONNECT.

DATETIME

Specifies the date and time format to be used.

DEF

Use a date and time format associated with the territory code of the database.

EUR

Use the IBM standard for Europe date and time format.

ISO

Use the date and time format of the International Standards Organization.

JIS

Use the date and time format of the Japanese Industrial Standard.

LOC

Use the date and time format in local form associated with the territory code of the database.

USA

Use the IBM standard for U.S. date and time format.

DBPROTOCOL

Specifies what protocol to use when connecting to a remote site that is identified by a three-part name statement. Supported by Db2 for z/OS® only. For a list of supported option values, refer to the documentation for Db2 for z/OS.

DEC

Specifies the maximum precision to be used in decimal arithmetic operations. This precompile or bind option is not supported by the server for Db2. The DRDA server will use a built-in default value if this option is not specified.

15

15-digit precision is used in decimal arithmetic operations.

31

31-digit precision is used in decimal arithmetic operations.

DECDEL

Designates whether a period (.) or a comma (,) will be used as the decimal point indicator in decimal and floating point literals. This precompile/bind option is not supported by the server for Db2. The DRDA server will use a built-in default value if this option is not specified.

COMMA

Use a comma (,) as the decimal point indicator.

PERIOD

Use a period (.) as the decimal point indicator.

DEFERRED_PREPARE

Provides a performance enhancement when accessing Db2 common server databases or DRDA databases. This option combines the SQL PREPARE statement flow with the associated OPEN, DESCRIBE, or EXECUTE statement flow to minimize inter-process or network flow.

NO

The PREPARE statement will be executed at the time it is issued.

YES

Execution of the PREPARE statement will be deferred until the corresponding OPEN, DESCRIBE, or EXECUTE statement is issued.

The PREPARE statement will not be deferred if it uses the INTO clause, which requires an SQLDA to be returned immediately. However, if the PREPARE INTO statement is issued for a cursor that does not use any parameter markers, the processing will be optimized by pre-OPENing the cursor when the PREPARE is executed.

ALL

Same as YES, except that a PREPARE INTO statement is also deferred. If the PREPARE statement uses the INTO clause to return an SQLDA, the application must not reference the content of this SQLDA until the OPEN, DESCRIBE, or EXECUTE statement is issued and returned.

DEGREE

Specifies the degree of parallelism for the execution of static SQL statements in an SMP system. This option does not affect CREATE INDEX parallelism.

1

The execution of the statement will not use parallelism.

degree-of-parallelism

Specifies the degree of parallelism with which the statement can be executed, a value between 2 and 32 767 (inclusive).

ANY

Specifies that the execution of the statement can involve parallelism using a degree determined by the database manager.

DISCONNECT

AUTOMATIC

Specifies that all database connections are to be disconnected at commit.

CONDITIONAL

Specifies that the database connections that have been marked RELEASE or have no open WITH HOLD cursors are to be disconnected at commit.

EXPLICIT

Specifies that only database connections that have been explicitly marked for release by the RELEASE statement are to be disconnected at commit.

DYNAMICRULES

Defines which rules apply to dynamic SQL at run time for the initial setting of the values used for authorization ID and for the implicit qualification of unqualified object references.

RUN

Specifies that the authorization ID of the user executing the package is to be used for authorization checking of dynamic SQL statements. The authorization ID will also be used as the default package qualifier for implicit qualification of unqualified object references within dynamic SQL statements. This is the default value.

BIND

Specifies that all of the rules that apply to static SQL for authorization and qualification are to be used at run time. That is, the authorization ID of the package owner is to be used for authorization checking of dynamic SQL statements, and the default package qualifier is to be used for implicit qualification of unqualified object references within dynamic SQL statements.

DEFINERUN

If the package is used within a routine context, the authorization ID of the routine definer is to be used for authorization checking and for implicit qualification of unqualified object references within dynamic SQL statements within the routine.

If the package is used as a stand-alone application, dynamic SQL statements are processed as if the package were bound with DYNAMICRULES RUN.

DEFINEBIND

If the package is used within a routine context, the authorization ID of the routine definer is to be used for authorization checking and for implicit qualification of unqualified object references within dynamic SQL statements within the routine.

If the package is used as a stand-alone application, dynamic SQL statements are processed as if the package were bound with DYNAMICRULES BIND.

INVOKERUN

If the package is used within a routine context, the current statement authorization ID in effect when the routine is invoked is to be used for authorization checking of dynamic SQL statements and for implicit qualification of unqualified object references within dynamic SQL statements within that routine.

If the package is used as a stand-alone application, dynamic SQL statements are processed as if the package were bound with DYNAMICRULES RUN.

INVOKEBIND

If the package is used within a routine context, the current statement authorization ID in effect when the routine is invoked is to be used for authorization checking of dynamic SQL statements and for implicit qualification of unqualified object references within dynamic SQL statements within that routine.

If the package is used as a stand-alone application, dynamic SQL statements are processed as if the package were bound with DYNAMICRULES BIND.

Because dynamic SQL statements will be using the authorization ID of the package owner in a package exhibiting bind behavior, the binder of the package should not have any authorities granted to them that the user of the package should not receive. Similarly, when defining a routine that will exhibit define behavior, the definer of the routine should not have any authorities granted to them that the user of the package should not receive since a dynamic statement will be using the authorization ID of the routine's definer.

The following dynamically prepared SQL statements cannot be used within a package that was not bound with DYNAMICRULES RUN: GRANT, REVOKE, ALTER, CREATE, DROP, COMMENT ON, RENAME, SET INTEGRITY, and SET EVENT MONITOR STATE.

ENCODING

Specifies the encoding for all host variables in static statements in the plan or package. Supported by Db2 for z/OS only. For a list of supported option values, refer to the documentation for Db2 for z/OS.

EXPLAIN

Stores information in the Explain tables about the access plans chosen for each SQL statement in the package.

NO

Explain information will not be captured.

YES

Explain tables will be populated with information about the chosen access plan at prep/bind time for static statements and at run time for incremental bind statements.

If the package is to be used for a routine and the package contains incremental bind statements, then the routine must be defined as MODIFIES SQL DATA. If this is not done, incremental bind statements in the package will cause a run time error (SQLSTATE 42985).

REOPT

Explain information for each reoptimizable incremental bind SQL statement will be placed in the Explain tables at run time. In addition, Explain information will be gathered for reoptimizable dynamic SQL statements at run time, even if the CURRENT EXPLAIN MODE special register is set to NO.

If the package is to be used for a routine, then the routine must be defined as MODIFIES SQL DATA, otherwise incremental bind and dynamic statements in the package will cause a run time error (SQLSTATE 42985).

ONLY

The ONLY option allows you to explain statements without having the privilege to execute them. The explain tables are populated but no persistent package is created. If an existing package with the same name and version is encountered during the bind process, the existing package is neither dropped nor replaced even if you specified ACTION REPLACE. If an error occurs during population of the explain tables, explain information is not added for the statement that returned the error and for any statements that follow it.

ALL

Explain information for each eligible static SQL statement will be placed in the Explain tables at prep/bind time. Explain information for each eligible incremental bind SQL statement will be placed in the Explain tables at run time. In addition, Explain information will be gathered for eligible dynamic SQL statements at run time, even if the CURRENT EXPLAIN MODE special register is set to NO.

If the package is to be used for a routine, then the routine must be defined as MODIFIES SQL DATA, otherwise incremental bind and dynamic statements in the package will cause a run time error (SQLSTATE 42985).

EXPLSNAP

Stores Explain Snapshot information in the Explain tables.

NO

An Explain Snapshot will not be captured.

YES

An Explain Snapshot for each eligible static SQL statement will be placed in the Explain tables at prep/bind time for static statements and at run time for incremental bind statements.

If the package is to be used for a routine and the package contains incremental bind statements, then the routine must be defined as MODIFIES SQL DATA or incremental bind statements in the package will cause a run time error (SQLSTATE 42985).

REOPT

Explain Snapshot information for each reoptimizable incremental bind SQL statement will be placed in the Explain tables at run time. In addition, Explain Snapshot information will be gathered for reoptimizable dynamic SQL statements at run time, even if the CURRENT EXPLAIN SNAPSHOT special register is set to NO.

If the package is to be used for a routine, then the routine must be defined as MODIFIES SQL DATA, otherwise incremental bind and dynamic statements in the package will cause a run time error (SQLSTATE 42985).

ALL

An Explain Snapshot for each eligible static SQL statement will be placed in the Explain tables at prep or bind time. Explain Snapshot information for each eligible incremental bind SQL statement will be placed in the Explain tables at run time. In addition, Explain Snapshot information will be gathered for eligible dynamic SQL statements at run time, even if the CURRENT EXPLAIN SNAPSHOT special register is set to NO.

If the package is to be used for a routine, then the routine must be defined as MODIFIES SQL DATA, or incremental bind and dynamic statements in the package will cause a run time error (SQLSTATE 42985).

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

ISOLATION

Determines how far a program bound to this package can be isolated from the effect of other executing programs.

CS

Specifies Cursor Stability as the isolation level.

NC

No Commit. Specifies that commitment control is not to be used. This isolation level is not supported by Db2.

RR

Specifies Repeatable Read as the isolation level.

RS

Specifies Read Stability as the isolation level. Read Stability ensures that the execution of SQL statements in the package is isolated from other application processes for rows read and changed by the application.

UR

Specifies Uncommitted Read as the isolation level.

KEEPDYNAMIC

This parameter specifies whether dynamic SQL statements are to be kept across transactions.

For details about the Db2 for z/OS supported option, see documentation in the Db2 for z/OS Information Center.

Starting with Db2 Version 9.8 Fix Pack 2, dynamic SQL statements prepared in a package bound with the KEEPDYNAMIC YES option are kept in the SQL context after a COMMIT or ROLLBACK operation. This is the default behavior.

YES

Instructs the SQL context to keep the statement text and section associated with prepared statements indefinitely. Dynamic SQL statements are kept across transactions. All packages bound with KEEPDYNAMIC YES are by default compatible with the existing package cache behavior.

No

Instructs the SQL context to remove the statement text and section associated with prepared statements at the end of each unit of work. Inactive dynamic SQL statements prepared in a package bound with KEEPDYNAMIC NO are removed from the SQL context during a COMMIT or ROLLBACK operation. The statements must be prepared again in a new transaction. The client, driver, or application needs to prepare any dynamic SQL statement it wants to reuse in a new unit of work again.

Dynamic SQL statements can remain active beyond the end of a transaction under the following circumstances:

• Cursors declared using the WITH HOLD option are open at a commit point.
• A dynamic SQL statement is executing a COMMIT or ROLLBACK operation.
• A dynamic SQL statement invokes a stored procedure or a user defined function that is executing COMMIT or ROLLBACK operation.

LANGLEVEL

Specifies the SQL rules that apply for both the syntax and the semantics for both static and dynamic SQL in the application.

MIA

Select the ISO/ANS SQL92 rules as follows:

• To support error SQLCODE or SQLSTATE checking, an SQLCA must be declared in the application code.
• C null-terminated strings are padded with blanks and always include a null-terminating character, even if truncation occurs.
• The FOR UPDATE clause is optional for all columns to be updated in a positioned UPDATE.
• A searched UPDATE or DELETE requires SELECT privilege on the object table of the UPDATE or DELETE statement if a column of the object table is referenced in the search condition or on the right side of the assignment clause.
• A column function that can be resolved using an index (for example MIN or MAX) will also check for nulls and return warning SQLSTATE 01003 if there were any nulls.
• An error is returned when a duplicate unique constraint is included in a CREATE or ALTER TABLE statement.
• An error is returned when no privilege is granted and the grantor has no privileges on the object (otherwise a warning is returned).

SAA1

Select the common IBM® Db2 rules as follows:

• To support error SQLCODE or SQLSTATE checking, an SQLCA must be declared in the application code.
• C null-terminated strings are not terminated with a null character if truncation occurs.
• The FOR UPDATE clause is required for all columns to be updated in a positioned UPDATE.
• A searched UPDATE or DELETE will not require SELECT privilege on the object table of the UPDATE or DELETE statement unless a fullselect in the statement references the object table.
• A column function that can be resolved using an index (for example MIN or MAX) will not check for nulls and warning SQLSTATE 01003 is not returned.
• A warning is returned and the duplicate unique constraint is ignored.
• An error is returned when no privilege is granted.

SQL92E

Defines the ISO/ANS SQL92 rules as follows:

• To support checking of SQLCODE or SQLSTATE values, variables by this name can be declared in the host variable declare section (if neither is declared, SQLCODE is assumed during precompilation).
• C null-terminated strings are padded with blanks and always include a null-terminating character, even if truncation occurs.
• The FOR UPDATE clause is optional for all columns to be updated in a positioned UPDATE.
• A searched UPDATE or DELETE requires SELECT privilege on the object table of the UPDATE or DELETE statement if a column of the object table is referenced in the search condition or on the right side of the assignment clause.
• A column function that can be resolved using an index (for example MIN or MAX) will also check for nulls and return warning SQLSTATE 01003 if there were any nulls.
• An error is returned when a duplicate unique constraint is included in a CREATE or ALTER TABLE statement.
• An error is returned when no privilege is granted and the grantor has no privileges on the object (otherwise a warning is returned).

LEVEL consistency-token

Defines the level of a module using the consistency token. The consistency token is any alphanumeric value up to 8 characters in length. The RDB package consistency token verifies that the requester's application and the relational database package are synchronized. This option is not recommended for general use.

LONGERROR

Indicates whether long host variable declarations will be treated as an error. For portability, sqlint32 can be used as a declaration for an INTEGER column in precompiled C and C++ code.

NO

Does not generate errors for the use of long host variable declarations. This is the default for 32 bit systems and for 64 bit Windows systems where long host variables can be used as declarations for INTEGER columns. The use of this option on 64 bit UNIX platforms will allow long host variables to be used as declarations for BIGINT columns.

YES

Generates errors for the use of long host variable declarations. This is the default for 64 bit UNIX systems.

MESSAGES message-file

Specifies the destination for warning, error, and completion status messages. A message file is created whether the bind is successful or not. If a message file name is not specified, the messages are written to standard output. If the complete path to the file is not specified, the current directory is used. If the name of an existing file is specified, the contents of the file are overwritten.

NOLINEMACRO

Suppresses the generation of the #line macros in the output .c file. Useful when the file is used with development tools which require source line information such as profiles, cross-reference utilities, and debuggers. This precompile option is used for the C/C⁺⁺ programming languages only.

OPTHINT

Controls whether query optimization hints are used for static SQL. Supported by Db2 for z/OS only. For a list of supported option values, refer to the documentation for Db2 for z/OS.

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.

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 IBM i data. Supported by Db2 for IBM 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. The package name cannot be a reserved package name. For more information on reserved package names, see Identifiers.

PREPROCESSOR "preprocessor-command"

Specifies the preprocessor command that can be executed by the precompiler before it processes embedded SQL statements. The preprocessor command string (maximum length 1024 bytes) must be enclosed either by double or by single quotation marks.

This option enables the use of macros within the declare section. A valid preprocessor command is one that can be issued from the command line to invoke the preprocessor without specifying a source file. For example,

   xlc -P -DMYMACRO=0

QUALIFIER qualifier-name

Provides an 128-byte implicit qualifier for unqualified objects contained in the package. The default is the owner's authorization ID, whether or not owner is explicitly specified.

QUERYOPT optimization-level

Indicates the required level of optimization for all static SQL statements contained in the package. The default value is 5. The SET CURRENT QUERY OPTIMIZATION statement describes the complete range of optimization levels available.

RELEASE

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

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

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 IBM i only. For a list of supported option values, refer to the documentation for Db2 for IBM i.

SQLRULES

Specifies:

• Whether type 2 CONNECTs are to be processed according to the Db2 rules or the Standard (STD) rules based on ISO/ANS SQL92.
• How an application specifies the format of LOB columns in the result set.

Db2

• Permits the SQL CONNECT statement to switch the current connection to another established (dormant) connection.
• This default setting allows an application to specify whether LOB values or LOB locators are retrieved only during the first fetch request. Subsequent fetch requests must use the same format for the LOB columns.

STD

• Permits the SQL CONNECT statement to establish a new connection only. The SQL SET CONNECTION statement must be used to switch to a dormant connection.
• The application can change between retrieving LOB values and LOB locators with each fetch request. This means that cursors with one or more LOB columns cannot be blocked, regardless of the BLOCKING bind option setting.

SQLWARN

Indicates whether warnings will be returned from the compilation of dynamic SQL statements (via PREPARE or EXECUTE IMMEDIATE), or from describe processing (via PREPARE...INTO or DESCRIBE).

NO

Warnings will not be returned from the SQL compiler.

YES

Warnings will be returned from the SQL compiler.

SQLCODE +238 is an exception. It is returned regardless of the SQLWARN option value.

STATICREADONLY

Determines whether static cursors will be treated as being READ ONLY.

NO

All static cursors will take on the attributes as would normally be generated given the statement text and the setting of the LANGLEVEL precompile option. This is the default value.

YES

Any static cursor that does not contain the FOR UPDATE or FOR READ ONLY clause will be considered READ ONLY.

STRDEL

Designates whether an apostrophe (') or double quotation marks (") will be used as the string delimiter within SQL statements. This precompile/bind option is not supported by the server for Db2. The DRDA server will use a built-in default value if this option is not specified.

APOSTROPHE

Use an apostrophe (') as the string delimiter.

QUOTE

Use double quotation marks (") as the string delimiter.

STRING_UNITS

Specifies the string units when character and graphic data types are used without explicit string units in static SQL statements. The default is based on the string_units database configuration parameter setting for the database.

SYSTEM

In a Unicode database, the setting has the following effect:

• CHAR, VARCHAR, and CLOB data types defined without specifying the CODEUNITS32 keyword will default to OCTETS.
• GRAPHIC, VARGRAPHIC, and DBCLOB data types defined without specifying the CODEUNITS32 keyword will default to CODEUNITS16.

In a non-Unicode database, the setting has the following effect:

• CHAR, VARCHAR, and CLOB data types defined without specifying the CODEUNITS32 keyword will default to OCTETS.
• GRAPHIC, VARGRAPHIC, and DBCLOB data types have implicit string units of double bytes.

CODEUNITS32

This setting is only valid for a Unicode database and has the following effect:

• CHAR, VARCHAR, and CLOB data types defined without specifying the BYTE or OCTETS keywords will default to CODEUNITS32.
• GRAPHIC, VARGRAPHIC, and DBCLOB data types defined without specifying the CODEUNITS16 keyword will default to CODEUNITS32.

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.

TRANSFORM GROUP

Specifies the transform group name to be used by static SQL statements for exchanging user-defined structured type values with host programs. This transform group is not used for dynamic SQL statements or for the exchange of parameters and results with external functions or methods.

groupname

An SQL identifier of up to 128 bytes in length. A group name cannot include a qualifier prefix and cannot begin with the prefix SYS since this is reserved for database use. In a static SQL statement that interacts with host variables, the name of the transform group to be used for exchanging values of a structured type is as follows:

• The group name in the TRANSFORM GROUP bind option, if any
• The group name in the TRANSFORM GROUP prep option as specified at the original precompilation time, if any
• The DB2_PROGRAM group, if a transform exists for the given type whose group name is DB2_PROGRAM
• No transform group is used if none of the previously listed conditions exist.

The following errors are possible during the bind of a static SQL statement:

• SQLCODE yyy, SQLSTATE xxxxx: A transform is needed, but no static transform group has been selected.
• SQLCODE yyy, SQLSTATE xxxxx: The selected transform group does not include a necessary transform (TO SQL for input variables, FROM SQL for output variables) for the data type that needs to be exchanged.
• SQLCODE yyy, SQLSTATE xxxxx: The result type of the FROM SQL transform is not compatible with the type of the output variable, or the parameter type of the TO SQL transform is not compatible with the type of the input variable.

In these error messages, yyyyy is replaced by the SQL error code, and xxxxx by the SQL state code.

UNSAFENULL

Avoids the unspecified indicator variable error, even when null indicator is not specified, when you use the precompile options UNSAFENULL YES and COMPATIBILITY_MODE ORA.

NO

By default UNSAFENULL functionality will be set to NO.

If the data type can handle nulls, the application must provide a null indicator. If a null indicator is not used and while fetching the result-set, it is found that one or more of the column data is NULL, unspecified indicator variable error will be returned to the application.

YES

Provides compatibility to suppress unspecified indicator variable error (generated when NULL value exists while the application has not specified NULL indicator in the program) while running Db2 applications which are primarily migrated from other database vendors.

VALIDATE

Determines when the database manager checks for authorization errors and object not found errors. The package owner authorization ID is used for validity checking.

BIND

Validation is performed at precompile or bind time. If all objects do not exist, or all authority is not held, error messages are produced. If SQLERROR CONTINUE is specified, a package or bind file is produced despite the error message, but the statements in error are not executable.

RUN

Validation is attempted at bind time. If all objects exist, and all authority is held, no further checking is performed at execution time.

If all objects do not exist, or all authority is not held at precompile/bind time, warning messages are produced, and the package is successfully bound, regardless of the SQLERROR CONTINUE option setting. However, authority checking and existence checking for SQL statements that failed these checks during the precompile/bind process can be redone at execution time.

VERSION

Defines the version identifier for a package. If this option is not specified, the package version will be "" (the empty string).

version-id

Specifies a version identifier that is any alphanumeric value, $, #, @, _, -, or ., up to 64 characters in length.

AUTO

The version identifier will be generated from the consistency token. If the consistency token is a timestamp (it will be if the LEVEL option is not specified), the timestamp is converted into ISO character format and is used as the version identifier.

WCHARTYPE

Specifies the format for graphic data.

CONVERT

Host variables declared using the wchar_t base type will be treated as containing data in wchar_t format. Since this format is not directly compatible with the format of graphic data stored in the database (DBCS format), input data in wchar_t host variables is implicitly converted to DBCS format on behalf of the application, using the ANSI C function wcstombs(). Similarly, output DBCS data is implicitly converted to wchar_t format, using mbstowcs(), before being stored in host variables.

NOCONVERT

Host variables declared using the wchar_t base type will be treated as containing data in DBCS format. This is the format used within the database for graphic data; it is, however, different from the native wchar_t format implemented in the C language. Using NOCONVERT means that graphic data will not undergo conversion between the application and the database, which can improve efficiency. The application is, however, responsible for ensuring that data in wchar_t format is not passed to the database manager. When this option is used, wchar_t host variables should not be manipulated with the C wide character string functions, and should not be initialized with wide character literals (L-literals).

WITH / WITHOUT TEMPORAL HISTORY

Specifies whether changes to data in system-period temporal tables made by static or dynamic SQL statements causes changes to the corresponding history table.

WITH

Specifies that changes to data in system-period temporal tables causes changes to the corresponding history table.

This is the default option.

WITHOUT

Specifies that changes to data in system-period temporal tables do not cause changes to the corresponding history table. The database manager can provide values to override the row-begin, row-end, and transaction-start-ID columns even though they are defined as GENERATED ALWAYS.

DBADM authority is required for this option.

Usage notes

A modified source file is produced, which contains host language equivalents to the SQL statements. By default, a package is created in the database to which a connection has been established. The name of the package is the same as the file name (minus the extension and folded to uppercase), up to a maximum of 8 characters. Although the maximum length of a package name is 128 bytes, unless the PACKAGE USING option is specified, only the first 8 characters of the file name are used to maintain compatibility with previous versions of Db2. The package name cannot be a reserved package name. For more information on reserved package names, see Identifiers.

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.

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.