Follow

Column-based VSAM-SQL Data Bridge

Introduction

The column-based VSAM-SQL bridge is one form of data transparency that enables the use of standard COBOL I/O statements (against indexed datasets) to access data that has been extracted and stored in SQL tables on a column basis (i.e., each COBOL record element is stored in a separate database column value).  "File" names in COBOL, ETP and EBP are denoted with file protocol VSQL as in "VSQL:CUSTOMERS.DAT" stored in a database as a table name designated in a side file and with column mapping defined by an XML file, both of which are packaged in the application's .war or .ear file.  An alternate form of the VSAM Transparency Mode stores COBOL records as a whole (file protocol "VDB").  See Record-based VSAM-SQL Data Bridge for more information.

Data can be shared between Elastic Transaction Platform (ETP) processes, Elastic Batch Platform job steps, and non-ETP processes running concurrently on a single system or across multiple systems in a scale-out Heirloom Platform-as-a-Service environment. VSQL and VDB files are the only forms of files accessible by ETP across multiple, concurrent sessions.  VSQL and VDB protocols are the only forms of files supported by ETP across multiple, concurrent sessions unless the TCP/IP network-based remote files supported by the Elastic COBOL file and lock server.

Advantages of VSQL over VDB transparency mode include:

  • Ease of accessing "COBOL files" by non-COBOL programs using standard SQL (including JDBC APIs) without understanding the internal representation (e.g., packed decimal) of individual columns
  • Supports VSAM Keyed Sequence Dataset (KSDS) compatibility
  • Leverage a database's ability to convert between different data types for a variety of uses
  • At the expense of performance, stores each COBOL element in a record as a separate database column (also allowing other non-COBOL applications to readily access the data)

Advantages of VDB over VSQL transparency mode include:

  • Supports mixed record types (COBOL groups with REDEFINES)
  • Does not require compiler support ($XFD directives) or an XML file
  • Does not require side-files or properties to indicate file/table mapping
  • Files (tables) and their indexes are created dynamically upon first reference
  • Supports VSAM Keyed Sequence Dataset (KSDS), Relative Record Dataset (RRDS) and Entry Sequenced Dataset (ESDS) compatibility
  • Loads files directly from SEQUENTIAL, LINE SEQUENTIAL, etc. via EBP IDCAMS or simple COBOL program that READs/WRITEs records at a time
  • Performance is faster, especially if there are large numbers of elements in the COBOL record (but the entire record is stored as a database BLOB column, making it more difficult for non-COBOL applications to access the data)

Using this Guide

Within each section, there are a set of suggested actions that you should take. These actions are identified as:

  • Perform the action described in this text.

Outline

This article covers the following steps:

  1. Setting up a sample dataset in Oracle
  2. Creating a new Elastic COBOL project
  3. Configuring the runtime environment
  4. Deployment and use of programs using the VSAM-SQL Bridge within job steps of the Elastic Batch Platform (EBP)

Setting up the Database

NOTE: To create the database table, we are going to use the same steps as the tutorial in the Getting Started Guide (which creates the "acctfil" table in Oracle Database Express), so if you have already done this, you can skip this section. Otherwise:

  1. Download and install Oracle Database 11g Express Edition from http://www.oracle.com/technetwork/database/express-edition/downloads/index.html – follow the instructions on the website for your platform.
  2. Unless you specified otherwise, by default, the HTTP listener for Oracle XE database will have been installed on the same port as the Apache application servers (i.e. 8080). To avoid this conflict:

    • Start a command console on your system, enter “sqlplus system” and enter the password (that you setup when you installed the database).
    •  In the “SQL Command Console”, enter:
      • SQL> begin dbms_xdb.sethttpport(’8081’); end; [enter]
      •  / [enter]
      • PL/SQL procedure successfully completed.
      • SQL> quit [enter]
  3. In the address bar of your browser, enterhttp://localhost:8081/apex/f?p=4950”. This will load the initial console for the Oracle Express Database.vsql7.PNG
  4. In the Oracle Express Database window, click on “Application Express”. Enter the username (e.g. “system”) and the password that you set the database up with when you installed it; and click “Login”.
  5. In the “Create Application Express Workspace” window, enter “ACCTUSER” in the “Database Username” text-box, enter “account” in the “Application Express Username” text-box, and enter “acctpassword” in both the “Password” and “Confirm Password” text-boxes.
  6. Click “Create Workspace”.
  7. Once the workspace has been created, you will see the following message appear. Click “click here” to login.vsql2.png
  8. On the login screen, enter “acctpassword” and click “Login”.
  9. We are now going to load the application data into the database. Click the “SQL Workshop” box, click the “SQL Scripts” box, and then click the “Create >” button.
  10. Enter “acctfil” in the script name text-box.
  11. Download the "oracleXE-acctfil.sql" file attached to this article, and open it in a text editor.
  12. Copy/Paste the contents into the script editor, and click the “Save” button (located at the top right of the page).vsql3.png
  13. Click the “Traffic Light” icon, and then click the “Run Now” button to execute the script.vsql4.png

The database is now populated with the applications dataset. Optionally, you can click on the icon below “View Results” to see the output from running the script. Here, you will see “1 Error” which is the result of a request to unload a database called “ACCTFIL” (on the chance that one already exists). 

Setting up the Elastic COBOL project

From within the Elastic COBOL IDE, complete the following steps:

  1. File > New > COBOL Project
  2. Enter "acctvsql" in "Project name:" and select "Samples > VSAM SQL Bridge > Finish".
  3. In the "Navigator" view, expand "acctvsql > listing". In this folder you will find "acctfil.xml", which was created by the Elastic COBOL compiler when the project was built.
    • This XML file is the result of the $XFD statement in acctvsql.cbl -- this generates the meta-data that the runtime uses to map a COBOL record layout to the database table.
  4. In the "Navigator" view drag the "acctfil.xml" file from the 'listing' folder to the 'resources' folder.
  5. In the "Navigator" view, right-click on the "acctvsql" project and select "Properties".
  6. In the "Properties for acctvsql" dialog, select "Java Build Path" and then select the "Libraries" tab.
  7. We are going to add the JDBC driver to the project, so click "Add External JARS..." and navigate to the location of the driver, and click "Open", and then click "OK" to confirm.vsql5.PNG

Runtime Configuration

Included in the project are 2 key files for configuring the runtime environment. They are:

  • resources/data.properties -- this file provides the COBOL filename to database table mapping.
  • resources/deploy_settings -- this file identifies the XML file to map the COBOL record layout to the database table, and the database connection settings (change these as required by your database configuration.
To execute the sample application:
  1. In the "Navigator" view, right-click on the "acctvsql" project, and select "Run As > COBOL Application".
See the "Console" view for the output from running the sample application:
vsql6.PNG
</>

Transactional vs. Traditional VSAM

For traditional VSAM KSDS sharing operations (e.g., VSAM SHAREOPTIONS 3,3 or RLS, record-level sharing), set Auto Commit "True."  For "transactional VSAM" operation which will include CICS I/O updates within the ETP transaction (controlled through SYNCPOINT processing), set Auto Commit "False"  in the database connection properties.  When not auto-committing, VSAM Transparency Mode will commit changes to the database every 1000 records, by default.  Use the runtime option -DDB-BUFFER-SIZE=n to change the default.  This value also defines how many records are read-ahead on SEQUENTIAL file operations such as START and READ NEXT.  This can improve read performance.

Using the VSAM-SQL Bridge from within Elastic Batch Platform (EBP)

See the attached program TEST80.JCL for an example of a job that runs a program from JCL where the COBOL INDEXED file reads are mapped to a database using the VSAM-SQL Bridge.  Items to note:

  1. Programs must be packaged as jars and the EXEC statement refers to PGMs contained in these jars.  Jars may be specified in the job's JOBLIB DD card, the step's STEPLIB DD card, or in the EBP configuration for the systemlib1..9 settings.
  2. The packaged jars must contain, in addition to the program and resources code, 
    1. The data.properties file containing a mapping from VSQL to the table name to which it is mapped, for example
      VSQL.SYS1.VSAM.DAT=myvsamtbl
    2. The deploy_settings properties file that contains database connection and VSQL XML file mapping information, for example

      file.test80=jdbc:test80.xml
      sql.file.user=postgres
      sql.file.password=abc000
      sql.file.url=jdbc:postgresql://localhost/tickets
      sql.file.autocommit=false

    3. The XML Mapping file generated by the compiler with the $XFD control within the source, for example TEST80.XML
    4. DD cards referencing the VSAM-SQL bridged files must have the keyword DCB=PROTOCOL=VSQL for the dataset name, for example SYS1.VSAM.DAT, referenced within the data.properties file.
  3. The Elastic COBOL IDE Deployment Wizard can package all of these files into a jar.
  4. The HCECOBOL built-in EBP procedure can package all of these files into the jar during the linking step, use the EXTRAJARFILES keyword parameter to call these files.
Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk