Figure 18-1: Build Settings Dialog Box
This chapter describes the DB2 External Compiler Module (ECM).
The DB2 External Compiler Module (MFHCOSQL.LBR) allows
programs checked with CHARSET(EBCDIC) directive to access
SQL tables using DB2 Universal Database. The DB2 ECM generates code to
convert all character data (columns defined as CHAR or
VARCHAR) from EBCDIC to ANSI before storing it in DB2
Universal Database tables and conversely converts it from ANSI to EBCDIC
on all retrievals from DB2 Universal Database tables.
This means that other products such as Microsoft Excel can still be used
to access data stored in the tables.
Warning!
The only time the DB2 ECM may have problems is where character
columns are defined as being FOR BIT DATA. The data stored
in the column is not necessarily ANSI character data to begin with. In
this case, the DB2 ECM still generates the code to convert the column.
Therefore, results can be unpredictable.
The DB2 ECM has the ability to handle DB2 syntax DDL statements within programs. DDL statements are converted automatically to DB2 Universal Database syntax. See the chapter Product Specifications for more details on which statements are supported and limitations.
If you are merging data from multiple sources using EBCDIC, you need access to SQL tables that are stored in databases created with the correct collating sequence. To get the data to come out in the correct sort sequence or to be included correctly when specified in WHERE clauses, all tables that are used with this pre-processor must be created in a database that specifies the EBCDIC collating sequence. See the chapter Create Database for more details on creating databases with EBCDIC collating sequences.
When you compile your programs, the DB2 ECM must be invoked to add the additional code needed to do the ANSI to EBCDIC translations as well as translate the EXEC SQL statements into database API calls.
Before you try to compile any SQL program, please make sure the following have been done:
Note: This is important since to compile a program, you must
first connect to the database. If the connect fails, your program will not
compile.
If you have done all of the above steps, and get a COBDB006 error "Required DB software: db2agapi could not be found", check the PATH statement that Mainframe Express generates from a Mainframe Express command prompt. The PATH gets modified by Mainframe Express and the directory where DB2 is installed may be beyond 255 bytes. Modify your PATH statement to insure that ..\SQLLIB\BIN directory is closer to the beginning of the PATH statement so that required database DLL's required to compile the program can be loaded.
When you create a new project, you must specify the database name for
Host Compatibility Option to use. The database-name you set here is used
for all SQL programs in the project whenever you compile a program. In
addition, when you add programs to the project, a scan is done to
determine if EXEC SQL statements are in the program. If
they are, the DB2 default directives are automatically set for that
program. You can set additional DB2 directives by selecting Build
Settings for that program (see Figure 18-1) and selecting the Preproc
tab and then clicking on the More button (see Figure 18-2).
Figure 18-1: Build Settings Dialog Box
Once all directives have been set, click Close to save the DB2 directives and then click OK in the Build Settings dialog box to save all of the directives. You can then select Compile to compile a program.
Figure 18-2: EXEC SQL Options
To compile a program that uses a remote DB2 server, you must first connect to that remote server. The DB2 ECM first attempts to connect to the database using the default values for the client workstation you logged on with. If the logon fails, the DB2 ECM will invoke the Micro Focus SQL Logon dialog box, from which you can specify a logon ID and password for the database you are trying to compile your program against. The dialog box is shown in Figure 18-3.
Figure 18-3: The Micro Focus SQL Dialog Box
There is an option to save your logon ID and password in memory so that
you do not need to be prompted the next time you try to compile a program
using the same database. This information is lost the next time you reboot
your client machine or if you type the following command from a Mainframe
Express command prompt: MFDAEMON CLOSE.
Having the graphical logon dialog box appear might not be acceptable for
automating compilations from a background process such as a command file.
There is a way to supply the logon information by setting an environment
variable and pointing the variable at a text file that contains the logon
ID and password. To do this, set the environment variable SQLPASS.TXT to
the name of the text file that contains the logon ID and password. For
example: SET SQLPASS.TXT=D:\BATCH.TXT. Then in the file d:\batch.txt,
specify the logon ID and password in the format id.password.
For example: MyId.Mypassword.
If the security system used to validate your logon ID and password is case sensitive, you need to specify the logon ID and password in the correct case in this text file.
Note: Specifying the logon and password in a text file does raise security concerns, so you should only use this facility if you are sure it is safe to do so.
DB2 is the Compiler directive that is used to invoke the DB2 ECM. As mentioned earlier some default values are automatically set for you if an EXEC SQL statement is detected in your program.
All of the DB2 options are passed straight to DB2 at compilation time and the DB2 option default is used when no other value is specified. This is done by using NO to prefix the option. For example:
NOEXPLAIN would be specified to use the default value for the EXPLAIN option
In these cases, however, the suitability of the default values for these options is dependent on the DB2 configuration of the server that the program is connected to. Because of this, the default Compiler setting of these options is then determined by DB2 itself and may be different than expected. Consult your IBM DB2 reference documentation for further details.
This section describes the DB2 Compiler directive options that can be
used with all DB2 servers unless noted. These options are:
Specifies the name of the package to be created and stored in the database.
NOACCESS ACCESS=package-name
package-name |
The name of the package to be created and stored in the database |
| NOACCESS | No package is created or stored in the database |
| Default: | ACCESS |
| Synonym: | PACKAGE |
If ACCESS is specified without a parameter, the package name defaults to the program name (without any extension).
Indicates whether the package can be added or replaced.
NOACTION
ACTION={ADD | REPLACE }
ADD |
Indicates that the named package does not exist, and that a new package is to be created. If the package already exists, execution stops, and a diagnostic error message is returned. |
REPLACE |
Indicates that the old package is to be replaced by a new one with the same location, collection, and package name. |
| NOACTION | Use the DB2 default setting |
| Default: | NOACTION |
This DRDA precompile/bind option is not supported by DB2.
Specifies the name of the bind file to be created.
NOBIND BIND=bindfile
bindfile |
The name of the bind file to be created |
| NOBIND | No bind file is created |
| Default: | NOBIND |
| Synonym: | BINDFILE |
When BIND is specified without a parameter, the bind file defaults to the program name with the filename extension replaced by .bnd.
Specifies the record blocking mode to be used on package creation.
BLOCK={UNAMBIG | ALL | NO}
UNAMBIG |
Specifies blocking for read-only cursors or cursors not specified as FOR UPDATE OF. Ambiguous cursors are treated as updateable. |
ALL |
Specifies blocking for read-only cursors or cursors not specified as FOR UPDATE OF. Ambiguous cursors are treated as read-only. |
NO |
Specifies no blocking of any cursors. Ambiguous cursors are treated as updateable. |
| Default: | UNAMBIG |
| Synonym: | BLOCKING |
For information about row blocking, see the IBM DB2 Administration Guide or the Application Programming Guide.
An integer specifying the coded character set identifier (CCSID) to be used for double byte characters in character column definitions (without a specific CCSID clause) in CREATE and ALTER TABLE SQL statements.
NOCCSIDG CCSIDG=double-ccsid
double-ccsid |
Specifies the default CCSID to use for double byte characters in character column definitions. |
| NOCCSIDG | Use the DB2 default setting |
| Default: | NOCCSIDG |
This DRDA precompile/bind option is not supported by DB2. The DRDA server uses a system-defined default value if this option is not specified.
An integer specifying the coded character set identifier (CCSID) to be used for mixed byte characters in character column definitions (without a specific CCSID clause) in CREATE and ALTER TABLE SQL statements.
NOCCSIDM CCSIDM=mixed-ccsid
mixed-ccsid |
Specifies the default CCSID to use for mixed byte characters in character column definitions. |
| NOCCSIDM | Use the DB2 default setting |
| Default: | NOCCSIDM |
This DRDA precompile/bind option is not supported by DB2. The DRDA server uses a system-defined default value if this option is not specified.
An integer specifying the coded character set identifier (CCSID) to be used for single byte characters in character column definitions (without a specific CCSID clause) in CREATE and ALTER TABLE SQL statements.
NOCCSIDS CCSIDS=sbcs-ccsid
sbcs-ccsid |
Specifies the default CCSID to use for single byte characters in character column definitions. |
| NOCCSIDS | Use the DB2 default setting |
| Default: | NOCCSIDS |
This DRDA precompile/bind option is not supported by DB2. The DRDA server uses a system-defined default value if this option is not specified.
Designates the default character sub-type that is to be used for column definitions in CREATE and ALTER TABLE SQL statements.
NOCHARSUB
CHARSUB={DEFAULT | BIT | SBCS | MIXED}
BIT |
Use the FOR BIT DATA SQL character sub-type in all new character columns for which an explicit sub-type is not specified. |
DEFAULT |
Use the target system defined default in all new character columns for which an explicit sub-type is not specified. |
MIXED |
Use the FOR MIXED DATA SQL character sub-type in all new character columns for which an explicit sub-type is not specified. |
SBCS |
Use the FOR SBCS DATA SQL character sub-type in all new character columns for which an explicit sub-type is not specified. |
| NOCHARSUB | Use the DB2 default setting |
| Default: | NOCHARSUB |
This DRDA precompile/bind option is not supported by DB2.
Specifies an 30-character collection identifier for the package.
NOCOLLECTION COLLECTION=schema-name
schema-name |
An 30-character collection identifier for the package |
| NOCOLLECTION | Use the DB2 default setting |
| Default: | NOCOLLECTION |
If this is not specified, the authorization identifier for the user processing the package is used.
Specifies where implicit COMMIT statements should be generated.
COMMIT={1 | 2 | 3 | 4}
1 |
No COMMIT statements implicitly generated |
2 |
COMMIT statements are implicitly generated on STOP RUN statements and at the end of the program |
3 |
COMMIT statements are implicitly generated on STOP RUN and EXIT PROGRAM statements and at the end of the program |
4 |
COMMIT statements are implicitly generated after every SQL statement |
| Default: | 2 |
Specifies the ANSI character code to use for the CONCAT symbol (|).
CONCAT=ansi-character-code
ansi-character-code |
The ANSI character code to be mapped to the standard CONCAT symbol |
| Default: | 33 |
DB2 uses the symbol (|) as the concatenation character. If your source contains a different character it can be mapped to the correct symbol.
Specifies that a CONNECT statement is to be processed as either a type 1 CONNECT or a type 2 CONNECT.
NOCONNECT
CONNECT={1 | 2}
1 |
CONNECT statement is to be processed as a type 1 CONNECT |
2 |
CONNECT statement is to be processed as a type 2 CONNECT |
| NOCONNECT | Use the DB2 default setting |
| Default: | NOCONNECT |
Allows host variables to be defined in COBOL COPY statements.
NOCOPY COPY
None
| Default: | NOCOPY |
Previous releases of Mainframe Express could not determine whether variables were defined in EXEC SQL INCLUDE statements or COBOL COPY statements. Mainframe Express can now make this distinction, so variables might be flagged as not being defined when they are specified in a COBOL COPY statement. If your organization has a way of expanding COBOL COPY statements before passing them to the DB2 preprocessor, you should set this directive.
Creates a trace file for submission to technical support if requested.
NOCTRACE CTRACE
None
| Default: | NOCTRACE |
The filename of the file that is created is sqltrace.txt.
Specifies the name of the database that the program accesses.
DB=database-name
database-name |
The name of the database that the program accesses |
| Default: | DB |
If DB is specified without a parameter, the database specified in the environment variable DB2DBDFT is used. You do not need to set this directive under Mainframe Express since Host Compatibility Option will get the database name from the project.
Specifies the maximum precision to be used in decimal arithmetic operations.
NODEC
DEC={31 | 15}
15 |
Use to specify that 15-digit precision is to be used in decimal arithmetic operations. |
31 |
Use to specify that 31-digit precision is to be used in decimal arithmetic operations. |
| NODEC | Use the DB2 default setting |
| Default: | NODEC |
This DRDA precompile/bind option is not supported by DB2. The DRDA server uses a system-defined default value if this option is not specified.
Designates whether a period (.) or a comma (,) is to be used as the decimal point indicator in decimal and floating point literals.
NODECDEL
DECDEL={PERIOD | COMMA}
COMMA |
Use a comma (,) as the decimal point indicator. |
PERIOD |
Use a period (.) as the decimal point indicator. |
| NODECDEL | Use the DB2 default setting |
| Default: | NODECDEL |
This DRDA precompile/bind option is not supported by DB2. The DRDA server uses a system-defined default value if this option is not specified.
Provides a performance enhancement by combining SQL PREPARE statement flow with the associated OPEN, DESCRIBE, or EXECUTE statement flow to minimize network traffic.
NODEFERRED_PREPARE
DEFERRED_PREPARE={NO | YES}
| NO | The PREPARE statement will be executed at the time it is issued. |
| YES | The PREPARE statement will be deferred until the corresponding OPEN, DESCRIBE or EXECUTE statement is issued. |
| Default: | NODEFERRED_PREPARE |
The PREPARE statement will not be deferred if it uses the INTO clause, which requires an SQLDA to be returned.
Specifies whether or not the query is to be executed using I/O parallel processing.
NODEGREE
DEGREE={1 | degree-of-I/O-parallelism | ANY}
1 |
Prohibits parallel I/O operations |
degree-of-I/O-parallelism |
Specifies the degree of parallel I/O operations, a value between 2 and 32767 (inclusive) |
ANY |
Allows parallel I/O operations. |
| NODEGREE | Use DB2 default settings |
| Default: | NODEGREE |
Specifies which database connections are to be disconnected at commit.
NODISCONNECT
DISCONNECT={EXPLICIT | CONDITIONAL | AUTOMATIC}
AUTOMATIC |
Specifies that all database connections are to be disconnected at commit. |
CONDITIONAL |
Specifies that the database connections that have been marked RELEASE or have no open WITH HOLD cursors are to be disconnected at commit. |
EXPLICIT |
Specifies that only database connections that have been explicitly marked for release by the RELEASE statement are to be disconnected at commit. |
| NODISCONNECT | Use the DB2 default setting |
| Default: | NODISCONNECT |
Specifies which authorization identifier to use when dynamic SQL in a package is executed.
NODYNAMICRULES
DYNAMICRULES={BIND | RUN | DEFINE | INVOKE}
BIND |
Indicates that the authorization identifier used for the execution of dynamic SQL is the package owner |
RUN |
Indicates that the authorization identifier used for the execution of dynamic SQL is the authid of the person executing the package |
DEFINE |
Indicates that the authorization identifier used for the execution of dynamic SQL is the definer of the UDF or stored procedure. This option is not supported by DB2. |
INVOKE |
Indicates that the authorization identifier used for the execution of dynamic SQL is the invoker of the UDB or stored procedure. This option is not supported by DB2. |
| NODYNAMICRULES | Use the DB2 default setting |
| Default: | NODYNAMICRULES |
Not all options are supported by DB2.
Stores information in the Explain tables about the access plans chosen for each SQL statement in the package.
NOEXPLAIN
EXPLAIN={NO | YES | ALL}
NO |
Explain information will not be captured. |
YES |
Explain tables will be populated with information about the chosen access plan. |
ALL |
Explain information for each eligible static SQL statement will be placed in the Explain tables. In addition, Explain information will be gathered for eligible dynamic SQL statements at run time, even if the CURRENT EXPLAIN SNAPSHOT register is set to NO. For more information about special registers, see the IBM DB2 SQL Reference. |
| NOEXPLAIN | Use the DB2 default setting |
| Default: | NOEXPLAIN |
DRDA does not support the ALL value for this option.
Stores Explain Snapshot information in the Explain tables.
NOEXPLSNAP
EXPLSNAP={NO | YES | ALL}
NO |
An Explain Snapshot will not be captured. |
YES |
An Explain Snapshot for each eligible static SQL statement will be placed in the Explain tables. |
ALL |
An Explain Snapshot for each eligible static SQL statement will be placed in the Explain tables. In addition, Explain Snapshot information will be gathered for eligible dynamic SQL statements at run time, even if the CURRENT EXPLAIN SNAPSHOT register is set to NO. For more information about special registers, see the IBM DB2 SQL Reference. |
| NOEXPLSNAP | Use the DB2 default setting |
| Default: | NOEXPLSNAP |
This DB2 precompile/bind option is not supported by DRDA.
Specifies whether a static SQL statement in the package references a nickname or federated view.
NOFEDERATED
FEDERATED={NO | YES}
| NO | The package does not reference a federated view. |
| YES | The package will access a DB2 federated system. |
| Default: | NOFEDERATED |
If you specify this directive with YES and don't reference a federated view or if you specify NO and do reference a federated view, an error is returned and the package will not be created.
Specifing NOFOR causes the DB2 ECM to modify any updateable cursor to add a FOR UPDATE clause. This allows compatabiltiy with the DB2 for OS/390 precompiler directive NOFOR.
NOFOR FOR
None.
| Default: | FOR |
If you don't want a cursor to be updateable when using the NOFOR directive, specify FOR READ ONLY in the cursor definition.
Specifies the date and time format when date/time fields are assigned to string representations in host variables.
FORMAT={DEF | USA | EUR | ISO | JIS | LOC}
DEF |
Format associated with the country code of the database. |
EUR |
European date and time format. |
ISO |
International Standards Organization. |
JIS |
Japanese Industrial Standard. |
LOC |
Local form associated with the country code of the database. |
USA |
U.S. date and time format. |
| Default: | LOC |
| Synonym: | DATETIME |
See IBM DB2 SQL Reference for examples of various formats.
Specifies the function path to be used in resolving user-defined distinct types and functions in static SQL.
NOFUNCPATH FUNCPATH=schema-name
schema-name |
A short SQL identifier, either ordinary or delimited, which identifies a schema that exists at the application server. No validation that the schema exists is made at precompile or at bind time. The same schema cannot appear more than once in the function path. The number of schemas that can be specified is limited by the length of the resulting function path, which cannot exceed 254 bytes. The schema SYSIBM does not need to be explicitly specified; it is implicitly assumed to be the first schema if it is not included in the function path. For more information, see the IBM DB2 Command Reference. |
| NOFUNCPATH | Use the DB2 default setting |
| Default: | NOFUNCPATH |
If NOFUNCPATH is specified, the default function path for DB2 Universal Database is:
"SYSIBM","SYS FUN",USER
where USER is the value of the USER special register. This DB2 precompile/bind option is not supported by DRDA servers.
Provides a means of passing new bind options to a target DRDA server.
NOGENERIC GENERIC="option1 value1 option2 value2..."
option & value |
Each option and value must be separated by one or more spaces and the whole string enclosed in double quotes. |
| Default: | NOGENERIC |
This DRDA option is not supported by DB2. For example:
db2(generic="keepdynamic yes")
Allows a program being precompiled or bound from a DB2 V2.1 client to a DATABASE 2 Parallel Edition server to request that data inserts be buffered to increase performance.
NOINSERT
INSERT={DEF | BUF}
BUF |
Specifies that inserts from an application should be buffered. |
DEF |
Specifies that inserts from an application should not be buffered. |
NOINSERT |
Use the DB2 default setting |
| Default: | NOINSERT |
Determines how far a program bound to this package can be isolated from the effect of other executing programs.
ISOLATION={CS | RR | UR | RS | NC}
CS |
Specifies Cursor Stability as the isolation level. |
NC |
(No Commit) specifies that commitment control is not to be used. This isolation level is not supported by DB2. |
RR |
Specifies Repeatable Read as the isolation level. |
RS |
Specifies Read Stability as the isolation level. Read Stability ensures that the execution of SQL statements in the package is isolated from other application processes for rows read and changed by the application. |
UR |
Specifies Uncommitted Read as the isolation level. |
| Default: | CS |
For more information about isolation levels, see the IBM DB2 SQL Reference.
For more information about this option, see the IBM DB2 Application Programming Guide.
LANGLEVEL={SAA1 | NONE | MIA | SQL92E}
MIA |
The FOR UPDATE clause is optional for positioned updates. C null-terminated strings are padded with blank characters, and always include the null-terminating character. This option is not supported by DB2 Connect. |
SAA1 |
Requires the FOR UPDATE clause for all columns that are updated in a positioned update. C null-terminated strings are not padded with blank characters, and do not include a null-terminating character if truncation occurs. |
NONE |
Synonym for SAA1. |
SQL92E |
Similiar to SAA1 except for how column functions are resolved using an index. See IBM manual for specific details. |
| Default: | SAA1 |
| Synonym: | STDLVL |
Defines the level of a module using the consistency token.
NOLEVEL LEVEL=consistency-token
consistency-token |
An alphanumeric value up to 8 characters in length |
| NOLEVEL | Use the DB2 default setting |
| Default: | NOLEVEL |
The consistency token is any alphanumeric value up to eight characters in length. The RDB package consistency token verifies that the requester's application and the relational database package are synchronized. This DRDA precompile option is not supported by DB2.
Note: This option is not recommended for general use.
Specifies the name of an alphanumeric data item to return SQL error messages to.
MSGAREA={data-item-name}
data-item-name |
The name of an alphanumeric data item |
| Default: | MFSQLMESSAGETEXT |
If this item is present in the program source it automatically contains a description of a DB2 error condition (when SQLCODE is non zero).
Specifies the ANSI character code to use for NOT character (¬).
NOT=ansi-character-code
ansi-character-code |
The ANSI character value to be mapped to the ¬ symbol. |
| Default: | 172 |
For compatibility with DB2 on the mainframe this COBOL system allows the use of the NOT symbol in SQL statements. If your source uses a different ANSI code for the NOT symbol, this directive allows you to specify the ANSI value to be mapped for the NOT symbol.
Designates an 30-character authorization identifier for the package owner.
NOOWNER OWNER=authorization-id
authorization-id |
An 30-character authorization identifier for the package owner |
| NOOWNER | Use the DB2 default setting |
| Default: | NOOWNER |
| Synonym: | SCHEMA |
The owner must have the privileges required to execute the SQL statements in the package. The default is the primary authorization ID of the precompile/bind process if this option is not specified.
Causes the DB2 ECM to append three characters to the name of the host variables when declaring them to DB2.
NOQUALFIX QUALFIX
None.
| Default: | QUALFIX |
This ensures that problems caused by qualification (where two or more host variables have identical names when not qualified) are avoided but has the side-effect that:
Provides an 30-character implicit qualifier for unqualified table names, views, indexes and aliases contained in the package.
NOQUALIFIER QUALIFIER=qualifier-name
qualifier-name |
An 30-character implicit qualifier for unqualified table names, views, indexes and aliases contained in the package |
| NOQUALIFIER | Use the DB2 default setting |
| Default: | NOQUALIFIER |
| Synonym: | CATALOG |
The default is the owner's authorization ID, whether or not the owner is explicitly specified. This precompile/bind option is supported by DB2 UDB V5.2 or later. If an earlier version of DB2 is used, this option is only supported by DRDA. However Host Compatibility Option uses this option to qualify table names for local tables. This is equivalent in previous versions of Host Compatibility Option to the pre-processor directive: TBLQUAL. See Figure 18-1 for an example of how to set it.
Indicates the desired level of optimization for all static SQL statements contained in the package.
NOQUERYOPT QUERYOPT=optimization-level
optimization-level |
The desired level of optimization for all static SQL statements contained in the package |
| NOQUERYOPT | Use the DB2 default setting |
| Default: | NOQUERYOPT |
The default value is 5. For the complete range of optimization levels available, see the SET CURRENT QUERY OPTIMIZATION statement in the SQL Reference. This DB2 precompile/bind option is not supported by DRDA.
Indicates whether resources are released at each COMMIT point, or when the application terminates.
NORELEASE
RELEASE={COMMIT | DEALLOCATE}
COMMIT |
Release resources at each COMMIT point. Used for dynamic SQL statements. |
DEALLOCATE |
Release resources only when the application terminates |
| NORELEASE | Use the DB2 default setting |
| Default: | NORELEASE |
This DRDA precompile/bind option is not supported by DB2.
Replaces a specific version of a package.
NOREPLVER REPLVER=version-id
version-id |
Specifies which version of the package you want to replace |
| NOREPLVER | Use the DB2 default setting |
| Default: | NOREPLVER |
The version identifier specifies which version of the package to replace. Maximum length is 254 characters.
Indicates whether EXECUTE authorities are to be preserved when a package is replaced.
NORETAIN
RETAIN={YES | NO}
NO |
Do not preserve EXECUTE authorities when a package is replaced |
YES |
Preserve EXECUTE authorities when a package is replaced |
| NORETAIN | Use the DB2 default setting |
| Default: | NORETAIN |
If ownership of the package changes, the new owner grants the BIND and EXECUTE authority to the previous package owner.
Indicates whether to create a package or a bind file if an error is encountered.
NOSQLERROR
SQLERROR={NOPACKAGE | CHECK | CONTINUE}
CHECK |
Specifies that the target system performs all syntax and semantic checks on the SQL statements being bound. A package is not created as part of this process. If, while creating a package, an existing package with the same name and version is encountered, the existing package is neither dropped nor replaced if action replace was specified. |
CONTINUE |
A package or a bind file is created even when SQL errors are encountered. This option is not supported by DB2. |
NOPACKAGE |
A package or a bind file is not created if an error is encountered. |
| NOSQLERROR | Use the DB2 default setting |
| Default: | NOSQLERROR |
| Synonym: | ERROR |
If you specify both the SQLERROR directive and the SYNTAX directive, the SYNTAX directive takes precedence. SYNTAX is equivalent to SQLERROR=CHECK.
Identifies and reports on deviations from the SQL language syntax specified.
NOSQLFLAG
SQLFLAG={MVSDB2V23 | MVSDB2V31 | MVSDB2V41 | SQL92E}-SYNTAX
MVSDB2V23-SYNTAX |
The SQL statements are checked against MVS DB2 Version 2.3 SQL language syntax. Any deviation from the syntax is reported in the compiler listing. |
MVSDB2V31-SYNTAX |
The SQL statements are checked against MVS DB2 Version 3.1 SQL language syntax. Any deviation from the syntax is reported in the compiler listing. |
MVSDB2V41-SYNTAX |
The SQL statements are checked against MVS DB2 Version 4.1 SQL language syntax. Any deviation from the syntax is reported in the compiler listing. |
SQL92E-SYNTAX |
The SQL statements are checked against ANSI or ISO SQL92 SQL language syntax. Any deviation from the syntax is reported in the compiler listing. |
| NOSQLFLAG | Use the DB2 default setting |
| Default: | NOSQLFLAG |
| Synonym: | FLAG |
A bind file or a package is created only if the BIND or the ACCESS directive is specified in addition to the SQLFLAG directive.
Local syntax checking is performed only if one of the following options is specified: BIND, ACCESS, SQLERROR CHECK, SYNTAX.
If SQLFLAG is not specified, the flagger function is not invoked, and the bind file or the package is not affected.
Specifies whether type 2 CONNECTs are to be processed according to the DB2 rules or the Standard (STD) rules based on ISO/ANS SQL92.
NOSQLRULES
SQLRULES={DB2 | STD}
DB2 |
Allow the use of the SQL CONNECT statement to switch the current connection to another established (dormant) connection. |
STD |
Allow the use of the SQL CONNECT statement to establish a new connection only. The SQL SET CONNECTION statement must be used to switch to a dormant connection. |
| NOSQLRULES | Use the DB2 default setting |
| Default: | NOSQLRULES |
| Synonym: | RULES |
Indicates whether warnings will be returned from the compilation of dynamic SQL statements (via PREPARE or EXECUTE IMMEDIATE), or from describe processing (via PREPARE...INTO or DESCRIBE).
NOSQLWARN
SQLWARN={YES | NO}
NO |
Warnings will not be returned from the SQL compiler. |
YES |
Warnings will be returned from the SQL compiler. |
| NOSQLWARN | Use the DB2 default setting |
| Default: | NOSQLWARN |
| Synonym: | WARN |
Note: SQLCODE +238 is an exception. It is returned regardless of the SQLWARN option value.
This DB2 precompile/bind option is not supported by DRDA.
Designates whether an apostrophe (') or double quotation marks (") is used as the string delimiter within SQL statements.
NOSTRDEL
STRDEL={APOSTROPHE | QUOTE}
APOSTROPHE |
Use an apostrophe (') as the string delimiter. |
QUOTE |
Use double quotation marks (") as the string delimiter. |
| NOSTRDEL | Use the DB2 default setting |
| Default: | NOSTRDEL |
This DRDA precompile/bind option is not supported by DB2. The DRDA server uses a system-defined default value if this option is not specified.
Specifies how commits or rollbacks are to be coordinated among multiple database connections.
NOSYNCPOINT
SYNCPOINT={ONEPHASE | TWOPHASE | NONE}
NONE |
Specifies that no Transaction Manager (TM) is to be used to perform a two-phase commit, and does not enforce single updater, multiple reader. A COMMIT is sent to each participating database. The application is responsible for recovery if any of the commits fail. |
ONEPHASE |
Specifies that no TM is to be used to perform a two-phase commit. A one-phase commit is to be used to commit the work done by each database in multiple database transactions. |
TWOPHASE |
Specifies that the TM is required to coordinate two-phase commits among those databases that support this protocol. |
| NOSYNCPOINT | Use the DB2 default setting |
| Default: | NOSYNCPOINT |
A synonym for directive SQLERROR=CHECK.
The description of a package.
NOTEXT TEXT=label
label |
An alphanumeric string of up to 255 characters. |
| NOTEXT | Use the DB2 default setting |
| Default: | NOTEXT |
Maximum length is 255 characters. The default value is blanks. This DRDA precompile/bind option is not supported by DB2.
Determines when the database manager checks for authorization errors and object not found errors.
NOVALIDATE
VALIDATE={RUN | BIND}
BIND |
Validation is performed at precompile/bind time. If all objects do not exist, or all authority is not held, error messages are produced. If SQLERROR=CONTINUE is specified, a package/bind file is produced despite the error message, but the statements in error are not executable. |
RUN |
Validation is attempted at bind time. If all objects
exist, and all authority is held, no further checking is performed at
execution time.
If all objects do not exist, or all authority is not held at precompile/bind time, warning messages are produced and the package is successfully bound, regardless of the SQLERROR=CONTINUE option setting. However, authority checking and existence checking for SQL statements that failed these checks during the precompile/bind process may be redone at execution time. |
| NOVALIDATE | Use the DB2 default setting |
| Default: | NOVALIDATE |
The package owner authorization ID is used for validity checking. You must set your message level to "WARNING" to see messages if VALIDATE is set to RUN.
Defines the version identifier for a package.
NOVERSION VERSION=version-id
version-id |
An alphanumeric value, $, #, @, _, -, or ., up to 254 characters in length. |
| NOVERSION | Use the DB2 default setting |
| Default: | NOVERSION |
The version identifier is a string of up to 254 characters where each character may be either an alphanumeric value, "$", "#", "@", " _", "-", or ".". This DRDA precompile option is not supported by DB2.
There are two significant differences when using DB2 Universal Database versus mainframe DB2. The first is that you have to connect to the database before you can compile a program. With mainframe DB2 you can pre-compile a program without DB2 being active. The second major difference is that all columns and tables referenced in the program must be created in the database before a program can be compiled whereas with mainframe DB2 they don't have to be. In both cases, the mainframe DB2 pre-compiler uses the DECLARE TABLE information in an SQL INCLUDE copybook to determine valid syntax as opposed to DB2 Universal Database which uses DB2 system catalog information to validate statements. In either case, a SQL Error could result using DB2 Universal Database.
In addition, if invalid SQL syntax is encountered while compiling your program, a SQL error is also generated for each statement in error. The text that is associated with the SQL error is displayed using the following format:
COBDB0100e SQLnnnnnn error-text
Where COBDB indicates error resulted from DB2 ECM (the
error number is always 0100); e is the error
severity, and SQLnnnnnn is the SQL
error code (nnnnnn may vary between 2 and 6
characters).
For example, if you had not issued a DB2START or NET START DB2 before trying to compile a program, you would get the following error message (see Figure:18-4):
Figure 18-4: SQL Error Message
The DB2 ECM also does some syntax checking before passing a SQL statement to precompiler services. The error message numbers are always in the format: COBDBnnnnl where l is the severity level of the error.
COBDB0002 <token>s is badly defined.
One of the following occurred:
Correct the definition or use the SQLCA copybook provided with Mainframe Express. If problem persists, report this error to your support representative. Trace files may be need to resolve this problem. See the section Creating Debug Files for details of how to create additional trace files that may be requested by your support representative.
COBDB0003 Program contains more than one definition of <token>
More than one definition exists of one or more of the following fields: SQLCA, SQLCODE, SQLSTATE or MFSQLMESSAGETEXT.
Remove the duplicate definitions and recompile the program.
COBDB0004 Nested or multi-source programs not allowed.
You are trying to compile a program that is nested or multi-source.
Use Micro Focus Net Express if you want to develop applications using nested or multi-source programs.
COBDB0005 An error occurred whilst processing the internal dictionary.
A system error has occurred trying to read an internal dictionary that the DB2 ECM creates of all host variables used in the program.
Report this error to your support representative. Trace files may be need to resolve this problem. See the section Creating Debug Files for details of how to create additional trace files that may be requested by your support representative.
COBDB0006 Required DB software <token> could be found.
The required IBM DB2 system software was not installed.
Install one of the required DB2 system software products and then either create or catalog DB2 database and tables to compile the program against. If the token is "db2agapi" then either the DB2 software was not installed or the PATH statement may need to be modified so that the DB2 installation directory is found. If the token is "db2aprep", make sure that the DB2 Application Development Client software is installed.
COBDB0007 Source file name is invalid.
A system error has occurred. The source file name passed to DB2 ECM is not valid.
Report this error to your support representative. Trace files may be need to resolve this problem. See the section Creating Debug Files for details of how to create additional trace files that may be requested by your support representative.
COBDB0008 MF DB2 Connect program missing or invalid.
To compile your program, the DB2 ECM must connect to a DB2 database. The DB2 ECM uses the Micro Focus DB2 Connect program to do this. The program was not found, was damaged or an older version of the program was used.
Uninstall any older versions of Mainframe Express or Workbench if they are installed and then re-install Mainframe Express.
COBDB0009 MF DB2 Connect program reported error condition <token>.
To compile your program, the DB2 ECM must connect to a DB2 database. The DB2 ECM uses the Micro Focus DB2 Connect program to do this. An unexpected SQL error was reported attempting to do the connect.
The <token> provides additional information about the error. You can use the IBM DB2 Error Reference manual to get additional information as to what is causing the connection error.
COBDB0010 DB2 rejected an unspecified option.
A sqlcode of -4905 was reported processing the DB2 directives specified.
Verify the directives specified are valid for the DB2 server that you are trying to compile the program against. Make any corrections necessary and then recompile the program.
COBDB0011 No database name was specified.
You either didn't specify a database name in the Mainframe Express project or did not set environment variable DB2DBDFT.
Select Project Settings from Project menu and select SQL tab. Fill in the database name and then re-compile your program.
COBDB0021 HCO must be selected and available.
You have installed both Host Compatibility Option and SQL Option, and SQL Option is the current selection.
Go to Start, Mainframe Express, Configuration, Database menu and select IBM.
COBDB0101 Statement is too long.
The SQL statement passed to the DB2 ECM is greater than 65536 characters long. The DB2 ECM gets passed a compressed version of the SQL statement rather than the original source which may have comments included in the source as well as leading and trailing blanks..
Look at breaking the SQL statement into multiple SQL statements and then try recompiling the program.
COBDB0102 Statement contains too many host variables.
The SQL statement contains more than 8000 host and indicator variables.
Look at breaking the SQL statement into multiple SQL statements and then try recompiling the program.
COBDB0103 <token> does not have a suitable definition to be used as a host variable.
The host variable data definition does not match the column type associated with it in the SQL statement. For example, you are trying to use a host variable defined as PIC X(n) with a column defined as SMALLINT.
Verify that the host variable corresponds to the column definition and make any changes to correct the error. Alternatviely use the Host Compatibility Option DCLGEN tool to generate a copybook of the host variable for the SQL table in question.
COBDB0104 Bad SQL data declaration.
An unexpected SQL declaration is passed to the DB2 ECM. Generally this error occurs only for host variables defined as:
<host-variable-name> SQL TYPE IS ....
and the DB2 ECM has not been set up to handle that definition.
Report this error to your support representative. Trace files may be needed to resolve this problem. See the section Creating Debug Files for details of how to create additional trace files that may be requested by your support representative.
COBDB0105 Syntax error.
The SQL statement includes invalid syntax or does not include required information. For example, this error would be generated if you only coded:
EXEC SQL INCLUDE END-EXEC
and did not specify the name of the copybook file to INCLUDE.
Correct the SQL statement and recompile the program.
COBDB0106 Filename too long.
A filename used in the program to specify a copybook or INCLUDE file is more than 260 characters long.
Correct the filename and recompile the program
COBDB0107 File <token> not found.
The copybook specified in <token> was not found.
The DB2 ECM looks first in the current directory, and then down the paths specified by environment variable SQLCPY and COBCPY. Under Mainframe Express the COBCPY environment is defined by going into the Dependency Library and Copybook folders and adding a new folder. Either correct the filename or update the Copybook folder to include the drive/folder where the copybook is located.
COBDB0108 <token> is non-unique and should be qualified.
The host variable displayed in <token> is not unique.
You must fully qualify a host variable in a SQL query if the variable name is not unique. For example if ORD-NO exists in both the ORDERS and ITEMS copybooks, you would need to specify something like this if you wanted the host variable from the ORDERS copybook to be used in the query:
:ORDERS.ORD-NO
COBDB0109 <token> is not a data item.
The host variable displayed in <token> was not defined or not defined correctly.
Verify that the host variable exists. If not, make any changes to correct the error, or use the Host Compatibility Option DCLGEN tool to generate a copybook of the host variable for the SQL table in question.
COBDB0110 Statement contains more than one SQLDA.
More than one SQLDA has been defined in Working-Storage.
The DB2 ECM currently has a limit of only one SQLDA definition per program. If this becomes an issue, contact your support representative.
COBDB0111 <token> is not a table of null indicator variables.
The DB2 ECM believes the <token> displayed should either be a null indicator or a null indicator table.
Correct the SQL statement and re-compile the program. If you are using a null indicator table, make sure you do not use the group name. For example, if the following is a definition of a null indicator table:
01 staff-null-tbl.
03 staff-null-ind occurs 7 times pic s9(04) comp.
you would specify "staff-null-ind" rather than "staff-null-tbl" in your query.
COBDB0112 <token> should not be defined with an OCCURS clause.
A host variable is defined as part of an OCCURS clause.
The DB2 ECM does not allow host variables to be defined this way.
COBDB0113 <token> should not be a subordinate to an item defined with an OCCURS clause.
A host variable is defined as part of an OCCURS clause or as part of a structure that includes an OCCURS clause.
The DB2 ECM does not allow host variables to be defined this way.
COBDB0114 Statement only allowed in the Procedure Division.
SQL statement in question was not defined in the Procedure Division.
Some SQL statements such as UPDATE, DELETE, INSERT, and SELECT..INTO can only be coded in the Procedure Division. Move the statement in question to the Procedure Division and then recompile the program.
COBDB0115 Statement not allowed in the PROCEDURE DIVISION.
An SQL statement such as EXEC SQL DECLARE TABLE was coded in the Procedure Division.
This statment cannot be coded in the Procedure Division. Move it into the Working-Storage Section and then recompile the program.
COBDB0116 An error occurred whilst processing the trace file.
A file error occurred while trying to write information to the DB2 ECM trace file.
Make sure you have write authority on the drive/folder where the file is being written to and that there is enough space left on the drive.
COBDB0117 Invalid format of compound SQL statement. This condition is non-recoverable; subsequent (valid) SQL statements may generate spurious errors.
A compound SQL statement did not precompile successfully.
Look at the IBM SQL Reference for details of how to specify a valid compound SQL statement.
COBDB0118 Mismatched DECLARE.
More than one DECLARE statment was found within an EXEC SQL statement.
Correct the statement and recompile the program.
COBDB0119 Host variable name is too long.
The host variable name is too long. If you are using a version of DB2 before DB2 Universal Database V5, the maximum length of the host variable is 30 characters. The DB2 ECM automatically adds 3 characters to a host variable name to make it unique unless you specify directive DB2(NOQUALFIX) to turn this feature off.
Specify directive DB2(NOQUALFIX). If you still get the error, make sure the host variable is a valid COBOL name. It should be 30 characters or less in length.
COBDB0120 Internal error - dictionary handle is too big.
The DB2 ECM creates an internal dictionary of all host variables used in a program. A system error has occurred.
Report this error to your support representative. Trace files may be need to resolve this problem. See the section Creating Debug Files for details of how to create additional trace files that may be requested by your support representative.
COBDB0121 Package name is too long, max=8 chars, use ACCESS directive to fix.
The package name specified is longer than 8 characters. If you did not specify an explicit package name via the ACCESS directive, the DB2 ECM defaults to use the filename of the program being compiled. If this name is more than 8 characters, this error message is generated.
Specify the directive DB2(ACCESS=name) where the name is 8 characters or less.
COBDB0127 A '(' is illegal in the INTO clause.
A left parenthesis "(" was found in the SQL statement in error.
Correct the syntax and recompile your program. See the SQL Reference for the correct syntax.
COBDB0131 SQLCA or SQLCODE not found.
An SQLCA or SQLCODE was not found in the WORKING-STORAGE or LINKAGE section.
Check to see if an SQLCA is included and declared correctly.
COBDB0137 Indicator variable <token> is not SMALLINT type.
The indicator variable data definition does not match the data type associate with it in the SQL statement. For example, you are trying to use an indicator variable defined as PIC X(n) rather than PIC S9(04) COMP.
Correct the picture clause for the indicator variable identified in the error message and recompile the program.
COBDB0900 An internal error occurred. Create a trace file using the compiler directive CHKECM(TRACE) and contact your support representative.
An unexpected error occured in the DB2 ECM.
Report this error to your support representative. Trace files may be need to resolve this problem. See the section Creating Debug Files for details of how to create additional trace files that may be requested by your support representative.
COBDB0901 This error prevents SQL processing from continuing - further EXEC SQL statements will be ignored.
If a severe SQL error occurs, any additional EXEC SQL statements will not be processed by the DB2 ECM.
Correct the severe error and then recompile the program.
If an error occurs when compiling a program that requires technical support, your support representative may request you to provide additional debug files to aid in determining the cause of the problem. There are three debug files that the support representative may ask you to provide to re-create the problem, in addition to source and data files. You may want to specify some of these directives to aid in your own debugging efforts. The directives are as follows:
| Directive |
File created |
Information within file
|
|---|---|---|
|
CHKECM(CTRACE) |
ecmtrace.txt |
This file contains pseudo COBOL code that shows the code generated to replace the EXEC SQL statements. This file is equivalent to output out of the IBM COBOL precompiler. |
| CHKECM(TRACE) | ecmtrace.txt | This file contains detailed information as to what information is passed between the DB2 ECM and the Compiler. If an error occurs that generates invalid syntax, this file will be required to help isolate where the problem occurred. |
| DB2(CTRACE) | sqltrace.txt | This file contains a detailed list of information passed to IBM precompiler services and the results. This file is very useful if an error may involve a bug in the DB2 system software as well as the DB2 ECM. |
Any new features or program fixes of note are located in ANSI text file that comes with each websync. To view release notes, you can use any ANSI text editor.
Copyright © 2001 Micro Focus International Limited. All rights reserved.
This document and the proprietary marks and names
used herein are protected by international law.