10 Error Messages, Diagnosis, and Reporting

Mapping DB2/400 Error Messages to Oracle Error Messages

DB2/400 error messages (SQLSTATE codes) are mapped to Oracle database error codes. Notice that multiple DB2/400 SQLSTATE codes can refer to the same Oracle database error code.

Interpreting Gateway Message Formats

Error messages are generally accompanied by additional message text, beyond the text associated with the Oracle 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 OCIErrorGet in the programmatic and Oracle Call Interfaces that you use with the gateway to view the entire message. Refer to the Oracle Database PL/SQL User's Guide and Reference for information about SQLGLM and the Oracle Call Interface Programmer's Guide for information about OCIErrorGet.

Gateway messages use the following format:

ORA-nnnnn error_message_text
DB2/400_error_messages
ORA-02063: preceding n lines from dblink

for example:

select * from scott.dummy@DB2link
ERROR at line 1:
ORA-00942 = table or view does not exist
SQL0204: DUMMY in SCOTT type * FILE not found
ORA-02063 preceding 2 lines from dblink

where:

nnnnn is an Oracle error number. If nnnnn is between 28500 and 28559, then the message is from the gateway. If it is not in this range, then it is a mapped error message. In the example above, the value for nnnnn is 00942.

error_message_text is the text of the message that is associated with the error. In the example above, the value for error_message_text is table or view does not exist.

DB2/400_error_messages are additional messages that are generated by DB2/400. In the example above, the value for DB2/400_error_messages is SQL0204 : DUMMY in SCOTT type *FILE not found.

n is the total number of DB2/400_error_messages lines that are referenced above the code entry line. In the example above, the value for n is 2.

dblink is the name of the database link that is used to access the gateway. In the example above, the value for dblink is DB2link.

Diagnosing Errors Detected by the Oracle Database 10g Server

If an error is detected by the Oracle Database 10g server, then the DB2/400 error messages do not occur. For example, if the gateway cannot be accessed because of an Oracle Net or gateway installation problem, then the DB2/400 error message is not present in the received error message.

Another example of Oracle database error messages without DB2/400 error messages occurs when an INSERT statement attempts to insert data into a table but does not include values for all of the columns in the table. This SQL statement causes an error message as follows:

SQL> INSERT INTO EMP@AS400 VALUES(9999);
ERROR at line 1:
ORA-00947: NOT ENOUGH VALUES

The ORA-00947 message is not accompanied by DB2/400 error messages because the error is detected by the Oracle database. The Oracle database obtains a description of the DB2/400 table before sending the INSERT statement to the gateway for processing. This allows the server to detect when the INSERT statement is invalid.

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.

Incorrect Output

In general, an incorrect output error occurs whenever an Oracle utility produces a result that differs from written Oracle product 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.)

• What are the exact SQL statements that are used to reproduce the problem?

• What are the full version numbers of the AS/400 system, Oracle Database, Oracle gateway, and related Oracle software?

• What is the problem, and how is it reproduced?

Oracle External Error

Oracle error messages are produced whenever an Oracle gateway, server, tool, or DB2/400 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 that are received and the complete circumstances surrounding the error.

Performance

AS/400 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 use, and DB2/400 resource availability and use, 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 workload characterization

• query execution plans

• DB2/400 resource information

Missing Functionality

Enhancement requests can be opened with Oracle Support Services to request the inclusion of functions and features that are 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.