Accessing native VSAM files using IDMS SQL.

Products

• IDMS
• IDMS - Database

Issue/Introduction

 It is possible to access native VSAM files from within IDMS applications using either network or SQL DML. This document describes the steps for accessing this data using SQL DML.

To use native VSAM files in an IDMS environment, VSAM structures must be defined in the Schema, Segment, and DMCL. Definitions for each of these are addressed in detail in the IDMS Database Administration Guide.

Environment

Release: All supported releases.
Component: SQL Option.

Resolution

To access a native VSAM file using IDMS SQL the steps below are required. If the native VSAM file is already accessible using network DML, then steps 1 to 3 have already been done.

1. Determine data eligibility. As long as the file is "standard" native VSAM, (KSDS, ESDS, RRDS) then it can be accessed with IDMS SQL.
2. Define the native VSAM file to the DMCL. On the CREATE FILE statement for the native VSAM file, it is important to specify file type VSAM. For a KSDS it is also important to include the clauses FOR CALC and FOR SET <set-name>. Set-name is the name of a set defined by a schema SET statement with the VSAM index clause.
3. Create a network schema that includes the definition of the VSAM file. At this point, the data can be accessed using network DML, and this should be tested to verify the success of the previous steps if desired.
4. Create an SQL schema that references the network schema in step 3. The syntax is CREATE SCHEMA SQLVSAM FOR NONSQL SCHEMA dictname.VSAMSCHM DBNAME <vsdbname>. Of course, use site-specific names for the SQL and non-SQL schemas in this syntax.
5. Then use this SQL schema to access the native VSAM file:  Select <column-list> FROM SQLVSAM.<vsam-record> WHERE ...; 

As long as the IDMS SQL option and IDMS Server are installed, the native VSAM files can be accessed without the need to purchase or install any additional software. Only the few additional set-up steps outlined above will be required. At run-time, the SQL optimizer will determine the physical path to the VSAM file on the IDMS side and the server will allow web access using either ODBC or JDBC. Set access to the VSAM record will be based on either the primary index key, or an alternate index, depending on how the definition is specified in the DMCL and how the SQL DML is coded.

Additional Information

SQL Reference