2 Gateway Features and Restrictions

2.1 Accessing Adabas Data

Oracle Connect for IMS, VSAM, and Adabas Gateways supports the following types of Adabas data sources:

• Adabas (Predict): The Adabas (Predict) data source uses Predict metadata.

• ADD-Adabas: The ADD-Adabas data source uses Oracle's internal repository (ADD), which is usually imported from Natural Data Definition Module (DDM) files. Alternatively, Predict metadata can be exported and subsequently imported into the ADD-Adabas data source.

For details on importing metadata, see the Oracle Database Gateway for IMS, VSAM, and Adabas Installation and Configuration Guide for UNIX or the Oracle Database Gateway for IMS, VSAM, and Adabas Installation and Configuration Guide for Microsoft Windows.

Both Adabas data sources provide very similar functions. The Adabas (Predict) data source supports one-phase transactions only. The ADD-Adabas data sources enjoys some added flexibility and functions resulting from the ability to customize the metadata in the ADD. Unless explicitly stated, all features and procedures described apply to both data sources.

Oracle supports data from Adabas version 6.2 and later on IBM z/OS platforms.

2.2 SQL Support

Oracle Database Gateway for Adabas supports SQL-based data access, allowing developers and applications to use this common and standard syntax for retrieving and updating Adabas 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 Adabas data. This means that Oracle Database Gateway for Adabas 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 Adabas 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 Adabas 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 Adabas 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 Adabas, 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 Adabas offers robust SQL support and relational access to non-relational, proprietary, legacy Adabas data. The following topics provide more information about specific SQL features and their support by the gateway.

This section includes the following topics:

• Pass-Through Feature

• Naming Rules

• Data Types

• SQL Execution

• Locking

SQL> INSERT INTO BINARY_TAB@Adabas VALUES ('0xff')

SELECT DATE_COL FROM TEST@Adabas WHERE DATE_COL = "1-JAN-2001";

SELECT DATE_COL FROM TEST@Adabas WHERE DATE_COL = TO_DATE("1-JAN-2001")

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

2.3 Handling Non-Relational Data

Non-relational data sources (excluding Adabas when Predict metadata is used) 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.

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.

2.4 Restrictions

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

• SQL Restrictions

• Adabas Restrictions

SQL> INSERT INTO "emp_target"@Adabas
         SELECT a."empno" FROM "emp_source"@Adabas a,
            "emp_source"@Adabas b WHERE b."empno"=9999

date + numbernumber + datedate - numberdate1 - date2

SQL> SELECT 9 || 9 FROM DUAL@Adabas;

ORA-6550 PLS-201 Identifier table_name must be declared.

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

ORA-1002:  fetch out of sequence