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.

Table 1. SQL processing options
Option keyword	Meaning
APOST¹	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)	Specifies the attachment facility that the application uses to access DB2 TSO, CAF, or RRSAF that load the attachment facility can use this option to specify the correct attachment facility, instead of coding a dummy DSNHLI entry point. 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: If the CCSID of the source program is specified by a compiler option, such as the COBOL CODEPAGE compiler option, the DB2 coprocessor uses that CCSID. If you also specify the CCSID suboption of the SQL compiler option that is different from the CCSID compiler option, a warning is returned, and the CCSID suboption value is not used. If the CCSID is not specified by a compiler option: If the CCSID suboption of the SQL compiler option is specified and contains a valid EBCDIC CCSID, that CCSID is used. If the CCSID suboption of the SQL compiler option is not specified, and the compiler supports an option for specifying the CCSID, such as the COBOL CODEPAGE compiler option, the default for the CCSID compiler option is used. If the CCSID suboption of the SQL compiler option is not specified, and the compiler does not support an option for specifying the CCSID, the default CCSID from DSNHDECP or a user-specified application defaults module is used. If the CCSID suboption of the SQL option is specified and contains an invalid CCSID, compilation terminates. 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: For static SQL statements in COBOL programs For dynamic SQL statements, when the value of installation parameter DYNRULS is NO and the package or plan that contains the SQL statements has DYNAMICRULES bind, define, or invoke behavior. 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. CONNECT(2) Default: Apply rules for the CONNECT (Type 2) statement CONNECT(1) Apply rules for the CONNECT (Type 1) statement If you do not specify the CONNECT option when you precompile a program, the rules of the CONNECT (Type 2) statement apply.
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)¹	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.
HOST¹(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: C if you do not want DB2 to fold lowercase letters in SBCS SQL ordinary identifiers to uppercase C(FOLD) if you want DB2 to fold lowercase letters in SBCS SQL ordinary identifiers to uppercase For C++, specify: CPP if you do not want DB2 to fold lowercase letters in SBCS SQL ordinary identifiers to uppercase CPP(FOLD) if you want DB2 to fold lowercase letters in SBCS SQL ordinary identifiers to uppercase For SQL procedural language, specify: SQL, to perform syntax checking and conversion to a generated C program for an external SQL procedure. SQLPL, to perform syntax checking for a native SQL procedure. 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.
LINECOUNT¹(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).
MARGINS¹(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 from 1 to 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(V`n`)	NEWFUN(YES) and NEWFUN(NO) options are deprecated. Use NEWFUN(V`n`) instead. Indicates whether to accept the function syntax that is new for DB2 10. NEWFUN(V8) Specifies that any syntax up to DB2 Version 8 will be allowed. NEWFUN(V9) Specifies that any syntax up to DB2 9 will be allowed. NEWFUN(V9) is equivalent to NEWFUN(NO) NEWFUN(V10) Specifies that any syntax up to DB2 10 will be allowed. NEWFUN(V10) is equivalent to NEWFUN(YES). The NEWFUN option applies only to the precompilation process by either the precompiler or the DB2 coprocessor, regardless of the current migration mode. 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.
NOSOURCE² 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.
OPTIONS¹ 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: For static SQL statements in COBOL programs For dynamic SQL statements, when the value of installation parameter DYNRULS is NO and the package or plan that contains the SQL statements has DYNAMICRULES bind, define, or invoke behavior. 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.
QUOTE¹ 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: CCSID(1026) and HOST(IBMCOB) CCSID(1155) and HOST(IBMCOB) 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.
SOURCE¹ 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.
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 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.
XREF⁵	Includes a sorted cross-reference listing of symbols that are used in SQL statements in the listing output.
Notes: The DB2 coprocessor ignores this option when the DB2 coprocessor is invoked by the compiler to prepare the application. This option is always in effect when the DB2 coprocessor is invoked by the compiler to prepare the application. You can use STDSQL(86) as in prior releases of DB2. The SQL statement processor treats it the same as STDSQL(YES). Precompiler options do not affect ODBC behavior. The DB2 coprocessor ignores this option when the DB2 coprocessor is invoked by the compiler to prepare the application. However, if you are using PL/I V4.1 or later, it is supported.