Oracle® Application Server Integration Adapter for VSAM Installation and User's Guide 10g (9.0.4) Part Number B10457-01 |
|
After the Oracle Application Server Integration Adapter for VSAM is installed and the modeling described in Chapter 3, "Modeling Interactions for Oracle Application Server Integration Adapter for VSAM" and Chapter 4, "Using Interactions in a Business Process" completed, you can use the Oracle Application Server Integration Adapter for VSAM to invoke interactions that access VSAM data.
This chapter contains the following sections:
When accessing VSAM directly, you model inbound interactions using API functions in COBOL, as described in Appendix E, "COBOL APIs to Applications".
Note:
VSAM does not include a native SQL-based interface. The Oracle Application Server Integration Adapter for VSAM provides support for standard ANSI '92 SQL to select, insert, update and delete VSAM records within the adapter interactions. Additionally, you can incorporate enhancements into the SQL specified in an interaction to handle hierarchical data in VSAM.
A hierarchical query is a query whose result is a hierarchy of rowsets reflecting parent-child relationships.
Rowsets with arrays of structures as columns are modeled such that the rows of an array constitute the children of a column in the containing parent row.
Hierarchical queries enable you to do the following:
You can handle this type of data in the following ways:
Appendix C, "Supported SQL Syntax and SQL Enhancements" for details
See Also:
A hierarchical query nests a SELECT
statement as one of the columns of the rowset retrieved by a nested SELECT
statement. You use braces ({}) to delimit the nesting. This type of query generates a child rowset, which enables you to incorporate drill-down operations in the application.
The following hierarchical query, when specified in an interaction, produces a child rowset:
SELECT C_name, {SELECT O_orderkey, {SELECT L_partkey, L_linenumber FROM lineitem WHERE L_orderkey = O_orderkey} AS items FROM torder WHERE O_custkey=C_custkey} AS orders FROM customer
The result has a three-tier hierarchical structure. The main ("root") rowset has two columns. The second column includes another ("child") rowset. This child rowset includes items
, a column that includes another child rowset.
Data stored hierarchically in a VSAM data source can be referenced by using a hyphen followed by a right arrow (->) to denote the parent child relationship in the source.
The following query, when specified in an interaction, accesses a child rowset:
select name from employees->hchild
This example assumes hierarchical data with a parent rowset called employees
. This rowset has ordinary columns plus a column called hchild
(one row for each of the children of this employee). The query lists all of the children of all of the employees.
You can produce a flattened view of hierarchical data by embedding a SELECT
statement inside the list of columns to be retrieved by another SELECT
statement. You use parentheses to delimit the nesting. This is equivalent to specifying a left outer join between the parent rowset and a child rowset, and the resulting rowset reproduces each row of the parent, combining it with one row for each of its children.
The nested SELECT
statement can reference a child rowset (using the parent->child syntax) only in its FROM
clause.
The examples that follow, when specified in interactions, assume hierarchical data with a parent rowset called employees
. This rowset has ordinary columns plus a column called hchild
(one row for each of the children of this employee).
Compare the following queries:
SELECT emp_id,(select name from employees->hchild)from employees
And
SELECT emp_id,(select name from e->hchild)from employees e
The first query, without an alias, lists for each employee all of the children of all of the employees. The second query uses an alias and produces a list containing the children stored in an array called hchild belonging to each employee.
The following query retrieves the number of children that study in each city for every city where the company has employees.
SELECT city, (SELECT COUNT(*) FROM employees->hchild->hschool A WHERE A.city = B.city) FROM cities B
The Oracle Application Server Integration Adapter for VSAM includes a set of APIs that enable sending a native event as an inbound interaction from a COBOL program, using a CICS transaction.
See Also:
Appendix E, "COBOL APIs to Applications" for details of APIs that can be used when not working under CICS |
To send an inbound interaction, perform the following tasks:
Before you can use the CICS transaction, set it up using the following procedure:
.LOAD(ATTCICSD)
to a CICS DFHRPL library.
.LOAD(TRANS3GL)
to a CICS DFHRPL library.
The following JCL can be used as a template:
//ATTCSD JOB 'ORACLE','CSD',MSGLEVEL=1,NOTIFY=&SYSUID //STEP1 EXEC PGM=DFHCSDUP,REGION=512K, // PARM='CSD(READWRITE),PAGESIZE(60),NOCOMPAT' //STEPLIB DD DSN=<HLQ1>.SDFHLOAD,DISP=SHR //DFHCSD DD UNIT=SYSDA,DISP=SHR,DSN=<HLQ2>.CSD //OUTDD DD SYSOUT=* //SYSPRINT DD SYSOUT=* //SYSIN DD * */********************************************************************/ */* ORACLE CICS Definitions */ */* */ */********************************************************************/ *---------------------------------------------------------------------* * Note: Install GROUP(ORA) - CEDA IN G(ORA) * * If you are rerunning this, uncomment the DELETE command. * *---------------------------------------------------------------------* * * Start ORACLE RESOURCES: * * DELETE GROUP(ORA) DEFINE PROGRAM(ATTCICSD) GROUP(ORA) LANGUAGE(C) DATALOCATION(ANY) DE(Oracle DLL) DEFINE PROGRAM(TRANS3GL) GROUP(ORA) LANGUAGE(C) DATALOCATION(ANY) DE(Oracle DLL) DEFINE PROGRAM(<PROG>) GROUP(ORA) LANGUAGE(<LANG>) DATALOCATION(ANY) DE(Oracle) DEFINE TRANSACTION(<ORATRAN>) GROUP(ORA) PROGRAM(<PROG>) TASKDATAL(ANY) DE(Oracle TRAN ID) LIST GROUP(ORA) * * End ORACLE RESOURCES * /* //
Make the following changes before running the JCL:
CEDA IN G(ORA)
The COBOL program sets up a buffer in the CICS COMMAREA that contains the information needed for the inbound interaction and then calls the TRANS3GL
transaction to send the interaction.
The buffer is formatted as follows:
The buffer is defined as follows:
* * COBOL COPY OF DATA BUFFER * 01 COMM-DATA-BUFF PIC X(5000). 01 COMM-DATA-BUFF-ERROR REDEFINES COMM-DATA-BUFF. 05 COMM-ERROR-STATUS PIC S9(8) COMP SYNC. 05 COMM-ERROR-MSG PIC X(256). 01 COMM-DATA-BUFF-INPUT REDEFINES COMM-DATA-BUFF. 05 INPUT-COMMAREA-3GL. 10 INCOM-VERSION PIC S9(8) COMP SYNC. 10 INCOM-SERVERS-URLS PIC X(256). * /* IP1:PORT[,IP2:PORT] [,...] */ 10 INCOM-USER-NAME PIC X(64). 10 INCOM-PASSWORD PIC X(64). 10 INCOM-WORKSPACE PIC X(64). 10 INCOM-ADAPTER-NAME PIC X(64). 10 INCOM-SCHEMA-FILE-NAME PIC X(256). 10 INCOM-ENC-KEY-NAME PIC X(64). 10 INCOM-ENC-KEY-VALUE PIC X(256). 10 INCOM-INTERACTION-NAME PIC X(64). 10 INCOM-DW-FLAGS PIC S9(8) COMP SYNC. 10 INCOM-INP-FORMAT PIC S9(8) COMP SYNC. 10 INCOM-EXEC-INPUT. 15 INCOM-XML-BUFF. 20 INCOM-XML-ILEN PIC S9(8) COMP SYNC. 20 INCOM-XML-INTER-OUTREC-NAME PIC X(64). * ====>>> CHANGE ??? TO LEN SPECIFIED IN INCOM-XML-ILEN 20 INCOM-XML-INPUT PIC X(???). 15 INCOM-PARAM-BUFF REDEFINES INCOM-XML-BUFF. 20 INCOM-PARAM-COUNT PIC S9(8) COMP SYNC. 20 INCOM-PARAM-VALUE-LEN PIC S9(8) COMP SYNC. 20 INCOM-PARAM-INT-OUTREC-NAME PIC X(64). * ====>>> CHANGE ?? TO COUNT SPECIFIED IN INCOM-PARAM-COUNT 20 INCOM-PARAM-NAME-VALUE OCCURS ?? TIMES. 25 INCOM-PARAM-NAME PIC X(32). * ====>>> CHANGE ?? TO LEN SPECIFIED IN INCOM-PARAM-VALUE-LEN 25 INCOM-PARAM-VALUE PIC X(??). 01 COMM-DATA-BUFF-OUTPUT REDEFINES COMM-DATA-BUFF. 05 COMM-OUT-STATUS PIC S9(8) COMP SYNC. 05 COMM-OUT-LENGTH PIC S9(8) COMP SYNC. 05 COMM-OUT-DATA PIC X(4992)
The TRAN3GL transaction is called as follows:
EXEC CICS LINK PROGRAM("TRANS3GL") COMMAREA(commDataBuff) LENGTH(iCommSize);
where:
commDataBuff - The buffer with the interaction details, used in the COMMAREA.
iCommSize - The size of the buffer. This value is also used to determine the size of the output string. Thus make sure the value is big enough for the expected output.
After defining the COMMAREA and calling the TRAN3GL transaction in the COBOL program, compile and move the COBOL program to a CICS DFHRPL (LOAD) library.
The output includes a 4 byte success flag: Zero for success, otherwise failure. The output overrides the input. If the result is failure, an error message with a length of 256 bytes is returned.
If XML was specified for the input and the result is success, the output is formatted as XML, as follows:
If parameters were specified for the input and the result is success, the output is formatted as follows:
You can configure a pool of server processes so that a server process is always available for a client request.
See Also:
"Configuring the Daemon for High Availability" for details about high availability |
If you encounter incompatibility problems not listed in this section, please contact Oracle Support Services. The following section describes the known restrictions and includes suggestions for dealing with them when possible:
When accessing VSAM data, the following restrictions apply:
DELETE
operations are not supported for ESDS files.
When accessing VSAM under CICS, the following additional restrictions apply:
When accessing VSAM directly (and not under CICS), the following restrictions apply:
Arrays (generated from a COBOL Copybook OCCUR
) cannot be updated.
|
![]() Copyright © 2003 Oracle Corporation. All Rights Reserved. |
|