10 Error Messages, Diagnosis, and Reporting

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. 

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)
ORA-2063:PRECEDING n LINES FROM dblink 

where

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

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.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.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.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:

R xx,[JOBNAME=(ssn)|ASID=(nnn),]SDATA=(CSA,PSA,TRT,RGN)

where: