| Oracle® Database Gateway for IMS User's Guide 11g Release 1 (11.1) Part Number B31053-01 |
|
|
View PDF |
| Oracle® Database Gateway for IMS User's Guide 11g Release 1 (11.1) Part Number B31053-01 |
|
|
View PDF |
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:
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.
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 rule issues include the following:
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 IMS documentation for more information on naming objects and Oracle Database Error Messages for more information on error messages.Query issues include the following:
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 (' ').
For VARCHAR bind variables, the gateway passes empty bind variables to IMS as a NULL value.
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.
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 ExampleKapp, Dan and Leben, Joe: IMS Programming Techniques. Van Nostrand Reinhold Company Inc., New York, 1986. includes a simple hierarchy HOSPITAL > WARD > PATIENT. The following figures show the relational model of this three-level hierarchy in Oracle Connect.
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.
In addition, the IMS data source employs a small but effective 'vocabulary' of IMS commands and SSA variations to satisfy incoming requests.
The following sections describe the restrictions and include suggestions for dealing with them if possible:
Restrictions related to SQL are described in the following sections:
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.
Table 2-1 lists the restrictions that apply to SQL syntax.
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 Administrator's Guide for configuration information.
See Also:
Oracle Database Error Messages for more information on error messages.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 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.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.The gateway is not multithreaded and cannot support shared database links. Each gateway session spawns a separate gateway process and connections cannot be shared.
When accessing IMS/DB data, the following restrictions aply:
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 strategie.
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.
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 explicitely 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.
The following restrictions apply to the IMS-DBCTL data source only:
Segments within a non-unique index are not supported on any level.
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.
