Accessing native VSAM files using CA IDMS SQL.

Document ID : KB000025513
Last Modified Date : 14/02/2018
Show Technical Document Details



It is possible to access native VSAM files from within CA 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 a CA IDMS environment, you must define VSAM structures in the Schema, Segment, and DMCL. Definitions for each of the3se are addressed in detail in the CA IDMS Database Administration Guide.


CA IDMS SQL option and the CA IDMS Server must be installed in order to use SQL DML.


To access a native VSAM file using CA IDMS SQL the steps below are required. If you are already able to access the native VSAM file using network DML. Then you have already accomplished steps 1 – 3 below; and can skip directly to step 4.

1-   1- First, we must determine data eligibility. As long as the file is 'standard' native VSAM, (KSDS, ESDS, RRDS) then you will be able to access it with CA IDMS SQL.

2-   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-   3- Create a network schema that includes the definition of the VSAM file.  At this point you will be able to access the data using network DML, and you can test this to verify the success of the previous steps if desired.

4-   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 your own names for the SQL and non-SQL schemas in this syntax. 

5-   5- You can then use this SQL schema to access the native VSAM file:  Select <column-list> FROM SQLVSAM.<vsam-record> WHERE ...; 

As long as your site has installed the CA IDMS SQL option and the CA IDMS Server, you will be able to access the native VSAM files 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:

Details on how to define a native VSAM file in the DMCL can be found in the CA IDMS Database Administration Guide.


Details on defining schemas and coding SQL DML can be found in the CA IDMS SQL Reference.