Rational Developer for System z
Enterprise PL/I for z/OS, Version 3.8, Programming Guide

SQL preprocessor options

When you specify SQL preprocessor options, the list of options must be enclosed in quotes (single or double, as long as they match). For example, to specify the DATE(ISO) option, you must specify PP(SQL(’DATE(ISO)’)).

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

Read syntax diagramSkip visual syntax diagram                             .-APOSTSQL-.
>>---PP----(----SQL----(--'--+----------+----------------------->
                             '-QUOTESQL-'
 
                                .-CCSID0---.
>--+-------------------------+--+----------+-------------------->
   |            .-TSO---.    |  '-NOCCSID0-'
   '-ATTACH--(--+-------+--)-'
                +-CAF---+
                '-RRSAF-'
 
>--+------------------+--+-----------------------+-------------->
   |          .-2-.   |  '-DATE--(--+-------+--)-'
   '-CONNECT(-+---+-)-'             +-ISO---+
              '-1-'                 +-USA---+
                                    +-EUR---+
                                    +-JIS---+
                                    '-LOCAL-'
 
>--+-------------------+--+-----------------------+------------->
   |         .-15-.    |  |           .-S390-.    |
   '-DEC--(--+----+--)-'  '-FLOAT--(--+------+--)-'
             '-31-'                   '-IEEE-'
 
   .-NOINCONLY-.  .-ONEPASS-.
>--+-----------+--+---------+--+------------------+------------->
   '-INCONLY---'  '-TWOPASS-'  '-LEVEL-+--------+-'
                                       '-(aaaa)-'
 
                                      .-OPTIONS---.
>--+--------------------+--+-------+--+-----------+------------->
   |         .-DB2-.    |  '-NOFOR-'  '-NOOPTIONS-'
   '-LOB--(--+-----+--)-'
             '-PLI-'
 
>--+-----------------------------------------------------+------>
   '-SQLFLAG--(--+----------------------------------+--)-'
                 +-STD-+--------------------------+-+
                 |     '-(ssname-+------------+-)-' |
                 |               '-,qualifier-'     |
                 '-IBM------------------------------'
 
>--+-----------------------+--+-----------------------+--------->
   |            .-NO--.    |  '-TIME--(--+-------+--)-'
   '-STDSQL--(--+-----+--)-'             +-ISO---+
                '-YES-'                  +-USA---+
                                         +-EUR---+
                                         +-JIS---+
                                         '-LOCAL-'
 
>--+--------------------+--+-------------------------+--'--)---->
   |         .-DB2-.    |  '-VERSION--(--+------+--)-'
   '-SQL--(--+-----+--)-'                +-aaaa-+
             '-ALL-'                     '-AUTO-'
 
>--)-----------------------------------------------------------><
 

In addition to these PL/I SQL preprocessor options, you may pass DB2 Coprocessor options in on the PP(SQL('options')) compiler option. For more information about DB2 Coprocessor options please consult the DB2 UDB for z/OS Application Programming and SQL Guide.

The table uses a vertical bar(|) to separate mutually exclusive options, and brackets ([ ]) to indicate that you can sometimes omit the enclosed option.

APOSTSQL
Recognizes the apostrophe (') as the string delimiter and the quotation mark (") as the SQL escape character within SQL statements.

For compatibility with older PL/I programs which used the DB2 precompiler, APOSTSQL should be chosen.

APOSTSQL and QUOTESQL are mutually exclusive options.

The default setting is APOSTSQL.

ATTACH(TSO|CAF|RRSAF)
Specifies the attachment facility that the application uses to access DB2. TSO, CAF and RRSAF. Applications that load the attachment facility can use this option to specify the correct attachment facility, instead of coding a dummy DSNHLI entry point.

The default is ATTACH(TSO).

CCSID0
CCSID0 specifies that no host variables be assigned a CCSID value.

If your program updates FOR BIT DATA columns with a data type that is not BIT data, you will want to choose CCSID0. CCSID0 tells DB2 that the host variable is not associated with a CCSID, allowing the assignment to be made. Otherwise, if a host variable that is associated with a CCSID that is not BIT data is assigned to a FOR BIT DATA column, a DB2 error occurs.

For compatibility with older PL/I programs which used the DB2 precompiler, CCSID0 should be chosen.

CCSID0 and NOCCSID0 are mutually exclusive options.

The default setting is CCSID0.

CONNECT(2|1)
Determines whether to apply type 1 or type 2 CONNECT statement rules.

The default is CONNECT(2).

For more information about this option, refer to the DB2 SQL Reference manual.

The CONNECT option can be abbreviated to CT.

DATE(ISO|USA|EUR|JIS|LOCAL)
Specifies that date output should always be returned in a particular format, regardless of the format specified as the location default. For a description of these formats, refer to the DB2 SQL Reference manual.

The default is in the field DATE FORMAT on the Application Programming Defaults Panel 2 when DB2 is installed.

You cannot use the LOCAL option unless you have a date exit routine.

DEC(15|31)
Specifies the maximum precision for decimal arithmetic operations.

The default is in the field DECIMAL ARITHMETIC on the Application Programming Defaults Panel 1 when DB2 is installed.

FLOAT(S390|IEEE)
Determines whether the contents of floating point host variables are in System/390 hexadecimal format or in IEEE format. An error message is issued if this FLOAT option is different than the PL/I compiler's DEFAULT(HEXADEC|IEEE) option.

The default setting is FLOAT(S390).

GRAPHIC
Indicates that the source code might use mixed data, and that X'0E' and X'0F' are special control characters (shift-out and shift-in) for EBCDIC data.

GRAPHIC and NOGRAPHIC are mutually exclusive options. The default is in the field MIXED DATA on the Application Programming Defaults Panel 1 when DB2 is installed.

INCONLY
This option specifies that the SQL preprocessor should process only EXEC SQL INCLUDE statements. No code is generated by the SQL preprocessor when this option is in effect. This option and the NOINCONLY option are mutually exclusive, and for compatibility, NOINCONLY is the default.
LEVEL[(aaaa)]
Defines the level of a module, where aaaa is any alphanumeric value of up to seven characters. This option is not recommended for general use, and the DSNH CLIST and the DB2I panels do not support it.

You can omit the suboption (aaaa). The resulting consistency token is blank.

The LEVEL option can be abbreviated to L.

LOB ( DB2 | PLI )
Determines the format of the LOB (Large Object) DECLARE and DEFINE statements generated by the SQL preprocessor.

Under LOB( DB2 ), the generated LOB DECLARE statements are consistent with the form generated by the DB2 Precompiler. Beginning with Enterprise PL/I V3R7 the code generated for all SQL TYPE declarations, including LOCATOR, ROWID, and *LOB_FILE types, will also be consistent with the DB2 Precompiler output. Choose this option if you are moving from the DB2 Precompiler.

For example, under this option the statement:

Dcl BLOB_VAR1 Sql Type Is BLOB(32000);

will be converted to:

   DCL
  /*$*$*$
  Sql Type Is BLOB(32000)
  $*$*$*/
        1 BLOB_VAR1,
              3 BLOB_VAR1_LENGTH FIXED BIN(31),
              3 BLOB_VAR1_DATA CHAR(32000);

Under LOB( PLI ), the generated LOB DEFINE statements are consistent with the form generated by the workstation PL/I compilers. Choose this option if you are using PL/I on both the mainframe and workstation platforms to provide cross platform consistency. For example, under this option the statement:

Dcl BLOB_VAR1 Sql Type Is BLOB(32000);

will be converted to:

DEFINE STRUCTURE
   1 BLOB$$x,
      2 BLOB_VAR1_LENGTH  FIXED BIN(31),
      2 BLOB_VAR1_DATA,
         3 BLOB_VAR1_DATA1(1) CHAR(32000);
 DCL BLOB_VAR1   TYPE  BLOB$$x        ;

The default is LOB( DB2 ).

NOCCSID0
NOCCSID0 allows host variables to be assigned a CCSID value.

If your program updates FOR BIT DATA columns with a data type that is not BIT data, you will want to choose CCSID0. CCSID0 tells DB2 that the host variable is not associated with a CCSID, allowing the assignment to be made. Otherwise, if a host variable that is associated with a CCSID that is not BIT data is assigned to a FOR BIT DATA column, a DB2 error occurs.

For compatibility with older PL/I programs which used the DB2 precompiler, CCSID0 should be chosen.

NOCCSID0 and CCSID0 are mutually exclusive options.

The default setting is CCSID0.

NOFOR
In static SQL, NOFOR eliminates the need for the FOR UPDATE of FOR UPDATE OF clause in DECLARE CURSOR statements. When you use NOFOR, your program can make positioned updates to any columns that the program has DB2 authority to update.

When you do not use NOFOR, if you want to make positioned updates to any columns that the program has DB2 authority to update, you need to specify FOR UPDATE with no column list in your DECLARE CURSOR statements. The FOR UPDATE clause with no column list applies to static or dynamic SQL statements.

Whether you use or do not use NOFOR, you can specify FOR UPDATE OF with a column list to restrict updates to only the columns named in the clause and specify the acquisition of update locks.

You imply NOFOR when you use the option STDSQL(YES).

If the resulting DBRM is very large, you might need extra storage when you specify NOFOR or use the FOR UPDATE clause with no column list.

NOGRAPHIC
Indicates the use of X'0E' and X'0F' in a string, but not as control characters.

GRAPHIC and NOGRAPHIC are mutually exclusive options. The default is in the field MIXED DATA on the Application Programming Defaults Panel 1 when DB2 is installed.

NOINCONLY
This option specifies that the SQL preprocessor should process all statements and not only EXEC SQL INCLUDE statements. This option and the INCONLY option are mutually exclusive, and for compatibility, NOINCONLY is the default.
NOOPTIONS
Suppresses the SQL Preprocessor options listing.

The NOOPTIONS option can be abbreviated to NOOPTN.

ONEPASS
Processes in one pass, to avoid the additional processing time for making two passes. Declarations must appear before SQL references if the ONEPASS option is used.

ONEPASS and TWOPASS are mutually exclusive options.

The default is ONEPASS.

The ONEPASS option can be abbreviated to ON.

OPTIONS
Lists SQL Preprocessor options.

The default is OPTIONS.

The OPTIONS option can be abbreviated to OPTN.

QUOTESQL
Recognizes the quotation mark (") as the string delimiter and the apostrophe (’) as the SQL escape character within SQL statements.

For compatibility with older PL/I programs which used the DB2 precompiler, APOSTSQL should be chosen.

QUOTESQL and APOSTSQL are mutually exclusive options.

The default setting is APOSTSQL.

SQL(ALL|DB2)
Indicates whether the source contains SQL statements other than those recognized by DB2 for z/OS.

SQL(ALL) is recommended for application programs whose SQL statements must execute on a server other than DB2 for z/OS using DRDA access. SQL(ALL) indicates that the SQL statements in the program are not necessarily for DB2 for and z/OS. Accordingly, the SQL statement processor then accepts statements that do not conform to the DB2 syntax rules. The SQL statement processor interprets and processes SQL statements according to distributed relational database architecture (DRDA) rules. The SQL statement processor also issues an informational message if the program attempts to use an IBM SQL reserved words as ordinary identifiers. SQL(ALL) does not affect the limits of the SQL statement processor.

SQL(DB2), the default, means to interpret SQL statements and check syntax for use by DB2 for z/OS. SQL(DB2) is recommended when the database server is DB2 for z/OS.

SQLFLAG(IBM|STD[(ssname[,qualifier])])
Specifies the standard used to check the syntax of SQL statements. When statements deviate from the standard, the SQL statement processor writes informational messages (flags) to the output listing. The SQLFLAG option is independent of other SQL statement processor options, including SQL and STDSQL.

IBM checks SQL statements against the syntax of IBM SQL Version 1.

STD checks SQL statements against the syntax of the entry level of the ANSI/ISO SQL standard of 1992. You can also use 86 for option, as in releases before Version 7.

ssname requests semantics checking, using the specified DB2 subsystem name for catalog access. If you do not specify ssname, the SQL statement processor checks only the syntax.

qualifier specifies the qualifier used for flagging. If you specify a qualifier, you must always specify the ssname first. If qualifier is not specified, the default is the authorization ID of the process that started the SQL statement processor.

STDSQL(NO|YES)
Indicates to which rules the output statements should conform.

STDSQL(YES) indicates that the precompiled SQL statements in the source program conform to certain rules of the SQL standard. STDSQL(NO) indicates conformance to DB2 rules.

The default is in the field STD SQL LANGUAGE on the Application Programming Defaults Panel 2 when DB2 is installed.

STDSQL(YES) automatically implies the NOFOR option.

TIME(ISO|USA|EUR|JIS|LOCAL)
Specifies that time output should always be returned in a particular format, regardless of the format specified as the location default. For a description of these formats, refer to the DB2 SQL Reference manual.

The default is in the field TIME FORMAT on the Application Programming Defaults Panel 2 when DB2 is installed.

You cannot use the LOCAL option unless you have a date exit routine.

TWOPASS
Processes in two passes, so that declarations need not precede references.

ONEPASS and TWOPASS are mutually exclusive options.

The default is ONEPASS.

The TWOPASS option can be abbreviated to TW.

VERSION(aaaa|AUTO)
Defines the version identifier of a package, program, and the resulting DBRM. When you specify VERSION, the SQL statement processor creates a version identifier in the program and DBRM. This affects the size of the load module and DBRM. DB2 uses the version identifier when you bind the DBRM to a plan or package.

If you do not specify a version at precompile time, then an empty string is the default version identifier. If you specify AUTO, the SQL statement processor uses the consistency token to generate the version identifier. If the consistency token is a timestamp, the timestamp is converted into ISO character format and used as the version identifier. The timestamp used is based on the System/370 Store Clock value.

When you compile your PL/I program against a DB2 V9 (or later) database the options provided in the listing are divided into the following two categories:

SQL Preprocessor Options Used
A list of the PL/I SQL preprocessor options that were in effect at the time of the compile.
DB2 for z/OS Coprocessor Options used
A list of the DB2 for z/OS Coprocessor options that were in effect at the time of the compile. Please refer to the DB2 UDB for z/OS Application Programming and SQL Guide for information on how they are determined.

Terms of use | Feedback

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