This chapter discusses error messages generated by Oracle Transparent Gateway for DB2, the diagnosis of suspected Oracle database server errors, and the requirements for documenting these errors to Oracle Support Services.
For information about Oracle Database 10g for z/OS-specific error messages, refer to the Oracle Database Messages Guide for IBM z/OS (OS/390).
This chapter includes the following sections:
The gateway architecture includes a number of separate components. Any of these components can detect and report an error condition while processing a SQL statement referring to one or more DB2 database tables. An error condition can be complex, involving error codes and supporting data from multiple components. In all cases, the application ultimately receives a single Oracle database server error code on which to act.
Error conditions are represented in one of two ways:
When possible, an error code from the DB2 database is converted to the Oracle database server error code associated with the same logical condition.
Error code mapping is provided to support application designs that test for and act on specific error conditions. The set of mapped errors is limited to those associated with conditions common to most relational databases.
Most gateway error conditions are reported to the application using one of the gateway error codes in the range of ORA-9100 through ORA-9199. These messages are less closely linked to specific DB2 database conditions. The message format is explained in "Interpreting Message Formats" .
The ORA-9100 error code is returned for all errors for which a more specific error code does not exist. When an ORA-9100 error code is returned, the error might have been caused in the gateway by a DB2 support component on the target database system.
DB2 error messages, that is, SQLSTATE
codes, are mapped to Oracle database server error codes. Notice that multiple DB2 SQLCODE
can refer to the same Oracle database server error code.
Description | SQLSTATE Code | Oracle Database Server Error Code |
---|---|---|
No rows selected | 02000 | 0 |
Unique index constraint violated | 23505 | ORA-0001 |
Table or view does not exist | 52004 or 42704 | ORA-00942 |
Object name greater than 18 characters and, therefore, object does not exist | 54003 or 42622 | ORA-00942 |
Insufficient privileges | 42501 | ORA-01031 |
Divisor is equal to zero | 01519 or 01564 | ORA-01476 |
Error messages are generally accompanied by additional message text, beyond the text associated with the Oracle database server message number. The additional text includes details about the error.
Most gateway messages exceed the 70-character message area in the Oracle SQLCA
. Use SQLGLM
or OERHMS
in the programmatic interfaces and the OCI that you use with the gateway to view the entire message. Refer to the Oracle Database Messages Guide for IBM z/OS (OS/390) for information about SQLGLM
and theOracle Call Interface Programmer's Guidefor information about OERHMS
.
Gateway messages use the following format:
ORA-nnnn:error message text gateway message line(s) ORA-2063:PRECEDING n LINES FROM dblink where
Parameter | Description |
---|---|
nnnn | is an Oracle database server error number. If nnnn is between 9100 and 9199, then the message is from the gateway. If it is not in this range, then it is a mapped error message. |
error message text | is the text of the message associated with the error. |
gateway message lines | are additional messages generated by the gateway. The gateway messages lines are described in "Diagnosing Errors Detected by the Oracle Database Server". |
n | is the total number of gateway message lines. |
dblink | is the name of the database link that is used to access the gateway. |
The following message is generated by Oracle Transparent Gateway for DB2.
ORA-02063 preceding n
lines from dblink
dblink
in the ORA-2063 message indicates the name of the database link that was used to access the gateway. n
in the ORA-02063 message indicates the total number of gateway message lines referenced in the ORA-28500 message. For additional information about the DB2 messages that are included in the ORA-28500 message, refer to the IBM documents for your platform and operating system.
If an error is detected by the Oracle database server, then the gateway message lines do not occur. For example, if the gateway cannot be accessed because of an Oracle Net or gateway installation problem, then the gateway message line is not present in the error message.
Another example of error messages without gateway message lines occurs when an INSERT
statement attempts to insert data into a table, but does not include values for all the columns in the table. The following SQL statement causes an error message:
SQL> insert into EMP@DB2 values(9999); ERROR at line 1: ORA-00947: not enough values
The ORA-00947 message is not accompanied by gateway message lines because the error is detected by the Oracle database server. The Oracle database server obtains a description of the DB2 table before sending the INSERT
statement to the gateway for processing. This allows the Oracle database server to detect when the INSERT
statement is invalid.
Oracle Support Services serves as the interface to the Oracle user community. Refer to the applicable Oracle Support Services publications for a discussion of policies and procedures for using their services.
During the error resolution cycle, Oracle Support Services might request that you provide them with computer-readable data. Send computer readable data, not formatted or printed data. The preferred method for providing error documentation is anonymous FTP. Please contact Oracle Support Services for instructions about how and where to provide documentation.
If you are requested to send data to the Support Center, then follow the documentation requirements provided in "General Documentation Requirements". Failure to follow these requirements might result in inability to process your tape. This might delay the resolution of errors reported.
When you report a suspected error, you might be asked to describe the Oracle subsystem and z/OS operating system environments in detail. Provide the full version number of each component that has an error. The full version number includes important PUT levels for the z/OS system.
Before you contact Oracle Support Services, ensure that the following information is available:
Oracle library naming conventions
Method of accessing Oracle utilities
Oracle subsystem name
Full version of the Oracle gateway
Full version of the Oracle database server client tools
Full version of the Oracle utility
Full version of the third party tool (if applicable)
PUT level
RMID of any relevant OS module
In addition to describing the Oracle operational environment, detailed documentation specific to the error might be required. This might include:
Gateway PARMLIB
members
Console logs and gateway job logs
Utility SYSOUT
System diagnostic messages
Oracle database server error messages
System dumps
Gateway trace data sets
Database engine trace data sets
Oracle Net trace data sets
Output from the CLIST PGMDESCC
Network level trace data sets for TCP/IP
Keep in mind that more than one error is often associated with a single failure. Describe all errors for the failure being reported. If your application uses Pro*C, Pro*COBOL, or another Oracle Precompiler, then ensure that your application displays or prints out all errors it encounters. Without this information, diagnosing the problem is more difficult.
When investigating a potential Oracle gateway error, start by determining which component is failing, where it is failing, and the error category.
Use the following error categories to describe the error:
Documentation errors
Incorrect output
Oracle database server external error
Abend
Program loop
Performance
Missing functionality
When reporting documentation errors, you are asked to provide the following information:
Document name
Document part number
Date of publication
Page number
Describe the error in detail. Documentation errors can include erroneous documentation and omission of required information.
In general, an incorrect output error exists whenever an Oracle utility produces a result that differs from written Oracle documentation. When describing errors of incorrect output, you need to describe, in detail, the operation of the function in error. Be prepared to describe your understanding of the proper function, the specific Oracle documentation that describes the proper operation of the function, and a detailed description of the incorrect operation.
If you think you have found a software bug, then be prepared to answer the following questions:
Does the problem occur in more than one Oracle tool? (Examples of Oracle tools are SQL*Plus and Oracle Developer forms.)
What are the exact SQL statements used to reproduce the problem?
What are the full version numbers of the Oracle database server, Oracle gateway, and related Oracle software?
What is the problem and how is it reproduced?
Oracle database server error messages are produced whenever an Oracle gateway, server, tool, or DB2 system detects an error condition. Depending on the circumstances, error messages might be fatal or nonfatal to the utility or server.
Be prepared to identify the exact error message and message number received and the complete circumstances surrounding the error.
Any program check in an Oracle utility or the Oracle gateway address is considered an error. A full system dump is required as documentation if there is a program check.
Ensure that the system dump contains all of the private area of the Oracle gateway address space; without it, diagnosis is sometimes impossible.
System abends might or might not indicate a failure of the Oracle subsystem, depending on circumstances. The following abends are not considered Oracle database server failures:
013 - open failure
122 - canceled by operator
222 - canceled by operator
322 - CPU time exceeded
722 - SYSOUT lines exceeded
A program loop is evident when the Oracle gateway task consumes CPU time rapidly, but no actual work is performed.
Any program loop occurring within an Oracle gateway address space is considered an Oracle gateway failure. Loop conditions are rarely experienced and are considered serious errors. The initial diagnostic approach with a loop consists of a system dump. If a task is in a program loop, then ensure that the system dump includes all of the private area of the gateway address space.
Further diagnosis might be required using z/OS SLIP commands. Oracle Support Services provides specific instructions on the use of SLIP, depending on the circumstances.
Oracle system performance is determined by many factors, most of which are not within the control of Oracle. Considerations such as system load, I/O topology, network topology utilization, and DB2 resource availability and utilization, make the documentation of performance errors difficult.
Provide detailed information about the state of your environment when reporting an error. Specific documentation might include:
CPU type and memory configuration
Database topology
I/O topology
Network topology
System workload by type
Oracle database server workload characterization
Query completion plans
DB2 threads and resource information
Enhancement requests can be opened with Oracle Support Services to request the inclusion of functions and features not currently available with Oracle products. When opening an enhancement request, describe the specific feature or function to be added to the product, and provide a business case to justify the enhancement.
When providing documentation on suspected Oracle database server failures, it might be necessary for you to provide a system dump of the Oracle gateway or utility address spaces. Dumps are initiated through the z/OS operator interface using the DUMP
and SLIP
commands or automatically by the Oracle gateway if it detects a problem.
Dumps sent to Oracle Support Services as documentation for suspected errors must not be formatted. Formatted dumps cannot be used. Formatting a system dump results in a significant delay in processing reported errors, and you might have to send a new, unformatted dump.
When specifying dump parameters in response to a z/OS DUMP COMM=('')
command, you must include the following specification:
CSA,PSA,TRT,RGN
Once a SYS1.DUMP
xx
data set is created, the system operator is notified whenever a dump to that data set occurs. Because all Oracle abends are dumped to SYS1.DUMP
data sets and are not dynamically allocated by OSDI, you must ensure that a SYS1.DUMP
data set is always available.
You must also ensure that the SYS1.DUMP
data set is large enough to accommodate the gateway address space.
If a SYS1.DUMP
data set is not available, then a dump might be lost.
Operator-initiated dumps are accomplished with the z/OS DUMP
command:
DUMP COMM=(text)
where text
is the title you want the dump to have.
After the DUMP
command has been entered, you must respond to the system WTOR with:
R xx,[JOBNAME=(ssn)|ASID=(nnn),]SDATA=(CSA,PSA,TRT,RGN)
where: