10 Error Messages, Diagnosis, and Reporting

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:

10.1 Message and Error Code Processing

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:

  • Mapped

    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.

  • Messages from the gateway

    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.

10.1.1 Mapping DB2 Error Messages to Oracle Error Messages

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

10.1.2 Interpreting Message Formats

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)

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.

10.1.3 Messages Generated by Oracle Transparent Gateway for DB2

The following message is generated by Oracle Transparent Gateway for DB2.

ORA-02063 preceding n lines from dblink

10.1.4 Examples

Cause: Indicates an error from Oracle Transparent Gateway for DB2.  The dblink in the ORA-2063 message indicates the name of the database link that was used to access the gateway. 
Action: Refer to the text of the preceding message to determine what action is required.
Cause: Indicates an error from DB2.  It is followed by messages from the DB2 database.  The n in the ORA-02063 message indicates the total number of gateway message lines referenced in the ORA-28500 message. 
Action: Refer to the text of the proceeding message to determine what action is required.
Cause: If this message is returned while attempting to run a DB2 stored procedure, then it is an indication that the definition of the DB2 stored procedure parameter list is inconsistent with the parameter list passed from the Oracle application.
Action: Correct the parameter list in the Oracle application to match the DB2 stored procedure being called.

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.

10.1.5 Diagnosing Errors Detected by the Oracle Database Server

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.

10.2 Oracle Support Services

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.

10.3 Providing Error Documentation

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. 

10.4 General Documentation Requirements

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.

10.5 Error Diagnosis

When investigating a potential Oracle gateway error, start by determining which component is failing, where it is failing, and the error category

10.5.1 Components

When reporting a problem to Oracle Support Services, identify the component suspected of failure, along with its full version and correct release level. 

10.6 Error Categories

Use the following error categories to describe the error:

  • Documentation errors

  • Incorrect output

  • Oracle database server external error

  • Abend

  • Program loop

  • Performance

  • Missing functionality

10.6.1 Documentation Errors

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. 

10.6.2 Incorrect Output

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?

10.6.3 Oracle Database Server External Error

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. 

10.6.4 Abend

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

10.6.5 Program Loop

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.

10.6.6 Performance

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

10.6.7 Missing Functionality

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.

10.7 System Dumps

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:


10.7.1 System Dump Data Sets

Once a SYS1.DUMPxx 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. 

10.7.2 Operator-Initiated Dumps

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:



Parameter Description
xx is the reply identification number.
ssn is the name of the Oracle subsystem.
nnn is the hexadecimal address space identifier of the address space you want to dump.

10.8 GTF

You might need to use GTF as a diagnostic tool under certain circumstances.  Oracle Support Services provides specific instructions if this is necessary.