1. Before defining the PL/SQL package, identify the transaction name, ID number (t#), and version (v#) from the TIP specification within the TIP.
2. Invoke PGAU REPORT WITH DEBUG specifying the same transaction name and version.

   REPORT selects definitions from the PG DD and produces a listing showing the DATA, CALL, and TRANSACTION definitions and the ID number of each user-supplied definition.

3. Compare the reported definitions with those used in the remote transaction program and identify all corresponding exchanges and the data formats transmitted.
4. Look for and investigate any mismatches, such as:

   • different numbers of send/receive calls

   • different sequence of send/receive calls

   • different parameter lists on send/receive calls

   • different data fields within each exchanged parameter

   • different lengths for each exchanged parameter

   • unsupported datatypes for each exchanged parameter

   • improperly initialized control fields for:

     • repeating group counts

       IBMVSCOBOLII affected clauses include

       OCCURS n TIMES DEPENDING ON field

     • remapped group criteria

       IBMVSCOBOLII affected clauses include

       REDEFINES field1 WHEN field2 = criteria

PGAU GENERATE error messages and TRACE(OC) entries reference SQL SELECT statements. Refer to Table 8-2 for the meaning of the name designations for each entry.

The SQL*Plus test scripts in Table 8-3 are provided to perform the identical SELECTS as GENERATE performs to determine which PG DD rows are being used when the TIP is generated. These files are loaded into the %ORACLE_HOME%\dg4appc\admin directory on Microsoft Windows or into the $ORACLE_HOME/dg4appc/admin directory on UNIX based systems, during installation.

The scripts are shown in the same order used by GENERATE and each script prompts the SQL*Plus user for the required input. The information retrieved from a previous select is often used as input to a subsequent select. If a you suspect that a PG DD field entry has produced inaccurate data, browse the .sql files listed above to determine the source of the problem. These files are loaded into the %ORACLE_HOME%\dg4appc\admin directory on Microsoft Windows or $ORACLE_HOME/dg4appc/admin directory on UNIX based systems, during installation.

Data conversion errors are usually the result of:

• incorrect determination of data type

  or

• incorrect specification of data position

PGAU determination of the data type is based on the values found in the PG DD, pga_fields(mask), and pga_fields(maskopts) columns. PGAU generates PL/SQL code to perform conversions based on the mask value:

• PIC X converted to CHAR with the same character length

• PIC G converted to CHAR with the same character length

• PIC 9 converted to NUMBER

Character data type is presumed for all PIC X and PIC G mask values and conversion errors are more likely the result of position, length, and justification errors.

Determination of numeric data type depends on several factors, including the combination of mask and maskopts values and how they apply to the actual remote host data in its internal format. Values for mask, maskopts, and data might conflict in unexpected ways. For example, an option such as USAGE IS COMP might be overridden if the data is in display format. While compilers occasionally perform such overrides correctly, they can cause unexpected results when exchanging data with systems coded in other languages.

To notify the user of such overrides, a warning function has been included in the following UTL_PG functions:

• MAKE _NUMBER_TO_RAW_FORMAT

• MAKE_RAW_TO_NUMBER_FORMAT

• NUMBER_TO_RAW

• RAW_TO_NUMBER

TIPs should be generated by the PGAU GENERATE command with the PKGEX(DC) diagnostic option to include TIP data conversion trace logic in the TIP. TIP function call trace logic is always included in every TIP. This is runtime trace instrumentation and has some overhead when tracing is enabled, but negligible overhead when tracing is disabled. Refer to GENERATE in Procedural Gateway Administration Utility for more information.

1. Regenerate TIPs with the PKGEX(DC, DR) options and recompile the TIP body file, tipname.pkb. Avoid recompiling the TIP specification.

2. Revise the application that calls the TIP initialization function (tipname_init) to pass the trace flags parameter with data conversion and function call tracing enabled. Refer to "Controlling TIP Runtime Data Conversion Tracing".

   If the problem causes an exception to be raised in the TIP and the application contains an exception handler, the application exception handler should be Commented out to prevent it from handling the exception and preventing the exception point of origin from being reported. When the TIP exception is next raised, its source line number in the TIP is reported. Record this information.

3. Execute the application with diagnostic TIP initialization.

   If the TIP trace pipe inlet overflows due to the application calls causing the TIP to write trace messages in the TIP trace pipe inlet, you have one minute from the start of the overflow condition to begin Step 4 and empty the TIP trace pipe.

   Otherwise, exception "ORA-20703 PGA-TIP: pipe send error" is issued, ending the diagnostic session, possibly before any relevant trace information is generated.

4. Retrieve and record the TIP trace message stream.

   Use SQL*Plus to connect to the same Oracle user ID executing the application or the user ID under which the TIP is executed. This establishes a second session from which the trace pipe outlet can be read, preventing the TIP trace pipe from overflowing at the TIP trace pipe inlet.

   1. Issue the command:

      set serveroutput on size nnnnn
      

   2. Issue the command to record the trace output:

      spool tipname.trc
      

   3. Issue the command to retrieve the trace stream:

      exec rtrace('tipname');
      

      If the application is long-running, repeat this command as often as needed until all trace messages have been retrieved.

5. If any exceptions are raised, note their prefix, number, and full message text.

6. Analyze the TIP trace message stream. A normal trace is shown for the pgadb2i TIP in Administration Utility Samples.

Runtime trace control is the second parameter specified on a TIP initialization call. It is a CHAR(8) datatype of the following form:

rc := yourtip_init(trannum,'wxyz0000');
  

Table 8-4 describes the value of positions one to four:

Positions 5 through 8 are reserved and ignored.

rc := yourtip_init(trannum,'10000000');

"UTL_PG warnings enabled"

rc := yourtip_init(trannum,'01000000');

'function entry/exit trace enabled'
'tipname_init entered'
'time date/time stamp'

rc := yourtip_init(trannum,'00100000');

'data conversion trace enabled'

rc := yourtip_init(trannum,'00010000');

'gateway exchange trace enabled'

After debugging is finished, there are two ways to suppress the following:

• data conversion tracing

• conversion warnings

• function entry/exit tracing

• gateway exchange tracing

You can:

1. Call the TIP initialization function without passing any diagnostic control parameters:

   rc := yourtip_init(trannum);
   

2. Call the TIP initialization function passing a revised diagnostic control parameter which disables all tracing and warnings:

   rc := yourtip_init(trannum,'00000000');
   

   A third method, described in Method C, removes the logic for:

   • data conversion tracing

   • conversion warnings

3. Generate the TIP again without:

   PKGEX(DC)
   

Or you can recompile the previous version of the TIP body if it was saved.

Methods A and B allow you to use the same TIP without alteration, but without tracing or warnings. These methods are reversible without alteration or replacement of the TIP. Tracing and warnings can be redisplayed should a problem recur.

Method C also suppresses data conversion tracing and warnings and incurs reduced overhead by avoiding tests, but is not reversible without regenerating the TIP or recompiling an alternate version with data conversion tracing and warning diagnostics imbedded.

The logic for function entry/exit and gateway exchange tracing is included in every TIP and cannot be removed. It can be disabled by method A or B.

Oracle Database Gateway for APPC data lengths are limited by PL/SQL to 32,763 bytes per APPC exchange and PL/SQL variable.

The following steps can be used to diagnose data conversion or truncation errors.

Refer to Creating a TIP to review the proper values and definitions referenced in items 1 through 4 below:

1. Ensure that the COBOL definitions used in the RHT match the input to PGAU;

2. Ensure the RHT transmission buffers are of sufficient length;

3. If your gateway uses SNA: Ensure the RHT APPC call addresses the correct transmission buffer and uses the correct data length;

   If your gateway uses TCP/IP: Ensure the RHT I/O PCB call addresses the correct transmission buffer and uses the correct data length

4. Ensure the client application has declared the correct TIP datatypes used as arguments in the TIP calls.

5. Ensure that the client application is calling the TIP functions in the proper sequence (init, user-defined..., term), and that any input data to the RHT is correct. Also ensure that if multiple user-defined functions exist, they are being called in the proper sequence and passed the correct input values, if any.

   DBMS_OUTPUT calls can be inserted in the client application to trace its behavior.

   For more information about calling TIP functions in proper sequence, refer to the section on configuring the Oracle database for first time installations in the installation guides.

6. Optionally, regenerate the TIP with diagnostic traces included and enable them. The following traces are particularly useful:

   • data conversion trace

   • function entry/exit trace

   • gateway exchange trace

   Refer to "Problem Analysis with TIP Runtime Traces" for more information about traces; refer also to GENERATE in Procedural Gateway Administration Utility .

   Note that the output of the trace is different for a gateway using SNA than for a gateway using TCP/IP. However, the method of invoking the trace is the same regardless of which communication protocol you are using.

   On Microsoft Windows, the gateway server tracing must also be enabled in %ORACLE_HOME%\dg4appc\admin\initsid.ora. Set the parameters SET TRACE_LEVEL=255 and SET LOG_DESTINATION=C:\oracle\pga\12.2\dg4appc\log

   On UNIX based systems, the gateway server tracing must also be enabled in $ORACLE_HOME/dg4appc/admin/initsid.ora. Set the parameters SET TRACE_LEVEL=255 and SET LOG_DESTINATION=/oracle/pga/12.2/dg4appc/log

   Refer to "Gateway Server Tracing" in this guide for more information about tracing.

   • If your gateway is using SNA: Refer to Appendix A, "Gateway Initialization Parameters for SNA Protocol" in your Oracle Database Gateway for APPC Installation and Configuration Guide for more information about these parameters;

   • If your gateway is using TCP/IP: Refer to Appendix B, "Gateway Initialization Parameters for TCP/IP Communication Protocol" in the Oracle Database Gateway for APPC Installation and Configuration Guide for IBM AIX on POWER Systems (64-Bit), Linux x86-64, Oracle Solaris on SPARC (64-Bit), and HP-UX Itanium or Oracle Database Gateway for APPC Installation and Configuration Guide for Microsoft Windows. for more information about these parameters.

   Rerun the client application and examine the trace (see the next step for details).

   To disable the trace, reset

   SET TRACE_LEVEL=0

7. Examine the trace output.

   The TIP trace output can be saved in a spool file, such as:

   spool tipname.trc
   

   TIP trace output is written to a named DBMS_PIPE and can be retrieved under SQL*Plus by issuing the following command:

   exec rtrace('tipname');

   or it can be purged by issuing the following command:

   exec ptrace('tipname');  
   

   Note:

   tipname is case-sensitive and must be specified exactly as it is in the TIP.

   Gateway server trace output is written to a log file in a default directory path specified by the SET LOG_DESTINATION gateway parameter in %ORACLE_HOME%\dg4appc\admin\initsid.ora for Microsoft Windows and in $ORACLE_HOME/dg4appc/admin/initsid.ora for UNIX based systems. For example, on Microsoft Windows:

   SET LOG_DESTINATION=C:\oracle\pga\12.2\dg4appc\log
   

   On UNIX based systems:

   SET LOG_DESTINATION=$ORACLE_HOME/dg4appc/log/
   

   Refer to "Gateway Server Tracing" for more information.

   The gateway server log file can be viewed be editing the file or by issuing other system commands that display file contents. The log file can also be copied and saved to document problem symptoms.

The gateway contains extensive tracing logic in the gateway remote procedural calls (RPCs), and the APPC-specific code. Tracing is enabled through gateway initialization parameters or dynamic RPC calls to the gateway. The trace provides information about the execution of the gateway RPC functions and about the execution of the APPC interface. The trace file contains a text stream written in chronological sequence of events. The trace is designed to assist application programmers with the debugging of their OLTP transaction programs and Oracle applications that communicate with those transaction programs through the gateway.

A single trace file is created for an entire gateway session from the time the database link is opened until it is closed. The trace can be directed to a specific path/filename or to a path (directory) only. In the first case, the file is overwritten each time a new session begins for the gateway being traced. When the trace target is a directory, a separate file with a generated name (containing the operating system process ID) is written for each gateway session. The latter approach must be used whenever the gateway to be traced might be the target of new sessions after the desired trace is written but before it can be copied and saved. Conversely, in some situations you might choose to create a distinct gateway system identifier used solely for tracing, and direct its trace to a single specific filename. This avoids the problem of an ever-increasing set of trace files when, for example, repeated attempts are necessary to reproduce or debug a problem. A fixed filename should never be used if there is any chance that an unexpected gateway session could overlay a useful trace.

sid_pid.log 

SET TRACE_LEVEL=trace