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