Rational Developer for System z
PL/I for Windows, Version 8.0, Programming Guide

SQL preprocessor options

The following syntax diagram illustrates all of the options supported by the SQL preprocessor.

Read syntax diagramSkip visual syntax diagram>>-PP--(--SQL--(--'--DBNAME-(dbname)---------------------------->
 
>--+---------------------------+-------------------------------->
   '-BLOCK-+-----------------+-'
           |   .-UNAMBIG-.   |
           '-(-+---------+-)-'
               +-ALL-----+
               '-NO------'
 
   .-NOBIND--------------------.
>--+---------------------------+-------------------------------->
   '-BIND-+------------------+-'
          '-(-+----------+-)-'
              '-bindname-'
 
>--+---------------------------------------+-------------------->
   '-CALL_RESOLUTION-+-------------------+-'
                     |   .-IMMEDIATE-.   |
                     '-(-+-----------+-)-'
                         '-DEFERRED--'
 
>--+------------------------------------+----------------------->
   '-COLLECTION-+---------------------+-'
                '-(-+-------------+-)-'
                    '-schema-name-'
 
>--+-----------------------+--+--------------------------+------>
   '-CONNECT-+-----------+-'  '-DATETIME-+-------------+-'
             |   .-1-.   |               |   .-DEF-.   |
             '-(-+---+-)-'               '-(-+-----+-)-'
                 '-2-'                       +-USA-+
                                             +-EUR-+
                                             +-ISO-+
                                             +-JIS-+
                                             '-LOC-'
 
>--+---------------+--+-----------------+--'--)--)-------------->
   '-DBUID(userid)-'  '-DBPWD(password)-'
 
   .-NODECK-.
>--+--------+--+------------------------------------+----------->
   '-DECK---'  '-DISCONNECT-+---------------------+-'
                            |   .-EXPLICIT----.   |
                            '-(-+-------------+-)-'
                                +-AUTOMATIC---+
                                '-CONDITIONAL-'
 
   .-NOINCONLY-.
>--+-----------+--+------------------------+-------------------->
   '-INCONLY---'  '-INSERT-+-------------+-'
                           |   .-DEF-.   |
                           '-(-+-----+-)-'
                               '-BUF-'
 
                                 .-ONEPASS-.  .-NOOPTIMIZE-.
>--+--------------------------+--+---------+--+------------+---->
   '-ISOLATION-+------------+-'  '-TWOPASS-'  '-OPTIMIZE---'
               |   .-CS-.   |
               '-(-+----+-)-'
                   +-RR-+
                   +-RS-+
                   '-UR-'
 
>--+------------------------------------+----------------------->
   '-OWNER-+--------------------------+-'
           '-(-+------------------+-)-'
               '-authorization-id-'
 
                                  .-NOPRINT-.
>--+---------------------------+--+---------+------------------->
   +-PLAN-+------------------+-+  '-PRINT---'
   |      '-(-+----------+-)-' |
   |          '-planname-'     |
   +-NOPLAN--------------------+
   '-NOPLANSYNTAX--------------'
 
                                             .-NOSCOPE-.
>--+--------------------------------------+--+---------+-------->
   '-QUALIFIER-+------------------------+-'  '-SCOPE---'
               '-(-+----------------+-)-'
                   '-qualifier-name-'
 
>--+-------------------------------+---------------------------->
   '-SQLFLAG-+-------------------+-'
             |   .-MVSDB2V23-.   |
             '-(-+-----------+-)-'
                 +-MVSDB2V31-+
                 '-MVSDB2V41-'
 
                                 .-NOSOURCE-.
>--+--------------------------+--+----------+------------------->
   '-SQLRULES-+-------------+-'  '-SOURCE---'
              |   .-DB2-.   |
              '-(-+-----+-)-'
                  '-STD-'
 
>--+--------------------------------+--------------------------->
   '-SYNCPOINT-+------------------+-'
               |   .-ONEPHASE-.   |
               '-(-+----------+-)-'
                   +-TWOPHASE-+
                   '-NONE-----'
 
>--+-----------------------------------------+------------------>
   '-TBQUAL-+------------------------------+-'
            '-(-+----------------------+-)-'
                '-table-qualifier-name-'
 
>--+---------------------------+-------------------------------->
   '-VALIDATE-+--------------+-'
              |   .-BIND-.   |
              '-(-+------+-)-'
                  '-RUN--'
 
>--+--------------------------------+--------------------------><
   '-VERSION-+--------------------+-'
             |   .-version-id-.   |
             '-(-+------------+-)-'
                 '-AUTO-------'
 

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:

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.

Terms of use | Feedback

This information center is powered by Eclipse technology. (http://www.eclipse.org)