Chapter 2: The Data Dictionary

The Data Dictionary is a single file describing your database (DL/I, DB2, and permanent VSAM files (ESDS or KSDS)). The source Data Dictionary definition file always has to have the filetype DICT, i.e. PEBSY.DICT, MASTER.DICT DAVS.DICT, etc.

All permanent files, i.e. all files existing longer than a single JOB runs, should be described in the Data Dictionary. This will both help you to DOCUMENT your database, as well as scheduling changes or enhancements probably.

If you do have multiple Data Dictionaries - for instance because you want to split them by application areas - the first three letters of the dictionary filename must be unique.
In this case, you will have to define a MASTER FILE with the following format:

MASTER.FILE

        USE dictionary-name-1

        USE dictionary-name-2

        USE dictionary-name-n.

Each individual Data Dictionary has the following layout:

Common Properties:

the common properties are used to specify GLOBAL properties  to all following Data Dictionary entries. This allows you to define needed properties like the DB2 schema name or the OWNER of the following File-classes when your organization has multiple OWNERS (the unit responsible to maintain the respectivy part of your DATABASE).

        OWNER = owner-name

        SCHEMA = DB2-schema-name

        DBSPACE = Db2-DBSpace-Name

File-Classes:

A FILECLASS is the definition of the structure of a number of files having the same structure.
Structure in this contents implies that all PHYSICAL files of the same FILECLASS have the same record-layout,
with the same variant records and/or hierarchical relationships.
Each FILECLASS is defined by the following clauses:

FILECLASS = file-class-name (max. 8 chars)

FILETYPE = {VSAM ESDS, VSAM KSDS, DLI, SQL}

TABLE = sql-table-name

SQLMAP =sql-map-file-name

FILEDESCR = short file description    

PHYFILE =physical-file-name1, ...

OVERFLOW = overflow-file-name

and, when you are usig the EURO*Transformer toolset

EUROCONV=euro-conversion-program-name

For each record-type in a FILECLASS, you have to define a RECORD-clause, followed by the record attributes:

RECORD = record-name

COPY = cobol-copybook-name

DESCR = short record description

TYPEID = fieldname, RECTYPE=value

PARENT = record-name2  

KEY = keyfields

INDEX = index-fields [ , INDEX = indexfields]

When you are defining a DB2 (SQL) table, or preparing your Dictionary for a DL/ to DB2 migration, the following record-clauses may be used in addition:

LPARENT = logical-parent-record WITH fetch-clause

DB2DROP = cobol-field-list

DB2DETAIL=cobol-array-name AS detail-record-name

When you are usig the EURO*Transformer toolset, which allows you to transform your databases from LOCAL currency to the EURO-currency, the following record-clauses are applicable:

MONEY = money-field-list

ROUND-UP = money-field-list-2

ROUND-DOWN = money-field-list-3

ADJUST = money-field-list-4

ADJUST2 = money-field-list-5

EXPAND = money-field-list-6

DUPLICATE = YES/NO

CURRENCY = YES/NO

ROUND-ERROR = YES/NO

ERROR = message

When a line starts with an asterisk(*), then the line is regarded as a comment.

Multiple attributes may be entered on the same line. A comma must be used in this case as a delimiter. Last, not least, blank lines may be used to enhance the readability of the Dictionary definition file., for example:

FILECLASS = myclass, FILETYPE = SQL

RECORD = record-name, COPY = copy-name

PARENT = record-name, KEY = key-fields

TYPEID = field-name, RECTYPE = value]

FILECLASS = myclass2,

RECORD  = record-name2

          TABLE = my-sql-table, SQLMAP=my-map

2.3 FILECLASS entry

FILECLASS = class-name

The FILECLASS entry defines one or more physical files having the same file structure. The file structure is described by the hierarchy of record-types, where each record-type is defined again thru the corresponding COPY book describing the fields of the respective record. File-class names are limited to 8 characters, and it may be good (and recommended) practice to use the same name used in the DLBL or DD statement of your JCL.

The FILETYPE entry is used to specify the respective IBM file- or database- type. When the FILETYPE is missing, a VSAM sequential (ESDS) file is assumed. 

Valid entries for filetype are:

• VSAM ESDS     The fileclass describes a VSAM Entry Sequenced Data-Set
• VSAM KSDS     The fileclass describes a VSAM Key Sequenced Data-Set.
• DL/I                      The fileclass describes an IBM DL/I database
• SQL or DB2        The fileclass describes a SQL (DB2) table.

For SQL-files, you might use the TABLE = sql-table-name clause to define the actual DB2 table name, if different from the FILECLASS name. If the SQL COLUMN-names of your SQL-table columns are different to the COBOL item names (except hyphens and underlines, oc course), then you must supply an SQLMAP as well, mapping the SQL columns to the proper COBOL items.

You may list the physical file names attached to the FILECLASS in the PHYFILE clause following the FILETYPE. When more than one physical file belongs to the file-class, list the physical filenames, separated by a comma or a new-line. With OVERFLOW = overflow-file-name you may specify the VSAM ESDS file used as the overflow area for VSAM KSDS and DL/I files.This feature is IBM specific and only needed for proper generation of the Job control language (JCL).

The EUROCONV = program-name entry allows you to specify the desired name for the conversion program. If you do not specify the name, a name EUCVnnnn is generated, where nnnn is a running number. Note that for all physical files of the same FILECLASS only one conversion program is needed and generated (as they all have the same record-structure).

The FILEDESCR entry allows you to give each file-class a short description for documantation purposes.

2.4 Record-structures

For each record-type in the respective file-class, a record description should be entered. This record description usually has the following parts:

RECORD = record-name

record-names must be unique within the DATA Dictionary. Record names are limited to 8 characters for reporting reasons.

COPY=copy-book-name

The record-structure is defined by referencing the COBOL COPY book. If the COPY book contains a 01 level, this is ignored, as the record-name is already specified in the RECORD=record-name clause. The COPY clause, when present, must preceede any clauses referencing single fields, like KEY= ..., for instance. When no COPY-clause is present, then the RECORD-name must be identical to the COPY-book-name!.

DESCR=text

 short descriptive text for the last RECORD-entry.

PARENT = record-name

If the file contains multiple record types, the parent relationships need to be specified. If, for instance, each customer record is followed by multiple address records, the file would be described as:

FILECLASS=CUSTFILE, RECORD = CUSTOMER, COPY=CUSTREC
   RECORD =ADDRESS, PARENT=CUSTOMER
     COPY=CUSTADDR, KEY=ADDRNO


Note that PARENT should only be used for hierarchical record structures, like DLI-databases or hierarchic VSAM file structures.

LPARENT = parent-record-name WITH fetch-clause

For DB2-databases (Tables) the ,Logical Parent may be defined. Semantically, this has exactly the same meaning as a PARENT relationship in a hierarchic database like DL/I.

An example would be:

RECORD=AGENTUR, COPY=AGENTUR, KEY=AGENTUR-NR

RECORD=BETREUER, COPY=BETREUER,

KEY=BET-AGENTUR-NR BET-BETREUER-NR

LPARENT= AGENTUR WITH AGN-AGENTUR-NR = BET-AGENTUR-NR

KEY = key-names

If the record has a primary key, this keyfield (or key-fields, separated by a blank) need to be specified, as the COBOL COPY book does not contain this information. Use the COBOL field names of the COBOL COPY book to specify the key field.

When a file-type requires a key field, but the KEY-clause is missing, the first item of the record-structure is taken as the key item by default.

INDEX = index-name(s)

If your file has secondary indices, you should specify thosein the INDEX-clause. Note, again, that the COBOL field names are used for the index-name(s) list.

Multiple INDEX clauses for a single record are allowed, each used for

TYPE-ID = field-name, RECTYPE = value

If an ordinary VSAM-file contains multiple record types, usually a single field at the start of each record is used to identify the respective record-type. Use the name of the type field of the master record (the first record type described) to specify which field contains the record-type, and the RECTYPE = value clause to specifiy the respective value uniquely identifying this record type. If the TYPE-ID = field-name clause is missing, but the RECTYPE=value is present, the first field of the record is used as the TYPE-ID field.

Note: it might be good practice to use the name of the COPY books (which will be unique anyhow) as the RECORD-name in your dictionary file. Consequently, if the COPY=copy-book-name clause is missing, your RECORD-name is used as a default value.

2.5  special EURO-conversion clauses

The clauses above are general data-dictionary clauses, not related to the EURO-Conversion.

The following clauses might be used to control the EURO-conversion.

MONEY = field-list

With this clause, you specify which record fields contain MONEY values. An alternate method is to mark the MONEY fields with a $ sign in column 2 of the prefix area in the respective COPY book. Both methods are equivalent.

EXPAND = field-list or EXPAND = YES

For specific currencies, it might be necessary to expand money items by adding 2 decimal places. In Austria, for instance, you might have values stored as integers now, which should contain 2 decimal places after the EURO-conversion. Specify those fields in the EXPAND-clause. With EXPAND=YES all money-fields without decimal places of this record will be expanded.

ROUND-DOWN = field-list or ROUND-DOWN = YES

Normally, during the conversion process, the converted EURO-values are rounded to the precision specified, i.e. a field with PICTURE 9(5)V99 will be rounded to 2 deimal places. Note, that the actual calculation is performed with 5 decimal places, and the cumulative rounding errors are captured for subsequent actions in the rounding error file EURORNDE.

With the ROUND-DOWN-clause you may specify which fields should be rounded down (truncated). This behaviour may be desired for prices for instance to conform to local government rules.

ROUND-UP = field-list or ROUND-UP = YES

Fields specified in the ROUND-UP clause are always rounded up to the next higher value during the conversion process

When neither ROUND-UP nor ROUND-DOWN is specified for the money field, those fields are commercially rounded to the new precision during the conversion process.

ADJUST = field-list or ADJUST = YES

An alternate method to EXPAND might be to adjust the pictures in place. When the current field has a picture of 9(5), adjustment will change the picture to 9(3)V99 after EURO-conversion, thus occupying the same physical space in the VSAM-file or DL/I segment. When ADJUST = YES, all integer money fields will be adjusted.

 DUPLICATE=field-list or DUPLICATE=YES

With the DUPLICATE clause, you my specify the money fields which should be changed to 2 corresponding fields. The first will contain the original amount in the current currency, the second one will contain the amount in the alternate currency. DUPLICATE implies CURRENCY = YES (see below)

CURRENCY = YES / NO / field-name

When CURRENCY = YES is specified, a 3-character field containing the primary currency is generated for each record. Thus, each database record will contain information which currency is used for its money values after the EUROFILE-conversion program run. When you specify a field-name, this name will be used. Otherwise, a unique program name (EUCVnnnn) name will be generated.

2.6  Advanced Techniques

A couple of advanced techniques are available for the description found in real world databases:

DB2DROP = field-list

You may specify a DROP-list for each record, i.e. a list of items which should be skipped (dropped) when downloading or converting your database. Typical candidates are OBSOLETE fields or COBOL FILLER items, which need not to be stored in the database at all anymore.

DB2DETAIL = cobol-name AS detail-record-name

The DB2DETAIL-clause permits you to force repeated detail information to a separate layer in a DB2 Database. Good candidates are OCCURS-items in COBOL, which should be stored in separate (detail) Tables for reduced record-length and access time reductions.

PREFIX = record-prefix

To obtain unique field names for COBOL items, usually a PREFIX is used abbreviating the corresponding record-type, e.g.: AGN-AGENTUR-NR is the AGENTUR-NR of the AGN-record. Thze first component of the COBOL-name is used as the default record-prefix.

DLIPREFIX = dli-record-prefix

For IBM DL/I databases, a unique prefix hasto be used for the SSA and CAL names. By default the dli-prefix is qual to the record-prefix.

DB2PREFIX = db2-record-prefix

To be able to generate the proper SQL/DB2  access functions, a unique DB2 prefix (up to 4 chars maximum) has to be selected. By default, the db2-record-prefix is equal to the record-prefix. 

2.7  Example Data Dictionary

The following example shows a small Test-Dictionary:

fosy.dict      2004-02-23 13:33:21


***********************************************************************
* FOSY.DICT: FOSY-Datenbank
***********************************************************************
* >>> SEGMENT-NAMEN aus Zugriffsroutine FO105CAL (Programm FO0105)
* >>> und Programm FO0102 (Zahlschein-Format)
* 13.06.2001, Th.S.
***********************************************************************
OWNER=DL
***********************************************************************
FILECLASS=FOSYD, FILETYPE=DL/I, DBDNAME=FOSY
PHYFILE='PAUT.FOSY.DATEN.DB'
EUROCONV=EUCVFOSY
RECORD=FOSYST, COPY=FOSYFST
KEY=FST-SATZART,FST-AUFTRAGSNR
RECORD=FOSYFO,PARENT=FOSYST, COPY=FOSYFFO

FILECLASS=FOSYIND, FILETYPE=DL/I, DBDNAME=FOSYINK
PHYFILE='PAUT.FOSY.INKASSO.BELEGE'
EUROCONV=EUCVFOIN
RECORD=FOSYINKR, COPY=FOSYINK
KEY=FIN-AUFTRAGSNR

FILECLASS=FOSYSQD, FILETYPE=DL/I, DBDNAME=FOSYSQ
PHYFILE='PAUT.FOSY.SEQU.FILE.KSDS'
OVERFLOW='PAUT.FOSY.SEQU.FILE.ESDS'
EUROCONV=EUCVFOSQ
* Achtung: MULTI-TYPE DL/I mit Segment-Namen 'FOSYSQR'
RECORD=FOSYSQR,COPY=FOSYTABH
TYPEID=TAB-SATZART, RECTYPE='F1',SPACES
KEY=TAB-RECORDNR,TAB-SATZART,TAB-AUFTRAGSNR,TAB-SATZART-KENNZIFFER

* ... Folgesatz
RECORD=FOSYTABF,COPY=FOSYTABF, RECTYPE='F2':'F9'
*
FILECLASS=FOSYAED, FILETYPE=DL/I, DBDNAME=FOSYAE
PHYFILE='PAUT.FOSY.AUFTRAG.EINHEIT'
EUROCONV=EUCVFOAE
RECORD=FOSYAER, COPY=FOSYAE
KEY=AE-KEY
*
FILECLASS=FOSYRED, FILETYPE=DL/I, DBDNAME=FOSYRE
PHYFILE='PAUT.FOSY.RECHNUNG.WESEN'
EUROCONV=EUCVFORE
RECORD=FOSYRER, COPY=FOSYRE
KEY=RE-KEY
*
FILECLASS=FOSYVWD, FILETYPE=DL/I, DBDNAME=FOSYRE
PHYFILE='PAUT.FOSY.VERTRIEB.WEG'
EUROCONV=EUCVFOVW
RECORD=FOSYVWR, COPY=FOSYVW2
KEY=VW-KEY
*
FILECLASS=FOZAHLD, FILETYPE=DL/I, DBDNAME=FOZAHL
PHYFILE='PAUT.FOSY.ZAHL.SCHEIN'
EUROCONV=EUCVFOZA
RECORD=FOZAHLR, COPY= FOSYZA
KEY = FZS-KEY
* Struktur aus Programm FO0201!
* COPY FOSYZA = MAHN-SATZ (FOSYMAHN) + HIGHKEY!
* SIEHE Programm FO0201 + FO201CAL COPY
 

2.8  Compiling your DATA DICTIONARY.

When you have completed your Data-Dictionary, you have to validate it using the DBDICT utility. The DBDICT utility reads your source file dictionary description, and checks it for validity.

 For instance, it is checked that all referenced COBOL COPY-books exist, then the COBOL copy books are parsed, as the COBOL field-names define the items of each RECORD. 

An internal format (called the IDECL-file) for each COBOL COPY book is saved for usage of the various preprocessors and program generators.

Additionally, of course, all key fields referenced must be valid field names of the referenced copy book. The RECORD-TYPE entries must be there, etc,ect.

DBDICT is invoked as usual by the following command:

cmd>DBDICT dictname

The compiled dictionary is stored with a filetype of DD (compiled Data Dictionary)

When executing the DBDICT utility, you will see the following messages on the screen. These messages are also written to the DBDICT LOG-file (DBDICT.log) for later inspection.

cmd> DBDICT FOSY

DBDICT.fosy.log      2004-02-23 13:34:39


File path of Dictionay entries is:
fp_idecl is: D:\DL\DataDictionary

File: fosy.dict
===============


Processing DICTIONARY: fosy
===========================


File Class: FOSYD
=================

Record: FOSYST
relevant copy is: M:\DLInte\DLSource\FOSYFST.cpy
Date-Form Table: D:\Cob2Mfc\DATEFORM.TABLE used.

File: M:\DLInte\DLSource\FOSYFST.cpy
====================================

Parsing Decls of file: M:\DLInte\DLSource\FOSYFST.cpy with cob_decl.nrx vs 14.00 23.02.2004 13:33:34
corresponding IDECL-file is: D:\DL\DataDictionary\FOSYFST.IDECL
copy: FOSYFST COBOL-structure:
first item: FST-RECORDNR level: 5 default prefix: FST
key-loc:4 key-size: 9
Record: FOSYFO
relevant copy is: M:\DLInte\DLSource\FOSYFFO.cpy
Date-Form Table: D:\Cob2Mfc\DATEFORM.TABLE used.

File: M:\DLInte\DLSource\FOSYFFO.cpy
====================================

Parsing Decls of file: M:\DLInte\DLSource\FOSYFFO.cpy with cob_decl.nrx vs 14.00 23.02.2004 13:33:36
corresponding IDECL-file is: D:\DL\DataDictionary\FOSYFFO.IDECL
copy: FOSYFFO COBOL-structure:
first item: FFO-RECORDNR-FOLGE level: 5 default prefix: FFO
file-class: FOSYD max. thread-length: 9

File Class: FOSYIND
===================

Record: FOSYINKR
relevant copy is: M:\DLInte\DLSource\FOSYINK.cpy
Date-Form Table: D:\Cob2Mfc\DATEFORM.TABLE used.

File: M:\DLInte\DLSource\FOSYINK.cpy
====================================

Parsing Decls of file: M:\DLInte\DLSource\FOSYINK.cpy with cob_decl.nrx vs 14.00 23.02.2004 13:33:39
corresponding IDECL-file is: D:\DL\DataDictionary\FOSYINK.IDECL
copy: FOSYINK COBOL-structure:
first item: FIN-AUFTRAGSNR level: 5 default prefix: FIN
key-loc:1 key-size: 7
file-class: FOSYIND max. thread-length: 7

File Class: FOSYSQD
===================

Record: FOSYSQR
relevant copy is: M:\DLInte\DLSource\FOSYTABH.cpy
Date-Form Table: D:\Cob2Mfc\DATEFORM.TABLE used.

File: M:\DLInte\DLSource\FOSYTABH.cpy
=====================================

Parsing Decls of file: M:\DLInte\DLSource\FOSYTABH.cpy with cob_decl.nrx vs 14.00 23.02.2004 13:33:41
corresponding IDECL-file is: D:\DL\DataDictionary\FOSYTABH.IDECL
copy: FOSYTABH COBOL-structure:
first item: TAB-RECORDNR level: 5 default prefix: TAB
record: FOSYSQR has RECTYPE: 'F1' SPACES
key-loc:1 key-size: 13
Record: FOSYTABF
relevant copy is: M:\DLInte\DLSource\FOSYTABF.cpy
Date-Form Table: D:\Cob2Mfc\DATEFORM.TABLE used.

File: M:\DLInte\DLSource\FOSYTABF.cpy
=====================================

Parsing Decls of file: M:\DLInte\DLSource\FOSYTABF.cpy with cob_decl.nrx vs 14.00 23.02.2004 13:33:43
corresponding IDECL-file is: D:\DL\DataDictionary\FOSYTABF.IDECL
copy: FOSYTABF COBOL-structure:
first item: TABF-RECORDNR-FOLGE level: 5 default prefix: TABF
record: FOSYTABF has RECTYPE: 'F2':'F9'
file-class: FOSYSQD max. thread-length: 13

File Class: FOSYAED
===================

Record: FOSYAER
relevant copy is: M:\DLInte\DLSource\FOSYAE.cpy
Date-Form Table: D:\Cob2Mfc\DATEFORM.TABLE used.

File: M:\DLInte\DLSource\FOSYAE.cpy
===================================

Parsing Decls of file: M:\DLInte\DLSource\FOSYAE.cpy with cob_decl.nrx vs 14.00 23.02.2004 13:33:46
corresponding IDECL-file is: D:\DL\DataDictionary\FOSYAE.IDECL
copy: FOSYAE COBOL-structure:
first item: AE-KEY level: 5 default prefix: AE
key-loc:1 key-size: 14
file-class: FOSYAED max. thread-length: 14

File Class: FOSYRED
===================

Record: FOSYRER
relevant copy is: M:\DLInte\DLSource\FOSYRE.cpy
Date-Form Table: D:\Cob2Mfc\DATEFORM.TABLE used.

File: M:\DLInte\DLSource\FOSYRE.cpy
===================================

Parsing Decls of file: M:\DLInte\DLSource\FOSYRE.cpy with cob_decl.nrx vs 14.00 23.02.2004 13:33:48
corresponding IDECL-file is: D:\DL\DataDictionary\FOSYRE.IDECL
copy: FOSYRE COBOL-structure:
first item: RE-KEY level: 5 default prefix: RE
key-loc:1 key-size: 10
file-class: FOSYRED max. thread-length: 10

File Class: FOSYVWD
===================

Record: FOSYVWR
relevant copy is: M:\DLInte\DLSource\FOSYVW2.cpy
Date-Form Table: D:\Cob2Mfc\DATEFORM.TABLE used.

File: M:\DLInte\DLSource\FOSYVW2.cpy
====================================

Parsing Decls of file: M:\DLInte\DLSource\FOSYVW2.cpy with cob_decl.nrx vs 14.00 23.02.2004 13:33:50
corresponding IDECL-file is: D:\DL\DataDictionary\FOSYVW2.IDECL
copy: FOSYVW2 COBOL-structure:
first item: VW-KEY level: 5 default prefix: VW
key-loc:1 key-size: 11
file-class: FOSYVWD max. thread-length: 11

File Class: FOZAHLD
===================

Record: FOZAHLR
relevant copy is: M:\DLInte\DLSource\FOSYZA.cpy
Date-Form Table: D:\Cob2Mfc\DATEFORM.TABLE used.

File: M:\DLInte\DLSource\FOSYZA.cpy
===================================

Parsing Decls of file: M:\DLInte\DLSource\FOSYZA.cpy with cob_decl.nrx vs 14.00 23.02.2004 13:33:52
corresponding IDECL-file is: D:\DL\DataDictionary\FOSYZA.IDECL
copy: FOSYZA COBOL-structure:
first item: FZS-KEY level: 2 default prefix: FZS
key-loc:1 key-size: 4
file-class: FOZAHLD max. thread-length: 4

no warnings
no errors encountered

Saving Data Dictionary:
=======================

7 File Classes defined.
Data Dictionary for: 'fosy' saved in: D:\DL\DataDictionary\fosy.DD