The following syntax diagram illustrates all of the options supported by
the SQL preprocessor.
Abbreviations:
DB, BLK, CRESO, DT, ISOL, ON, TW, S, NS, D, ND, OPT, NOPT, INS, COL, CON,
DISC, SQLR, SYNC
- DBNAME
- Specifies the original or alias name of a database. This option directs
the preprocessor to process the SQL statements against the specified database.
If you omit this option or do not specify a database name, the preprocessor
uses the default database if an implicit connect is enabled. The default database
is specified by the environment variable DB2DBDFT. Further information is
available in your DB2 documentation.
The preprocessor must have a database
to work with or an error occurs.
- BLOCK
- Specifies the type of record blocking to be used and how ambiguous cursors
are to be treated. The valid values for this option are:
- UNAMBIG
- Blocking occurs for read-only cursors, those that are not specified
as FOR UPDATE OF, have no static DELETE WHERE CURRENT OF statements, and have
no dynamic statements. Ambiguous cursors can be updated.
- ALL
- Blocking occurs for read-only cursors, those that are not specified
as FOR UPDATE OF, and for which no static DELETE WHERE CURRENT OF statements
are executed. Ambiguous and dynamic cursors are treated as read-only.
- NO
- No blocking is done on any cursors in the package. Ambiguous cursors
can be updated.
- BIND or NOBIND
- Determines whether or not a bind file bindname is created.
The bind file has an extension .BND and is saved either in the current directory
or the directory specified by the IBM_BIND environment variable. If you
do not specify a bindname, the name defaults to the name of the input source
file.
- CALL_RESOLUTION
- Determines whether the CALL statement will be executed as an invocation
of the deprecated sqleproc() API or as a normal SQL statement. Note that SQL0204
will be issued if the precompiler fails to resolve the procedure on a CALL
statement with CALL_RESOLUTION IMMEDIATE.
- IMMEDIATE
- The CALL statement will be executed as a normal SQL statement. This
is the default.
- DEFERRED
- The CALL statement will be executed as an invocation of the deprecated
sqleproc() API.
- COLLECTION
- Specifies an eight character collection identifier for the package.
- schema-name
- Eight character identifier.
There is no default value for the COLLECTION option. If the
COLLECTION is specified, a schema-name must also be provided.
- CONNECT
- Specifies the type of CONNECT that is made to the database.
- 1
- Specifies that a CONNECT command is processed as a type 1 CONNECT. This
is the default setting.
- 2
- Specifies that a CONNECT command is processed as a type 2 CONNECT.
The default option value is CONNECT(1). The following option
strings evaluate to CONNECT(1): CON, CONNECT, CON(), and CONNECT().
- DATETIME
- Determines the date and time format used when date and time fields are
assigned to string representations in host variables. The following three-letter
abbreviations are valid for the variable location:
- DEF
- Use the date/time format associated with the country code of the database.
This is also the default if DATETIME is not specified.
- USA
- IBM standard for U.S. form.
- Date format: mm/dd/yyyy
- Time format: hh:mm xM (AM or PM)
- EUR
- IBM standard for European form.
- Date format: dd.mm.yyyy
- Time format: hh.mm.ss
- ISO
- International Standards Organization.
- Date format: yyyy-mm-dd
- Time format: hh.mm.ss
- JIS
- Japanese Industrial Standards.
- Date format: yyyy-mm-dd
- Time format: hh:mm:ss
- LOC
- Local form, not necessarily equal to DEF
- DBUID and DBPWD
- Allows you to specify a userid and password for those
database managers which require that these values be supplied when a remote
connection is attempted. For example, these values might be required during
a compile against a remote database resident on a Windows server.
The
options DBUID and DBPWD can be in either case, but the values of userid (maximum length is 8 characters) and password (maximum length
is 18 characters) are case sensitive.
The userid and password are
only used by the SQL preprocessor to connect to the database manager during
the compile process. When the application connects during execution, the userid
and password for that connect must be provided on the EXEC SQL CONNECT statement
in the program.
- DECK or NODECK
- This option specifies that the SQL preprocessor output source is written
to a file with the extension .DEK and the file is put the current directory.
- DISCONNECT
- Specifies the type of DISCONNECT that is made to the database.
- EXPLICIT
- Specifies that only database connections that have been explicitly marked
for release by the RELEASE statement are disconnected at commit. This is the
default setting.
- AUTOMATIC
- Specifies that all database connections are disconnected at commit.
- CONDITIONAL
- Specifies that the database connections that have been marked RELEASE
or have no open WITH HOLD cursors are disconnected at commit.
The default option value is DISCONNECT(EXPLICIT). The following
option strings evaluate to DISCONNECT(EXPLICIT): DISC, DISCONNECT, DISC(),
DISCONNECT().
- INCONLY or NOINCONLY
- Determines whether or not the SQL preprocessor should process
only EXEC SQL INCLUDE statements. When INCONLY is specified, no code
is generated by the SQL preprocessor and all of the EXEC SQL INCLUDE
statements are expanded. When NOINCONLY is specified, the SQL preprocessor
processes all statements and generates code.
INCONLY and NOINCONLY are mutually exclusive, and for
compatibility, NOINCONLY is the default.
- INSERT
- Requests that the data inserts be buffered to increase performance on
the DB2/6000 Parallel Edition server.
- DEF
- Use standard INSERT with VALUES execution. This is the default setting.
- BUF
- Use buffering when executing INSERTs with VALUES.
Note:
This option can only be used when precompiling against
a DB2 Parallel Edition server. If INSERT is used against a DB2 V1.x server,
it is ignored and a warning message is issued. If INSERT is used against a
DB2 V2.x server, it is ignored, a warning message is issued, and the option
is added to the bind file.
- 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.
- RR
- Repeatable readSpecifies 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.
- ONEPASS or TWOPASS
- ONEPASS is the default and indicates that host variables must be declared
before use. Use of TWOPASS indicates that host variables do not need to be
declared before use.
- OPTIMIZE or NOOPTIMIZE
- If you specify OPTIMIZE, SQLDA initialization is optimized for SQL statements
that use host variables. Do not specify this option when using AUTOMATIC host
variables or in other situations when the address of the host variable might
change during the execution of the program. (NOOPTIMIZE) is the default.
- OWNER
- Designates a 30-character authorization-id 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-id 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.
- PLAN, NOPLAN, or NOPLANSYNTAX
- Determines whether or not an access plan planname is created.
If you do not specify a planname, the name defaults to the name of the input
source file.
If you specify NOPLANSYNTAX, no plan is created and a syntax
check is performed against DB2 Version 2.1 syntax.
- PRINT or NOPRINT
- Specifies whether or not the source code generated by the SQL preprocessor
is printed in the source listing(s) produced by subsequent preprocessors or
the compiler.
- QUALIFIER
- Provides an 30-character implicit qualifier-name for
unqualified objects contained in the package. The default is the owner's authorization
ID, whether or not owner is explicitly specified.
- SCOPE or NOSCOPE
- Determines whether the PL/I rules for the scope of declarations are
applied when resolving host variables references.
When SCOPE is specified, the PL/I rules for the scope of declarations
are applied when resolving host variable references. This means
that a host variable reference in an SQL statement will always be
resolved in the same manner as if the variable were used in any other
PL/I statement.
To use the SCOPE option, note the following restrictions:
- The data lists in GET and PUT statements must not include data-list
items with Type 3 DO specifications.
- Do not use the following words as variable names: BEGIN, DO, END,
PACKAGE, PROC, PROCEDURE and SELECT.
When NOSCOPE is specified, the names of all host variables should be
unique within each program. If a host variable name is declared more
than once, a reference to it in an SQL statement will be resolved by
using its first declaration.
The default is NOSCOPE.
- SOURCE or NOSOURCE
- Specifies whether or not the source input to the SQL preprocessor is
printed.
- SQLFLAG
- Identifies and reports on deviations from SQL language syntax specified
in this option. If this option is not specified, the flagger function is not
invoked. Further information is available in your DB2 documentation.
- MVSDB2V23
- SQL statements are checked against the MVS DB2 V2.3 SQL language syntax.
This is the default setting.
- MVSDB2V31
- SQL statements are checked against the MVS DB2 V3.1 SQL language syntax.
- MVSDB2V41
- SQL statements are checked against the MVS DB2 V4.1 SQL language syntax.
- SQLRULES
- Specifies whether type 2 CONNECTs should be processed according to the
DB2 rules or the Standard (STD) rules based on ISO/ANS SQL92.
- DB2
- Allows the use of the SQL CONNECT statement to switch the current connection
to another established (dormant) connection. This is the default setting.
- STD
- Allows the use of the SQL CONNECT statement to establish a new connection
only. The SQL SET CONNECTION must be used to switch to a dormant connection.
The default option value is SQLRULES(DB2). The following option
strings evaluate to SQLRULES(DB2): SQLR, SQLRULES, SQLR(), SQLRULES().
- SYNCPOINT
- Specifies how commits or rollbacks are coordinated among multiple database
connections.
- ONEPHASE
- Specifies that no Transaction Manager (TM) is used to perform a two-phase
commit. A one-phase commit is used to commit the work done by each database
in multiple database transactions. This is the default setting.
- TWOPHASE
- Specifies that the TM is required to coordinate two-phase commits among
those databases that support this protocol.
- NONE
- Specifies that no TM is 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.
The default option value is SYNCPOINT(ONEPHASE). The following
option strings evaluate to SYNCPOINT(ONEPHASE): SYNC, SYNCPOINT, SYNC(), SYNCPOINT().
- TBQUAL
- Provides an 8-character implicit table-qualifier-name for unqualified objects contained in the package.
- VALIDATE
- Determines when the database manager checks for authorization errors
and object not found errors. The package owner authorization ID is used for
validity checking.
- BIND
- Validation is performed at precompile/bind time. If all objects do not
exist, or all authority is not held, error messages are produced. If sqlerror continue is specified, a package/bind file is produced despite
the error message, but the statements in error are not executable.
- RUN
- Validation is attempted at bind time. If all objects exist, and all
authority is held, no further checking is performed at execution time. If
all objects do not exist, or all authority is not held at precompile/bind
time, warning messages are produced, and the package is successfully bound,
regardless of the sqlerror continue option setting.
However, authority checking and existence checking for SQL statements that
failed these checks during the precompile/bind process can be redone at execution
time.
- VERSION
- Defines the version identifier for a package. If this option is not
specified, the package version will be "" (the empty string).
- version-id
- Specifies a version identifier that is any alphanumeric value, $, #,
@, _, -, or ., up to 64 characters in length.
- AUTO
- The version identifier will be generated from the consistency token.
If the consistency token is a timestamp (it will be if the LEVEL option is
not specified), the timestamp is converted into ISO character format and is
used as the version identifier.