| Oracle® Database Gateway for VSAM User's Guide 11g Release 1 (11.1) Part Number B31054-01 |
|
|
View PDF |
| Oracle® Database Gateway for VSAM User's Guide 11g Release 1 (11.1) Part Number B31054-01 |
|
|
View PDF |
After the gateway is installed and configured, you can use the gateway to access the Virtual Storage Access Method (VSAM) data, pass VSAM commands from applications to the VSAM database, perform distributed queries, and copy data.
This chapter contains the following sections:
You can access VSAM data either directly or under CICS, as follows:
VSAM (CICS): If your VSAM files are installed under CICS, the VSAM (CICS) data source accesses VSAM by making calls from the external CICS interface (EXCI) to a CICS program that is provided as part of the Oracle Connect installation. This CICS program does the actual VSAM reads and writes from within CICS.
When you access VSAM data using this data source, the following restrictions apply:
SQL DELETE operations are not supported for ESDS files.
Using an alternate index to access an entry sequenced data set (ESDS) file is not supported.
A non-unique alternate index for a key sequenced data set (KSDS) file is not supported.
VSAM Batch: This data source connects directly to the VSAM data and is limited if the VSAM files are managed by CICS. In this case, it is recommended to use this data source for read-only access to VSAM files. However, this may not give you adequate read integrity if some changes are buffered by CICS. Another alternative is to use the VSAM under CICS data source.
When you access VSAM data using the VSAM/Batch data source, the following restrictions apply:
Transactions are not supported when you access VSAM directly. When you update VSAM data, the update is committed immediately. You cannot issue rollback to un-commit the update.
Locking is not supported.
You cannot update an array value (a child record in a hierarchical table) when the parent record is included in the SQL in a subquery.
SQL DELETE operations are not supported for ESDS files.
An relative record data set (RRDS) file cannot have an alternate index.
The primary key of a KSDS file must be one segment only (however, it can be several consecutive fields). You cannot modify the primary key value of a KSDS file.
Oracle Connect for IMS, VSAM, and Adabas Gateways supports VSAM data on IBM z/OS platforms. VSAM files of types Key Sequenced Data Structure (KSDS), Entry Sequenced Data Set (ESDS), and Relative Record Data Set (RRDS) are supported.
Oracle Database Gateway for VSAM supports SQL-based data access, allowing developers and applications to use this common and standard syntax for retrieving and updating VSAM 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 VSAM data. This means that Oracle Database Gateway for VSAM 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 VSAM 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 VSAM 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 VSAM 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 VSAM, 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 VSAM offers robust SQL support and relational access to non-relational, proprietary, legacy VSAM data. The following topics provide more information about specific SQL features and their support by the gateway.
This section includes the following topics:
The VSAM (CICS) data source supports global transactions.
To use Oracle Connect for IMS, VSAM, and Adabas Gateways with global transactions, you must have RRS installed and configured and have CICS TS 1.3 or later installed.
The VSAM file participating in the 1PC or global transaction must be defined as recoverable. To define a file as recoverable, perform these steps.
Install the VSAM file under CICS.
In CICS, go to the recovery parameters section of the relevant file.
Set the value of the RECOvery parameter to Backoutonly and the value of the Fwdrecovlog parameter to no.
You can import a COBOL copybook to define a VSAM record structure of an existing VSAM data set. However, you can also create a VSAM data set and define its record structure by using the pass-through feature. You can then view this structure in Oracle Studio for IMS, VSAM, and Adabas Gateways.
Note:
This option is not supported when you access VSAM data under CICS.Use the DBMS_HS_PASSTHROUGH package in a PL/SQL block to specify the statement to be passed to the VSAM database, as follows:
DECLARE
num_rows INTEGER;
BEGIN
num_rows := DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@VSAM('command');
END;
/
Where command cannot be one of the following:
BEGIN TRANSACTION
COMMIT
ROLLBACK
SAVE
SHUTDOWN
VSAM tool commands
For example, to create a table using the pass-through feature, run the following command:
SQL> declare
ret integer;
begin
ret := dbms_hs_passthrough.execute_immediate@dg4vsam(' create table sample(I int,c char(10))');
end;
/
PL/SQL procedure successfully completed.
The DBMS_HS_PASSTHROUGH package supports passing bind values and executing SELECT statements.
Note:
TRUNCATE cannot be used in a pass-through statement.See Also:
PL/SQL Packages and Types Reference and Chapter 3 of Oracle Database Heterogeneous Connectivity Administrator's Guide for more information about the DBMS_HS_PASSTHROUGH package.Naming rule issues include the following:
The concept of owner does not exist for objects from VSAM or VSAM/CICS 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 VSAM. You must not use an explicit owner qualifier to reference VSAM or VSAM/CICS 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 VSAM documentation for more information on naming objects and Oracle Database Error Messages for more information on error messages.Data type issues include the following:
Oracle SQL uses hexadecimal digits surrounded by single quotes to express literal values being compared or inserted into columns defined as data type RAW.
This notation is not converted to syntax compatible with the VSAM VARBINARY and BINARY data types (a 0x followed by hexadecimal digits, surrounded by single quotes).
For example, the following statement is not supported:
SQL> INSERT INTO BINARY_TAB@VSAM VALUES ('0xff')
Where BINARY_TAB contains a column of data type VARBINARY or BINARY. Use bind variables when inserting into or updating VARBINARY and BINARY data types.
VSAM does not support implicit date conversions. Such conversions must be explicit.
For example, the gateway issues an error for the following SELECT statement:
SELECT DATE_COL FROM TEST@VSAM WHERE DATE_COL = "1-JAN-2001";
To avoid problems with implicit conversions, add explicit conversions, as in the following:
SELECT DATE_COL FROM TEST@VSAM WHERE DATE_COL = TO_DATE("1-JAN-2001")
See Also:
Appendix C, "Data Dictionary" for more information about restrictions on data types and Oracle Database Error Messages for more information on error messages.Query issues include the following:
VSAM evaluates a query condition for all selected rows before returning any of the rows. If there is an error in the evaluation process for one or more rows, no rows are returned even though the remaining rows satisfy the condition.
Oracle evaluates the query condition row-by-row and returns a row when the evaluation is successful. Rows are returned until a row fails the evaluation.
See Also:
Oracle Database Error Messages for more information on error messages.Oracle processes an empty string in a SQL statement as a null value. VSAM processes an empty string as an empty string.
Comparing to an empty string
The Gateway passes literal empty strings to the VSAM database without any conversion. If you intended an empty string to represent a null value,VSAM 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"@VSAM where "ename" IS NULL;
Selecting an empty string
For VARCHAR columns, the gateway returns an empty string to the Oracle Database 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 the VSAM database as a NULL value.
The locking model for a VSAM database differs significantly from the Oracle model. The gateway depends on the underlying VSAM behavior, so Oracle applications that access VSAM through the gateway can be affected by the following possible scenarios:
Read access may block write access.
Write access may block read access.
Statement-level read consistency is not guaranteed.
See Also:
VSAM documentation for information about the VSAM locking model.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:
As virtual views. This method generates a virtual view for every array in the parent record that contains all the array members.
As a sequential view (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 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.
Table 2-1 SQL Syntax Restrictions
| Syntax | Restriction |
|---|---|
|
WHERE CURRENT OF clause |
|
|
CONNECT BY clause |
The gateway does not support the |
|
ROWID |
The Oracle ROWID implementation is not supported. |
|
Subqueries in INSERT statement |
Subqueries of SQL> INSERT INTO "emp_target"@VSAM
SELECT a."empno" FROM "emp_source"@VSAM a,
"emp_source"@VSAM b WHERE b."empno"=9999
|
|
EXPLAIN PLAN statement |
The |
|
Date arithmetic |
The following SQL expressions do not function correctly with the gateway: date + numbernumber + datedate - numberdate1 - date2
Statements with the preceding expressions are sent to the VSAM database without any translation. Since VSAM does not support these date arithmetic functions, the statements return an error. See Also: Oracle Database Error Messages for more information on error messages. |
|
String functions |
If you concatenate numeric literals using the "||" operator when using the gateway to query a VSAM database, the result is an arithmetic addition. For example, the result of the following statement is 18: SQL> SELECT 9 || 9 FROM DUAL@VSAM; The result is 99 when using Oracle to query an Oracle database. |
|
Schema names and PL/SQL |
If you do not prefix a VSAM database object with its schema name in a SQL statement within a PL/SQL block, the following error message occurs: ORA-6550 PLS-201 Identifier table_name must be declared. Change the SQL statement to include the schema name of the object. See Also: Oracle Database Error Messages for more information on error messages. |
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 VSAM-CICS data source supports global transactions. If the VSAM-CICS 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.The VSAM batch data source only supports auto-commit mode. Therefore, the gateway cannot guarantee transactional integrity. Each UPDATE, INSERT, and DELETE statement is immediately committed on the VSAM side and cannot be rolled back.
Note:
You can choose to run the gateway for the VSAM batch data source in read-only mode by setting the HS_FDS_TRANSACTION_MODE parameter toREAD_ONLY. Carefully weigh the advantages and disadvantages of executing updates on the VSAM data source using the gateway.Any COMMIT or ROLLBACK issued in a PL/SQL cursor loop closes all open cursors, which can result in the following error:
ORA-1002: fetch out of sequence
To prevent this error, move the COMMIT or ROLLBACK statement outside the cursor loop.
See Also:
Oracle Database Error Messages for more information on error messages.VSAM commands cannot be issued using the pass-through feature. You should only use the pass-through feature to issue CREATE TABLE statements under the VSAM batch data source that result in the creation of a VSAM data set whose record format is exposed to Oracle Studio for IMS, VSAM, and Adabas Gateways.
The gateway is not multithreaded and cannot support shared database links. Each gateway session spawns a separate gateway process and connections cannot be shared.
The FLOAT data type precision is (7).
The VARBINARY data type is reported as BINARY.
Oracle Database no longer supports the initialization parameter DBLINK_ENCRYPT_LOGIN. Up to version 7.3, this parameter's default true value prevented the password for the login user ID from being sent over the network (in the clear). Later versions automatically encrypt the password.
When accessing VSAM data, the following restrictions apply:
The following restrictions apply to both the VSAM Batch data source and the VSAM (CICS) data source:
DDL is not supported.
Record-level locking is not supported.
Record-level sharing (RLS) is not supported.
UPDATE operations on arrays (OCCURS clauses) are not supported. See Normalizing Non-Relational Data for details on handling arrays.
Compressed VSAM files are only supported if the compression package makes the compression transparent.
Records in ESDS files cannot be deleted from a table. Therefore, when deleting a record from a table, first make sure that it is not included in a file of type ESDS.
The following restrictions apply to the VSAM (CICS) data source only:
SQL DELETE operations are not supported for ESDS files.
Using an alternate index to access an ESDS file is not supported.
A non-unique alternate index for a KSDS file is not supported.
The following restrictions apply to the VSAM Batch data source only:
Transactions are not supported when accessing VSAM directly.
When accessing VSAM under CICS, global transactions are supported.
Locking is not supported.
SQL DELETE operations are not supported for ESDS files.
An RRDS file cannot have an alternate index.
