3 Gateway Features and Restrictions

Accessing IMS/DB Data

Oracle Connect supports SQL data access to IMS/DB data in the following three IMS/DB environments:

• IMS-DLI: Batch access. The Oracle Connect server issues direct DLI commands to retrieve data as a standalone batch program. This means that the database is accessed from the Oracle Connect-started task without going through any of the IMS control regions. This access method is suited to nightly processing, such as bulk loading when the IMS control region is down. It is usually not suited for general multi-user data access.

• IMS-DBCTL: This data source is suited to users employing CICS as their primary application platform for accessing IMS data. All Oracle Connect servers communicate with an Oracle Connect-supplied CICS program. This program accepts requests for scheduling PSBs and retrieving data through DBCTL services.

• IMS-DBDC: This data source is suited to users employing IMS/TM as their primary application platform for accessing IMS/DB data. All Oracle Connect servers communicate with an Oracle Connect-supplied IMS/TM transaction. This MPP transaction accepts requests from Oracle Connect servers and performs the DLI requests on their behalf.

SQL Support

Oracle Database Gateway for IMS supports SQL-based data access, allowing developers and applications to use this common and standard syntax for retrieving and updating IMS data, and for using SELECT, INSERT, UPDATE, and DELETE statements.

While SQL is supported by relational database management systems, it is not available for legacy, non-relational data such as IMS data. This means that Oracle Database Gateway for IMS goes beyond the functionality provided by other Oracle gateways that connect to relational databases, as those merely deal with the translation and delegation of SQL to the target database. Oracle Database Gateway for IMS takes care of the translation of the SQL statements into system and file specific access primitives, and their execution in an optimized manner, effectively serving as the SQL processor for the non-relational system.

In terms of SQL support, the gateway supports a wide range of SQL capabilities, from basic SQL statements to statements that include advanced features, such as joining of data from multiple IMS sources, use of subselects, and support of data manipulation functions. In addition, the gateway optimizes query performance by supporting such advanced capabilities in where clauses, processing most of them before returning the data to Oracle.

Because IMS is a non-relational system, its data model is not normalized. The gateway provides a complete normalization process that imports existing legacy metadata and produces a relational format that can be used by Oracle users. A key consideration in the normalization process takes care of the hierarchical data structures that are common in IMS, such as arrays.

Going through the import process, the gateway translates the hierarchical structures and embedded arrays into a relational model that maps the hierarchy to several tables. In addition, the process automatically generates the necessary foreign constraints that can later be retrieved using regular Oracle Data Dictionary queries.

In respect to the data dictionary, the gateway provides all the necessary information although the actual data dictionary is not located in the Oracle database. The gateway supports standard data dictionary queries, making the interaction with the non-Oracle data completely transparent to the user, and taking care of translating the non-Oracle model into the Oracle standard data dictionary format including joins across non-Oracle data dictionary tables.

While the typical use case only supports the retrieval of information using SELECT statements, the gateway also supports data manipulation using INSERT, UPDATE, and DELETE statements. Furthermore, it supports advanced options such as distributed transactions.

To summarize, Oracle Database Gateway for IMS offers robust SQL support and relational access to non-relational, proprietary, legacy IMS data. The following topics provide more information about specific SQL features and their support by the gateway.

This section includes the following topics:

• Naming Rules

• SQL Execution

SELECT * from "emp"@IMS where "ename" IS NULL;

Handling Non-Relational Data

Non-relational data sources require metadata, which is kept separately from the data itself. This metadata is stored as a data source definition in a data source repository, on the machine where the data source is defined. It lets you access the data from a non-relational database with SQL commands.

The metadata is imported and maintained using Oracle Studio for IMS, VSAM, and Adabas Gateways. If COBOL copybooks describing the data source records are available, you can import the metadata by using the metadata import procedure in the Design perspective, on the Metadata tab. If the metadata is provided in a number of COBOL copybooks that use different filter settings, you first import the metadata from copybooks with the same settings and later the metadata from other copybooks.

When the non-relational data contains arrays, these arrays can be exposed as follows:

Once the metadata is imported, the data from the non-relational data source can be normalized to maintain transparency.

• As virtual views. This method generates a virtual view for every array in the parent record that contains all the array members.

• As a single table. This method maps all the record fields of the non-relational file to a single table that contains both parent and child records.

Hierarchical Modelling

The IMS/DB data sources map the hierarchical model of IMS/DB to the relational model in the following manner:

• Every segment is mapped to a table.

• The fields in a table consist of the IMS segment buffer and the IMS keyfeedback area.

• The index for an IMS 'table' consists of the keyfeedback, i.e. the entire path leading to the specific segment.

The Hospital Database Example includes a simple hierarchy HOSPITAL > WARD > PATIENT. The following figures show the relational model of this three-level hierarchy in Oracle Connect.

Figure 3-1 HOSPITAL in Relational Model

Description of "Figure 3-1 HOSPITAL in Relational Model"

Figure 3-2 WARD in Relational Model

Description of "Figure 3-2 WARD in Relational Model"

Figure 3-3 PATIENT in Relational Model

Description of "Figure 3-3 PATIENT in Relational Model"

Constructing DLI Commands from SQL Requests

When accessing any segment, the data source driver first needs to select a PCB to be used for this purpose. The choice of PCB is made according to the metadata. The metadata import includes the PSB as one of the import sources. As a result, each table definition in the Oracle Connect data dictionary includes a list of PCB numbers that can be used for every table.

For example, in the following figure, PCB0 will be used to access the HOSPITAL database. Note that you can have several PCBs for each table if your PSB includes several PCBs for the same database.

Figure 3-4 PCB Selection

Description of "Figure 3-4 PCB Selection"

In addition, the IMS data source employs a small but effective 'vocabulary' of IMS commands and SSA variations to satisfy incoming requests.

Restrictions

The following sections describe the restrictions and include suggestions for dealing with them if possible:

• SQL Restrictions

• IMS/DB Restrictions

ORA-02070: database dblink does not support savepoint in this context

ORA-00972: Identifier is too long