3 Gateway Features and Restrictions

After the gateway is installed and configured, you can use the gateway to access IMS data, perform distributed queries, and copy data.

This chapter contains the following sections:

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:

See Also:

Supported SQL Syntax and Functions for details; SQL Restrictions for restrictions.

Naming Rules

Naming rule issues include the following:

Rules for Naming Objects

The concept of owner does not exist for objects from IMS/DB data sources. The userid of the dblink is not used to qualify the object. The owner field in the data dictionary tables (see Appendix C, "Data Dictionary" for details) is hardcoded as IMS. You must not use an explicit owner qualifier to reference IMS/DB tables. Using an explicit owner name results in a message like the following:

ORA-00942: table or view does not exist

See Also:

Oracle Database Reference and Adabas documentation for more information on naming objects and Oracle Database Error Messages for more information on error messages.

Case Sensitivity

Object names are not case sensitive. Both Oracle Studio for IMS, VSAM, and Adabas Gateways and the gateway automatically use upper case for data source metadata.

SQL Execution

Query issues include the following:

Empty Strings

Oracle processes an empty string in a SQL statement as a null value. IMS processes an empty string an empty string.

Comparing to an empty string

The gateway passes literal empty string to IMS without any conversion. If you intended an empty string to represent a null value, IMS does not process the statement that way; it uses the empty string.

You can avoid this problem by using NULL or IS NULL in the SQL statement instead of the empty string syntax, as in the following example:

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

Selecting an empty string

For VARCHAR columns, the gateway returns an empty string to the Oracle database server as NULL value.

For CHAR columns, the gateway returns the full size of the column with each character as empty space (' ').

Empty Bind Variables

For VARCHAR bind variables, the gateway passes empty bind variables to IMS as a NULL value.

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

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

Figure 3-2 WARD in Relational Model

This image shows the WARD in the relational model
Description of "Figure 3-2 WARD in Relational Model"

Figure 3-3 PATIENT in Relational Model

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

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

Restrictions related to SQL are described in the following sections:

Unsupported SQL Functions

When an unsupported SQL function is used in an UPDATE, DELETE, or INSERT statement, an error occurs.

See Supported SQL Syntax and Functions for a list of the supported functions.

SQL Syntax

Table 3-1 lists the restrictions that apply to SQL syntax.

Table 3-1 SQL Syntax Restrictions

Syntax Restriction

CONNECT BY clause

The gateway does not support the CONNECT BY clause in a SELECT statement.

ROWID

The Oracle ROWID implementation is not supported.

Transaction Capability

The gateway does not support savepoints. If a distributed update transaction is under way involving the gateway and a user attempts to create a savepoint, the following error occurs:

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

By default, the IMS-DBCTL data source supports global transactions. If the IMS-DBCTL data source is configured differently in Oracle Studio for IMS, VSAM, and Adabas Gateways, see the Oracle Database Heterogeneous Connectivity User's Guide for configuration information.

See Also:

Oracle Database Error Messages for more information on error messages.

Transactional Integrity

If the IMS data source is defined as auto-commit (the IMS-DLI and IMS-DBDC data sources support only auto-commit), the gateway cannot guarantee transactional integrity. In this case, each UPDATE, INSERT, and DELETE statement is immediately committed on the IMS side and cannot be rolled back.

Note:

You can choose to run the gateway for the IMS-DLI data source in read-only mode by setting the HS_FDS_TRANSACTION_MODE parameter to READ_ONLY. Carefully weigh the advantages and disadvantages of executing updates on the IMS data source using the gateway.

Pass-Through Feature

IMS commands cannot be issued using the pass-through feature.

Table and Column Names

The gateway metadata defined for IMS allows record and field names to be over 30 characters in length, but the Oracle database server limits both table and column names to 30 characters. Because of this difference, if you access an IMS field with a name defined in the gateway metadata that is over 30 characters long, the name is truncated to 30 characters and the following Oracle error message is returned:

ORA-00972: Identifier is too long

To avoid this incompatibility, define all names in the gateway metadata less than or equal to 30 characters.

See Also:

Oracle Database Error Messages for more information on error messages.

Database Links

The gateway is not multithreaded and cannot support shared database links. Each gateway session spawns a separate gateway process and connections cannot be shared.

Stored Procedures

Stored procedures are not supported.

IMS/DB Restrictions

When accessing IMS/DB data, the following restrictions apply:

General IMS/DB Restrictions

The following restrictions apply to all IMS/DB data sources:

  • Supported are only databases that have a unique key for every element in a hierarchy, except for end-segments. The support of end-segments without a unique key is limited to read only, with no array (OCCURS clause) support.

  • DDL is not supported.

  • UPDATE operations on arrays (OCCURS clauses) are not supported. See Normalizing Non-Relational Data for details on handling arrays.

  • Logical databases are supported.

  • Logical children are not supported.

  • Secondary indexes are not supported.

  • Segments whose key is partitioned to several fields in the COBOL layout are supported by exposing both the COBOL-level fields and an additional field that overlays these fields and spans the entire key. The following restrictions apply:

    • Only queries that refer to the overlaid field in the WHERE clause develop an efficient execution strategic.

    • For such a key, only alphanumeric field types are supported.

  • CREATE TABLE operations are not supported.

  • IMS/TM transactions turn lowercase letters in IMS/DB data into uppercase letters.

Restrictions Specific to IMS-DLI

The following restrictions apply to the IMS-DLI data source (batch access) only:

  • The IMS-DLI data source must be set up in a separate workspace for every PSB accessed because the PSB is explicitly coded in the started task JCL.

  • Transactional operations, such as COMMIT and ROLLBACK, are not supported. All DML operations are therefore non-transactional.

  • UPDATE operations are supported, but it is not recommended to run them over several servers in parallel.

Restrictions Specific to IMS-DBCTL

The following restrictions apply to the IMS-DBCTL data source only:

Segments within a non-unique index are not supported on any level.

Restrictions Specific to IMS-DBDC

The following restrictions apply to the IMS-DBDC data source only:

  • Transaction processing is not supported.

  • Every data source definition can work with a single PSB only. Multiple data source can be created for multiple PSBs.

  • Segments within a non-unique index are not supported at any level.