5 Error Messages, Diagnosis, and Reporting

This chapter provides information about error messages and error codes. This data is specific to the 11.1 release of the Oracle Database Gateway for DRDA. This chapter contains the following sections:

5.1 Interpreting Gateway Error Messages

The gateway architecture consists of different components. Any component may detect and report an error condition while processing SQL statements that refer to one or more DRDA database tables. This means that errors can be complex, involving error codes and supporting data from multiple components. In all cases, however, the application ultimately receives a single error code or a return code.

As most gateway messages exceed the 70 character message area in the Oracle SQL Communications Area (SQLCA), the programmatic interfaces and Oracle Call Interfaces, that you use to access data through the gateway should use SQLGLM or OERHMS to view the entire text of messages. Refer to the programmer's guide to the Oracle precompilers for additional information about SQLGLM, and refer to the Oracle C++ Call Interface Programmer's Guide for additional information about OERHMS.

Errors encountered when using the gateway can originate from many sources, as follows:

  • Errors detected by the Oracle database

  • Errors detected by the gateway

  • Errors detected in the DRDA software, either on the client or server side

  • Communication errors

  • Errors detected by the server database

5.1.1 Errors Detected by the Oracle Database

Errors detected by the Oracle database are reported back to the application or tool with the standard ORA type message. Refer to Oracle Database Error Messages for descriptions of these errors. For example, the following error occurs when an undefined database link name is specified:

ORA-02019:  connection description for remote database not found 

Errors in the ORA-9100 to ORA-9199 range are reserved for the generic gateway layer (components of the gateway that are not specific to DRDA). Messages in this range are documented in Oracle Database Error Messages.

5.1.2 Errors Detected by the Gateway

Errors detected by the generic gateway are prefixed with HGO- and are documented in Oracle Database Error Messages.

A sample error message is:

HGO-00706:  HGO:  Missing equal sign for parameter in initialization file.

5.1.3 Errors Detected in the DRDA Software

Errors detected in the DRDA gateway, on the client or server side, are usually reported with error ORA-28500, followed by a gateway-specific expanded error message. There are two return codes reported in the expanded message:

Note:

Error code ORA-28500 was error code ORA-09100 prior to gateway version 8. Error code ORA-28501 was listed as ORA-09101 prior to gateway version 8.

The values in parentheses that follow the drc values are used for debugging by Oracle Support Services. The errp field indicates the program (client or server) that detected the error. If present, errmc lists any error tokens.

For example, the following error message is returned when the database name specified with the DRDA_REMOTE_NAME parameter in the initsid.ora file is not defined at the DRDA server:

ORA-28500: connection from ORACLE to a non-Oracle system returned the message:
DG4DRDA v11.1.0.5.0 grc=0, drc=-30061 (839C,0000), errp=GDJRFS2E
errmc=XNAME

5.1.4 Communication Errors

Communication errors are reported with an ORA-28501 followed by a gateway specific error message with drc=-30080 or drc=30081 indicating either a network error or lost session condition. errmc indicates which network function encountered the error, followed by a network interface specific error number.

For example, the following error message is returned when there is a failure to establish a session because DRDA_CONNECT_PARM in the initsid.ora file specifies a Side Information Profile that is not defined:

ORA-28501: communication error on heterogeneous database link
DG4DRDA v11.1.0.5.0 grc=0, drc=-30081  (839C,0001), errp= file or directory(2)
errmc=Initialize_Conversation (CMINIT) CM_PROGRAM_PARAMETER_CHECK(24) No such > file or directory(2)

Refer to the appropriate host operating system documentation for more information.

5.1.5 Errors Detected by the Server Database

Errors detected by the server database are reported with an ORA-28500 followed by a gateway-specific expanded error message with drc=-777 sqlcode follows.) This is followed by another line that contains the sqlcode, sqlstate, errd (error array), and errmc (error tokens) returned from the DRDA server database. Refer to IBM documentation for the specific database being used. Also refer to Mapped Errors in this chapter for some SQL errors that get translated.

Note:

Error code ORA-28500 was error code ORA-09100 prior to gateway version 8. Error code ORA-28501 was listed as ORA-09101 prior to gateway version 8.

For example, the following error message indicates that the DRDA server database did not recognize the collection ID or package name specified with the DRDA_PACKAGE_COLLID or DRDA_PACKAGE_NAME parameters in the sid{{Emphasis Role='CodeInline'}}.ora{{/Emphasis}}"?>initsid.ora file:

ORA-28500: connection from ORACLE to a non-Oracle system returned the message:
DG4DRDA v11.1.0.5.0 grc=0, drc=-30020 (839C,0000), errp=GDJMRCM
sqlcode=-805, sqlstate=51002, errd=FFFFFF9C,0,0,FFFFFFFF,0,0
errmc=124c

5.2 Mapped Errors

Some SQL errors are returned from the DRDA server database and are translated to an Oracle error code. This is needed when the Oracle instance or gateway provides special handling of an error condition. The following table lists the mapped SQLstate error numbers, descriptions, and their corresponding Oracle error codes:

Table 5-1 Mapped sqlstate Errors

Description sqlstate error Oracle error

No rows selected

02000

0

Unique index constraint violated

23505

ORA-00001

Object does not exist

52004 or 42704

ORA-00942

Object name too long (more than 18 characters), and therefore object does not exist

54003 or 42622

ORA-00942

Insufficient privileges

42501

ORA-01031

Invalid CCSID (unimplemented character set conversion)

22522

ORA-01460

Invalid username/password; logon denied

N/A

ORA-01017

Divide by zero error

01519 or 01564

ORA-01476


The following is an example of a translated object does not exist error:

ORA-00942: table or view does not exist
DG4DRDA v11.1.0.5.0 grc=0, drc=-942 (839C,0001), errp=DSNXEDST
sqlcode=-204, sqlstate=52004, errd=32,0,0,FFFFFFFF,0,0
errmc=AJONES.CXDCX

5.3 Gateway Error Codes

Listed below are the common Oracle Database Gateway for DRDA error codes that appear in the drc= field of the expanded error messages. If you obtain a drc value that does not appear here, then contact Oracle Support Services.

-700 Invalid ORA_MAX_DATE specified
Cause: An invalid value was specified for ORA_MAX_DATE in the initsid.ora file.
Action: Correct the value of ORA_MAX_DATE. The correct format is ORA_MAX_DATE=YYYY-MM-DD, where MM is in the range of 1 to 12, and DD is in the range of 1 to 31 (and must be valid for the month).
-701 Default CCSID value not supported
Cause: The value specified for DRDA_DEFAULT_CCSID in the initsid.ora file is not supported by the Oracle Database Gateway for DRDA.
Action: Refer to Appendix C, "Globalization Support for DRDA", for a list of supported DRDA server character sets.
-702 Application Host (bind) variable exceeds 32K
Cause: An application program specified a host variable with length greater than the DRDA allowed maximum of 32K.
Action: The application must be modified to take into account DRDA limits.
-703 Local character set not supported
Cause: The character set specified for the LANGUAGE parameter in the initsid.ora file is not supported.
Action: Refer to Appendix C, "Globalization Support for DRDA", for a list of supported character sets.
-704 UserID length greater than maximum
Cause: The user ID used for the logon by the gateway is longer than 8 characters.
-705 Password length greater than maximum
Cause: The password being used for the logon by the gateway is longer than 8 characters.
-777 DRDA Server RDBMS (SQL) Error
Cause: Server database detected an application-level SQL error.
Action: Refer to "Interpreting Gateway Error Messages". sqlcode and, for more information to fix your application.
-30060 Invalid UserID/Password (DRDA Server RDBMS Authorization)
Cause: You have used a user ID or password that is not accepted by the DRDA server database.
-30061 RDB not found
Cause: The remote database specified with the DRDA_REMOTE_DB_NAME parameter is not a valid database in the DRDA server.
Action: Correct the value of the DRDA_REMOTE_DB_NAME parameter in the initsid.ora file.
-30080 Communication Error
Cause: The gateway encountered a communication error.
Action: Try processing the received error. If it persists, then refer to "Interpreting Gateway Error Messages" and report to your system administrator.
-30081 Communication Error - lost session
Cause: The current DRDA network session was disconnected.
Action: Try processing the received error. If it persists, then refer to "Interpreting Gateway Error Messages" and report it to your system administrator.

5.4 SQL Tracing and the Gateway

When developing applications, it is often useful to be able to see the exact SQL statements that are being passed through the gateway. This section describes setting appropriate trace parameters and setting up the debug gateway.

5.4.1 SQL Tracing in the Oracle Database

Oracle database has a command for capturing the SQL statement which is actually sent to the gateway. This command is called EXPLAIN PLAN. EXPLAIN PLAN is used to determine the execution plan that Oracle database follows to execute a specified SQL statement. This command inserts a row (describing each step of the execution plan) into a specified table. If you are using cost-based optimization, then this command also determines the cost of executing the statement. The syntax of the command is:

EXPLAIN PLAN [ SET STATEMENT_ID = 'text' ]
    [ INTO [schema.]table[@dblink] ] FOR statement 

For detailed information on this command, refer to the Oracle Database SQL Language Reference.

Note:

In most cases, EXPLAIN PLAN should be sufficient to extract the SQL statement that is actually sent to the gateway, and thus sent to the DRDA server. However, certain SQL statement form have post-processing performed on them in the gateway.

5.4.2 SQL Tracing in the Gateway

For Unix Based Systems:

The production gateway does not have built-in tracing built into it for the purpose of enhancing its speed. The product ships with a debug library that can be used to build a debug gateway for the purposes of tracing and debugging applications.

First, login using the Admin user ID of the gateway and setup the environment:

$ su - <gateway-Admin-User>
 

Next, build the debug gateway:

$ cd $ORACLE_HOME/dg4drda/lib 
$ make -f dg4drda.mk ORACLE_HOME=your_oracle_home g4drsrvd 

This will create the debug gateway and store it in $ORACLE_HOME/bin/g4drsrvd. Next, change the listener.ora to invoke the debug gateway. Change the PROGRAM parameter to specify the debug program name:

(SID_DESC=
        (SID_NAME=drdahoa1)
        (ORACLE_HOME=/oracle/dg4drda/11.1.0)
        (PROGRAM=g4drsrvd))

The listener will need to be reloaded for this change to take effect. Next, edit the Gateway Initialization File and add the following parameters:

TRACE_LEVEL and

ORACLE_DRDA_TCTL

You may optionally add the LOG_DESTINATION parameter, but it is not required.

The following is a fragment of a Gateway Initialization File with the parameters set:

#
TRACE_LEVEL=255
ORACLE_DRDA_TCTL=debug.tctl
#

The above example provides full tracing of both gateway and DRDA. In many cases, only the gateway tracing is desirable. To obtain only gateway tracing, remove (or comment out) the "ORACLE_DRDA_TCTL" parameter.

If you specify a LOG_DESTINATION, then you may specify only the file name (for example, drda.trc), in which case the log will be written to the log directory of the gateway ($ORACLE_HOME/dg4drda/log). Or you may specify a fully qualified path name. If you do not specify a LOG_DESTINATION, then a unique log file in a default format will be generated.

The logfile name will be of the form:

gatewaysid_pid.trc 

where:

gatewaysid is the SID of the gateway. The value of this is determined by the setting of the FDS_INSTANCE parameter, and pid is the process identifier (PID) of the gateway process.

An example log file name would be:

drdahoa1_3875.trc 

When searching for the SQL statements that are passed to the DRDA server, look for the strings '*** HGAPARS ***' and '*** HGAXMSQL ***'. The string after HGAPARS will be the incoming statement from the Oracle Database 11g Relational database Management System (RDBMS). The string after HGAXMSQL will be the outgoing statement after any date substitution is done. This is the actual SQL statement which will be given to the DRDA server.

When you have developed your application, revert the PROGRAM= value in the listener.ora to its previous value and reload the listener to use the production gateway again. You should also comment out the trace parameters in the Gateway Initialization Files.

For Microsoft Windows:

To enhance speed of the gateway, tracing was not built into the production gateway.

The product ships with a debug version of the gateway for the purposes of tracing and debugging applications.

This process entails changing the listener.ora file to use the debug gateway:

  1. Log in as the Administrator user ID of the gateway and set up the environment.

  2. Stop the Oracle Net Listener:

    > lsnrctl stop
    
  3. Edit the listener.ora with any text editor:

    > notepad C:\Oracle\GTWHome\network\admin\listener.ora
    
  4. Find the TNS entry for the gateway and change the program this way:

    PROGRAM=g4drsrvd
    
  5. Save the file and exit. Next, restart the Oracle Net Listener:

    > lsnrctl start
    
  6. Edit the gateway's initsid.ora file with any text editor:

    > notepad C:\Oracle\GTWHome\dg4drda\admin\initsid.ora
    
  7. Set the following parameters:

    TRACE_LEVEL=255
    ORACLE_DRDA_TCTL=debug.tctl
    

You may, as an option, add the LOG_DESTINATION parameter, but it is not required. If you specify a LOG_DESTINATION, then you may specify just the file name (for example, drda.trc), or you may specify a fully qualified path name. If you specify a LOG_DESTINATION with just the file name, then the log will be written to the log directory (ORACLE_HOME\dg4drda\trace) of the gateway. If you do not specify a LOG_DESTINATION, then a unique log file in a default format will be generated. The log file name will be of the form:

gatewaysid_tid.trc

Where:

gateway sid is the SID of the gateway.

tid is the thread identifier (TID) of the gateway service.

An example log file name would be:

drdahoa1_3875.trc

When searching for the SQL statements which are passed to the DRDA Server, look for the strings '*** HGAPARS ***' and '*** HGAXMSQL ***'. The string after HGAPARS will be the incoming statement from the Oracle Database 11g RDBMS. The string after HGAXMSQL will be the outgoing statement after any date substitution is done. This is the actual SQL statement which will be given to the DRDA Server.

When you have finished developing your application, revert the PROGRAM= value in the listener.ora file to its previous value and reload the listener to use the production gateway again. You should also comment out the trace parameters in the gateway initialization files.