1
Introduction to Oracle Procedural Gateway for APPC

This Oracle Procedural Gateway for APPC User's Guide is intended for users of the the following UNIX-based platforms:

• HP9000 Series 700/800

• Sun Solaris, and

• IBM RS/6000

The Oracle Procedural Gateway for APPC allows you to initiate transaction program execution on remote online transaction processors (OLTPs). The Oracle Procedural Gateway for APPC provides Oracle applications seamless access to IBM mainframe data and services through remote procedural call (RPC) processing.

Read this chapter to learn more about the architecture, uses and features of the Oracle Procedural Gateway for APPC.

This chapter contains the following sections:

• Overview

• Terms

• Examples and Sample Files

• Features

• Architecture

• Transaction Types

Overview

The Oracle Procedural Gateway for APPC extends the remote procedural call (RPC) facilities available with the Oracle® server. The gateway enables any client application to use PL/SQL^TM to request execution of a remote transaction program residing on a host. The gateway provides RPC (remote procedural call) processing to systems using the SNA APPC (Advanced Program-to-Program Communication) protocol. This architecture allows efficient access to invaluable data and transactions available on the IBM mainframe.

The use of a generic and standard protocol, APPC, allows the gateway to access a multitude of systems. The gateway can communicate with virtually any APPC-enabled system, including IBM Corporation's CICS on any platform, IBM Corporation's IMS and APPC/MVS, and CA's IDMS. These transaction monitors provide access to a broad range of systems, allowing the gateway to access many datastores, including VSAM, DB2 (static SQL), IMS, IDMS, ADABAS, and others.

The gateway requires no Oracle software on the remote system. Thus, the gateway uses existing transactions with little or no programming effort on the remote system. The gateway helps leverage your investment in existing applications and gives you the ability to move toward new distributed environments.

Terms

The following terms are used throughout this guide as follows:

gateway initialization file

This file is known as initsid.ora and it contains parameters that govern the operation of the gateway. Refer to Appendix A of the Oracle Procedural Gateway for APPC Installation and Configuration Guide for your platform more information.

gateway remote procedure

Oracle Procedural Gateway for APPC provides pre-built remote procedures. In general, functions: the following three remote procedures are used:

• PGAINIT, which initializes transactions,

• PGAXFER, which transfers data, and

• PGATERM, which terminates transactions

Refer to "Remote Procedural Call Functions", as well as to Appendix C, "Gateway RPC Interface" for more information about gateway remote procedures.

Oracle integrating server

This is any Oracle server instance that communicates with the Oracle Procedural Gateway for APPC for purposes of performing remote procedural calls to execute remote transaction programs (RTP). The Oracle integrating server can be on the same machine with the gateway or on a different machine. If it is on a different machine, then Oracle® Net is required on both systems. Refer to Figure 1-1, "Relationship of Gateway and Oracle Integrating Server on UNIX Host" and Figure 1-2, "Oracle Procedural Gateway for APPC Architecture" for a view of the gateway architecture.

OLTP (Online Transaction Processor)

OLTP is any of a number of online transaction processors available from other vendors, including CICS, IMS/TM, and IDMS-DC.

PGA (Procedural Gateway Administration)

PGA is a general reference within this guide to all or most components comprising the Oracle Procedural Gateway for APPC. This term is used when references to a specific product or component are too narrow.

PGAU (Procedural Gateway Administration Utility)

PGAU is the tool that is used to define and generate PL/SQL transaction interface packages (TIPs). Refer to Chapter 2, "Procedural Gateway Administration Utility" for more information about PGAU.

PG DD (Data Dictionary)

This component of the Oracle Procedural Gateway for APPC is a repository of remote host transaction definitions and data definitions. PGAU accesses definitions in the PG DD when generating TIPs. The PG DD has datatype dependencies because it supports the PGAU and is not intended to be directly accessed by the customer. Refer to Appendix A, "Procedural Gateway for APPC Data Dictionary" for a list of PG DD tables.

PGDL (Procedural Gateway Definition Language)

PGDL is the collection of statements used to define transactions and data to the PGAU.

PL/SQL stored procedure specification (PL/SQL package)

This is a precompiled PL/SQL procedure that is stored in the Oracle integrating server.

remote procedural call (RPC)

RPC is a programming call that executes program logic on one system in response to a request from another system. See "gateway remote procedure" for more information, and refer to Appendix C, "Gateway RPC Interface" as well.

remote transaction program (RTP)

A remote transaction program is a customer-written transaction, running under the control of an OLTP, which the user invokes remotely using a PL/SQL procedure. To execute a remote transaction program through the gateway, you must use RPC to execute a PL/SQL program to call the gateway functions.

transaction interface package (TIP)

A TIP is an Oracle PL/SQL package that exists between your application and the remote transaction program. The transaction interface package, or TIP, is a set of PL/SQL stored procedures that invoke the remote transaction program through the gateway. TIPs perform the conversion and reformatting of remote host data using PL/SQL and UTL_RAW/UTL_PG functions.

UTL_RAW PL/SQL package (the UTL_RAW functions)

This component of the Oracle Procedural Gateway for APPC is a series of data conversion functions for PL/SQL RAW variables and remote host data. The types of conversions performed depend on the language of the remote host data. Refer to "UTL_RAW Functions" in Appendix D for more information.

UTL_PG PL/SQL package (the UTL_PG functions)

This component of the Oracle Procedural Gateway for APPC is a series of COBOL numeric data conversion functions.

Refer to "NUMBER_TO_RAW and RAW_TO_NUMBER Argument Values" in Appendix D for supported numeric datatype conversions.

Figure 1-1 illustrates where the terminology discussed above applies to the gateway's architecture.

Figure 1-1 Relationship of Gateway and Oracle Integrating Server on UNIX Host

Text description of the illustration ch10.gif

Examples and Sample Files

Throughout this book, we use a CICS-DB2 inquiry as an example. Transaction Interface Package (TIP) PGADB2I sends an employee number, empno, to a DB2 application and receives an employee record, emprec.

The CICS-DB2 inquiry sample and its associated PGAU commands are documented in Appendix F, "CICS APPC Transaction Samples" and Appendix E, "Administration Utility Samples" respectively. These samples are also available in the $ORACLE_HOME/pg4appc/demo/CICS directory. The sample CICS-DB2 inquiry used as an example in this chapter is in files pgadb2i.pkh and pgadb2i.pkb. Refer to the README.doc file in the same directory for information about installing and using the samples.

Features

Using the Oracle Procedural Gateway for APPC to access data has the following advantages:

• Fast interface

  The gateway is optimized so that remote execution of a program is achieved with minimum network traffic. The interface to the gateway is an optimized PL/SQL stored procedure specification (called the "PL/SQL package") precompiled in the Oracle integrating server. Because there are no additional software layers on the remote system, overhead occurs only when your program executes.

• Location transparency

  Client applications need not be operating system-specific. For example, your application can call a program in a CICS region on MVS. If you move the program to a CICS region on OS/2, then you need not change the application.

• Application transparency

  Users calling applications that execute a remote transaction program are unaware that a request is sent to a host.

• Flexible interface

  You can use the gateway to interface with existing procedural logic or to integrate new procedural logic into an Oracle integrating server environment.

• Oracle server integration

  The integration of the Oracle server with the gateway enables the gateway to benefit from existing and future Oracle features. For example, the gateway can be called from an Oracle stored procedure or database trigger.

• Transactional support

  The gateway and the Oracle integrating server allow remote transaction updates and Oracle server updates to be performed in a coordinated fashion. Oracle two-phase commit protection is extended to remote transactions without any special programming in the Oracle application.

• Wide selection of tools

  The gateway supports any tool or application that supports PL/SQL.

• PL/SQL code generator

  The Oracle Procedural Gateway for APPC provides a powerful development environment, including:

  • a dictionary to store information relevant to the remote transaction

  • a tool to generate the PL/SQL Transaction Interface Package, or TIP

  • a report utility to view the information stored in the gateway dictionary

  • a complete set of tracing and debugging facilities

  • a wide set of samples to demonstrate the use of the product against datastores such as DB2, IMS, IDMS, CICS, and ADABAS

• Site autonomy and security

  The Oracle Procedural Gateway for APPC provides site autonomy, allowing you to do such things as authenticate users. It also provides role-based security compatible with any security package running on your mainframe computer.

• Automatic conversion

  Through the TIP, the following conversions are performed:

  • ASCII to and from EBCDIC

  • remote transaction program datatypes to and from PL/SQL datatypes

  • National Language Support for many languages

Architecture

The architecture of the Oracle Procedural Gateway for APPC consists of three components:

1. The Oracle integrating server

   This server should include the distributed option. The Oracle integrating server is usually installed on the same UNIX system as the Oracle Procedural Gateway for APPC.

   If you install the Oracle integrating server on a system other than the UNIX system on which the gateway is installed, then you must install Oracle Net with the Oracle integrating server and with the gateway. The Oracle integrating server must be capable of connecting to the gateway through any supported Oracle Net protocol. The Oracle integrating server can also be used for non-gateway applications. Refer to the Oracle Procedural Gateway for APPC Installation and Configuration Guide for your platform for a list of Oracle Net protocols supported by the gateway and tools.

2. The gateway

   The Oracle Procedural Gateway for APPC must be installed on a UNIX operating system.

3. An OLTP

   The OLTP must be on a system accessible to UNIX using the SNA APPC protocol. Multiple Oracle integrating servers can access the same gateway. A single UNIX gateway installation can be configured to access more than one OLTP.

The following diagram illustrates the architecture of the Oracle Procedural Gateway for APPC as described above.

Figure 1-2 Oracle Procedural Gateway for APPC Architecture

Text description of the illustration ch11.gif

Starting the Gateway

The gateway is not started in the same way as an Oracle database instance. It has no background processes and does not need a management utility such as Oracle® Enterprise Manager. Each Oracle integrating server user session that accesses a particular gateway creates an independent process on the UNIX system that runs the gateway server and executes SNA functions to communicate with an OLTP.

Communication

All of the communication between the Oracle integrating server, the gateway, and the target system are handled through a Transaction Interface Package (TIP). The TIP is a standard PL/SQL procedural gateway program (PL/SQL package) that provides the following functions:

• declares the PL/SQL variables that can be exchanged with a remote transaction program

• calls the gateway packages that handle the APPC communication for starting the conversation, exchanging data, and terminating the conversation

• handles all datatype conversions between PL/SQL datatypes and the target program datatypes

The Procedural Gateway Administration Utility (PGAU), provided with the gateway, automatically generates the TIP specification.

The gateway is identified to the Oracle integrating server using a database link. The database link is the same construct used to identify other Oracle server databases. The functions in the gateway are referenced in PL/SQL as:

function_name@dblink_name 

Remote Procedural Call Functions

The Oracle Procedural Gateway for APPC provides a set of functions that are invoked by the client through remote procedural call (RPC). These functions direct the gateway to initiate, transfer data with, and terminate remote transaction programs running under an OLTP on another system.

The remote procedural call functions and the correlating commands invoked in the gateway and remote system are shown in the following table.

Application	Oracle TIP	Gateway	Remote System
call tip_init	tip_init call pgainit@gateway	PGAINIT	Initiate program
call tip_main	tip_main call pgaxfer@gateway	PGAXFER	Exchange data
call tip_term	tip_term call pgaterm@gateway	PGATERM	Terminate program

Remote Transaction Initiation

The TIP initiates a connection to the target system using one of the gateway functions, PGAINIT. PGAINIT provides, as input, the required SNA parameters to start a conversation with the target transaction program. These parameters are sent across the SNA network, which returns a conversation identifier to PGAINIT. Any future calls to the target program use the conversation identifier as an INPUT parameter.

Data Exchange

After the conversation is established, a procedural gateway function called PGAXFER can exchange data in the form of input and output variables. PGAXFER sends and receives buffers to and from the target transaction program. The gateway sees a buffer as only a RAW stream of bytes. The TIP that is residing in the Oracle integrating server is responsible for converting the application's PL/SQL datatypes to RAW before sending the buffer to the gateway. It is also responsible for converting RAW to the PL/SQL datatypes before returning the results to the application.

Remote Transaction Termination

When communication with the remote program is complete, the gateway function PGATERM terminates the conversation between the gateway and the target system. PGATERM uses the conversation identifier as an INPUT parameter to request conversation termination.

Transaction Types

The Oracle Procedural Gateway for APPC supports three types of transactions that read data from and write data to remote systems:

• One-shot

  In a one-shot transaction, the application executes initialization, exchanges data and terminates the connection, all in a single call.

• Persistent

  In a persistent transaction, multiple calls to exchange data with the remote transaction can be executed before terminating the conversation.

• Multi-conversational

  In a multi-conversation transaction, the procedural gateway server can be used to exchange multiple records in one call to the remote transaction program. This transaction type is only supported for AIX users of the Oracle Procedural Gateway for APPC.

Refer to "Remote Host Transaction Types" in Chapter 4, "Client Application Development" for more information about transaction types.

The following examples demonstrate the power of the Oracle Procedural Gateway for APPC:

• You can initiate a CICS transaction on the mainframe to retrieve data from a VSAM file for a PC application.

• You can modify and monitor the operation of a remote process control computer.

• You can initiate an IMS/TM transaction that executes static SQL in DB2.

• You can initiate a CICS transaction that returns a large number of records in a single call.

Simple Communication Between Mainframe and Oracle Integrating Server

The Oracle Procedural Gateway for APPC lets you write your own procedures to begin transferring information between the Oracle integrating server and a variety of programs on an IBM mainframe, including IBM CICS, IMS, and APPC/MVS.

For an illustration of the communications function of the Oracle Procedural Gateway for APPC, refer to "Sample pgacics.sql File" in Appendix F, which is a simple sample communication between the Oracle server and CICS on an IBM mainframe. Executing a simple PL/SQL procedure (like pgacics.sql) causes the Oracle server to invoke the procedural gateway, which uses APPC to converse with the PGAFLIP RTP in the mainframe.

Note: You will already have created the following stored procedure when you configured the gateway. If you are unsure whether communication has been established, execute the sample pgacics.sql file located in the $ORACLE_HOME/pg4appc/demo/CICS directory.

Steps to Communication Between Oracle Server and Mainframe

The following steps describe the UNIX-to-mainframe communications process illustrated in Figure 1-3, "Communication Between the Oracle Server and the Mainframe".

1. From the SQL*Plus, execute pgacics; this invokes the PL/SQL stored procedure in the Oracle integrating server.

   $sqlplus <userid>/<password>@<database_specification_string
   SQL> execute pgacics('==< .SCIC htiw gnitacinummoc si yawetag ruoy 
   ,snoitalutargnoC >==');
   
   

2. The pgacics PL/SQL stored procedure will start up the gateway. The gateway will start up communication with the mainframe through APPC and will call PGAFLIP;

3. PGAFLIP processes the input, generates the output and sends the output back to the procedural gateway;

4. Finally, the procedural gateway will send the output back to the PL/SQL stored procedure in the Oracle integrating server. The result will be displayed in SQL*Plus:

   ==> Congratulations, your gateway is communicating with CICS. <==
   PL/SQL procedure successfully completed.
   
   

   Figure 1-3, "Communication Between the Oracle Server and the Mainframe" illustrates the communications process described in steps one through four, above.

Figure 1-3 Communication Between the Oracle Server and the Mainframe

Text description of the illustration ch12.gif

Writing TIPs to Generate PL/SQL Programs

Most transactions are much larger and more complex than the simple sample pgacics.sql file referred to above and communication with a normal-sized RTP (remote transaction program) would require you to create an extremely long PL/SQL file. Oracle Procedural Gateway for APPC's TIP function generates the PL/SQL procedure for you.

Following is a brief description of the four steps necessary for you to generate a TIP. Refer to Chapter 3, "Creating a TIP" for detailed information about this procedure, and refer to Chapter 2, "Procedural Gateway Administration Utility" for more information about PGAU.

All parameter names in this section are taken from a file called pgadb2i.ctl in the $ORACLE_HOME/pga4appc/demo/CICS directory. Refer to "Sample PGAU Script File (pgadb2i.ctl)" in Appendix E, "Administration Utility Samples" to see a sample of the entire file.

Note: TIPs support only COBOL at this time.

1. Create a control file:
   The user writes the control files. The control file has four main types of PGAU commands:
   DEFINE DATA. This is used to define PL/SQL functions calls to be generated as part of the package.

   Sample define data:

   define data empno plsdname(empno) usage(pass) language(ibmvscobolii)
                     infile("empno.cob");
   
   

   DEFINE CALL. This is used to define input and output fields, using COBOL data definitions.

   Sample define call:

   define call db2imain pkgcall(pgadb2i_main) 
                        parms((empno in),(emprec out));
   
   

   DEFINE TRANSACTION. This is used to group the above functions and specify other parameters on which the TIP depends.

   Sample define transaction:

   define transaction db2i call(db2imain,db2idiag)
                           sideprofile(CICSPGA) 
                           tpname(DB2I)
                           logmode(oraplu62)
                           synclevel(0)
                           nls_language("american_america.we8ebcdic37c");
   
   

   GENERATE. This is used to generate the TIP specification files from the previously stored transaction, call and data definitions.

   Sample generate transaction:

   generate db2i pkgname(pgadb2i) pganode(pga) outfile("pgadb2i");
   
   

   Execute the control file within PGAU to create PG DD entries for the data, call and transaction definitions, and to generate the specification files (for example, pgadb2i.pkh and pgadb2i.pkb):

   $pgau
   PGAU>CONNECT<userid>/<password>@<database>_specification_string>
   PGAU>@pgadb2i.ctl
   
   

   Execute the specification files to create the PL/SQL stored procedures. Note that the header specification file (for example: pgadb2i.pkh) must be run first.

   $sqlplus<userid>/<password>@<database_specification_string>
   SQL>@pgadb2i.pkh;
   SQL>@pgadb2i.pkb;
   
   

   The TIP is now ready for use. For convenience, it will usually be called via a driver procedure (for example: db2idriv), which will call the individual stored procedures in the correct order. Create the driver procedure and execute it:

   $sqlplus<userid>/<password>@<database_specification string> 
   SQL>@pgadb2id.sql
   SQL>execute db2idriv('000320');