PK
[.Aoa, mimetypeapplication/epub+zipPK [.A iTunesMetadata.plistR
artistNameOracle Corporationbook-infocover-image-hash817233584cover-image-pathOEBPS/dcommon/oracle-logo.jpgpackage-file-hash612462790publisher-unique-idE12072-01unique-id289132381genreOracle DocumentationitemNameOracle® Database Gateway for IMS User's Guide, 11g Release 2 (11.2)releaseDate2009-07-29T19:12:38Zyear2009
PKGőW R PK [.A META-INF/container.xml
PKYu PK [.A OEBPS/c_array_handling.htm\v
Normalizing Non-Relational Data
2 Normalizing Non-Relational Data
This section describes the support methods that Oracle Connect for IMS, VSAM, and Adabas Gateways applies to normalize non-relational data. It includes the following topics:
Oracle Connect for IMS, VSAM, and Adabas Gateways exposes a purely relational front end through the HOA API. However, it connects to non-relational data sources, which include non-relational data models. As such, Oracle Connect for IMS, VSAM, and Adabas Gateways provides a logical mapping that exposes the non-relational constructs in a relational manner. The most prevalent problem in this domain is the issue of arrays, which is described in this section.
About Arrays
An array is a group of similar elements of the same size. Arrays contain a series of data elements that are of the same data type, which can be simple or complex (group). A specific element is defined and accessed by its position in the array, which is provided by an index.
Arrays are a convenient way to store a fixed amount of data that is accessed in an unpredictable fashion. They are not efficient, however, when you need to insert or delete individual elements of the array.
Representing Metadata
Before looking at the different methods of handling arrays, you should understand how metadata is represented in Oracle Studio for IMS, VSAM, and Adabas Gateways.
Figure 2-1 shows an example record with arrays and nested arrays.
When you import this metadata into Oracle Studio for IMS, VSAM, and Adabas Gateways, the import process creates a data dictionary definition that is equivalent to the original structure, usually mapping the fields one to one. The import process also writes all primary and foreign key definitions to the ALL_CONSTRAINTS table.
Oracle Studio for IMS, VSAM, and Adabas Gateways represents the flattened view of the metadata on the table editor's Columns tab, as shown in Figure 2-2.
Figure 2-2 Representation of Metadata on the Columns tab in Oracle Studio for IMS, VSAM, and Adabas Gateways
The (+) to the left of a column indicates a group field. This type of field usually has a Dimension value. This value is not mandatory, but it optimizes the access to an array. You can click (+) to display the group members. In Figure 2-2, all groups are expanded.
Table 2-1 Metadata Column Tab Definition
Column
Description
Column name
The name of the column.
Data type
The data type of the column. Selecting this field displays a drop-down box listing the possible data types.
Size
The size of the column for data types of a non-fixed size.
Scale
The information entered in this field depends on the data type:
For decimal data types, this is the number of digits to the right of the decimal place. This number must not be greater than the number of digits. The default value is 0.
For scaled data types, this is the total number of digits. The number must be negative.
Dimension
The maximum number of entries of an array. An array has a dimension other than zero. This value is not mandatory, but it optimizes the access to an array.
Offset
Not relevant for arrays.
Fixed offset
Not relevant for arrays.
Primary Key Column
The column is part of the table's primary key.
The tree in the Metadata view displays a normalized view of the tables, as shown in Figure 2-3, where STUDENT_BOOK, STUDENT_COURSE, and STUDENT_COURSE_ASSIGNMENTS are virtual views and STUDENT_ST is a single table, also called sequential view.
Figure 2-3 Normalized View of the Tables in the Metadata View
The following SQL query on the Oracle data dictionary produces the result shown in Example 2-1. In this query, data source is the name of the data source from which the metadata was created. The result shows the metadata of the original table after virtual arrays were created.
SQL> select TABLE_NAME,COLUMN_NAME,DATA_TYPE from ALL_TAB_COLUMNS@dg4[data_source] where table_name like 'STUDENT%';
Example 2-1 SQL Query Result
TABLE_NAME COLUMN_NAME DATA_TYPE
------------------------------ --------------------------- ---------------------
STUDENT ID NUMBER
STUDENT FIRST_NAME CHAR
STUDENT LAST_NAME CHAR
STUDENT DATE_OF_BIRTH CHAR
STUDENT NUMOF_COURSES NUMBER
STUDENT NUMOF_BOOKS NUMBER
STUDENT_BOOK BOOK_ROWNUM NUMBER
STUDENT_BOOK ISBN CHAR
STUDENT_BOOK RETURN_DATE CHAR
STUDENT_COURSE COURSE_ROWNUM NUMBER
STUDENT_COURSE COURSE_ID NUMBER
STUDENT_COURSE COURSE_TITLE CHAR
STUDENT_COURSE INSTRUCTOR_ID NUMBER
STUDENT_COURSE NUMOF_ASSIGNMENTS NUMBER
STUDENT_COURSE_ASSIGNMENTS COURSE_ROWNUM NUMBER
STUDENT_COURSE_ASSIGNMENTS ASSIGNMENTS_ROWNUM NUMBER
STUDENT_COURSE_ASSIGNMENTS ASSIGNMENT_TYPE CHAR
STUDENT_COURSE_ASSIGNMENTS ASSIGNMENT_TITLE CHAR
STUDENT_COURSE_ASSIGNMENTS DUE_DATE CHAR
STUDENT_COURSE_ASSIGNMENTS GRADE NUMBER
STUDENT_ST __LEVEL VARCHAR2
STUDENT_ST __SEQUENCE NUMBER
STUDENT_ST COURSE_ROWNUM NUMBER
STUDENT_ST ASSIGNMENTS_ROWNUM NUMBER
STUDENT_ST BOOK_ROWNUM NUMBER
STUDENT_ST ID NUMBER
STUDENT_ST FIRST_NAME CHAR
STUDENT_ST LAST_NAME CHAR
STUDENT_ST DATE_OF_BIRTH CHAR
STUDENT_ST NUMOF_COURSES NUMBER
STUDENT_ST NUMOF_BOOKS NUMBER
STUDENT_ST COURSE_ID NUMBER
STUDENT_ST COURSE_TITLE CHAR
STUDENT_ST INSTRUCTOR_ID NUMBER
STUDENT_ST NUMOF_ASSIGNMENTS NUMBER
STUDENT_ST ASSIGNMENT_TYPE CHAR
STUDENT_ST ASSIGNMENT_TITLE CHAR
STUDENT_ST DUE_DATE CHAR
STUDENT_ST GRADE NUMBER
STUDENT_ST ISBN CHAR
STUDENT_ST RETURN_DATE CHAR
41 rows selected.
Methods of Handling Arrays
Oracle Studio for IMS, VSAM, and Adabas Gateways lets you handle arrays by using the following methods:
Exposing arrays as virtual views is a commonly used technique to handle arrays. It generates a virtual view for every array in the parent record that contains all the array members. Virtual views contain primary key fields from the parent to connect the parent and the virtual view. Optionally, they can also contain all fields from the parent table.
During the import process or when you set the virtual array policy on the table level, Oracle Studio for IMS, VSAM, and Adabas Gateways generates virtual views and names them by appending the array name to the parent name. When an array includes another array, the name of the resulting virtual table consists of the parent name, the array name, and the name of the nested array, as follows:
parentName_arrayName_nestedArrayName
For example, a parent table called STUDENT with an array called COURSE and a nested array called ASSIGNMENTS is represented by the virtual view STUDENT_COURSE_ASSIGNMENTS (see Figure 2-5).
The number of nested-array levels is not limited.
Virtual views include the following:
The array member columns from the original structure.
The fields from the parent's first unique key, or all parent fields, depending on the selection you make during the import process or when setting the virtual array policy on the table level.
If all parent fields are included in the virtual view, the parent's indexes are available in the view definition and can be used for efficient optimization strategies.
Note:
Inherited keys lose their uniqueness in the virtual view.
If the view does not include all parent fields, the primary key fields (if the primary key is not the parent's first unique key).
If selected, a column called <array_name>_ROWNUM, which identifies the row in the array.
The unique key and <array_name>_ROWNUM columns are generated automatically. Together, they uniquely identify each row in the virtual view and form a unique key.
Figure 2-4 shows the STUDENT_BOOKS virtual view with the two array member columns (ISBN and RETURN_DATE) and the column that identifies the row in the array (BOOK_ROWNUM).
Oracle Studio for IMS, VSAM, and Adabas Gateways also maintains primary and foreign key definitions that connect between the parent table and the array tables. This allows graphical tools to easily match parent and array.
When working with virtual views, consider the following limitations:
Virtual views are read-only.
Virtual views do not support arrays within variants that have a selector field.
Including all parent fields in the virtual view greatly reduces the need for performing join operations because this in itself is an implicit join. In general, the query processor can devise efficient access strategies because Oracle Connect for IMS, VSAM, and Adabas Gateways copies all relevant indexes from the parent to the virtual view.
Oracle Studio for IMS, VSAM, and Adabas Gateways indicates virtual views by using a different colored icon in the Metadata view, as shown in Figure 2-5.
Figure 2-5 Display of Virtual Views in Oracle Studio for IMS, VSAM, and Adabas Gateways
Performing a bulk load of complex data from a non-relational system to a relational database requires a carefully thought-out algorithm that keeps I/O operations at a minimum.
In a bulk load scenario, a method such as Virtual Views requires a full scan of the physical file for every single array. An efficient method of performing this task presents a kind of row-wise normalization, called sequential flattening. This method reads all data in the physical file in a single scan.
Sequential flattening replaces arrays in a non-relational system by a sequence of rows. It maps all the record fields of the non-relational file to a single table that contains both parent and child records. In this way, sequential flattening enables the reception of a stream of data by using a single SELECT statement.
The sequentially flattened view of a complex table is referred to as a single table or sequential view. You can choose to create a sequential view in Oracle Studio for IMS, VSAM, and Adabas Gateways by selecting the Generate sequential view check box during the Metadata Model Selection step of the Metadata Import procedure. The single table is read-only.
The flattened table is called <table>_ST, where <table> is the name of the parent table and ST indicates a single table. For example, if a parent table is called STUDENT, the single table, or sequential view, is called STUDENT_ST.
The structure of the single table is identical to the original table's structure, except that Oracle Connect for IMS, VSAM, and Adabas Gateways removes all array dimensions and adds some control fields. When reading a record, Oracle Connect for IMS, VSAM, and Adabas Gateways performs a tree traversal of the parent and its array hierarchy. Each record in the resulting recordset deals with a specific array member; other arrays are nulled out.
The sequentially flattened single table includes the following columns:
The parent fields, that is the non-repeating fields.
The array fields for all arrays within the parent.
For each array, an optional column called <array_name>_ROWNUM, which identifies the row in the array. This column is generated automatically for the array.
The sequentially flattened single table includes a record (row) for each array record.
See Figure 2-1 for an illustration of arrays and nested arrays.
Figure 2-6 shows the metadata that sequential flattening produces for a data source with three arrays (COURSE, ASSIGNMENTS, and BOOK) in the SQL View window. The window presents read-only information about each of the columns in the table.
Figure 2-6 SQL View of the Single Table's Metadata
The following table describes the information presented in this window.
Table 2-2 SQL View Window
Information Type
Description
Name
The name of the column.
Data Type
The data type supported by that column. For example, string or integer.
Size
Indicates the maximum size allowable for the data in the column. The size is in standard units for the data type. For example, a string with size 40 can have no more than forty characters.
Scale
Indicates the number of digits allowed after the decimal point for a numeric value.
Precision
Indicates the total number of digits allowed for a numeric value in the column. If the value has a scale of one or more, then the total number of digits allowed before the decimal point is the precision value minus the scale value. For example, a value with precision 4 and scale 2 can be no larger than 99.99.
Nullable
Indicates whether the column can have a null value. If True, the column is nullable.
Figure 2-7 shows the actual single table after running a SELECT clause. It contains a column for each row in the preceding SQL view.
Figure 2-7 STUDENT_ST with All Parent and Child Records
Oracle Database SQL Reference for detailed descriptions of keywords, parameters, and options and the Adabas documentation for details of executing SQL statements in a transaction.
Supported SQL Statements
With a few exceptions, the gateway provides full support for Oracle DELETE, INSERT, SELECT, and UPDATE statements.
The gateway does not support Oracle data definition language (DDL) statements. No form of the Oracle ALTER,CREATE, DROP, GRANT, or TRUNCATE statements can be used. Instead, for ALTER, CREATE, DROP, and GRANT statements, use the pass-through feature of the gateway if you need to use DDL statements against the Adabas database.
Note:
TRUNCATE cannot be used in a pass-through statement.
DELETE
The DELETE statement is fully supported. However, only Oracle functions supported by Adabas can be used.
The SELECT statement is fully supported, with these exceptions:
CONNECT BY condition
NOWAIT
START WITH condition
WHERE CURRENT OF
UPDATE
The UPDATE statement is fully supported. However, only Oracle functions supported by Adabas can be used. Also, you cannot have SQL statements in the subquery that refer to the same table name in the outer query. Subqueries are not supported in the SET clause.
All functions are evaluated by the Adabas database after the gateway has converted them to Adabas SQL.
Functions Not Supported by Adabas
Oracle SQL functions with no equivalent function in Adabas are not supported in DELETE, INSERT, or UPDATE statements, but are evaluated by the Oracle database server if the statement is a SELECT statement. That is, the Oracle database server performs post-processing of SELECT statements sent to the gateway.
If an unsupported function is used in a DELETE, INSERT, or UPDATE, statement, the following Oracle error occurs:
ORA-02070: database db_link_name does not support function in this context
Contributing Author: Maitreyee Chaliha, Sami Zeitoun, Oussama Mkaabal
Contributor: Vira Goorah, Peter Wong
This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.
The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.
If this software or related documentation is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:
U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to U.S. Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use, duplication, disclosure, modification, and adaptation shall be subject to the restrictions and license terms set forth in the applicable Government contract, and, to the extent applicable by the terms of the Government contract, the additional rights set forth in FAR 52.227-19, Commercial Computer Software License (December 2007). Oracle USA, Inc., 500 Oracle Parkway, Redwood City, CA 94065.
This software is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications which may create a risk of personal injury. If you use this software in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure the safe use of this software. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software in dangerous applications.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
This software and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.
PKF PK [.A OEBPS/preface.htm
Preface
Preface
This manual describes the Oracle Database Gateway for Adabas, which enables Oracle client applications to access Adabas data through Structured Query Language (SQL). The gateway, with the Oracle Database, creates the appearance that all data resides on a local Oracle Database, even though the data can be widely distributed.
Audience
This manual is intended for Oracle database administrators who perform the following tasks:
Installing and configuring the Oracle Database Gateway for Adabas
Diagnosing gateway errors
Using the gateway to access Adabas data
Note:
You should understand the fundamentals of Oracle Database Gateways before using this guide to install or administer the gateway.
Documentation Accessibility
Our goal is to make Oracle products, services, and supporting documentation accessible to all users, including users that are disabled. To that end, our documentation includes features that make information available to users of assistive technology. This documentation is available in HTML format, and contains markup to facilitate access by the disabled community. Accessibility standards will continue to evolve over time, and Oracle is actively engaged with other market-leading technology vendors to address technical obstacles so that our documentation can be accessible to all of our customers. For more information, visit the Oracle Accessibility Program Web site at http://www.oracle.com/accessibility/.
Accessibility of Code Examples in Documentation
Screen readers may not always correctly read the code examples in this document. The conventions for writing code require that closing braces should appear on an otherwise empty line; however, some screen readers may not always read a line of text that consists solely of a bracket or brace.
Accessibility of Links to External Web Sites in Documentation
This documentation may contain links to Web sites of other companies or organizations that Oracle does not own or control. Oracle neither evaluates nor makes any representations regarding the accessibility of these Web sites.
Deaf/Hard of Hearing Access to Oracle Support Services
To reach Oracle Support Services, use a telecommunications relay service (TRS) to call Oracle Support at 1.800.223.1711. An Oracle Support Services engineer will handle technical issues and provide customer support according to the Oracle service request process. Information about TRS is available at http://www.fcc.gov/cgb/consumerfacts/trs.html, and a list of phone numbers is available at http://www.fcc.gov/cgb/dro/trsphonebk.html.
Related Documents
For more information, see the following documents in the Oracle Database Gateway 11g Release 2 (11.2) documentation set:
The Oracle Database Gateway for Adabas translates a query that refers to an Oracle database server data dictionary table into a query that retrieves the data from an Adabas data dictionary. You perform queries on data dictionary tables over the database link in the same way you query data dictionary tables in the Oracle database server. The gateway data dictionary is similar to the Oracle database server data dictionary in appearance and use.
Data dictionary information is stored in the gateway's advanced data dictionary (ADD). The views and tables that the gateway supports provide information on the following:
The tables existing in the data source
The columns for each table
The primary key for each table
The foreign keys for each table
Which primary key each foreign key references
The existing users and indexes
The following paragraphs describe the Oracle Database Gateway for Adabas data dictionary support.
Accessing the Gateway Data Dictionary
Accessing an ADD record definition is identical to accessing a data dictionary table or view definition on an Oracle database. You issue a SQL SELECT statement specifying a database link. The Oracle database server data dictionary view and column names are used to access the gateway data dictionary. Synonyms of supported views are also acceptable. For example, the following statement queries the data dictionary table ALL_TABLES:
SQL> SELECT * FROM ALL_TABLES@IMS;
When a data dictionary access query is issued, the gateway performs the following operations:
Maps the requested table, view, or synonym to one or more ADD names.
Processes the query within the gateway.
May convert the retrieved data to give it the appearance of the Oracle database server data dictionary table.
Passes the data dictionary information to the Oracle database server.
Supported Views and Tables
The gateway supports the following views and tables:
Supported Views and Tables
ALL_CATALOG
ALL_CONS_COLUMNS
ALL_IND_COLUMNS
ALL_OBJECTS
ALL_TAB_COMMENTS
ALL_USERS
DBA_CATALOG
DBA_OBJECTS
DICTIONARY
USER_CATALOG
USER_CONS_COLUMNS
USER_IND_COLUMNS
USER_TAB_COMMENTS
USER_OBJECTS
USER_USERS
ALL_COL_COMMENTS
ALL_CONSTRAINTS
ALL_INDEXES
ALL_TAB_COLUMNS
ALL_TABLES
ALL_VIEWS
DBA_TAB_COLUMNS
DICT_COLUMNS
DUAL
USER_COL_COMMENTS
USER_CONSTRAINTS
USER_INDEXES
USER_TAB_COLUMNS
USER_TABLES
USER_VIEWS
No other Oracle database server data dictionary tables or views are supported. If you use a view not on the list, you receive the Oracle database server error code for no more rows available.
To select data dictionary views using SELECTFROMDBA_*, first connect as Oracle user SYSTEM or SYS. Otherwise, you receive the following error message:
ORA-28506: Parse error in data dictionary translation for %s stored in %s
Queries through the gateway of any data dictionary table or view beginning with ALL_ can returns rows from Adabas even when access privileges for those Adabas objects have not been granted. When querying an Oracle database with the Oracle data dictionary, rows are returned only for those objects you are permitted to access.
There is a minor difference between the gateway data dictionary and a typical Oracle database server data dictionary. The Oracle database server columns that are missing in an Adabas data dictionary table are filled with zeros, spaces, null values, or default values, depending on the column type.
Adabas Data Dictionary Descriptions
The gateway data dictionary tables and views provide the following information:
Name, datatype, and width of each column.
The contents of columns with fixed values.
In the descriptions that follow, the values in the Null? column may differ from the Oracle database server data dictionary tables and views. Any default value is shown to the right of an item.
Note:
In all data dictionary tables, the OWNER column has the value IMS.
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.
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.
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.
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.
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.
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.
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:
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.
PKY;zi ui PK [.A " OEBPS/img_text/normalized_view.htm4
Description of the illustration normalized_view.gif
This image shows a normalized view of the tables
PKP PK [.A $ OEBPS/img_text/datasource_editor.htm
Description of the illustration datasource_editor.gif
This image shows where to configure the default behavior for handling arrays
PKϏ PK [.A OEBPS/img_text/ims040.htm=
Description of the illustration ims040.gif
The image shows PATIENT in the relational model.
PKw(P PK [.A ! OEBPS/img_text/metadata_model.htm2
Description of the illustration metadata_model.gif
The image shows the Metadata Model Selection screen
PK PK [.A + OEBPS/img_text/single_table_data_oracle.htm$
Description of the illustration single_table_data_oracle.gif
This table shows table data after sequential flattening
PKܕu PK [.A ! OEBPS/img_text/vtables_studio.htm3
Description of the illustration vtables_studio.gif
This image shows a display of virtural table views
PKIOi] PK [.A OEBPS/img_text/ims030.htm@
Description of the illustration ims030.gif
The image shows WARD in the relational model.
PK& PK [.A ' OEBPS/img_text/array_representation.htm
Description of the illustration array_representation.gif
This image shows an example of OCCUR and DEPENDING ON clauses in COBOL
PKJ& PK [.A OEBPS/img_text/ims050.htmA
Description of the illustration ims050.gif
The image shows PCB Selection in XML format.
PK >PI PK [.A . OEBPS/img_text/metadata_studio_columns_tab.htm-
Description of the illustration metadata_studio_columns_tab.gif
This image shows the columns tab in Oracle Studio for IMS, VSAM, and Adabas Gateways, which displays a representation of the metadata.
PK2pd2 - PK [.A OEBPS/img_text/cobol_sample.htm>
Description of the illustration cobol_sample.gif
This image shows a COBOL metadata example
PKF PK [.A & OEBPS/img_text/select_virtual_view.htmA
Description of the illustration select_virtual_view.gif
This image shows a virtual view
PKhZP PK [.A % OEBPS/img_text/gateway_processing.htm:
Description of the illustration gateway_processing.gif
This image shows the Gateway processing
PK PK [.A ' OEBPS/img_text/gateway_process_flow.htm/
Description of the illustration gateway_process_flow.gif
This figure illustrates the gateway process flow
PK&2W PK [.A OEBPS/img_text/ims020.htm<
Description of the illustration ims020.gif
The image shows HOSPITAL in the relational model.
PK U PK [.A ) OEBPS/img_text/sql_view_vtable_oracle.htm*
Description of the illustration sql_view_vtable_oracle.gif
This image shows the SQL view of a table's metadata
PKbͿ PK [.A OEBPS/img_text/table_editor.htm
Description of the illustration table_editor.gif
This image shows how to modify the array handling policy on the table level
PK\ PK [.A OEBPS/ims_datatype_conver.htm v
Data Type Conversion
The gateway converts atomic data types in the advanced data dictionary (ADD) to Oracle data types as described in Table A-1.
Table A-1 Atomic Data Types
ADD Data Type
Oracle Data Type
Details
apt_date
DATE
Date packed into a 4-character string.
Format: DMYY
Example: 23-Jul-1998 is represented by four bytes: 19, 98, 7, and 23.
apt_time
DATE
ADD date-time format.
bit
NUMBER(3)
Single bit within a byte.
Size: 1 byte.
Format: datatype="bit"
onBit="n"
Where n specifies which bit (within a byte) the field uses.
If more than one bit is defined, then the additional bits may be defined sequentially within the same byte (or bytes, if the number of bits requires this much space).
Where n specifies which bit (within a byte) to start from and m is the number of bits. If n is not specified then n defaults to 1 for the first occurrence of the field and is contiguous thereafter.
The maximum number of bits you can map is 32.
cstring
VARCHAR2
A null-terminated string of alphanumeric characters; maximum length must be specified. An extra byte is required for the null flag.
cv_datetime
DATE
CorVision date-time format.
date
DATE
ODBC date format.
date6
DATE
Date in a string having the form YYMMDD
date8
DATE
Date in a string having the form YYYYMMDD
dfloat
FLOAT(49)
Double floating-point number (D_FLOAT)
Size: 8 bytes
Range: 0.29E-38 to 1.7E38
Precision: 16 digits.
filler
RAW
Allocation for future use, string type; length must be specified.
fixed_cstring
FLOAT(49)
A fixed null-terminated string of numeric characters; length must be specified. An extra byte is required for the null flag.
int1
NUMBER(3)
Signed byte integer.
Size: 1 byte
Range: -128 to +127
int2
NUMBER(5)
Signed word integer.
Size: 1 byte
Range: -32768 to +32767
int3
NUMBER(10)
Signed integer
Size: 3 bytes
int4
NUMBER(10)
Signed long integer.
Size: 4 bytes
Range: -2147483648 to +2147483647
int6
NUMBER(10)
Signed integer
Size: 6 bytes
int_date
DATE
Date in a four-byte integer having the form YYMMDD or YYYYMMDD.
Example, 23-Jul-1998 has the form: 980723 or 19980723.
jdate
DATE
Julian date
Size: 2 bytes
Bits 0-6: (non-century) year
Bits 7-15: day of the year
logical
NUMBER(10)
Signed long integer
Values: 1 for true, 0 for false
mvs_date
DATE
z/OS date format.
mvs_datetime
DATE
z/OS date-time format.
mvs_time
DATE
z/OS time format.
padded_str_date
DATE
Padded date format, not null-terminated.
padded_str_datetime
DATE
Padded date format, not null-terminated.
padded_str_time
DATE
Padded date format, not null-terminated.
phdate
DATE
Size: 2 bytes
Bits 0-6: (non-century) year
Bits 7-10: number of month
Bits 11-15: day of month
single
FLOAT(23)
Single floating-point number (F_FLOAT).
Size: 4 bytes
Range: 0.29E-38 to 1.7E38
Precision: 6 digits
str_date
CHAR(10)
Atomic date string
Size: 10 characters
Format: YYYY-MM-DD
str_datetime
CHAR(19)
Atomic date-time string.
Size: 19 characters
Format: YYYY-MM-DD HH:MM:SS
str_time
CHAR(8)
Atomic date string
Size: 8 characters
Format: HH:MM:SS
string
CHAR
String of alphanumeric characters; length must be specified.
Where n specifies which bit (within a byte) to start from and m is the number of bits. If n is not specified, then n defaults to1 for the first occurrence of the field and its contiguous thereafter.
The maximum number of bits you can map is 31.
uint1
NUMBER(3)
Unsigned byte integer.
Size: 1 byte
Range: 0 to +254
uint2
NUMBER(10)
Unsugned word integer.
Size: 2 bytes
Range: 0 to+65534
uint4
NUMBER(11)
Signed long integer
Size: 4 bytes
Range: 0 to +4,294,967,294
uint6
NUMBER
Unsugned integer.
Size: 6 bytes
unicode
VARCHAR2
A null-terminated alphanumeric unicode string. Maximum length must be specified.
varstring
VARCHAR2
16-bit count, followed by a string.
varstring4
VARCHAR2
32-bit count, followed by a string.
Conversion of Non-Atomic Data Types
The gateway converts atomic data types to Oracle data types as described in Table A-1.
Table A-2 Non-atomic Data Types
ADD Data Type
Oracle Data Type
Details
binary
RAW
Unknown data type, string type; length must be specified.
nls_string
CHAR
A string based on language and driven by table.
Conversion of Decimal Data Types
The gateway converts decimal data types to Oracle data types as described in Table A-3.
Table A-3 Decimal Data Types
ADD Data Type
Oracle Data Type
Details
decimal
NUMBER
Packed decimal
Maximum number of digits: 31
Maximum fractions: 11
Length = int (number of digits/2) + 1 bytes
numeric_cstring
NUMBER
A null-terminated string of numeric characters; maximum length must be specified. An extra byte is required for the null flag.
numstr_bdn
NUMBER
Signed numeric string. Sign is the first character of the string.
Maximum number of digits: 31
Maximum fractions: 11
Note: the number of fractions includes the decimal point.
numstr_nlo
NUMBER
Signed numeric string. A left overpunched sign is implemented.
Maximum number of digits: 31
Maximum fractions: 11
numstr_nl
NUMBER
Signed numeric string. Sign is the first character of the string.
Maximum number of digits: 31
Maximum fractions: 11
numstr_nr
NUMBER
Signed numeric string. Sign is the last character of the string.
Maximum number of digits: 31
Maximum fractions: 11
numstr_s
NUMBER
Signed numeric string. A right overpunched sign is implemented.
Maximum number of digits: 31
Maximum fractions: 11
The number must be right-justified (for example, " 1234N" is -12345). The number can be left-padded by either spaces or zeros. If a scale is provided, it is a fixed positional scale. No decimal point is provided in the data (for example, a value of "1234E" with scale 2 is interpreted as "123.45").
numstr_u
NUMBER
Unsigned numeric string.
Maximum number of digits: 31
Maximum fractions: 11
numstr_zoned
NUMBER
Signed numeric string
Maximum number of digits: 31
Maximum fractions: 11
Conversion of Scaled Data Types
The gateway converts scaled data types to Oracle data types as described in Table A-4.
Perform the following steps in Oracle Studio for IMS, VSAM, and Adabas Gateways to set the default array handling behavior:
In the Design Perspective Configuration Viewexpand the Machine folder and then expand the machine with the data source where you want to set the array handling behavior.
Expand the binding with the data source you are working with.
Expand the Data Sources folder and then right-click the data source and select Edit Data Source.
The Configuration Properties screen opens.
On the Advanced tab, in the Virtual View Policy section, configure how arrays will be handled by selecting the relevant check boxes. The following options are available:
Generate sequential view: Select this option if you want to map a non-relation file to a single table.
Generate virtual views: Select this option if you want to have an individual table created for every array in the non-relational file.
Include row number column: Select this option if you want to include a column that specifies the row number in the virtual or sequential view.
Inherit all parent columns: Select this option if you want the virtual views to include all the columns of the parent record.
Figure 4-1 Setting the Default Behavior for Handling Arrays
Modifying the Default Behavior During Metadata Import
During the metadata import procedure, the Metadata Model Selection step lets you modify the array handling policy for the tables to be imported. You can define specfic setings per table or keep the default values, which are inherited from the settings that you defined on the data source level (see Setting the Default Behavior for Array Handling). After importing a table you can change its array handling settings on the Modeling tab of the table editor (see Modifying the Array Handling Policy for a Specific Table).
See Oracle Database Gateway for IMS, VSAM, and Adabas Installation and Configuration Guide for AIX 5L Based Systems (64-Bit), HP-UX Itanium, Solaris Operating System (SPARC 64-Bit), Linux x86, and Linux x86-64 or Oracle Database Gateway for IMS, VSAM, and Adabas Installation and Configuration Guide for Microsoft Windows for details on the import procedure per data source.
Perform the following steps to select the metadata model.
In the Import Wizard, click Next until you reach the Metadata Model Selection step.
Select from the following:
Default values for all tables: Select this option if you want to apply the same values to all tables to be imported.
Note:
Selecting this check box discards all table-specific settings.
Generate sequential view: Select this option if you want to map a non-relational file to a single table.
Generate virtual views: Select this option if you want to have an individual table created for every array in the non-relational file.
Virtual views include row number: Select this optoin if you want to include a column that specifies the row number in the virtual or sequential view.
Virtual views inherit all parent columns: Select this option if you want the virtual to include all the columns of the parent record.
Specific virtual array view settings per table: Select this option if you want to apply different values to the tables to be imported. then select the relevant check boxes.
Modifying the Array Handling Policy for a Specific Table
You can also change the array handling policy on the table level. Perform the following steps to modify the policy for a specific table:
In the Design Perspective Configuration Viewexpand the Machine folder and then expand the machine with the data source where you want to set the array handling behavior.
Expand the binding with the data source you are working with.
Expand the data sources folder, then right-click the data source and select Edit metadata.
The Metadata view opens on the node of the respective data source.
Expand the data source node, and then expand the Tables node.
Double-click the table whose array handling settings you want to modify.
The table editor opens.
On the Modeling tab, configure how arrays will be handled by selecting the relevant check boxes. The following options are available:
Generate sequential view: Select this option if you want to map a non-relation file to a single table.
Generate virtual views: Select this option if you want to have an individual table created for every array in the non-relational file.
Include row number column: Select this option if you want to include a column that specifies the row number in the virtual or sequential view.
Inherit all parent columns: Select this option if you want the virtual views to include all the columns of the parent record.
Figure 4-3 Modifying the Array Handling Policy on the Table Level
Heterogeneous data access is a problem that affects a lot of companies. A lot of companies run several different database systems. Each of these systems stores data and has a set of applications that run against it. Consolidation of this data in one database system is often hard—in large part because many of the applications that run against one database may not have an equivalent that runs against another. Until such time as migration to one consolidated database system is made feasible, it is necessary for the various heterogeneous database systems to interoperate.
Oracle Database Gateways provide the ability to transparently access data located in a non-Oracle system from an Oracle environment. This transparency eliminates the need for application developers to customize their applications to access data from different non-Oracle systems, thus decreasing development efforts and increasing the mobility of the application. Applications can be developed using a consistent Oracle interface for both Oracle and Adabas.
Gateway technology is composed of two parts: a component that has the generic technology to connect to a non-Oracle system, which is common to all the non-Oracle systems, called Heterogeneous Services, and a component that is specific to the non-Oracle system that the gateway connects to. Heterogeneous Services, in conjunction with the Database Gateway agent and Oracle Connect for IMS, VSAM, and Adabas Gateways, enables transparent access to non-Oracle systems from an Oracle environment.
Oracle Heterogeneous Services
Heterogeneous Services provides the generic technology for connecting to non-Oracle systems. As an integrated component of the database, Heterogeneous Services can exploit features of the database, such as the powerful SQL parsing and distributed optimization capabilities.
Heterogeneous Services extend the Oracle SQL engine to recognize the SQL and procedural capabilities of the remote non-Oracle system and the mappings required to obtain necessary data dictionary information. Heterogeneous Services provides two types of translations: the ability to translate Oracle SQL into the proper dialect of the non-Oracle system and the ability to handle data dictionary translations so that the metadata of the non-Oracle system is displayed in the local format. For situations where no translations are available, native SQL can be issued to the non-Oracle system using the pass-through feature of Heterogeneous Services.
Heterogeneous Services also maintains the transaction coordination between Oracle and the remote non-Oracle system, such as providing the global transaction protocol to ensure distributed transaction integrity, even for non-Oracle systems that do not natively support global transactions.
See Also:
Oracle Database Heterogeneous Connectivity User's Guide for more information about Heterogeneous Services.
Oracle Database Gateways
Oracle Database Gateway for Adabas allows Oracle client applications to access Adabas data through Structured Query Language (SQL). The gateway, with the Oracle database server, creates the appearance that all data resides on a local Oracle database server, even though data might be widely distributed. If data is moved from this data source to an Oracle database, no changes in the client application's design or function are needed because the gateway handles all differences in data types or SQL functions between the applications and the database.
Using Oracle SQL, Oracle client applications can access the Adabas data source as if the data was stored in an Oracle table. A single SQL statement can access data residing in Oracle and Adabas data sources, performing heterogeneous joins and subselects. This means that you can develop one set of portable applications to use against Oracle and these non-relational data sources. You can continue to develop new information systems without losing your investment in existing data and applications.
Transactions updating Oracle and this non-relational data source are automatically protected by the Oracle global transactions feature. Use of synonyms is another Oracle feature. By setting up synonyms in the Oracle database server that point to database links to Adabas files, the physical location of the data is transparent to the client application. This allows future migration of data from Adabas to Oracle to be transparent to the client applications.
The gateway requires the Oracle database server, Oracle Connect for IMS, VSAM, and Adabas Gateways, and Oracle Studio for IMS, VSAM, and Adabas Gateways. All other Oracle products are optional. However, using other Oracle products with the gateway can greatly extend the gateway's capabilities.
The gateway can be installed on a computer where the Oracle database is installed, or on a second, standalone machine. Each configuration has its advantages and disadvantages. The issues to consider when you determine where to install the gateway are network traffic, availability of the operating system platform, hardware resources, and storage.
Oracle Connect for IMS, VSAM, and Adabas Gateways must be installed on the z/OS system where the Adabas data source is installed. To be able to configure and manage Oracle Connect for IMS, VSAM, and Adabas Gateways, you need to install Oracle Studio for IMS, VSAM, and Adabas Gateways on a computer running Windows or Linux.
Gateway Architecture
The gateway is invoked by the listener. The gateway is not multi-threaded and cannot support shared database links. Each gateway session spawns a separate gateway process, and connections cannot be shared.
The gateway is located on a Windows or UNIX computer. The non-relational data source resides on a computer running IBM z/OS. The Oracle database server can reside on the same machine as the gateway or on another machine.
The gateway interacts with the Oracle database server to interface between client applications and the Adabas data source, as shown in Figure 1-1.
Note:
The non-Oracle system in Figure 1-1 and Figure 1-2 represents the Adabas data source.
Client applications, such as Developer, connect to the Oracle database server by using Oracle Net.
The Oracle database server, which includes Heterogeneous Services and the database itself, resides on a single system. This Oracle database server also stores definitions of database links for the non-Oracle system.
The gateway to Adabas resides on a second system.
The Adabas data source, together with Oracle Connect for IMS, VSAM, and Adabas Gateways, resides on a third system, which is an IBM z/OS platform.
Oracle Studio for IMS, VSAM, and Adabas Gateways, which is used to configure Oracle Connect for IMS, VSAM, and Adabas Gateways, resides on a fourth system, which can be running a Windows or Linux operating system.
The Oracle database server on the first system uses Oracle Net to connect directly to the Adabas gateway on the second system. The gateway itself uses the Oracle Connect for IMS, VSAM, and Adabas Gateways protocol to connect to Oracle Connect for IMS, VSAM, and Adabas Gateways, which then connects to the non-Oracle system using the same protocol.
The Oracle database server and the gateway work together to present the appearance of a single Oracle database to the client. All data accessed by the client appears to reside in a single Oracle database. The client application sends a request to the Oracle database server, and the Oracle database server sends the request to the gateway.
For the first transaction in a session, the gateway logs into the Adabas data source using a username and password that is valid in the respective data source. The gateway converts the SQL statement to a native Adabas statement, and the Adabas data source performs the request. The gateway converts the retrieved data to a format compatible with the Oracle database server and returns the results to the Oracle database server, which returns the results to the client application.
Gateway Process Flow
Figure 1-2 shows a typical gateway process flow. The steps explain the sequence of the events that occurs when a client application queries the Adabas data source through the gateway.
The client application sends a query over Oracle Net to the Oracle database server.
The Oracle database server sends the query over to the gateway, again using Oracle Net.
The gateway passes the query on to Oracle Connect for IMS, VSAM, and Adabas Gateways.
For the first transaction in a session, Oracle Connect for IMS, VSAM, and Adabas Gateways logs into the Adabas data source using a user name and password that is valid in the respective data source.
Oracle Connect for IMS, VSAM, and Adabas Gateways converts the Oracle SQL statement into a data access operation understood by the data source.
Oracle Connect for IMS, VSAM, and Adabas Gateways retrieves the data.
Oracle Connect for IMS, VSAM, and Adabas Gateways converts the retrieved data into a format compatible with the Oracle database server.
Oracle Connect for IMS, VSAM, and Adabas Gateways passes the data to the gateway using the Oracle Connect for IMS, VSAM, and Adabas Gateways protocol.
The gateway returns the query results to the Oracle database server, again using Oracle Net.
The Oracle database server passes the query results to the client application by using Oracle Net. The database link remains open until the gateway session is finished or the database link is explicitly closed.
Setup Flow
To be able to access Adabas data, you need to perform the tasks described in the following list, in the specified order. Each step in the list directs you to the relevant manual or chapter.
Install Oracle Connect for IMS, VSAM, and Adabas Gateways
PKUzS S PK [.A OEBPS/c_nls.htm`
Globalization Support
D Globalization Support
The main aspect of the Globalization Support in Oracle Connect for IMS, VSAM, and Adabas Gateways is the recognition of the different characters associated with a language and the way they are encoded in various operating systems and data sources. For each supported language, a special definition file called a character set file is supplied where all the language related information is stored. For complex languages such as Chinese, Japanese, and Korean, a special library is also provided where specific conversion rules are implemented.
As a distributed product that accesses heterogeneous data sources on varied platforms, Oracle Connect for IMS, VSAM, and Adabas Gateways offers seamless conversion of text between the different character encodings used on the different platforms. Examples of such automatic conversion include:
Conversion between ASCII based encoding on open systems and EBCDIC based encoding on IBM mainframes and AS/400 machines
Conversions to and from Unicode for databases that store data in Unicode
Conversions between different encodings of the same language used on different platforms
Conversions of legacy data stored using old character encodings (such as 7-bit encoding) into the current platform encoding standard
Getting this kind of seamless Globalization Support requires the proper setting of the character set definitions according to the kind of encoding in use in the various data sources and platforms.
This section discusses the different encoding schemes in use, the character set definitions required and other Globalization Support related aspects, and contains information on the following topics:
The following terminology is used to describe character sets.
Single-Byte Character Sets
In a single-byte character set, each character is represented by a single-byte value, that is, a number between 1 and 255, inclusive. Single-byte character sets are typical of Western languages. For example, in the ISO-8859-1 (Latin) character set, the character 'A' is represented by the single byte value of 65, whereas in the US-EBCDIC character set (or in the IBM-037 character set), the same character is represented by the single-byte value of 193.
Multibyte Character Sets
In a multibyte character set, some or all of the characters are represented by more than one byte value. Multibyte character sets are typical in complex languages such as Chinese, Japanese and Korean.
Unicode Character Sets
Unicode is a universal numbering of all known characters, with each character identified by a unique number - its codepoint. Unicode has several encoding schemes, of which Oracle Application Development Framework Controller API Reference supports UTF-8 and, to a lesser extent, UCS-2.
Since the product uses 8-bit characters, the only Unicode encoding that qualifies as a 'character set' is the UTF-8 encoding. The product supports UCS-2 in its data sources (through special Unicode data types).
Customized Character Sets
The Globalization Support of Oracle Connect can be customized to add new languages and character sets not currently supported as well as to introduce special conversion cases. The customization involves editing special character set source files and building .cp files from them using the NAV_UTIL program.
Globalization Support Settings
The minimal globalization Support configuration amounts to adding the HS_LANGUAGE parameter to the HS initialization parameter file and telling the product what national language is in use.
Save the change. New servers will use the language selected.
When a language is selected, a default character set is automatically used based on the language and the platform. Table D-1 summarizes the languages, their codes, and their character sets.
Table D-1 Globalization Support Language Codes
EBCDIC CP Name
Description
Base ASCII CP
Multibyte
AR8EBCDIC420
Arabic bilingual
AR8ISO8859P6
AR8EBCDICX
Arabic + Latin
AR8ISO8859P6
BLT8EBCDIC1112
Baltic multilingual
BLT8ISO8859P13
CL8EBCDIC1025
Cyrillic multilingual
CL8ISO8859P5
CL8EBCDIC1158
Cyrillic Ukraine + Euro
CLMSWIN1251
D8EBCDIC1141
Austria - Germany + Euro
WE8ISO8859P15
D8EBCDIC273
Germany - Austria
WE8ISO8859P1
DK8EBCDIC1142
Denmark - Norway + Euro
WE8ISO8859P15
DK8EBCDIC277
Denmark - Norway
NE8ISO8859P10
EE8EBCDIC870
Latin 2 multilingual
EE8ISO8859P2
EL8EBCDIC423
Greece
EL8ISO8859P7
EL8EBCDIC875
Greece
EL8ISO8859P7
EL8EBCDIC875R
Greece
EL8ISO8859P7
F8EBCDIC1147
France + Euro
WE8ISO8859P15
F8EBCDIC297
France
WE8ISO8859P1
I8EBCDIC1144
Italy + Euro
WE8ISO8859P15
I8EBCDIC280
Italy
WE8ISO8859P1
IW8EBCDIC1086
Hebrew
IW8ISO8859P8
IW8EBCDIC424
Hebrew
IW8ISO8859P8
JA16DBCS
Japan
JA16SJIS
Yes
JA16EBCDIC930
Japan
JA16SJIS
Yes
KO16DBCS
Korea
KO16KSC5601
Yes
S8EBCDIC1143
Finland - Sweden + Euro
WE8ISO8859P15
S8EBCDIC278
Finland - Sweden
WE8ISO8859P1
TH8TISEBCDIC
Thai IS 620-2533 EBCDIC 8-bit
TH8TISASCII
TR8EBCDIC1026
Turkey
WE8ISO8859P9
WE8EBCDIC1047
Latin 1
WE8ISO8859P1
WE8EBCDIC1140
USA, Canada + Euro
WE8ISO8859P15
WE8EBCDIC1145
Spanish + Euro
WE8ISO8859P15
WE8EBCDIC1146
UK + Euro
WE8ISO8859P15
WE8EBCDIC1148
International ECECP + Euro
WE8ISO8859P15
WE8EBCDIC1148
Western Europe + Euro
WE8ISO8859P15
WE8EBCDIC284
Spanish
WE8ISO8859P1
WE8EBCDIC285
UK
WE8ISO8859P1
WE8EBCDIC37
USA + Canada
WE8ISO8859P1
WE8EBCDIC37
Canadian French
WE8ISO8859P1
WE8EBCDIC500
Western Europe
WE8ISO8859P1
WE8EBCDIC871
Iceland
NE8ISO8859P10
WE8EBCDIC924
Latin 9
WE8ISO8859P9
ZHS16DBCS
Simplified Chinese
ZHS16CGB231280
Yes
ZHT16DBCS
Traditional Chinese
ZHT16BIG5
Yes
PK
` ` PK [.A OEBPS/content.opf,~
Oracle® Database Gateway for IMS User's Guide, 11g Release 2 (11.2)en-USE12072-01Oracle CorporationOracle CorporationOracle® Database Gateway for IMS User's Guide, 11g Release 2 (11.2)2009-07-29T19:12:38ZDescribes the Oracle Database Gateway for ODBC, which enables Oracle client applications to access non-Oracle systems data through Structured Query Language (SQL).
PKʆ, , PK [.A OEBPS/dcommon/prodbig.gif GIF87a !!!)))111BBBZZZsss{{ZRRcZZ!!1!91)JB9B9)kkcJJB991ssc絽Zcc!! {祽BZc!9B!c{ !)c{9{Z{{cZB1)s Jk {{Z{kBsZJ91)Z{!{ BcsRsBc{ 9Z Zk甽k Bk R!BZ 9c )JJc{!))BZks{BcR{JsBk9k)Zck ! !B Z1k!ZcRBZc ZJkBk1Z9c!R!c9kZRZR B Z9{ 9 9 !R 1{ 99R{ 1!1)c 1 J)1B ! B J Rkk{ƽ絵ތkk絵RRs{{{{JJsssBBkkk!!9ss{{ZZssccJJZZRRccRRZZ))cBBJJ99JJ!!c11991199Z11!c!!))Z!! ! 1 B R c k { )!cJBkZRZ , HP)XRÇEZ֬4jJ0 @ "8p YҴESY3CƊ @* U:lY0_0#
5tX1E: C_xޘeK TV%ȣOΏ9??:a"\fSrğjAsKJ:nOzO=}E1-I)3 (QE QE QE QE QE QE QE ֝Hz a<["2"p O#f8 M[RL(,?g93QSZ uy"lx4h`O!LŏʨXZvq& c ՚]+: ǵ@+J]tQ]~ [[eϸ (]6A&>ܫ~+כzmZ^(<57KsHf妬Ϧmnẁ&F!:-`b\/(tF *Bֳ ~V{ WxxfCn MvF=;5_,6%S>}cQ Qjs OO5 =)Ot [W9 / {^tyNg#ЄGsֿ1 -4o oTZ? K
G c+oy ڙo Nuh ^ iSo5{\ܹ3Y os } $.nQ-~n,-zr~-|K4R"8a{]^;I<ȤL5"EԤP7_j>OoK; *U.at*K[fym3ii^#wcC ' IIkIp$|CtĈpW¹l{9>⪦*ͯj.LfGߍԁw] |WW18>w.ӯ! VӃ
:#1~
+މ=;5c__b@W@ +^]ևՃ7 n&g2I8Lw7 uҭ$"&"b
eZ":8)D'%{} 5 {; w]iu;_dLʳ4R-,2H6>½HLKܹR ~foZKZ 1[ oZ 7Z7 R ¢ ?«'y? A}C _iG5s_~ ^
J5? tp]X/c' r%eܺA|4ծ-Ե+ْe1M38Ǯ
`|Kյ OVڅu;"d56, X 5kYR<̭CiطXԮ];Oy)OcWj֩}=܅s۸QZ*<~%뺃ȶp f~Bðzb\ݳzW*y{=[
C/ Ak oX C k t_s }{'y? AmC jޓ{
WRV7r. g~Q"7&+c<=,dJ1V߁=T)TR՜*N4
^Bڥ% B+=@fE5ka}ędܤF H^i1k\Sgdk>
֤aOM\_\T)8靠㡮3ģR: jj ,p k/K!t,=ϯZ6(( ( ( ( ( ( 49
xn_kLk&f9sK`zx{ {y8H
8 b4>ÇНE |7v( z/] k7IxM}8!ycZRQ
pKVr(RPEr?^}'ðh{ x+ՀLW154cK@ Ng C)rr9+c:b Жf*s^ fKS7^}
*{zq_@8# p F~
[VPe(nw0MW=3#kȵ z晨cy PpG#W:% drMh] 3HH<\]ԁ |_ W
HHҡb}P >k {ZErxMX@8C&qs kLۙOnO^sCk7ql2XCw5VG.S~ H 8=(s 1~ cV5z
%v|U2QF=NoW]ո?<`~}=ӬfԵ,=; "~Iy7K#g{ñJ?5$y`
zz @-~ m7 mG宝Gٱ>G&K#]y1$$t>wqjstX.b̐{Wej)Dxfc:8)=$y|L`xV8ߙ~E)HkwW$J0uʟk>6S gp~;4W+חc"=|ř9bc5 > *r g {~cj1rnI#G|8v4wĿhFb><^ pJLm[Dl1;Vx5IZ: 1* p)إ1Z bAK(1ׅ|S& 5{^
KG ^ 5r>;X K^?
s fk^8O/"J)3K]N)iL ?5 !ƾq :G_ =X -
i,vi2N3
|03Qas ! 7}kZU781M,->e;@Qz
T (G K(ah ( ( ( ( ( ( ( Y[×j2F}o־oYYq $+]%$ v^rϭ` nax,Z EuWSܽ,g%~"MrsrY~Ҿ"Fت;8{ѰxYEfP^;WPwqbB:c?zp<7 ;SB fZ)dϛ;
7s^ >}⍱ x?Bix^#hf,*P9S{w[]GF?1Z_nG ~]kk)9Sc5Ո<<6J-ϛ}xUi>ux#ţc'{ᛲq?Oo?x&mѱ'#^t)ϲbb0
F «kIVmVsv@ }kҡ!ˍUT txO̧] ORb|2yԵk܊{sPIc_?ħ:Ig)= Z~'
"\M2VSSMyLsl⺿U~" C7 \hz_ R s$~?
TAi< lO* > U}+'f >7_ K N s 8g1^C e КÿE ;{+Y\ O5|Y{/o+ L Vc O;7Zx-Ek&dpzbӱ+ TaB0gNy
3^c T\ $ ⫫?F33?t ._ Q~Nln:U/Ceb1-im
WʸQM+Vp afR3 dé|Aү-q*I P7:y&]hX^Fbtpܩ?|W uʫxJ3ߴm"(uqA}j .+?S
wV ~
[B&<^U?r Ϝ_O H\'. ;|. % pw/ ZZG'1 j(#0UT` Wzw}>_*9m>F?EL3"zpubzΕ$+0܉&3zڶ+jyr1QE
(
(
(
(
(
(
(
(UIdC0EZm+]Y6^![
ԯsmܶ捆?+ me+ZE29)B[ ;я*wG xsK 7;5w)}gH ~ .Ɣx?X \ߚ } A@tQ(:ͧ|Iq(CT ?v [sKG+*ר qҍck <#Ljα5݈`8cXP6T5i.K!xX*p&ќZǓϘ7 *oƽ:wlຈ:Q5yIEA /2 *2jAҐe}k%K$N9R2? 7ýKMV!{W9\ PA+c4w`
Wx=Ze\X{}yXI Ү!aOÎ{ ]Qx)#D@9E:*NJ}b|Z>_k7:d$ z
> & VvWlR:R qJfGإd9Tm(ҝEt O}1O[x xEYt 8,3v bFF
)ǙrPNE8=O #V*Cc&l&cmCh<.P{ ʦ&ۣ Y+G xs~k5$> ӥPquўZt~Tl >Q .g> %k#ú:Kn'&{[yWQGqF}AЅ /}<;VYZa$wQg!$;_
$N KS} _{ MY| w7G!" \J tRy+贾d| o/;5jz_6fHwk<Ѱ J#]kAȎ J =YNu%dxRwwbEQE QE QE QE QE QE QE QE QE 'f LQZ(1F)hQ@X1KE QE -Q@ 1KE 3h=iPb( ( (1GjZ(- ʹRPbR@ 1KE 7`bڒyS0(-&)P+ ڎԴP11F)h&: LRmQ@Q@(((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((?l:ϊ w
"{{-3j3 % {sj~2=
7
~MڅKrHb|P3
r=Ҁ
+ Ş/$iu 7=q2dԂxn⸷9$l]H #WI ;\[ݚD8C3p&0U9^AnK vI+!I8>5(zqj03Y.X ,@85 ߛ8>pq8=}
\xmm常8` $Q@$ v7zwp]ɝA GX; y_]覮O&4 SPtY.X),@84U=7Vuv K4, $g {@<+uqtiG w3;
I@ORմn5MBp%8' ƫ%u6uB JrHRN2@ϸ
J(9i[[mh =k O} 7ߗg Ee^%tk56v@ DrF@b2ϱ7zwp]ɝA G@(/zhzhZ G$e8?.v:f xoSUԙ I (b+xV+{O+8fv' $(ryᵷX$/$0UE$x sQvqX]wk&vMDlppA X t[ ?BywwNՋ=2K--cF I TpO
ռW4x䍃+sǟƞĺ43$K ܢgiսis=칎pTH>r*VZ8fWh[$a9S#PʎyᵷX$/$0UE$x sRWntoYi.FZث"nrsW r8
7ĺp^N]IX.@
IH\?<1LnoldXPk.d} D[;>_-q#
*g vgdH$F TwZݾhQlX Q@(KjK[b GEchNrr01EjPEPEPEPEPEPEPEPEPEPEPEP_ § FװWf_ |68`SgI*m$ עw ? P7fIF 3(%E ##lxjmG eow%1Gs wB 9W sT|绒K6z!eAۆe% Ӝg>%6kWТ;dV*9vXNx yW-% o2>h >_Ny 5E|?R}C:O(Etqw7 3CK}
?ϷyQOg-<~ wm˝wǺԓt I"
*fLnIN.F33lZwl%BrpH ``zA7'-٧iFR/)D7x[KÍoK5;͍6qj ʹzN8 oSռ' ݻfrsg,GL/ Njad&Yq%H9/q'%_C z S m+%'ze֟2Kw2s[! `_OQ3P
~,ŧh5 HynNYIm
`uS}C xim5jM'I%fX*3|odnA@pGP#[ z;eK ^(#/ HB31 t($.ljn<$ m=\)@27'p<ēIYcŐFG^A O#sj:^5pczݷʁ$x
*ڏ'B;o \
p$u
.xAb0Ms :{{HุXUr`: bj_ '-l F 3>ُ'8ϙ~ M^麏.#K+Ds)GRX&s 8<>;y ۭ Z|VKm^TW{H6y#8= yGT^iW
cO +Nn n@w 5CT0`ιx
XEi B" vQw T8h4~ A9aX&FxqYu
Ɣp'զ t9ҀZi>-'-oM2/K)h"01 2<6gh7w8:ՔKM+ݾU.70 `^@ ?#_t {cshĐܧsُ`;{:;9-婁?~@6*z?$Ơq#Ӣ]GRK n# lX]݀N8 gIj:_Yle~ݾEBy$
]ǂ|O ,u ^g<3n>s=;7 _V=6{)uG$ 2yF~peiMh \T Tr:99=gWxZX̗֙w2Mq#yÃrĸ$Y>_ U =ƓJzVUH!Jn_pF| W! ?l =$ٳ~3w8k/Fz]
wg
F.G]
q,& &kvJvo8Ͻz մnTϷr<3x$r+o6R:C!ˑrރs(@^x:Ŧkm (|w@ wQK~
1%ƥ,,&V
æf!&9th ~ښIaN9q c/>hSxo'XÚapZ%<%M R]ތo)Y"!
.U( ϶?s}h?to1ۣHW=/E- obKͱ,3
(>x N= I9YA\ #W^n#TUQ}NJJɐgo5cwEf,u93r077p6yo%8um7\ݯ:> ˦8P"ڣ 7:<$]_=KJ8`r)>j )$,M{dP'
ZF6A\A1PKC&xRo |>-ۈ#g
`A
9n u۟|+%A˪˹.}lUULf nn
ⷂ|;gF{U/H|Þ ¾ %OQ<;z+ȐęC,;" i9q\g<5lz"ΦL1 ̯,OxF}/Ӯ>}=[ͼ#<[p`r9_⎿g#z|9yv..!ĉ C#0 p V ( *m t 2\ŧq}g* RoޭI
U88
(((((((((((( ~I g;Ws н r :nn^ic9vK` aW(89 |>war#s« j܋,5m" {Vi6@HNLztP~~!ӞWղvLH#r' 0Fx5ῇ~%}H, hL b:9/]c) G 8jhz\ kw9$28 -.S62:u ӿ ?K>/#{ٳng9~gn{M.}Ȁ f+Ws[=:WaE s?# HI2lqߔ Fqt sK5;hld?F pA "Q@ = C D2 9Gbq^hѴ GT,R_:o58b@ۦ: 4;IgӨ1J.ѺD!' AQ{ׄ|+xo4}.IWNY+ |QE G<[oqsA*92<G n%;y fE( ڻ(5[z} ;XV%-2B3}\ +ï^k$#PXDžm{Q;?>Kγ5|yE랕E ׄ|Ux/5 Y'~ -
lWQE c{º,ͮAe}Y$8 Qx[F . S|7]|ʜH^[PEPEPEPEPEPEPEPEPEPEPEPEPSյ-?_ h.213"̾l 1>ַ>/i u .6M7\8a$~5_ § FAI n Q r|I>xs_.SiL4K͜K[ <4<`tUb a 3)9 zWx6SH5
F2P̤ W Ÿ<i!@( QHp>1\ ]OĒ|EӵvPxw$o"1dy"O3Kx'1 ) @pN#rr 3/t>, T
j|Efs]+9u5 bhI% q#2X
|![ ( Lꭎ@c0$g}z4^$#էW m¨M!c5 ϊ jVч .P2*_ ~x-<'M ƺs^h~oO I!f##*H8':
xK.gmN4i-fh)*A@8 w?± h+>ږ[/P8ѤbT WWO4
`Ʃ|?5M7 g6 ܐFk<7/ Ku sǷw^ Je?(dHPrw`> _[kwd_-.~ˍA3g^9^v-zΡ"i4q+q] &TcQ^O G?5V >#4J|ՕdT+1V h.OCZVօpjʫ(EpÿN w umKF-Ɨ]ڜhZ1_*STq+oǞ:Ȗu"Òe$/+ͷn/~ .2Љe}hc
(_.h SEM̀}f灷$
>1G\5A^ x+)'t?I+x%Yk #ik7@2>^?
;]u(uzn,
S@ e _ W?J
o@ &n|#n7/¼ y V?-zGCoO :"2G nb89
PRINT NOGEN
DBD NAME=HOSPDBD,ACCESS=HDAM,RMNAME=(DFSHDC40,40,100)
DATASET DD1=PRIME,DEVICE=3390
SEGM NAME=HOSPITAL,PARENT=0,BYTES=80
FIELD NAME=(HOSPNAME,SEQ,U),BYTES=20,START=1,TYPE=C
FIELD NAME=ADMIN,BYTES=20,START=61,TYPE=C
SEGM NAME=WARD,PARENT=HOSPITAL,BYTES=31
FIELD NAME=(WARDNO,SEQ,U),BYTES=2,START=1,TYPE=C
FIELD NAME=BEDAVAIL,BYTES=3,START=9,TYPE=C
FIELD NAME=WARDTYPE,BYTES=20,START=12,TYPE=C
SEGM NAME=PATIENT,PARENT=WARD,BYTES=125
FIELD NAME=(BEDIDENT,SEQ,U),BYTES=4,START=61,TYPE=C
FIELD NAME=PATNAME,BYTES=20,START=1,TYPE=C
FIELD NAME=DATEADMT,BYTES=6,START=65,TYPE=C
SEGM NAME=SYMPTOM,PARENT=PATIENT,BYTES=77
FIELD NAME=(SYMPDATE,SEQ),BYTES=6,START=21,TYPE=C
FIELD NAME=DIAGNOSE,BYTES=20,START=1,TYPE=C
SEGM NAME=TREATMNT,PARENT=PATIENT,BYTES=113
FIELD NAME=(TRDATE,SEQ),BYTES=6,START=21,TYPE=C
FIELD NAME=TRTYPE,BYTES=20,START=1,TYPE=C
SEGM NAME=DOCTOR,PARENT=PATIENT,BYTES=80
FIELD NAME=DOCTNAME,BYTES=20,START=1,TYPE=C
FIELD NAME=SPECIALT,BYTES=20,START=61,TYPE=C
SEGM NAME=FACILITY,PARENT=HOSPITAL,BYTES=26
FIELD NAME=FACTYPE,BYTES=20,START=1,TYPE=C
FIELD NAME=FACAVAIL,BYTES=3,START=24,TYPE=C
DBDGEN
FINISH
END
