OpenFrame VSAM Performance Optimization

This appendix describes methods for optimizing the performance of OpenFrame VSAM (TSAM).

1. Overview

OpenFrame 7 VSAM (TSAM) can be defined with JCL utility IDCAMS or the command-line tool idcams.

OpenFrame 7 VSAM is defined as an RDB table, and its structure is defined by referencing the copybook file with the same name as the data set to be created in the ds.TSAM_RDB.COPYBOOK_PATH directory. Since this copybook is used only for table definition and OpenFrame engine operations, it does not need to be the same as the migration schema or FD definition of the user program.

The column types defined depending on the field type are as follows:

Field Type Column Type Remark

Character (X, N)

VARCHAR

Zoned/Packed decimal,

binary (COMP,COMP-4,COMP-5)

NUMBER

Float (COMP-1)

BINARY_FLOAT

Double (COMP-2)

BINARY_DOUBLE

EBCDIC (EBC)

NVARCHAR2

Can only be used in X and A types.

Example) PIC X(10) EBC.

OpenFrame 7 VSAM allows data to be arranged in columns and stored in tables in desired format. It can utilize the benefits of RDB and easily integrate with other modern systems and applications.

However, in input and output operations through COBOL programs, when reading columnarized data, there is an additional cost incurred due to the serialization for reading as byte arrays and deserialization for writing. Therefore, in batch jobs where the execution time is crucial, performance optimization may be necessary.

If the ds.TSAM_RDB.PRINT_DEBUG_MSG_ON option is set to YES, OpenFrame 7 VSAM outputs the SQL requested by the engine according to program commands and displays the execution time.

The following is an example of the log.

 [START]
  * SQL
   SELECT /*+ INDEX_ASC (BASE "PROD_VSAM_KEY") */ SM_KEY, STYLE_NUMBER, YEAR, SIZE, SIZE_SCALE_PCT, EACH_PRICE_1, EACH_PRICE_2, EACH_PRICE_3, EACH_PRICE_4 FROM "PROD_VSAM" BASE  WHERE ("SM_KEY", "STYLE_NUMBER", "YEAR", "SIZE") >= (:SM_KEY, :STYLE_NUMBER, :YEAR, :SIZE) AND ROWNUM = 1  ORDER BY "SM_KEY", "STYLE_NUMBER", "YEAR", "SIZE" ASC
  * KEY
   SM_KEY(50), STYLE_NUMBER(000100009), YEAR(22), SIZE()
  * Current Time   : 2023-03-06 11:26:03
  * Execution Time : 0.0020s

 [OPEN]
  * SQL
   SELECT /*+ INDEX_ASC (BASE "PROD_VSAM_KEY") */ SM_KEY, STYLE_NUMBER, YEAR, SIZE, SIZE_SCALE_PCT, EACH_PRICE_1, EACH_PRICE_2, EACH_PRICE_3, EACH_PRICE_4 FROM "PROD_VSAM" BASE  WHERE ("SM_KEY", "STYLE_NUMBER", "YEAR", "SIZE") >= (:SM_KEY, :STYLE_NUMBER, :YEAR, :SIZE)   ORDER BY "SM_KEY", "STYLE_NUMBER", "YEAR", "SIZE" ASC
  * KEY
   SM_KEY(50), STYLE_NUMBER(000100009), YEAR(22), SIZE(1X         )
  * Current Time   : 2023-03-06 11:26:03
  * Execution Time : 0.0441s

Check the execution plans and tuning points for SQL queries with a long execution time.

2. Additional Optimization Methods

The following describes other performance optimization methods users can try.

  • Reducing the number of copybook fields

    As the number of columns increases, the SQL processing performance deteriorates and it also causes a load on the OpenFrame engine. Therefore, merging fields to reduce the number of columns enables faster input and output.

  • Making copybook fields X

    For zoned decimal, packed decimal, and integer type fields, conversion is necessary during input and output processes since the data format following COBOL conventions and numeric format stored in RDB are different. Therefore, if there are many such fields, the load increases.

    On the other hand, X type fields are stored directly in VARCHAR columns without any data conversion, allowing relatively fast input and output.

    By combining these two cases, you can simplify a copybook into one X type field to be used as an index key and another X type field for rest of the data. This can significantly improve input/output performance of COBOL programs. However, it can also make the data manipulation using SQL more challenging.

  • Utilizing the PAGESIZE option

    In some user programs, the READ command is repeated because only a small number of records are read at a time even through the total number of records is large. In such cases, the OpenFrame engine determines the repetitive READ commands are normal and queries to retrieve all records that come after the specified key at the time of READ commands. This query operates quite inefficiently when dealing with a large number of records.

    If the PAGESIZE=n option is specified in the the JCL’s DCB, you can specify the number of records to retrieve at once. It allows you to configure it depending on the job and reduce execution time.

    //JOB0    EXEC PGM=READTEST
//DDONE   DD DSN=TEST.VSAM,DISP=OLD,
//         DCB=(PAGESIZE=100)
//SYSOUT  DD SYSOUT=*
//SYSPRINT DD SYSOUT=*