Database storage system (V2R)

As described in the concepts section, XVSAM may store files on relational database tables. Each file is stored in a single table, and keys and indexed are managed by means of database features.

File access to database stored file is always performed using the usual XVSAM interfaces: EXEC CICS READ in XCICS/TS and normal I/O statement in COBOL programs (i.e. READ, WRITE).

The database access is achieved by means of file specific I/O programs (normally referenced as PROGIO). At generation time, XVSAM creates and compiles a COBOL program which is called for each I/O operation on the file and takes care to correctly manage it with a corresponding SQL command.

Supported database

XVSAM database storage currently supports the following RDBMS:

Generation rules

During the database layer generation, the following rules are applied to transform VSAM files into RDBMS tables.

The structure of the table containing the XVSAM file matchs the file structure, containing one column for each single field contained in the file. In addition to the file fields, XVSAM adds some system columns to allow direct access to table rows from legacy programs and easier access from new programs.

The name of the host table will be automatically composed by the logical name of the file, prefixed by the tag XVSAM_:

I.e.

XVSAM_CLUST01

Data types correspondency

K_ columns

Whenever the record key is a structure composed by multiple fields, in order to grant access to the data to non-legacy programs too, the some additional columns are generated for each field composing the original VSAM key. These columns are always prefixed by "K_", and they are used to maintain an user-friendly copy of the subfields of the record key. These columns are automatically kept up-to-date by xvsam PROGIOs during the update operations.

For UDB and SQL Server, when database data are updated by non-xvsam applications (i.e. Visual Basic apps), you must be aware to correctly update the K_ columns from within your non-xvsam applications.

Configuration

XVSAM database interface is configured by means of the following environment variables:

The database connection string

The database connection string is an identifier that describes the connection to the database. It specifies user, password and database identifier, in the following format

<user>/<password>@<database_identifier>

I.e.

usr/mypass@DBNAME

The database identifier

The database identifier, specified in the connection string with the parameter database_identifier, is name used by the database communication layer to identify a local or remote database instance. It corresponds at the following objects, depending on the database in use.

Additional environments

Oracle

Oracle users must be aware of the setting of NLS_LANG variable: it affects the data conversion between oracle client and server. XVSAM may store binary data into database fields, so no conversion should occur: because of that NLS_LANG variable must be set with the same language of the oracle server.

I.e.

export XVSAM_CONNECT=scott/tiger@ORCL
export XVSAM_PROGIO_PATH=$HOME/progio:$HOME/objs/int
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

Microsoft SQL Server

XVSAM uses the COBOL compiler to build the access layer for the file being handled. Therefore a proper configuration for the COBOL compiler is required. For Microsoft SQL Server the following variables must be set before to use the XVSAM create command:

set COBPCY=%COBDIR%\SOURCE

IBM UDB

XVSAM uses the COBOL compiler to build the access layer for the file being handled. Therefore a proper configuration for the COBOL compiler is required. For IBM UDB the following variables must be set before to use the XVSAM create command:

set COBPCY=%DB2_HOME%\include\cobol_mf

export COBPCY=$DB2_HOME/include/cobol_mf

Using with XCICS/TS

When database stored files are accessed by XCICS applications, the running region must be configured to access the database system where the XVSAM files are stored. To do that, you must correctly define a database connection in your xcics.conf, according to the instructions contained in the XCICS System Management Guide.

Environment generation

Of course the database must be properly setup before XVSAM may use it to store its files. Database user must be correctly setup with all the privileges required to create and access tables and with space enough to store data.

Additionally,for Oracle Database only, some XVSAM specific functions must be defined for the oracle databse user: the source for these functions is the file $XFRAMEHOME/etc/v2rfun_ora.sql.

Files generation

Whenever an XVSAM file must be stored on database, it must be properly created, supplying its record structure representation. XVSAM uses the XFRAME XML-based data structure descriptor.

This XML file may be obtained using "cpy2xml" utility (see XCONV Data Conversion Toolkit), and it contains information concerning the structure of the file.

I.e.

<xml version="1.0" encoding="UTF-8"?>
<datadef source="FUNZIONI.cpy" language="COBOL" built="Wed May 24 12:43:01 MEST 2006">
   <datastruct>
       <dataitem name="FUNZIONI" level="1">
           <dataitem name="CAMPO-CHIAVE" level="2">
               <dataitem name="CAMPO" level="3">
                  <dataitem name="CAMPO1" level="4" picture="S9(5)" length="5" usage="display" 
                                         signed="true"/>
                 <dataitem name="CAMPO2" level="4" picture="S9(4)" length="2" usage="comp"
                                        signed="true"/>
                 <dataitem name="CAMPO3" level="4" picture="S9(4)" length="3" usage="comp-3"
                                        signed="true"/>
               </dataitem>
           </dataitem>
       </dataitem>
   </datastruct>
</datadef>

The structure managed by "xvsam" PROGIO generator cannot contain REDEFINES clauses.

Once obtained the correct XML for record description, the command "xvsam" must be used to create the file, the database objects and the progio:

Alternate indexes are generated with a similar command:

xvsam –create --aix
--catalog=<CATALOG NAME>
--type=ksds
--key=<KEY INFO>
--storage-engine=database
--logical=<LOGICAL NAME
--primary=<PRIMARY CLUSTER NAME>
--xml-fd=<XML file>
--database=db2
<ALTERNATE INDEX NAME>

Providing a customized PROGIO

In some situations, it may be necessary to provide a customized PROGIO. In this case the option --use-progio may be used to pass to xvsam the name of the COBOL source to compile and use as I/O backend for the cluster.

xvsam --create -C REGTEST --key=0,5 --record-length=30 --storage=database \
     --logical=VDR01 --use-progio=MYPROGIO.cbl V2R.FILE

The best way to write a PROGIO is to let XVSAM to generate it for you the first time: the generated program will serve as a skeleton for your coding.

Handling REDEFINES

As described above, the structure provided to the "xvsam" PROGIO generator cannot contain REDEFINES clauses. Whenever the structure should contain a REDEFINES clause some manual work must be done. You have two alternatives:

Merge redefined fields in a single field
You can edit the copybook removing the redefines clauses and merging all the redefined fields into one single data item. This system, allows to use the new copybook directly with the standard PROGIO generation mechanism described above. In this case you must be aware of external access to the data stored (i.e. other applications) and about data encoding (some databases, such as SQL Server, perform automatic data conversion and truncation for CHAR data, making this choice ineffective). I.e.

Before:

01  RECORD-1.
          05  KEY-K1.
              10  CHOLD-K1                      PIC X(2).
              10  CSOC-K1                       PIC X(2).
              10  ARIF-K1                       PIC 9(4).
              10  TISOC-K1                      PIC X(2).
          05  DESCRIPTION                       PIC X(15).
          05  DATE-START                        PIC 9(9)     COMP-3.
          05  DATE-END                          PIC 9(9)     COMP-3.
          05  FILLER                            PIC X(23).
          05  OPID                              PIC X(3).
          05  TERMID                            PIC X(4).
          05  TODAY-DATE                        PIC 9(9)     COMP-3.
          05  COUNT-R1                          PIC S9(4)    COMP.
        01  RECORD-2 REDEFINES RECORD-1.
          05  FILLER-KEY                        PIC X(10).
          05  COD-PROV                          PIC X.
          05  PERC-PART                         PIC 9(3)V9999 COMP-3.
          05  DATE-MOV-INFR                     PIC 9(9)      COMP-3.
          05  DATE-MOV-MARC                     PIC 9(9)      COMP-3.
          05  FILLER                            PIC X(33).
          05  FILLER-END                        PIC X(14).

After:

        01  RECORD.
          05  KEY-1                             PIC X(10).
          05  USER-DATA                         PIC X(62).

Write a customized PROGIO
You can split the data among several tables, using a master (pivot) table to drive the accesses to the different child row. In this case either data definition structures (DDL) and PROGIO must be created manually. The customized PROGIO must be passed to xvsam, using the "--use-progio" option.

Restrictions

The following restriction applies: