10
Error Messages, Diagnosis, and Reporting

This chapter discusses error messages generated by Oracle Transparent Gateway for DB2/400, the diagnosis of suspected Oracle errors, and the requirements for documenting these errors to Oracle Support Services.

This chapter contains the following sections:

• Message and Error Code Processing

• Contacting Oracle Support Services

• Error Categories

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 that refers to one or more DB2/400 database tables.  An error condition can be complex, involving error codes and supporting data from multiple components.  In all cases, the application receives a single Oracle error code upon which to act.

When possible, an error code from DB2/400 is converted to the Oracle error code associated with the same logical condition.

Error code mapping is provided to support application designs that test for and act upon specific error conditions.  The set of mapped errors is limited to those associated with conditions that are common to most relational databases.

Mapping DB2/400 Error Messages to Oracle Error Messages

DB2/400 error messages, that is, SQLSTATE codes, are mapped to Oracle9i database server error codes.  Notice that multiple DB2/400 SQLSTATE codes can refer to the same single Oracle database server error code.

Description	SQLSTATE Code	Oracle Database Server Error Code
No rows selected	0200	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

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 you use with the gateway to view the entire message.  Refer to the 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 this format:

ORA-nnnn error_message_text
DB2/400_error_messages
ORA-2063: 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:

nnnn	is an Oracle error number.  If nnnn is between 28500 and 28559, the message is from the gateway.  If it is not in this range, it is a mapped error message.  In the example above, the value for nnnn is 00942
error_message_ text	is the text of the message 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 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 referenced above.  In the example above the value for n is 2.
dblink	is the name of the database link used to access the gateway.  In the example above the value for dblink is DB2link.

Diagnosing Errors Detected by the Oracle9i database server

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

Another example of 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:

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 Oracle9i database server.  The server 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.

Contacting Oracle Support Services

To maximize the effectiveness of your inquiries, gather the following information before contacting Oracle Support Services:

• OS/400 release number

• gateway release number (found in the JobLog for a Gateway task)

• Oracle Net release number (found in the JobLog for a Gateway task)

• Oracle database server release number and platform

Error Categories

Use the following error categories to describe the error:

• documentation errors

• incorrect output

• Oracle external error

• abnormal termination

• program loop

• performance

• missing functionality

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 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, 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 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 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 Corporation.  Considerations such as system load, I/O topology, network topology utilization, and DB2/400 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 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 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.