CICS VSAM Transparency for z/OS, Version 1.2


The mapping component

CICS® VT has to understand the structure of the records in a VSAM file and how this relates to the structure of the data in DB2®. The relationship between these structures is achieved through a CICS VT process called mapping.

Although there are two separate mapping methods, an automated method and a manual method, both achieve the same purpose. You should understand the mapping component regardless of the method you use.

Figure 1 shows the IDCAMS DEFINE CLUSTER statements for a KSDS data set and is used here to illustrate the mapping process.

Figure 1. Sample define cluster for a KSDS
 DEFINE    CLUSTER                                  -
             NAME(VID.VIDKSDS)                      -
             INDEXED SHR(2 3) FREESPACE(30 10))     -
           DATA                                     -
             (NAME(VID.VIDKSDS.DATA)                -        
             KEYS(6 0) VOL(CTC002) RECSZ(80 80)     -                           
             CISZ(16384) CYL(1 10))                 -
           INDEX                                    - 
             (NAME(VID.VIDKSDS.INDEX)               -        
             CISZ(2048) VOL(CTC002) CYL(1 1)) 

Next, look at the COBOL copybook for this data set.

Figure 2. Sample copybook
01 ITEM-DEF
   02 ITEM-NUMBER                  PIC X(6).
   02 ITEM-NAME                    PIC X(12).
   02 ITEM-DESC.
      03    ITEM-COLOUR            PIC X(6).
      03    ITEM-WEIGHT            PIC X(4).
      03    ITEM-COST              PIC S99999V99 COMP-3.
   02 ITEM-REORDER-QUANTITY 			PIC S999.
   02 ITEM-SUPPLIER-CODE 				PIC X(3).
   02 ITEM-DATE-FIRST-SHIP 			PIC S9(9) COMP-3.
   02 ITEM-SHELF-LIFE 					PIC X(2).
   02 ITEM-DESCRIPTION 				PIC X(35).

The design of the DB2 table for this VSAM file depends on its planned usage outside of CICS VT. For example, you might want to migrate a data set to DB2 to share the data between your online programs and your batch housekeeping utilities. The simplest DB2 design to achieve this objective is as follows:

Figure 3. DB2 DDL for a key and data solution
 CREATE TABLE VID_ITEM
       (ITEM_KEY                   CHAR(6)
       ,ITEM_DATA                  CHAR(74)
   (PRIMARY KEY(ITEM_ROOT,ITEM_NUMBER))
IN ITEMDB.ITEMTS;   

In this design, which is referred to as a key and data design, no copybook is used. An advantage of this design is that potential data inconsistencies are eliminated because DB2 imposes no rules on character data. This table design is appropriate if you simply want to convert a VSAM data set to DB2 so that the data is under the control of a DBMS. Normally, there are no additional requirements to access tables using this design.

The obvious disadvantage of this design is that the value of the table from an SQL perspective is minimal. You could not use this design if you plan to provide end user access to the migrated tables, although CICS VT allows you to change the design of the DB2 table after the initial migration.

If you plan to write new programs to access the data after it has been migrated to DB2 or to provide ad hoc access to end users, a copybook based table design is required. For the sample copybook shown in Figure 2 the DB2 design could look as follows:

Figure 4. DB2 DDL solution using a copybook
 CREATE TABLE VID_ITEM
       (ITEM_NUMBER                CHAR(6)
       ,ITEM_NAME                  CHAR(12)
       ,ITEM_COLOUR                CHAR(6)
       ,ITEM_WEIGHT                CHAR(4)
       ,ITEM_COST                  DEC(7,2)
       ,ITEM_REORDER_NO            SMALLINT
       ,ITEM_SUPP_CODE             CHAR(3)
       ,ITEM_DATE_FSHIP            DATE
       ,ITEM_SHELF_LIFE            CHAR(2)
       ,ITEM_DESCRIPTION           CHAR(35)  
   (PRIMARY KEY(ITEM_NUMBER))
    IN ITEMDB.ITEMTS;   

There are a number of points to note about this DB2 table design:

  1. The DB2 primary key is always the key of the KSDS data set. For an RRDS data set, the 4-byte relative record number becomes the DB2 primary key.
  2. The DB2 primary key must be the first column or columns in the table.
  3. Each copybook field shown in Figure 2 corresponds to a DB2 column. The copybook group field ITEM-DESC can optionally be added as an SQL COMMENT on the DB2 column for your documentation purposes.
  4. The copybook field ITEM-REORDER-QUANTITY is signed, zoned decimal with an attribute of PIC S999. The DB2 column it maps to is data type SMALLINT. Data conversion is automatically handled by CICS VT. By using the data re-engineering capability of CICS VT in this way, SQL arithmetic functions can be performed against this column. This could be useful for end user queries.
  5. The copybook field ITEM-DATE-FIRST-SHIP is packed decimal. The DB2 column it maps to is data type DATE. CICS VT handles data conversion automatically, but the layout of the field must be defined to CICS VT. For example, this could be CCYYMMDD or MMDDCCYY. CICS VT converts the field value from its packed decimal form to the correct DB2 DATE value when a record is inserted or updated. For retrieval calls, CICS VT converts the DATE data from DB2 into a packed decimal value according to the date field layout.

Using the re-engineering capabilities of CICS VT enhances the business value of your data once it is migrated to DB2. This can be especially useful for end users. However, it assumes that the VSAM data field values are consistent with the copybook attributes. Inconsistent data will increase the time to perform data migration.

You should consider the requirements for each VSAM file, before deciding on the most appropriate DB2 table design.





Last updated: November 9, 2012 20:42:45