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.
.-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.
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.
The default is ATTACH(TSO).
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.
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.
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.
The default is in the field DECIMAL ARITHMETIC on the Application Programming Defaults Panel 1 when DB2 is installed.
The default setting is FLOAT(S390).
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.
You can omit the suboption (aaaa). The resulting consistency token is blank.
The LEVEL option can be abbreviated to L.
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 ).
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.
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.
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.
The NOOPTIONS option can be abbreviated to NOOPTN.
ONEPASS and TWOPASS are mutually exclusive options.
The default is ONEPASS.
The ONEPASS option can be abbreviated to ON.
The default is OPTIONS.
The OPTIONS option can be abbreviated to OPTN.
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) 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.
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(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.
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.
ONEPASS and TWOPASS are mutually exclusive options.
The default is ONEPASS.
The TWOPASS option can be abbreviated to TW.
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: