Skip Headers
Oracle® Database Gateway for VSAM User's Guide
11g Release 1 (11.1)

Part Number B31054-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

2 Gateway Features and Restrictions

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:

2.1 Accessing VSAM Data

You can access VSAM data either directly or under CICS, as follows:

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.

2.2 SQL Support

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:

See Also:

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

2.2.1 Transaction Support

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.

  1. Install the VSAM file under CICS.

  2. In CICS, go to the recovery parameters section of the relevant file.

  3. Set the value of the RECOvery parameter to Backoutonly and the value of the Fwdrecovlog parameter to no.

2.2.2 Pass-Through Feature

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.

2.2.3 Naming Rules

Naming rule issues include the following:

2.2.3.1 Rules for Naming Objects

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.

2.2.3.2 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.

2.2.4 Data Types

Data type issues include the following:

2.2.4.1 Binary Literal Notation

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.

2.2.4.2 Data Type Conversion

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.

2.2.5 SQL Execution

Query issues include the following:

2.2.5.1 Row Selection

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.

2.2.5.2 Empty Strings

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 (' ').

2.2.5.3 Empty Bind Variables

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

2.2.6 Locking

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.

2.3 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:

2.4 Restrictions

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

2.4.1 SQL Restrictions

Restrictions related to SQL are described in the following sections:

2.4.1.1 Unsupported SQL Functions

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

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

2.4.1.2 SQL Syntax

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

Table 2-1 SQL Syntax Restrictions

Syntax Restriction

WHERE CURRENT OF clause

UPDATE and DELETE statements with the WHERE CURRENT OF clause are not supported by the gateway because they rely on the Oracle ROWID implementation. To update or delete a specific row through the gateway, a condition style WHERE clause must be used.

CONNECT BY clause

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

ROWID

The Oracle ROWID implementation is not supported.

Subqueries in INSERT statement

Subqueries of INSERT statements cannot use multiple aliases for the same table. For example, the following statement is not supported:

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 EXPLAIN PLAN statement is not supported.

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.


2.4.1.3 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 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.

2.4.1.4 Transactional Integrity

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 to READ_ONLY. Carefully weigh the advantages and disadvantages of executing updates on the VSAM data source using the gateway.

2.4.1.5 COMMIT or ROLLBACK in PL/SQL Cursor Loops

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.

2.4.1.6 Pass-Through Feature

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.

2.4.1.7 Database Links

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

2.4.1.8 Data Types

The FLOAT data type precision is (7).

The VARBINARY data type is reported as BINARY.

2.4.1.9 Encrypted Format Login

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.

2.4.1.10 Data Dictionary Views and PL/SQL

Do not refer to data dictionary views in SQL statements contained within PL/SQL blocks.

2.4.2 VSAM Restrictions

When accessing VSAM data, the following restrictions apply:

2.4.2.1 General VSAM Restrictions

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.

2.4.2.2 Restrictions Specific to VSAM (CICS)

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.

2.4.2.3 Restrictions Specific to VSAM Batch

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.