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.
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.
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:
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:
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:
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.