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

SQL preprocessor options

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

Table 7 lists all the SQL preprocessor options with their abbreviations (if any) and their IBM-supplied default values. This table uses a vertical bar(|) to separate mutually exclusive options, and brackets ([ ]) to indicate that you can sometimes omit the enclosed option.

The paragraphs following Table 7 describe the options in the alphabetical order.

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

Table 7. SQL preprocessor options and IBM-supplied defaults
SQL Preprocessor Option
Abbreviated Name
z/OS Default
APOSTSQL | QUOTESQL
-
APOSTSQL
ATTACH(TSO | CAF | RRSAF)
-
ATTACH(TSO)
CCSID0 | NOCCSID0
-
CCSID0
CONNECT(2 | 1)
CT
CONNECT(2)
DATE(ISO | USA | EUR |
JIS | LOCAL)
-
In the field DATE FORMAT on the Application
Programming Defaults Panel 2 when DB2 is installed
DEC(15 | 31)
-
In the field DECIMAL ARITHMETIC on the Application
Programming Defaults Panel 1 when DB2 is installed
FLOAT(S390 | IEEE)
-
FLOAT(S390)
GRAPHIC | NOGRAPHIC
-
In the field MIXED DATA on the Application
Programming Defaults Panel 1 when DB2 is installed
INCONLY | NOINCONLY
-
NOINCONLY
LEVEL[(aaaa)]
L
-
LOB ( DB2 | PLI )
-
LOB( DB2 )
NOFOR
-
-
ONEPASS | TWOPASS
ON | TW
ONEPASS
SCOPE | NOSCOPE
-
NOSCOPE
SQL(ALL | DB2)
-
SQL(DB2)
SQLFLAG(IBM |
STD[(ssname[,qualifier])])
-
-
STDSQL(NO | YES)
-
In the field STD SQL LANGUAGE on the Application
Programming Defaults Panel 2 when DB2 is installed
TIME(ISO | USA | EUR |
JIS | LOCAL)
-
In the field TIME FORMAT on the Application
Programming Defaults Panel 2 when DB2 is installed
VERSION(aaaa | AUTO)
-
-
XREF | NOXREF
-
NOXREF

APOSTSQL

The APOSTSQL option specifies that the apostrophe (') is recognized as the string delimiter and the quotation mark (") as the SQL escape character within SQL statements.

The QUOTESQL option specifies that the quotation mark (") is recognized as the string delimiter and the apostrophe (’) as the SQL escape character within SQL statements.

Read syntax diagramSkip visual syntax diagram   .-APOSTSQL-.
>>-+----------+------------------------------------------------><
   '-QUOTESQL-'
 

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 is APOSTSQL.

ATTACH

The ATTACH option specifies the attachment facility that the application uses to access DB2, TSO, CAF, and RRSAF.

Read syntax diagramSkip visual syntax diagram              .-TSO---.
>>-ATTACH--(--+-------+--)-------------------------------------><
              +-CAF---+
              '-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

The CCSID0 option specifies that no host variables be assigned a CCSID value by the PL/I SQL preprocessor.

The NOCCSID0 option allows host variables to be assigned a CCSID value by the PL/I SQL preprocessor.

Read syntax diagramSkip visual syntax diagram   .-CCSID0---.
>>-+----------+------------------------------------------------><
   '-NOCCSID0-'
 

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 is CCSID0.

CONNECT

The CONNECT option determines whether to apply type 1 or type 2 CONNECT statement rules.

Read syntax diagramSkip visual syntax diagram            .-2-.
>>-CONNECT(-+---+-)--------------------------------------------><
            '-1-'
 

ABBREVIATION: CT

CONNECT(2)
applies rules for the CONNECT (Type 2) statement.
CONNECT(1)
applies rules for the CONNECT (Type 1) statement.

The default is CONNECT(2).

For more information about this option, see DB2 SQL Reference.

DATE

The DATE option 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, see DB2 SQL Reference.

Read syntax diagramSkip visual syntax diagram>>-+-----------------------+-----------------------------------><
   '-DATE--(--+-------+--)-'
              +-ISO---+
              +-USA---+
              +-EUR---+
              +-JIS---+
              '-LOCAL-'
 

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

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

DEC

The DEC option specifies the maximum precision for decimal arithmetic operations.

Read syntax diagramSkip visual syntax diagram>>-+-------------------+---------------------------------------><
   |         .-15-.    |
   '-DEC--(--+----+--)-'
             '-31-'
 

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

FLOAT

The FLOAT option determines whether the contents of floating point host variables are in System/390 hexadecimal format or in IEEE format.

Read syntax diagramSkip visual syntax diagram             .-S390-.
>>-FLOAT--(--+------+--)---------------------------------------><
             '-IEEE-'
 

An error message is issued if this FLOAT option is different than the PL/I compiler's DEFAULT(HEXADEC|IEEE) option.

The default is FLOAT(S390).

GRAPHIC

The GRAPHIC option 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.

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

Read syntax diagramSkip visual syntax diagram>>-+-----------+-----------------------------------------------><
   +-GRAPHIC---+
   '-NOGRAPHIC-'
 

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

The INCONLY 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.

The NOINCONLY option specifies that the SQL preprocessor should process all statements and not only EXEC SQL INCLUDE statements.

Read syntax diagramSkip visual syntax diagram   .-NOINCONLY-.
>>-+-----------+-----------------------------------------------><
   '-INCONLY---'
 

The INCONLY option and the NOINCONLY option are mutually exclusive.

For compatibility, the default is NOINCONLY.

LEVEL

The LEVEL option defines the level of a module.

Read syntax diagramSkip visual syntax diagram>>-+------------------+----------------------------------------><
   '-LEVEL-+--------+-'
           '-(aaaa)-'
 

ABBREVIATION: L

aaaa
is any alphanumeric value of up to seven characters.

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

This option is not recommended for general use, and the DSNH CLIST and the DB2I panels do not support it.

LOB

The LOB option determines the format of the LOB (Large Object) DECLARE and DEFINE statements generated by the SQL preprocessor.

Read syntax diagramSkip visual syntax diagram           .-DB2-.
>>-LOB--(--+-----+--)------------------------------------------><
           '-PLI-'
 

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

NOFOR

In static SQL, NOFOR eliminates the need for the FOR UPDATE of FOR UPDATE OF clause in DECLARE CURSOR statements.

Read syntax diagramSkip visual syntax diagram>>-+-------+---------------------------------------------------><
   '-NOFOR-'
 

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.

ONEPASS

The ONEPASS option specifies that the SQL preprocessor 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.

The TWOPASS option specifies that the SQL preprocessor processes in two passes, so that declarations need not precede references.

Read syntax diagramSkip visual syntax diagram   .-ONEPASS-.
>>-+---------+-------------------------------------------------><
   '-TWOPASS-'
 

ABBREVIATIONS: ON, TW

ONEPASS and TWOPASS are mutually exclusive options.

The default is ONEPASS.

SCOPE

The SCOPE option determines whether the PL/I rules for the scope of declarations are applied when resolving host variables references.

Read syntax diagramSkip visual syntax diagram   .-NOSCOPE-.
>>-+---------+-------------------------------------------------><
   '-SCOPE---'
 

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.

SQL

The SQL option indicates whether the source contains SQL statements other than those recognized by DB2 for z/OS.

Read syntax diagramSkip visual syntax diagram           .-DB2-.
>>-SQL--(--+-----+--)------------------------------------------><
           '-ALL-'
 
DB2
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.

ALL
indicates that the SQL statements in the program are not necessarily for 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.

When SQL(ALL) is specified, the SQL statement processor takes the following actions:

The default is SQL(DB2).

SQLFLAG

The SQLFLAG option specifies the standard used to check the syntax of SQL statements.

Read syntax diagramSkip visual syntax diagram>>-+-----------------------------------------------------+-----><
   '-SQLFLAG--(--+----------------------------------+--)-'
                 +-STD-+--------------------------+-+
                 |     '-(ssname-+------------+-)-' |
                 |               '-,qualifier-'     |
                 '-IBM------------------------------'
 
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.

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.

STDSQL

The STDSQL option indicates to which rules the output statements should conform.

Read syntax diagramSkip visual syntax diagram>>-+-----------------------+-----------------------------------><
   |            .-NO--.    |
   '-STDSQL--(--+-----+--)-'
                '-YES-'
 
NO
indicates conformance to DB2 rules.
YES
indicates that the precompiled SQL statements in the source program conform to certain rules of the SQL standard.

STDSQL(YES) automatically implies the NOFOR option.

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

TIME

The TIME option 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, see DB2 SQL Reference.

Read syntax diagramSkip visual syntax diagram>>-+-----------------------+-----------------------------------><
   '-TIME--(--+-------+--)-'
              +-ISO---+
              +-USA---+
              +-EUR---+
              +-JIS---+
              '-LOCAL-'
 

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

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

VERSION

The VERSION option defines the version identifier of a package, program, and the resulting DBRM.

Read syntax diagramSkip visual syntax diagram>>-+-------------------------+---------------------------------><
   '-VERSION--(--+------+--)-'
                 +-aaaa-+
                 '-AUTO-'
 

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. See DB2 UDB for z/OS Application Programming and SQL Guide for information about how they are determined.

XREF

The (NO)XREF option determines whether a cross-reference table of names that are used in SQL statements is included in the compiler listing.

Read syntax diagramSkip visual syntax diagram   .-NOXREF-.
>>-+--------+--------------------------------------------------><
   '-XREF---'
 

When the XREF option is specified, the compiler listing includes a cross-reference table that includes the following items:

When the NOXREF option is specified, the compiler listing does not include the cross-reference table of names that are used in SQL statement.

The use of the XREF SQL preprocessor option requires DB2 for z/OS V10 or later.

The default is NOXREF.


Terms of use | Feedback

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