Skip Headers
Oracle Procedural Gateway® for APPC User's Guide
10g Release 2 (10.2) for UNIX

Part Number B16210-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

G Administration Utility Samples

Use the following sample input statements and report output for the Procedural Gateway Administration Utility to guide you in designing your own PGAU statements.

This appendix contains these sample PGAU statements:

G.1 Sample PGAU DEFINE DATA Statements

DEFINE DATA EMPNO
       PLSDNAME (EMPNO)
       USAGE (PASS)
       LANGUAGE (IBMVSCOBOLII)
       (
       01 EMP-NO PIC X(6).
       ); 

DEFINE DATA EMPREC
       PLSDNAME (DCLEMP)
       USAGE (PASS)
       LANGUAGE (IBMVSCOBOLII)
       INFILE("emp.cob");

where the file emp.cob contains the following:

01 DCLEMP.
   10 EMPNO              PIC X(6).
   10 FIRSTNME.  
     49 FIRSTNME-LEN     PIC S9(4) USAGE COMP.
     49 FIRSTNME-TEXT    PIC X(12).
   10 MIDINIT            PIC X(1).
   10 LASTNAME.
     49 LASTNAME-LEN     PIC S9(4) USAGE COMP.
     49 LASTNAME-TEXT    PIC X(15).
   10 WORKDEPT           PIC X(3).
   10 PHONENO            PIC X(4).
   10 HIREDATE           PIC X(10).
   10 JOB                PIC X(8).
   10 EDLEVEL            PIC S9(4) USAGE COMP.
   10 SEX                PIC X(1)
   10 BIRTHDATE          PIC X(10).
   10 SALARY             PIC S9999999V99 USAGE COMP-3.
   10 BONUS              PIC S9999999V99 USAGE COMP-3.
   10 COMM               PIC S9999999V99 USAGE COMP-3.

DEFINE DATA DB2INFO
       PLSDNAME (DB2)
       USAGE (PASS)
       LANGUAGE (IBMVSCOBOLII)
       INFILE("db2.cob");

where the file db2.cob contains the following:

01  DB2.
    05 SQLCODE          PIC S9(9) COMP-4.
    05 SQLERRM.
      49 SQLERRML       PIC S9(4) COMP-4.
      49 SQLERRT        PIC X(70).
    05 DSNERRM.
      49 DSNERRML       PIC S9(4) COMP-4.
      49 DSNERRMT       PIC X(240) OCCURS 8 TIMES
                                       INDEXED BY ERROR-INDEX

G.2 Sample PGAU DEFINE CALL Statements

DEFINE CALL DB2IMAIN
       PKGCALL (PGADB2I_MAIN)
       PARMS ( (EMPNO      IN ),
               (EMPREC     OUT)  );
DEFINE CALL DB2IDIAG
       PKGCALL (PGADB2I_DIAG)
       PARMS ( (DB2INFO    OUT)  );

G.3 Sample PGAU DEFINE TRANSACTION Statement

DEFINE TRANSACTION DB2I
   CALL (   DB2IMAIN,
            DB2IDIAG   )
   SIDEPROFILE(CICSPROD)
   TPNAME(DB2I)
   LOGMODE(ORAPLU62)
   SYNCLEVEL(0)
   NLS_LANGUAGE("AMERICAN_AMERICA.WE8EBCDIC37C");

G.4 Sample PGAU GENERATE Statement

GENERATE DB2I
   PKGNAME(PGADB2I)
   OUTFILE("pgadb2i");

A user's high-level application now uses this TIP by referencing these PL/SQL datatypes passed and returned.

Table G-1 provides a description of the TIP user transaction datatypes in package name PGADB2I:

Table G-1 TIP User Transaction Datatypes Used in Package Name PGADB2I

Datatype Description
PGADB2I.EMPNO is a PL/SQL variable corresponding to COBOL EMPNO.
PGADB2I.DCLEMP Which is a PL/SQL RECORD corresponding to COBOL DCLEMP.
PGADB2I.DB2 Which is a PL/SQL RECORD corresponding to COBOL DB2INFO.

and the application calls:

PGADB2I.PGADB2I_INIT(trannum);
PGADB2I.PGADB2I_MAIN( trannum, empno, emprec );
PGADB2I.PGADB2I_DIAG( trannum, db2 );
PGADB2I.PGADB2I_TERM(trannum, termtype);

G.5 Sample Implicit Versioning Definitions

The examples are sample definitions of DATA, CALL, and TRANSACTION entries with implicit versioning.

This example creates a new DATA version of 'EMPREC' because 'EMPREC' DATA was defined previously:

DEFINE DATA EMPREC
       PLSDNAME (NEWEMP)
       USAGE (PASS)
       LANGUAGE (IBMVSCOBOLII)
       INFILE("emp2.cob");

where the file emp2.cob contains the following:

01 NEWEMP.
    10 EMPNO                   PIC X(6).
    10 FIRSTNME.
      49 FIRSTNME-LEN          PIC S9(4) USAGE COMP.
      49 FIRSTNME-TEXT         PIC X(12).
    10 MIDINIT                 PIC X(1).
    10 LASTNAME.
      49 LASTNAME-LEN          PIC S9(4) USAGE COMP.
      49 LASTNAME-TEXT         PIC X(15).
    10 WORKDEPT                PIC X(3).
    10 PHONENO                 PIC X(3).
    10 HIREDATE                PIC X(10).
    10 JOB                     PIC X(8).
    10 EDLEVEL                 PIC S9(4) USAGE COMP.
    10 SEX                     PIC X(1).
    10 BIRTHDATE               PIC X(10).
    10 SALARY                  PIC S9999999V99 USAGE COMP-3.
    10 BONUS                   PIC S9999999V99 USAGE COMP-3.
    10 COMM                    PIC S9999999V99 USAGE COMP-3.
    10 YTD.
      15 SAL                   PIC S9(9)V99 USAGE COMP-3.
      15 BON                   PIC S9(9)V99 USAGE COMP-3.
      15 COM                   PIC S9(9)V99 USAGE COMP-3.

To determine which DATA version number was assigned, this SQL query can be issued:

SELECT MAX(pd.version)
       FROM pga_data pd
       WHERE pd.dname = 'EMPREC';

To determine additional information related to the updated version of 'EMPREC' this query can be used:

SELECT *
       FROM pga_data pd
       WHERE pd.dname = 'EMPREC';

This example creates a new CALL version of 'DB2IMAIN' because the 'DB2IMAIN' CALL was defined previously:

DEFINE CALL DB2IMAIN
       PKGCALL (PGADB2I_MAIN)
       PARMS ( (EMPNO      IN                   ),
               (EMPREC     OUT   VERSION(ddddd) )  );

where ddddd is the version number of the EMPREC DATA definition queried after the previous DEFINE DATA updated EMPREC.

To determine which call version number was assigned, this SQL query can be issued:

SELECT MAX(pc.version)
       FROM pga_call pc
       WHERE pc.cname = 'DB2IMAIN';

To determine additional information related to the updated version of 'DB2IMAIN' this query can be used:

SELECT *
        FROM pga_call pc
        WHERE pc.cname = 'DB2IMAIN';

The DEFINE TRANSACTION example creates a new TRANSACTION version of 'DB2I' because the 'DB2I' TRANSACTION was defined previously. The essential difference of the new version of the DB2I transaction is that the first call uses a new PL/SQL record format "NEWEMP" (which corresponds to the COBOL NEWEMP format) to query the employee data.


Caution:

Record format changes like that discussed above must be synchronized with the requirements of the remote transaction program. Changes to the PGA TIP alone result in errors. A new remote transaction program with the corequisite changes could be running on a separate CICS system and started through the change from "CICSPROD" to "CICSTEST" in the SIDEPROFILE parameter below.

DEFINE TRANSACTION DB2I
       CALL (  DB2IMAIN   VERSION (ccccc),
               DB2IDIAG )
       SIDEPROFILE(CICSTEST)
       TPNAME(DB2I)
       LOGMODE(ORAPLU62)
       SYNCLEVEL(0)
       NLS_LANGUAGE("AMERICAN_AMERICA.WE8EBCDIC37C");

where ccccc is the version number of the DB2IMAIN CALL definition queried after the previous DEFINE CALL updated DB2IMAIN.

There are two versions of the DB2I transaction definition in the PGA DD. The original uses the old "DCLEMP" record format and starts transaction "DB2I" on the production CICS system. The latest uses the "NEWEMP" record format and starts transaction "DB2I" on the test CICS system.

To determine which transaction version number was assigned, this SQL query can be issued:

SELECT MAX(pt.version)
  FROM pga_trans pt
  WHERE pt.tname = 'DB2I';

To determine additional information related to the updated version of 'DB2I' this query can be used:

SELECT *
  FROM pga_trans pt
  WHERE pt.tname = 'DB2I';

This example generates a new package using the previously defined new versions of the TRANSACTION, CALL, and DATA definitions:

GENERATE DB2I
VERSION(ttttt)
PKGNAME(NEWDB2I)
OUTFILE("pgadb2i");

where ttttt is the version number of the DB2I TRANSACTION definition queried after the previous DEFINE TRANSACTION updated DB2I.

Note that the previous PL/SQL package files pgadb2i.pkh and pgadb2i.pkb are overwritten. To keep the new package separate, change the output file specification. For example:

GENERATE DB2I
VERSION(ttttt)
PKGNAME(NEWDB2I)
OUTFILE("newdb2i");

A user's high-level application now uses this TIP by referencing the PL/SQL datatypes passed and returned.

Table G-2 provides a description of the TIP user transaction datatypes in package name NEWDB2I:

Table G-2 TIP User Transaction Datatypes for Package Name NEWDB2I

Datatype Description
NEWDB2I.EMPNO Is a PL/SQL variable corresponding to COBOL EMPNO.
NEWDB2I.NEWEMP Is a PL/SQL RECORD corresponding to COBOL NEWEMP.
NEWDB2I.DB2 Is a PL/SQL RECORD corresponding to COBOL DB2.

and the application calls:

NEWDB2I.PGADB2I_INIT(trannum);
NEWDB2I.PGADB2I_MAIN( trannum, empno, newemp );
NEWDB2I.PGADB2I_DIAG( trannum, db2 );
NEWDB2I.PGADB2I_TERM(trannum, termtype);

G.6 Sample PGAU REDEFINE DATA Statements

Single-field redefinition in which EDLEVEL USAGE becomes COMP-3:

REDEFINE DATA EMPREC
         PLSDNAME(DCLEMP)
         LANGUAGE(IBMVSCOBOLII)
         FIELD(EDLEVEL)
         PLSFNAME(PLSRECTYPE)
       (
       10 EDLEVEL  PIC S9(4) USAGE IS COMP-3.
       );

By default, this redefines the latest version of EMPREC which implicitly affects the latest call and transaction definitions which refer to it.

Sample multi-field redefinition in which the employee's first and last name fields are expanded and the employee's middle initial is removed.

REDEFINE DATA EMPREC
         VERSION(1)
         PLSDNAME(DCLEMP)
         LANGUAGE(IBMVSCOBOLII)
         INFILE("emp1.cob");

where the file emp1.cob contains the following:

01 DCLEMP.
  10 EMPNO                     PIC X(6).
  10 FIRSTNME.
       49 FIRSTNME-LEN         PIC S9(4) USAGE COMP.
       49 FIRSTNME-TEXT        PIC X(15).
  10 LASTNAME.
       49 LASTNAME-LEN         PIC S9(4) USAGE COMP.
       49 LASTNAME-TEXT        PIC X(20).
  10 WORKDEPT                  PIC X(3).
  10 PHONENO                   PIC X(4).
  10 HIREDATE                  PIC X(10).
  10 JOB                       PIC X(8).
  10 EDLEVEL                   PIC S9(4) USAGE COMP.
  10 SEX                       PIC X(1).
  10 BIRTHDATE                 PIC X(10).
  10 SALARY                    PIC S9999999V99 USAGE COMP-3.
  10 BONUS                     PIC S9999999V99 USAGE COMP-3.
  10 COMM                      PIC S9999999V99 USAGE COMP-3.
    

The assumption is that version 1 of the data definition for 'EMPREC' is to be redefined. This causes a redefinition of the first 'EMPREC' sample data definition without changing the version number. Thus, existing call and transaction definitions which referenced version 1 of 'EMPREC' automatically reflect the changed 'EMPREC'. This change becomes effective when a TIP is next generated for a transaction that references the call which referenced version 1 of 'EMPREC'.

This implicitly affects both versions of the transaction because both refer to EMPREC in the second call to update the employee data.

G.7 Sample PGAU UNDEFINE Statements

These samples illustrate the deletion of a specific version of a definition which has multiple versions, followed by deletion of all versions of a specific named definition.

Deletion of DATA Definitions:

UNDEFINE DATA EMPREC VERSION (ddddd);
UNDEFINE DATA EMPREC VERSION (ALL);
UNDEFINE CALL DB2IMAIN VERSION (ccccc);
UNDEFINE CALL DB2IMAIN VERS (all);
UNDEFINE TRANSACTION DB2I vers (ttttt);
UNDEFINE TRANSACTION DB2I vers (all);

Note that the previous UNDEFINE statements leave the DATA definition for EMPNO and the CALL definition for DB2IDIAG in the PGA DD.

G.8 Sample PGAU REPORT Output

PGAU> report transaction db2i;
 
    /* Transaction DB2I version 298 created by PGAADMIN on FEB 14, 2005 17:38.02 */
       DEFINE TRANSACTION DB2I
                          CALL (DB2IMAIN VERSION(672),
                                DB2IDIAG VERSION(673))
                          SIDEPROFILE(CICSPGA)
                          TPNAME(DB2I)
                          LOGMODE(ORAPLU62)
                          SYNCLEVEL(0)
                          NLS_LANGUAGE(american_america.we8ebcdic37c);
 
 
  PGAU> report transaction db2i with calls;
 
     /* Call DB2IMAIN version 672 created by PGAADMIN on FEB 14, 2005 17:38.01 */
      DEFINE CALL DB2IMAIN PKGCALL(PGADB2I_MAIN)
                  PARMS(EMPNO IN VERSION(638),
                        EMPREC OUT VERSION(639));
 
     /* Call DB2IDIAG version 673 created by PGAADMIN on FEB 14, 2005 17:38.02 */
      DEFINE CALL DB2IDIAG PKGCALL(PGADB2I_DIAG)
                  PARMS(DB2INFO OUT VERSION(640));

    /* Transaction DB2I version 298 created by PGAADMIN on FEB 14, 2005 17:38.02 */
       DEFINE TRANSACTION DB2I
                          CALL (DB2IMAIN VERSION(672),
                                DB2IDIAG VERSION(673))
                          SIDEPROFILE(CICSPGA)
                          TPNAME(DB2I)
                          LOGMODE(ORAPLU62)
                   SYNCLEVEL(0)
                   NLS_LANGUAGE(american_america.we8ebcdic37c);
 
 
  PGAU> report transaction db2i with data;
 
     /* Data Definition EMPNO version 638 created/updated by PGAADMIN */ 
     /* on FEB 14, 2005 17:02.58                         */
      DEFINE DATA EMPNO LANGUAGE(IBMVSCOBOLII) USAGE(PASS)
  ( 
             01 EMPNO PICTURE IS X(6).
  );
 
 
     /* Data Definition EMPREC version 639 created/updated by PGAADMIN */
     /* on FEB 14,2005 17:02.59                                               */
      DEFINE DATA EMPREC PLSDNAME(DCLEMP) LANGUAGE(IBMVSCOBOLII) USAGE(PASS)
      (
             01 DCLEMP.
              10 EMPNO PICTURE IS X(6).
              10 FIRSTNME.
           * Field FIRSTNME-LEN has PL/SQL name FIRSTNME_LEN
                49 FIRSTNME-LEN USAGE IS COMP PICTURE IS S9(4).
           * Field FIRSTNME-TEXT has PL/SQL name FIRSTNME_TEXT
                49 FIRSTNME-TEXT PICTURE IS X(12).
              10 MIDINIT PICTURE IS X(1).
              10 LASTNAME.
           * Field LASTNAME-LEN has PL/SQL name LASTNAME_LEN
                49 LASTNAME-LEN USAGE IS COMP PICTURE IS S9(4).
           * Field LASTNAME-TEXT has PL/SQL name LASTNAME_TEXT
                49 LASTNAME-TEXT PICTURE IS X(15).
              10 WORKDEPT PICTURE IS X(3).
              10 PHONENO PICTURE IS X(4).
              10 HIREDATE PICTURE IS X(10).
              10 JOB PICTURE IS X(8).
              10 EDLEVEL USAGE IS COMP PICTURE IS S9(4).
              10 SEX PICTURE IS X(1).
              10 BIRTHDATE PICTURE IS X(10).
              10 SALARY USAGE IS COMP-3 PICTURE IS S9999999V99.
              10 BONUS USAGE IS COMP-3 PICTURE IS S9999999V99.
              10 COMM USAGE IS COMP-3 PICTURE IS S9999999V99.
  );
 
     /* Call DB2IMAIN version 672 created by PGAADMIN on FEB 14, 2005 17:38.01*/
      DEFINE CALL DB2IMAIN PKGCALL(PGADB2I_MAIN)
                  PARMS(EMPNO IN VERSION(638),
                        EMPREC OUT VERSION(639));
 
     /*Data 2005 DB2INFO version 640 created/updated by PGAADMIN */
     /* on FEB 14,2005 17:02.00*/
      DEFINE DATA DB2INFO PLSDNAME(DB2) LANGUAGE(IBMVSCOBOLII) USAGE(PASS)
  (
            01 DB2.
              05 SQLCODE USAGE IS COMP-4 PICTURE IS S9(9).
              05 SQLERRM.
                49 SQLERRML USAGE IS COMP-4 PICTURE IS S9(4).
                49 SQLERRMT PICTURE IS X(70).
              05 DSNERRM.
                49 DSNERRML USAGE IS COMP-4 PICTURE IS S9(4).
                49 DSNERRMT PICTURE IS X(240) OCCURS 8 TIMES.
  ); 
     
     /* Call DB2IDIAG version 673 created by PGAADMIN on FEB 14, 2005 17:38.02*/
      DEFINE CALL DB2IDIAG PKGCALL(PGADB2I_DIAG)
                  PARMS(DB2INFO OUT VERSION(640));
 
     /* Transaction DB2I version 298 created by PGAADMIN on  FEB 14, 2005 17:38.02*/
       DEFINE TRANSACTION DB2I
                          CALL (DB2IMAIN VERSION(672),
                                DB2IDIAG VERSION(673))
                          SIDEPROFILE(CICSPGA)
                          TPNAME(DB2I)
                          LOGMODE(ORAPLU62)
                          SYNCLEVEL(0)
                          NLS_LANGUAGE(american_america.we8ebcdic37c);

  PGAU> report transaction db2i with data with debug;
 
     /* Data Definition EMPNO version 638 created/updated by PGAADMIN */
     /* on FEB 14, 2005 17:02.58        */
      DEFINE DATA EMPNO /* d#=565 */ LANGUAGE(IBMVSCOBOLII) USAGE(PASS)
      (
             * f#=4005
             01 EMPNO PICTURE IS X(6).
      );
 
 
     /* Data Definition EMPREC version 639 created/updated by PGAADMIN */
     /* on FEB 14,2005 17:02.59                                   */
      DEFINE DATA EMPREC /* d#=566 */ PLSDNAME(DCLEMP) LANGUAGE(IBMVSCOBOLII) USAGE(PASS)
      (
           * f#=4006
             01 DCLEMP.
           * f#=4007
              10 EMPNO PICTURE IS X(6).
           * f#=4008
              10 FIRSTNME.
           * f#=4009
           * Field FIRSTNME-LEN has PL/SQL name FIRSTNME_LEN
                49 FIRSTNME-LEN USAGE IS COMP PICTURE IS S9(4).
           * f#=4010
           * Field FIRSTNME-TEXT has PL/SQL name FIRSTNME_TEXT
                49 FIRSTNME-TEXT PICTURE IS X(12).
           * f#=4011
              10 MIDINIT PICTURE IS X(1).
           * f#=4012
              10 LASTNAME.
           * f#=4013
           * Field LASTNAME-LEN has PL/SQL name LASTNAME_LEN
                49 LASTNAME-LEN USAGE IS COMP PICTURE IS S9(4).
           * f#=4014
           * Field LASTNAME-TEXT has PL/SQL name LASTNAME_TEXT
                49 LASTNAME-TEXT PICTURE IS X(15).
           * f#=4015
              10 WORKDEPT PICTURE IS X(3).
           * f#=4016
              10 PHONENO PICTURE IS X(4).
           * f#=4017
              10 HIREDATE PICTURE IS X(10).
           * f#=4018
              10 JOB PICTURE IS X(8).
           * f#=4019
              10 EDLEVEL USAGE IS COMP PICTURE IS S9(4).
           * f#=4020
              10 SEX PICTURE IS X(1).
           * f#=4021
              10 BIRTHDATE PICTURE IS X(10).
           * f#=4022
              10 SALARY USAGE IS COMP-3 PICTURE IS S9999999V99.
           * f#=4023
              10 BONUS USAGE IS COMP-3 PICTURE IS S9999999V99.
           * f#=4024
              10 COMM USAGE IS COMP-3 PICTURE IS S9999999V99.
  );
 
     /* Call DB2IMAIN version 672 created by PGAADMIN on FEB 14, 2005 17:38.01*/
      DEFINE CALL DB2IMAIN PKGCALL(PGADB2I_MAIN) /* c#=672 */
                  PARMS(EMPNO IN VERSION(638) /* d#=565 */,
                        EMPREC OUT VERSION(639) /* d#=566 */);
 
     /*Data Definition DB2INFO version 640 created/updated by PGAADMIN */
     /* on FEB 14,2005 17:02.00*/
      DEFINE DATA DB2INFO /* d#=567 */ PLSDNAME(DB2) LANGUAGE(IBMVSCOBOLII) USAGE(PASS)
      (
           * f#=4025
            01 DB2.
           * f#=4026
              05 SQLCODE USAGE IS COMP-4 PICTURE IS S9(9).
           * f#=4027
              05 SQLERRM.
           * f#=4028
                49 SQLERRML USAGE IS COMP-4 PICTURE IS S9(4).
           * f#=4029
                49 SQLERRMT PICTURE IS X(70).
           * f#=4030
              05 DSNERRM.
           * f#=4031
                49 DSNERRML USAGE IS COMP-4 PICTURE IS S9(4).
           * f#=4032
                49 DSNERRMT PICTURE IS X(240) OCCURS 8 TIMES.
      ); 
     
     /* Call DB2IDIAG version 673 created by PGAADMIN on FEB 14, 2005 17:38.02*/
      DEFINE CALL DB2IDIAG PKGCALL(PGADB2I_DIAG) /* c#=673 */
                  PARMS(DB2INFO OUT VERSION(640) /* d#=567 */); 

     /* Transaction DB2I version 298 created by PGAADMIN on  FEB 14, 2005 17:38.02*/
       DEFINE TRANSACTION DB2I /* t#=298 */
                          CALL (DB2IMAIN VERSION(672) /* c#=672 */,
                                DB2IDIAG VERSION(673) /* c#=673 */)
                          SIDEPROFILE(CICSPGA) /* a#=2 */
                          TPNAME(DB2I) /* a#=3 */
                          LOGMODE(ORAPLU62) /* a#=4 */
                          SYNCLEVEL(0) /* a#=6 */
                          NLS_LANGUAGE(american_america.we8ebcdic37c) /* a#=7 */ ;

  PGAU> 

  PGAU>  spool off

G.9 Sample TIP Content Documentation

PG4APPC TIP pgadb2i contents:
 
TIP generation date: 15-FEB-05 10:14:17
TIP generation user: PGAADMIN
PG DD version:       10.2.0.1.0
PGAU  version:       10.2.0.1.0
 
/*------------------------------------------------------------------*/
/*  TIP user-transaction definition                                 */
/*------------------------------------------------------------------*/
   remote host transaction program name: DB2I
   remote host transaction sync level:   0
   remote host application LU name:      
   remote host application mode entry:   ORAPLU62
   gateway CPI-C Side Profile name:      CICSPGA
 
   remote host environment: IBM370
   remote host NLS name:    AMERICAN_AMERICA.WE8EBCDIC37C
 
/*------------------------------------------------------------------*/
/*  TIP user-transaction-data type declarations                     */
/*------------------------------------------------------------------*/
 
   PL/SQL Variable name      PL/SQL Variable Type
 
   TYPE FIRSTNME_Typ is RECORD (
       FIRSTNME_LEN          NUMBER(4,0),
       FIRSTNME_TEXT         CHAR(12));

   TYPE LASTNAME_Typ is RECORD (
       LASTNAME_LEN          NUMBER(4,0),
       LASTNAME_TEXT         CHAR(15));

   TYPE DCLEMP_Typ is RECORD (
       EMPNO                 CHAR(6),
       FIRSTNME              FIRSTNME_Typ,
       MIDINIT               CHAR(1),
       LASTNAME              LASTNAME_Typ,
       WORKDEPT              CHAR(3),
       PHONENO               CHAR(4),
       HIREDATE              CHAR(10),
       JOB                   CHAR(8),
       EDLEVEL               NUMBER(4,0),
       SEX                   CHAR(1),
       BIRTHDATE             CHAR(10),
       SALARY                NUMBER(9,2),
       BONUS                 NUMBER(9,2),
       COMM                  NUMBER(9,2));

   TYPE SQLERRM_Typ is RECORD (
       SQLERRML              NUMBER(4,0),
       SQLERRMT              CHAR(70));

   DSNERRMT_Key BINARY_INTEGER;

   TYPE DSNERRMT_Tbl is TABLE of CHAR(240)
        INDEX by BINARY_INTEGER;

   TYPE DSNERRM_Typ is RECORD (
       DSNERRML              NUMBER(4,0),
       DSNERRMT              DSNERRMT_Tbl);

   TYPE DB2_Typ is RECORD (
       SQLCODE               NUMBER(9,0),
       SQLERRM               SQLERRM_Typ,
       DSNERRM               DSNERRM_Typ);

/*------------------------------------------------------------------*/
/*  TIP default functions                                           */
/*------------------------------------------------------------------*/
 
   TYPE override_Typ IS RECORD (
             tranname        VARCHAR2(2000), /* Transaction Program */
             transync        BINARY_INTEGER, /* RESERVED            */
             trannls         VARCHAR2(50),   /* RESERVED            */
             oltpname        VARCHAR2(2000), /* Logical Unit        */
             oltpmode        VARCHAR2(2000), /* LOG Mode Entry      */
             netaddr         VARCHAR2(2000));/* Side Profile        */
 
   FUNCTION pgadb2i_init(             /* init standard   */
             tranuse  IN OUT BINARY_INTEGER)
             RETURN INTEGER;
 
   FUNCTION pgadb2i_init(             /* init override   */
             tranuse  IN OUT BINARY_INTEGER,
             override IN     override_Typ)
             RETURN INTEGER;
 
   FUNCTION pgadb2i_init(             /* init diagnostic */
             tranuse  IN OUT BINARY_INTEGER,
             tipdiag  IN     CHAR)
             RETURN INTEGER;
 
   FUNCTION pgadb2i_init(             /* init over-diag  */
             tranuse  IN OUT BINARY_INTEGER,
             override IN     override_Typ,
             tipdiag  IN     CHAR)
             RETURN INTEGER;
 
   FUNCTION pgadb2i_term(             /* terminate       */
             tranuse  IN     BINARY_INTEGER,
             termtype IN     BINARY_INTEGER)
             RETURN INTEGER;
 
/*------------------------------------------------------------------*/
/*  TIP user-transaction-call function definitions                  */
/*------------------------------------------------------------------*/
   FUNCTION PGADB2I_MAIN(
             tranuse     IN      BINARY_INTEGER,
             EMPNO            IN      CHAR,
             DCLEMP           OUT     DCLEMP_Typ)
             RETURN INTEGER;

   FUNCTION PGADB2I_DIAG(
             tranuse     IN      BINARY_INTEGER,
             DB2              OUT     DB2_Typ)
             RETURN INTEGER;

/*------------------------------------------------------------------*/
/*  TIP field variables                                             */
/*------------------------------------------------------------------*/
 
   PL/SQL name                                    PL/SQL type
 
   EMPNO                                              CHAR(6)

   DCLEMP                                             DCLEMP_Typ
   DCLEMP.EMPNO                                       CHAR(6)
   DCLEMP.FIRSTNME                                    FIRSTNME_Typ
   DCLEMP.FIRSTNME.FIRSTNME_LEN                       NUMBER(4,0)
   DCLEMP.FIRSTNME.FIRSTNME_TEXT                      CHAR(12)
   DCLEMP.MIDINIT                                     CHAR(1)
   DCLEMP.LASTNAME                                    LASTNAME_Typ
   DCLEMP.LASTNAME.LASTNAME_LEN                       NUMBER(4,0)
   DCLEMP.LASTNAME.LASTNAME_TEXT                      CHAR(15)
   DCLEMP.WORKDEPT                                    CHAR(3)
   DCLEMP.PHONENO                                     CHAR(4)
   DCLEMP.HIREDATE                                    CHAR(10)
   DCLEMP.JOB                                         CHAR(8)
   DCLEMP.EDLEVEL                                     NUMBER(4,0)
   DCLEMP.SEX                                         CHAR(1)
   DCLEMP.BIRTHDATE                                   CHAR(10)
   DCLEMP.SALARY                                      NUMBER(9,2)
   DCLEMP.BONUS                                       NUMBER(9,2)
   DCLEMP.COMM                                        NUMBER(9,2)

   DB2                                                DB2_Typ
   DB2.SQLCODE                                        NUMBER(9,0)
   DB2.SQLERRM                                        SQLERRM_Typ
   DB2.SQLERRM.SQLERRML                               NUMBER(4,0)
   DB2.SQLERRM.SQLERRMT                               CHAR(70)
   DB2.DSNERRM                                        DSNERRM_Typ
   DB2.DSNERRM.DSNERRML                               NUMBER(4,0)
   DB2.DSNERRM.DSNERRMT(DSNERRMT_Key)                 CHAR(240)

G.10 Sample TIP Trace Output

SQL> set serveroutput on size 20000
SQL> exec db2idriv('000340','11110000');
Name                                                                            
JASON R GOUNOT                                                                  
Sex Birthdate  Edlevel                                                          
M   05/17/1926    16                                                            
Empno  Dept   Job    Phone  Hiredate    Salary      Bonus   Commission          
000340 E21  FIELDREP  5698 05/05/1947   23840.00     500.00    1907.00          
 
PL/SQL procedure successfully completed.
 
SQL> exec rtrace('PGADB2I');
UTL_PG warnings enabled                                                         
function entry/exit trace enabled                                               
data conversion trace enabled                                                   
gateway exchange trace enabled                                                  
enter PGADB2I_init diagnostic                                                   
time 22-MAR-1995 13:54:47                                                       
from PGAADMIN                                                                   
exit PGADB2I_init diagnostic                                                    
enter PGADB2I_MAIN                                                              
enter updsndlt                                                                  
sendcnt, parmlenm => 1, 6                                                       
sendllst(sendcnt) => 0                                                          
sendllst(sendcnt) => 6                                                          
exit updsndlt                                                                   
EMPNO CHAR(6)                                                                   
at pos, off, len => 1, 1, 6                                                     
enter updrcvlt                                                                  
recvcnt, parmlenm => 1, 91                                                      
recvllst(recvcnt) => 0                                                          
recvllst(recvcnt) => 91                                                         
exit updrcvlt                                                                   
enter dataxfer                                                                  
tranuse => 2                                                                    
enter bldsxfrl                                                                  
sendmax => 1                                                                    
sendbufl => 6                                                                   
sxfrllst => 0000000100000006                                                    
exit bldsxfrl                                                                   
recvmax => 1                                                                    
enter bldrxfrl                                                                  
recvbufl => 91                                                                  
rxfrllst => 000000010000005B                                                    
exit bldrxfrl                                                                   
enter xfersr                                                                    
tranuse  => 2                                                                   
trancid  => 200E8E08200F2E1800000000                                            
sendbufl => 6                                                                   
sxfrllst => 000000010000000600000000                                            
recvbufl => 91                                                                  
rxfrllst => 000000010000005B00000000                                            
recvbufl => 91                                                                  
rxfrllst => 000000010000005B00000000                                            
exit xfersr                                                                     
exit dataxfer                                                                   
enter getrcvla                                                                  
recvcnt => 1                                                                    
recvalst(recvcnt) => 0                                                          
recvalst(recvcnt) => 91                                                         
numplen => 91                                                                   
exit getrcvla                                                                   
DCLEMP.EMPNO CHAR(6)                                                            
at pos, off, len => 1, 1, 6                                                     
DCLEMP.FIRSTNME.FIRSTNME_LEN NUMBER(4,0)                                        
at pos, off, len => 7, 7, 2                                                     
DCLEMP.FIRSTNME.FIRSTNME_TEXT CHAR(12)                                          
at pos, off, len => 9, 9, 12                                                    
DCLEMP.MIDINIT CHAR(1)                                                          
at pos, off, len => 21, 21, 1                                                   
DCLEMP.LASTNAME.LASTNAME_LEN NUMBER(4,0)                                        
at pos, off, len => 22, 22, 2                                                   
DCLEMP.LASTNAME.LASTNAME_TEXT CHAR(15)                                          
at pos, off, len => 24, 24, 15                                                  
DCLEMP.WORKDEPT CHAR(3)                                                         
at pos, off, len => 39, 39, 3                                                   
DCLEMP.PHONENO CHAR(4)                                                          
at pos, off, len => 42, 42, 4                                                   
DCLEMP.HIREDATE CHAR(10)                                                        
at pos, off, len => 46, 46, 10                                                  
DCLEMP.JOB CHAR(8)                                                              
at pos, off, len => 56, 56, 8                                                   
DCLEMP.EDLEVEL NUMBER(4,0)                                                      
at pos, off, len => 64, 64, 2                                                   
DCLEMP.SEX CHAR(1)                                                              
at pos, off, len => 66, 66, 1                                                   
DCLEMP.BIRTHDATE CHAR(10)                                                       
at pos, off, len => 67, 67, 10                                                  
DCLEMP.SALARY NUMBER(9,2)                                                       
at pos, off, len => 77, 77, 5                                                   
DCLEMP.BONUS NUMBER(9,2)                                                        
at pos, off, len => 82, 82, 5                                                   
DCLEMP.COMM NUMBER(9,2)                                                         
at pos, off, len => 87, 87, 5                                                   
exit PGADB2I_MAIN                                                               
enter PGADB2I_term                                                              
exit PGADB2I_term                                                               
time 22-MAR-1995 13:54:49                                                       
from PGAADMIN                                                                   
no more PGADB2I trace messages                                                  
 
PL/SQL procedure successfully completed.
 
SQL> spool off

G.11 Sample TIP Output

SQL> exec db2idriv('000340');
Name
JASON R GOUNOT
Sex Birthdate  Edlevel
M   05/17/1926    16
Empno  Dept   Job    Phone  Hiredate    Salary      Bonus   Commission
000340 E21  FIELDREP  5698 05/05/1947   23840.00     500.00    1907.00
 
PL/SQL procedure successfully completed.
 
SQL> spool off