This chapter discusses error messages that are generated by Oracle Database 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:
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 on which to act.
When possible, an error code from DB2/400 is converted to the Oracle error code that is 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 errors that are associated with conditions that are common to most relational databases.
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.
Table 10-1 SQLSTATE Codes Mapped to Oracle Server Error Codes
Description | SQLSTATE Code | Oracle 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 too long. 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 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.
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.
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 release number and platform
Use the following error categories to describe the error:
documentation errors
incorrect output
Oracle external error
abnormal termination
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 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 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.
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
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.