Oracle Procedural Gateway® for APPC User's Guide 10g Release 2 (10.2) for UNIX Part Number B16210-01 |
|
|
View PDF |
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:
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
DEFINE CALL DB2IMAIN PKGCALL (PGADB2I_MAIN) PARMS ( (EMPNO IN ), (EMPREC OUT) ); DEFINE CALL DB2IDIAG PKGCALL (PGADB2I_DIAG) PARMS ( (DB2INFO OUT) );
DEFINE TRANSACTION DB2I CALL ( DB2IMAIN, DB2IDIAG ) SIDEPROFILE(CICSPROD) TPNAME(DB2I) LOGMODE(ORAPLU62) SYNCLEVEL(0) NLS_LANGUAGE("AMERICAN_AMERICA.WE8EBCDIC37C");
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);
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);
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.
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.
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
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)
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