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 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.
|
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 |
- |
|
NOFOR |
- |
- |
|
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 |
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.
.-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.
The ATTACH option specifies the attachment facility that the application uses to access DB2, TSO, CAF, and RRSAF.
.-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).
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.
.-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.
The CONNECT option determines whether to apply type 1 or type 2 CONNECT statement rules.
.-2-. >>-CONNECT(-+---+-)-------------------------------------------->< '-1-'
ABBREVIATION: CT
The default is CONNECT(2).
For more information about this option, see DB2 SQL Reference.
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.
>>-+-----------------------+----------------------------------->< '-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.
The DEC option specifies the maximum precision for decimal arithmetic operations.
>>-+-------------------+--------------------------------------->< | .-15-. | '-DEC--(--+----+--)-' '-31-'
The default is in the field DECIMAL ARITHMETIC on the Application Programming Defaults Panel 1 when DB2 is installed.
The FLOAT option determines whether the contents of floating point host variables are in System/390 hexadecimal format or in IEEE format.
.-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).
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.
>>-+-----------+----------------------------------------------->< +-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.
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.
.-NOINCONLY-. >>-+-----------+----------------------------------------------->< '-INCONLY---'
When you specify the INCONLY option, the compiler does not produce the SQL options listing, because all other options are ignored under INCONLY.
The INCONLY option and the NOINCONLY option are mutually exclusive.
For compatibility, the default is NOINCONLY.
The LEVEL option defines the level of a module.
>>-+------------------+---------------------------------------->< '-LEVEL-+--------+-' '-(aaaa)-'
ABBREVIATION: L
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.
In static SQL, NOFOR eliminates the need for the FOR UPDATE of FOR UPDATE OF clause in DECLARE CURSOR statements.
>>-+-------+--------------------------------------------------->< '-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.
The SQL option indicates whether the source contains SQL statements other than those recognized by DB2 for z/OS.
.-DB2-. >>-SQL--(--+-----+--)------------------------------------------>< '-ALL-'
SQL(DB2) is recommended when the database server is 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).
The SQLFLAG option specifies the standard used to check the syntax of SQL statements.
>>-+-----------------------------------------------------+----->< '-SQLFLAG--(--+----------------------------------+--)-' +-STD-+--------------------------+-+ | '-(ssname-+------------+-)-' | | '-,qualifier-' | '-IBM------------------------------'
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.
The STDSQL option indicates to which rules the output statements should conform.
>>-+-----------------------+----------------------------------->< | .-NO--. | '-STDSQL--(--+-----+--)-' '-YES-'
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.
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.
>>-+-----------------------+----------------------------------->< '-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.
The VERSION option defines the version identifier of a package, program, and the resulting DBRM.
>>-+-------------------------+--------------------------------->< '-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:
The (NO)XREF option determines whether a cross-reference table of names that are used in SQL statements is included in the compiler listing.
.-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.