Descriptions of SQL processing options
You can specify any SQL processing options regardless of whether you use the Db2 precompiler or the Db2 coprocessor. However, the Db2 coprocessor might ignore certain options because host language compiler options exist that provide the same information.
The following table shows the options that you can specify when you use the Db2 precompiler or Db2 coprocessor. The table also includes abbreviations for those options and indicates which options are ignored for a particular host language or by the Db2 coprocessor. This table uses a vertical bar (|) to separate mutually exclusive options, and brackets ([ ]) to indicate that you can sometimes omit the enclosed option.
Option keyword | Meaning |
---|---|
APOST1 | Indicates
that the Db2 precompiler is
to use the apostrophe (') as the string delimiter in host language
statements that it generates. This option is not available in all languages. APOST and QUOTE are mutually exclusive options. The default is in the field STRING DELIMITER on Application Programming Defaults Panel 1 during installation. If STRING DELIMITER is the apostrophe ('), APOST is the default. |
APOSTSQL | Recognizes the apostrophe (') as the string delimiter
and the double quotation mark (") as the SQL escape character
within SQL statements. APOSTSQL and QUOTESQL are mutually exclusive options. The default is in the field SQL STRING DELIMITER on Application Programming Defaults Panel 1 during installation. If SQL STRING DELIMITER is the apostrophe ('), APOSTSQL is the default. |
ATTACH(TSO|CAF| RRSAF|ULI) | Specifies the attachment facility that the application uses to access Db2
TSO, CAF, RRSAF, or DSNULI applications that load the attachment facility can
use this option to specify the correct attachment facility, instead of coding a dummy DSNHLI entry
point.
You can specify ATTACH(ULI) only when you use the Db2 coprocessor. This option is not available for Fortran applications. The default is ATTACH(TSO). |
CCSID(n) | Specifies the numeric value n of the CCSID in which the source program is written. The number n must be an EBCDIC CCSID. The default setting is the EBCDIC system CCSID as specified on the panel DSNTIPF during installation. The Db2 coprocessor uses the following process to determine the CCSID of the source statements:
CCSID supersedes the GRAPHIC and NOGRAPHIC SQL processing options. If you specify CCSID(1026) or CCSID(1155), the Db2 coprocessor does not support the code point 'FC'X for the double quotation mark ("). |
COMMA | Recognizes the comma (,) as the decimal point indicator
in decimal or floating point literals in the following cases:
COMMA and PERIOD are mutually exclusive options. The default (COMMA or PERIOD) is chosen under DECIMAL POINT IS on Application Programming Defaults Panel 1 during installation. |
CONNECT(2|1)
CT(2|1) |
Determines whether to apply type 1 or type 2 CONNECT
statement rules.
|
DATE(ISO|USA |EUR|JIS|LOCAL) | Specifies that date output should always be returned
in a particular format, regardless of the format that is specified
as the location default. The default is specified in the field DATE FORMAT on Application Programming Defaults Panel 2 during installation. The default format is determined by the installation defaults of the system where the program is bound, not by the installation defaults of the system where the program is precompiled. You cannot use the LOCAL option unless you have a date exit routine. |
DEC(15|31)
DEC15 | DEC31 D15.s | D31.s |
Specifies the maximum precision for decimal arithmetic
operations. The default is in the field DECIMAL ARITHMETIC on Application Programming Defaults Panel 1 during installation. If the form Dpp.s is specified, pp must be either 15 or 31, and s, which represents the minimum scale to be used for division, must be a number between 1 and 9. |
DECP(name) | name represents
the 1 to 8 character name of the application defaults data-only load
module that is to be used. The default name DSNHDECP is used if this parameter is omitted. |
FLAG(I|W|E|S)1 | Suppresses diagnostic messages below the specified
severity level (Informational, Warning, Error, and Severe error for
severity codes 0, 4, 8, and 12 respectively). The default setting is FLAG(I). |
FLOAT(S390|IEEE) | Determines
whether the contents of floating-point host variables in assembler,
C, C++, or PL/I programs are in IEEE floating-point format or z/Architecture® hexadecimal
floating-point format. Db2 ignores
this option if the value of HOST is anything other than ASM, C, CPP,
or PLI. The default setting is FLOAT(S390). |
GRAPHIC | This option is no longer used for SQL statement processing. Use the CCSID option instead. 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 (GRAPHIC or NOGRAPHIC) is specified in the field MIXED DATA on Application Programming Defaults Panel 1 during installation. |
HOST1(ASM|C[(FOLD)]|
CPP[(FOLD)]| IBMCOB| PLI| FORTRAN| SQL| SQLPL) |
Defines the host language that contains the SQL
statements. Use IBMCOB for Enterprise COBOL for z/OS®. For C, specify:
For C++, specify:
For SQL procedural language, specify:
If you omit the HOST option, the Db2 precompiler issues a level-4 diagnostic message and uses the default value for this option. The default is in the field LANGUAGE DEFAULT on Application Programming Defaults Panel 1 during installation. This option also sets the language-dependent defaults. |
LEVEL[(aaaa)]
L |
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. For assembler, C, C++, Fortran, and PL/I, you can omit the suboption (aaaa). The resulting consistency token is blank. For COBOL, you need to specify the suboption. |
LINECOUNT1(n)
LC |
Defines the number of lines per page to be n for the Db2 precompiler listing. This includes header lines that are inserted by the Db2 precompiler. The default setting is LINECOUNT(60). |
MARGINS1(m,n[,c])
MAR |
Specifies what part of each source record contains
host language or SQL statements. For assembler, this option also specifies
where column continuations begin. The first option (m) is the beginning
column for statements. The second option (n) is the ending column
for statements. The third option (c) specifies where assembler continuations
begin. Otherwise, the Db2 precompiler
places a continuation indicator in the column immediately following
the ending column. Margin values can range 1–80. Default values depend on the HOST option that you specify. The DSNH CLIST and the DB2I panels do not support this option. In assembler, the margin option must agree with the ICTL instruction, if presented in the source. |
NEWFUN(Vn) |
Deprecated function: The NEWFUN processing option is deprecated. Use the SQLLEVEL option instead.
Indicates whether to accept the function syntax that is new for Db2 12.
The NEWFUN option applies only to the precompilation process by either the precompiler or the Db2 coprocessor, regardless of whether new functions are activated on the subsystem. You are responsible for ensuring that you bind the resulting DBRM on a subsystem in the correct migration mode. |
NOFOR | In static SQL, eliminates the need for the FOR
UPDATE or 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. Regardless of whether you use NOFOR, you can specify FOR UPDATE OF with a column list to restrict updates to only the columns that are named in the clause, and you can 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 | This option is no longer used for SQL statement processing. Use the CCSID option instead. 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 (GRAPHIC or NOGRAPHIC) is specified in the field MIXED DATA on Application Programming Defaults Panel 1 during installation. The NOGRAPHIC option applies to only EBCDIC data. |
NOOPTIONS
NOOPTN |
Suppresses the Db2 precompiler options listing. |
NOPADNTSTR | Indicates that output host variables that are NUL-terminated
strings are not padded with blanks. That is, additional blanks
are not inserted before the NUL-terminator is placed at the end of
the string. PADNTSTR and NOPADNTSTR are mutually exclusive options. The default (PADNTSTR or NOPADNTSTR) is specified in the field PAD NUL-TERMINATED on Application Programming Defaults Panel 2 during installation. This option applies to only C and C++ applications. |
NOSOURCE2
NOS |
Suppresses the Db2 precompiler source listing. This is the default. |
NOXREF | Suppresses the Db2 precompiler cross-reference listing. This is the default. |
ONEPASS
ON |
Processes in one pass, to avoid the additional
processing time for making two passes. Declarations must appear before
SQL references. Default values depend on the HOST option specified. ONEPASS and TWOPASS are mutually exclusive options. |
OPTIONS1
OPTN |
Lists Db2 precompiler options. This is the default. |
PADNTSTR | Indicates that output host variables that are NUL-terminated
strings are padded with blanks with the NUL-terminator placed at the
end of the string. PADNTSTR and NOPADNTSTR are mutually exclusive options. The default (PADNTSTR or NOPADNTSTR) is specified in the field PAD NUL-TERMINATED on Application Programming Defaults Panel 2 during installation. This option applies to only C and C++ applications. |
PERIOD | Recognizes the period (.) as the decimal point
indicator in decimal or floating point literals in the following cases:
COMMA and PERIOD are mutually exclusive options. The default (COMMA or PERIOD) is specified in the field DECIMAL POINT IS on Application Programming Defaults Panel 1 during installation. |
QUOTE1
Q |
Indicates
that the Db2 precompiler is
to use the quotation mark (") as the string delimiter in host
language statements that it generates. QUOTE is valid only for COBOL applications. QUOTE is not valid for either of the following combinations of precompiler options:
The default is specified in the field STRING DELIMITER on Application Programming Defaults Panel 1 during installation. If STRING DELIMITER is the double quotation mark (") or DEFAULT, QUOTE is the default. APOST and QUOTE are mutually exclusive options. |
QUOTESQL | Recognizes the double quotation mark (") as
the string delimiter and the apostrophe (') as the SQL escape character
within SQL statements. This option applies only to COBOL. The default is specified in the field SQL STRING DELIMITER on Application Programming Defaults Panel 1 during installation. If SQL STRING DELIMITER is the double quotation mark (") or DEFAULT, QUOTESQL is the default. APOSTSQL and QUOTESQL are mutually exclusive options. |
SOURCE1
S |
Lists Db2 precompiler source and diagnostics. |
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 that Db2 for z/OS using DRDA access. SQL(ALL) indicates that the SQL statements in the program are not necessarily for Db2 for 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 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. |
SQLLEVEL(V10R1|V11R1|function-level) | Indicates whether to accept the SQL syntax that is new in Db2 12 function levels.
The function level activated on the Db2 subsystem does not restrict the SQLLEVEL value. However, you must ensure that you bind the resulting DBRM with the correct application compatibility level on a Db2 subsystem with the correct function level activated. |
STDSQL(NO|YES)3 | Indicates to which rules the output statements
should conform. STDSQL(YES)3 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 specified in the field STD SQL LANGUAGE on Application Programming Defaults Panel 2 during installation. STDSQL(YES) automatically implies the NOFOR option. |
TIME(ISO|USA|EUR|JIS| LOCAL) | Specifies that time output always return in a particular
format, regardless of the format that is specified as the location
default. The default is specified in the field TIME FORMAT on Application Programming Defaults Panel 2 during installation. The default format is determined by the installation defaults of the system where the program is bound, not by the installation defaults of the system where the program is precompiled. You cannot use the LOCAL option unless you have a time exit routine. |
TWOPASS
TW |
Processes in two passes, so that declarations need
not precede references. Default values depend on the HOST option that
is specified. ONEPASS and TWOPASS are mutually exclusive options. For the Db2 coprocessor, you can specify the TWOPASS option for only PL/I applications. For C/C++ and COBOL applications, the Db2 coprocessor uses the ONEPASS option. |
VERSION(aaaa|AUTO) | Defines the version identifier of a package, program, and the resulting DBRM. A version identifier is an SQL identifier of up to 64 EBCDIC bytes. 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 package. If you do not specify a version at precompile time, 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 is used as the version identifier. The timestamp that is used is based on the store clock value. |
XREF5 | Includes a sorted cross-reference listing of symbols that are used in SQL statements in the listing output. |
Notes:
|