Chapter 4: Project Conversion Sample - DL/I to DB2


4.01 Overview

The following example is intended to list the steps necessary in a DL/I to DB2 conversion project.

The sample Data Dictionary is a small part of the actual DONAULAND Data Dictionary.

DONAULAND is an Austrian book-club, a daughter company of BERTELSMANN, Germany.

The Data Base used in the sample is a part of the so-called PEBSY System, and is used to show the straightforward DB-123 approach to convert a hierachic database and the related COBOL programs from IBM DL/I to IBM DB2.
This project was carried out at Donauland from Mach 2003 thru September 2003, concurrently with the migration project from
IBM VSE COBOL II to MICROFOCUS COBOL (see also Chapter 5).

4.02 Defining the Data Dictionary of the old (DL/I) sample database PEBSY

The Data Dictionary below shows a small Part of the Donauland Database.
The original
COPY-books used to define the record structure are listed in detail in APPENDIX A.
As you see, the dictionary itself is very compact, as the actual RECORD-structures are derived from the
COBOL copy-books already present for the application in question.

The example also shows some declaratives formerly used for the
EURO-conversion effort some years ago
(using the
EURO*Transformer tool-set, © Th. Schneider)

pebsy.dict      2004-02-23 17:08:15

************************************************************************
* PEBSY.DICT: Donauland PEBSY Data Dictionary                          *
************************************************************************
***********************************************************************
* ... Donauland PEBSY: EURO-Konversion
*     die PKU-Felder in Agentur und Betreiuersatz werden 'adjusted',
*     das heißt mit 2 Nachkomma-Stellen versehen.
***********************************************************************
* 29.01.2003: mit Horst abgestimmt.
* 05.02.2003: Umsaetze Betreuer (PUMSDB)
*           : Korekturen lt. Fr. Peschke TOTE Datenbanken
* 09.04.2003: PEBSY DICT enthält nur mehr den sogenannten PEBSY KERN.
*           : Das restliche PEBSY ist in DLPEBSY.DICT definiert. 
* 22.04.2003: DB2DROP-items of UMSPRO (prefix BUP) corrected
***********************************************************************
OWNER=DL
***********************************************************************
FILECLASS=PEBSY, FILETYPE=DL/I
  PHYFILE=PAUT.PEBSY.DATEN
  FILEDESCR=PEBSY (DB Persönliche Betreuung)
  EUROCONV=EUCVPEBS

  RECORD=AGENTUR, DB2PREFIX=AGN
     DESCR=Agentur-Stammsatz
     KEY=AST-AGENTUR-NR
     ADJUST=YES
     DB2DROP=AST-FILLER AST-ABTEILUNG AST-BENUTZER-CODE AST-SACHBEARBEITER-CODE
  RECORD=BETREUER, PARENT=AGENTUR
     DESCR=Betreuer-Stammsatz
     KEY=BET-BETREUER-NR
     DB2DROP=BET-ABTEILUNG BET-BENUTZER-CODE BET-SACHBEARBEITER-CODE
  RECORD=BINFIX  , PARENT=AGENTUR
     DESCR=Betreuer-Info fix
     MULTIPLE KEY=BIF-SATZ-INDEX
      DB2DROP=BIF-FILLER BIF-ABTEILUNG BIF-BENUTZER-CODE BIF-SACHBEARBEITER-CODE
  RECORD=BINVAR  , PARENT=AGENTUR
     DESCR=Betreuer-Info variabel
     KEY=BIV-SEITE
     DB2DROP=BIV-FILLER BIV-ABTEILUNG BIV-BENUTZER-CODE BIV-SACHBEARBEITER-CODE
  RECORD=LIEFSOFO, PARENT=AGENTUR
     DESCR=Lieferung sofort
     MULTIPLE KEY=ALS-TITEL-NR
      
  RECORD=LIEFTERM, PARENT=AGENTUR
     DESCR=Lieferung Termin
     MULTIPLE KEY=ALT-TITEL-NR
     DB2DROP=ALT-FILLER
  RECORD=UMSPRO  , PARENT=BETREUER
     DESCR=Betreuer-Historie
     PREFIX=BUP
     DB2DROP=BUP-FILLER BUP-ABTEILUNG BUP-BENUTZER-CODE BUP-SACHBEARBEITER-CODE
     *...  nur EIN UMSPRO-Satz pro Agentur/Betreuer, daher kein SEQ-Feld !!!
     SEQUENCE=NO  
  RECORD=GUTBEL1, PARENT=BETREUER, COPY=GUTBEL
     DESCR=Gutschrift/Belastung 1
     * NO key, use SEQ-NO as SQL-key!
      DB2DROP=GUB-FILLER GUB-ABTEILUNG GUB-BENUTZER-CODE GUB-SACHBEARBEITER-CODE
  RECORD=GUTBEL2, PARENT=BETREUER, COPY=GUTBEL2
     * NO key, use SEQ-NO as SQL-key!
     DESCR=Gutschrift/Belastung 2
     DB2DROP=GU2-FILLER GU2-ABTEILUNG GU2-BENUTZER-CODE GU2-SACHBEARBEITER-CODE
   RECORD=REMISS, PARENT=BETREUER, DB2PREFIX=RMS
     DESCR=Remission
     KEY=REM-RETOUR-NR REM-AGENTUR-NR REM-BETREUER-NR
     DB2DETAIL=REM-RETOURWAREN AS REMRET PREFIX RMT
*.... ende PEBSY KERN (KOOP)

  RECORD=BANKBET, PARENT=BETREUER, COPY=BANKBET, PREFIX=BBB 
     DESCR=Bank-Betreuer
   * achtung: Bank-Betreuer hat KEINEN DL/I key, use SEQ-NO as SQL-key
    DB2DROP=BBB-FILLER BBB-ABTEILUNG BBB-BENUTZER-CODE BBB-SACHBEARBEITER-CODE
  
   RECORD=AUSZABET, PARENT=BANKBET, PREFIX=ABB 
*    Korrektur lt Fr. Peschke (Parent=BANKBET)
     DESCR=Auszahlung für Brutto-Betreuer
     MULTIPLE KEY= ABB-JJMMTT-BUCHUNG
     DB2DROP=ABB-FILLER ABB-ABTEILUNG ABB-BENUTZER-CODE ABB-SACHBEARBEITER-CODE
 

Given the DATA DICTIONARY above, DBDICT is executed as follows:

cmd>DBDICT pebsy

Which produces the following LOG-file:

DBDICT.pebsy.log      2004-02-26 14:41:19

DBDICT Processing of DICTIONARY: PEBSY
======================================

File Class: PEBSY
=================

Date-Form Table: DATEFORM.TABLE used.

File: .\includes\AGENTUR.cpy
============================

Parsing Decls of file: .\includes\AGENTUR.cpy with cob_decl.nrx vs 14.00 23.02.2004 17:08:49
    corresponding IDECL-file is: AGENTUR.IDECL
         copy: AGENTUR COBOL-structure: AGENTUR-STAMM-SATZ
   first item: AST-AGENTUR-NR level: 5 default prefix: AST
DB2-record-prefix is: AGN
         key-loc:1 key-size: 5
   Record: BETREUER
    relevant copy is: .\includes\BETREUER.cpy

File: .\includes\BETREUER.cpy
=============================

Parsing Decls of file: .\includes\BETREUER.cpy with cob_decl.nrx vs 14.00 23.02.2004 17:08:53
    corresponding IDECL-file is: BETREUER.IDECL
         copy: BETREUER COBOL-structure: BETREUER-STAMM-SATZ
   first item: BET-BETREUER-NR level: 5 default prefix: BET
         key-loc:1 key-size: 1
   Record: BINFIX
    relevant copy is: .\includes\BINFIX.cpy

File: .\includes\BINFIX.cpy
===========================

Parsing Decls of file: .\includes\BINFIX.cpy with cob_decl.nrx vs 14.00 23.02.2004 17:08:57
    corresponding IDECL-file is: BINFIX.IDECL
         copy: BINFIX COBOL-structure: BETREUER-INFO-SATZ-FIX
   first item: BIF-SATZ-INDEX level: 5 default prefix: BIF
         key-loc:1 key-size: 4
   Record: BINVAR
    relevant copy is: .\includes\BINVAR.cpy

File: .\includes\BINVAR.cpy
===========================

Parsing Decls of file: .\includes\BINVAR.cpy with cob_decl.nrx vs 14.00 23.02.2004 17:09:01
    corresponding IDECL-file is: BINVAR.IDECL
         copy: BINVAR COBOL-structure: BETREUER-INFO-SATZ-VARIABLE
   first item: BIV-SEITE level: 5 default prefix: BIV
         key-loc:1 key-size: 1
   Record: LIEFSOFO
    relevant copy is: .\includes\LIEFSOFO.cpy

File: .\includes\LIEFSOFO.cpy
=============================

Parsing Decls of file: .\includes\LIEFSOFO.cpy with cob_decl.nrx vs 14.00 23.02.2004 17:09:04
    corresponding IDECL-file is: LIEFSOFO.IDECL
         copy: LIEFSOFO COBOL-structure: LIEFERUNG-SOFORT-SATZ
   first item: ALS-TITEL-NR level: 5 default prefix: ALS
         key-loc:1 key-size: 6
   Record: LIEFTERM
    relevant copy is: .\includes\LIEFTERM.cpy

File: .\includes\LIEFTERM.cpy
=============================

Parsing Decls of file: .\includes\LIEFTERM.cpy with cob_decl.nrx vs 14.00 23.02.2004 17:09:07
    corresponding IDECL-file is: LIEFTERM.IDECL
         copy: LIEFTERM COBOL-structure: LIEFERUNG-TERMIN-SATZ
   first item: ALT-TITEL-NR level: 5 default prefix: ALT
         key-loc:1 key-size: 6
   Record: UMSPRO
    relevant copy is: .\includes\UMSPRO.cpy

File: .\includes\UMSPRO.cpy
===========================

Parsing Decls of file: .\includes\UMSPRO.cpy with cob_decl.nrx vs 14.00 23.02.2004 17:09:10
    corresponding IDECL-file is: UMSPRO.IDECL
         copy: UMSPRO COBOL-structure: UMSATZ-PROVISIONS-SATZ
   first item: BUP-PROVISION-QUARTAL level: 5 default prefix: BUP
    record-prefix is: BUP
   Record: GUTBEL1
    relevant copy is: .\includes\GUTBEL.cpy

File: .\includes\GUTBEL.cpy
===========================

Parsing Decls of file: .\includes\GUTBEL.cpy with cob_decl.nrx vs 14.00 23.02.2004 17:09:12
    corresponding IDECL-file is: GUTBEL.IDECL
         copy: GUTBEL COBOL-structure: GUTSCHRIFT-BELASTUNG
   first item: GUB-FAELLIG-JJMMTT level: 5 default prefix: GUB
   Record: GUTBEL2
    relevant copy is: .\includes\GUTBEL2.cpy

File: .\includes\GUTBEL2.cpy
============================

Parsing Decls of file: .\includes\GUTBEL2.cpy with cob_decl.nrx vs 14.00 23.02.2004 17:09:15
    corresponding IDECL-file is: GUTBEL2.IDECL
         copy: GUTBEL2 COBOL-structure: GUTSCHRIFT-BELASTUNG-2
   first item: GU2-FAELLIG-JJMMTT level: 5 default prefix: GU2
   Record: REMISS
    relevant copy is: .\includes\REMISS.cpy

File: .\includes\REMISS.cpy
===========================

Parsing Decls of file: .\includes\REMISS.cpy with cob_decl.nrx vs 14.00 23.02.2004 17:09:18
    corresponding IDECL-file is: REMISS.IDECL
         copy: REMISS COBOL-structure: REMISSIONS-SATZ
   first item: REM-RETOUR-NR level: 5 default prefix: REM
DB2-record-prefix is: RMS
         key-loc:1 key-size: 9
   Record: BANKBET
    relevant copy is: .\includes\BANKBET.cpy

File: .\includes\BANKBET.cpy
============================

Parsing Decls of file: .\includes\BANKBET.cpy with cob_decl.nrx vs 14.00 23.02.2004 17:09:21
    corresponding IDECL-file is: BANKBET.IDECL
         copy: BANKBET COBOL-structure: BANK-BRUTTO-BETREUER
   first item: BBB-BANKLEITZAHL level: 5 default prefix: BBB
    record-prefix is: BBB
   Record: AUSZABET
    relevant copy is: .\includes\AUSZABET.cpy

File: .\includes\AUSZABET.cpy
=============================

Parsing Decls of file: .\includes\AUSZABET.cpy with cob_decl.nrx vs 14.00 23.02.2004 17:09:24
    corresponding IDECL-file is: AUSZABET.IDECL
         copy: AUSZABET COBOL-structure: AUSZAHLUNG-BRUTTO-BETREUER
   first item: ABB-JJMMTT-BUCHUNG level: 5 default prefix: ABB
    record-prefix is: ABB
         key-loc:1 key-size: 6
      file-class: PEBSY max. thread-length: 26

       no warnings
       no errors encountered

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

        1 File Classes defined.
        
Data Dictionary for: 'PEBSY' saved in: PEBSY.DD

4.03 Generating the DOWNLOAD Programs

The respective DOWNLOAD-Programs for the Data Dictionary PEBSY may now be generated using the command

cmd>DB123 DOWNLOAD PEBSY

which displays the following results:

db123.download.pebsy.log      2004-02-23 17:37:32

DB123 vs 14.05: DOWNLOAD PEBSY
==============================

... Loading Dictionary: PEBSY options: ADD

List of defined records of Data Dictionary: PEBSY
=================================================

    File          Record   Logical         Prefix
 ## Class    Type Name     Parent   CBL  DL/I DB2  DB2-Table / Physical Filename

--- -------- ---- -------- -------- ---- ---- ---- -----------------------------
  1 PEBSY    DL/I AGENTUR           AST  AST  AGN  PEBSY
  2 PEBSY    DL/I BETREUER AGENTUR  BET  BET  BET  PEBSY
  3 PEBSY    DL/I BINFIX   AGENTUR  BIF  BIF  BIF  PEBSY
  4 PEBSY    DL/I BINVAR   AGENTUR  BIV  BIV  BIV  PEBSY
  5 PEBSY    DL/I LIEFSOFO AGENTUR  ALS  ALS  ALS  PEBSY
  6 PEBSY    DL/I LIEFTERM AGENTUR  ALT  ALT  ALT  PEBSY
  7 PEBSY    DL/I UMSPRO   BETREUER BUP  BUP  BUP  PEBSY
  8 PEBSY    DL/I GUTBEL1  BETREUER GUB  GUB  GUB  PEBSY
  9 PEBSY    DL/I GUTBEL2  BETREUER GU2  GU2  GU2  PEBSY
 10 PEBSY    DL/I REMISS   BETREUER REM  REM  RMS  PEBSY
 11 PEBSY    DL/I BANKBET  BETREUER BBB  BBB  BBB  PEBSY
 12 PEBSY    DL/I AUSZABET BANKBET  ABB  ABB  ABB  PEBSY

Processing Data-Dictionary: PEBSY
=================================

Generate DOWNLOAD programs (to CSV-file) for file class: PEBSY
==============================================================

Generating CVPEB001 for file-class: PEBSY
=========================================

   conversion Method: 
    Template program: DB12DLI4.MACRO used for code-generation
   converting macro: DB12DLI4.MACRO to: D:\db123\samples\old_pebsy\CVPEB001.cbl

   gen_code_file is: D:\db123\samples\old_pebsy\CVPEB001.cbl
   gen_decl_file is: D:\db123\samples\old_pebsy\CVPEB001.cbl

record: AGENTUR DB2DROP: AST-FILLER AST-ABTEILUNG AST-BENUTZER-CODE AST-SACHBEARBEITER-CODE
Record: AGENTUR DLM: AST-UPDATE-DATE generated.
Record: AGENTUR level: 0 UID: AST-UPDATE-ID generated.
record #: 1 record: AGENTUR CSV-rec-size: 22 bytes

record: BETREUER DB2DROP: BET-ABTEILUNG BET-BENUTZER-CODE BET-SACHBEARBEITER-CODE
Record: BETREUER DLM: BET-UPDATE-DATE generated.
Record: BETREUER level: 0 UID: BET-UPDATE-ID generated.
record #: 2 record: BETREUER CSV-rec-size: 44 bytes

record: BINFIX DB2DROP: BIF-FILLER BIF-ABTEILUNG BIF-BENUTZER-CODE BIF-SACHBEARBEITER-CODE
Record: BINFIX DLM: BIF-UPDATE-DATE generated.
Record: BINFIX level: 0 UID: BIF-UPDATE-ID generated.
record #: 3 record: BINFIX CSV-rec-size: 66 bytes

record: BINVAR DB2DROP: BIV-FILLER BIV-ABTEILUNG BIV-BENUTZER-CODE BIV-SACHBEARBEITER-CODE
Record: BINVAR DLM: BIV-UPDATE-DATE generated.
Record: BINVAR level: 0 UID: BIV-UPDATE-ID generated.
record #: 4 record: BINVAR CSV-rec-size: 88 bytes

Record: LIEFSOFO DLM: ALS-UPDATE-DATE generated.
Record: LIEFSOFO level: 0 UID: ALS-UPDATE-ID generated.
record #: 5 record: LIEFSOFO CSV-rec-size: 110 bytes

record: LIEFTERM DB2DROP: ALT-FILLER
Record: LIEFTERM DLM: ALT-UPDATE-DATE generated.
Record: LIEFTERM level: 0 UID: ALT-UPDATE-ID generated.
record #: 6 record: LIEFTERM CSV-rec-size: 132 bytes

record: UMSPRO DB2DROP: BUP-FILLER BUP-ABTEILUNG BUP-BENUTZER-CODE BUP-SACHBEARBEITER-CODE
Record: UMSPRO DLM: BUP-UPDATE-DATE generated.
Record: UMSPRO level: 0 UID: BUP-UPDATE-ID generated.
record #: 7 record: UMSPRO CSV-rec-size: 154 bytes

record: GUTBEL1 DB2DROP: GUB-FILLER GUB-ABTEILUNG GUB-BENUTZER-CODE GUB-SACHBEARBEITER-CODE
Record: GUTBEL1 DLM: GUB-UPDATE-DATE generated.
Record: GUTBEL1 level: 0 UID: GUB-UPDATE-ID generated.
record #: 8 record: GUTBEL1 CSV-rec-size: 176 bytes

record: GUTBEL2 DB2DROP: GU2-FILLER GU2-ABTEILUNG GU2-BENUTZER-CODE GU2-SACHBEARBEITER-CODE
Record: GUTBEL2 DLM: GU2-UPDATE-DATE generated.
Record: GUTBEL2 level: 0 UID: GU2-UPDATE-ID generated.
record #: 9 record: GUTBEL2 CSV-rec-size: 198 bytes

record: REMISS DB2DETAIL: REM-RETOURWAREN AS REMRET PREFIX RMT
Record: REMISS DLM: REM-UPDATE-DATE generated.
Record: REMISS level: 0 UID: REM-UPDATE-ID generated.
record #: 10 record: REMISS CSV-rec-size: 220 bytes

record: BANKBET DB2DROP: BBB-FILLER BBB-ABTEILUNG BBB-BENUTZER-CODE BBB-SACHBEARBEITER-CODE
Record: BANKBET DLM: BBB-UPDATE-DATE generated.
Record: BANKBET level: 0 UID: BBB-UPDATE-ID generated.
record #: 11 record: BANKBET CSV-rec-size: 242 bytes

record: AUSZABET DB2DROP: ABB-FILLER ABB-ABTEILUNG ABB-BENUTZER-CODE ABB-SACHBEARBEITER-CODE
Record: AUSZABET DLM: ABB-UPDATE-DATE generated.
Record: AUSZABET level: 0 UID: ABB-UPDATE-ID generated.
record #: 12 record: AUSZABET CSV-rec-size: 264 bytes

record: AGENTUR DB2DROP: AST-FILLER AST-ABTEILUNG AST-BENUTZER-CODE AST-SACHBEARBEITER-CODE
record: BETREUER DB2DROP: BET-ABTEILUNG BET-BENUTZER-CODE BET-SACHBEARBEITER-CODE
record: BINFIX DB2DROP: BIF-FILLER BIF-ABTEILUNG BIF-BENUTZER-CODE BIF-SACHBEARBEITER-CODE
record: BINVAR DB2DROP: BIV-FILLER BIV-ABTEILUNG BIV-BENUTZER-CODE BIV-SACHBEARBEITER-CODE
record: LIEFTERM DB2DROP: ALT-FILLER
record: UMSPRO DB2DROP: BUP-FILLER BUP-ABTEILUNG BUP-BENUTZER-CODE BUP-SACHBEARBEITER-CODE
record: GUTBEL1 DB2DROP: GUB-FILLER GUB-ABTEILUNG GUB-BENUTZER-CODE GUB-SACHBEARBEITER-CODE
record: GUTBEL2 DB2DROP: GU2-FILLER GU2-ABTEILUNG GU2-BENUTZER-CODE GU2-SACHBEARBEITER-CODE
record: REMISS DB2DETAIL: REM-RETOURWAREN AS REMRET PREFIX RMT
record: BANKBET DB2DROP: BBB-FILLER BBB-ABTEILUNG BBB-BENUTZER-CODE BBB-SACHBEARBEITER-CODE
record: AUSZABET DB2DROP: ABB-FILLER ABB-ABTEILUNG ABB-BENUTZER-CODE ABB-SACHBEARBEITER-CODE
record: AGENTUR DB2DROP: AST-FILLER AST-ABTEILUNG AST-BENUTZER-CODE AST-SACHBEARBEITER-CODE
record: BETREUER DB2DROP: BET-ABTEILUNG BET-BENUTZER-CODE BET-SACHBEARBEITER-CODE
record: BINFIX DB2DROP: BIF-FILLER BIF-ABTEILUNG BIF-BENUTZER-CODE BIF-SACHBEARBEITER-CODE
record: BINVAR DB2DROP: BIV-FILLER BIV-ABTEILUNG BIV-BENUTZER-CODE BIV-SACHBEARBEITER-CODE
record: LIEFTERM DB2DROP: ALT-FILLER
record: UMSPRO DB2DROP: BUP-FILLER BUP-ABTEILUNG BUP-BENUTZER-CODE BUP-SACHBEARBEITER-CODE
record: GUTBEL1 DB2DROP: GUB-FILLER GUB-ABTEILUNG GUB-BENUTZER-CODE GUB-SACHBEARBEITER-CODE
record: GUTBEL2 DB2DROP: GU2-FILLER GU2-ABTEILUNG GU2-BENUTZER-CODE GU2-SACHBEARBEITER-CODE
record: REMISS DB2DETAIL: REM-RETOURWAREN AS REMRET PREFIX RMT
record: BANKBET DB2DROP: BBB-FILLER BBB-ABTEILUNG BBB-BENUTZER-CODE BBB-SACHBEARBEITER-CODE
record: AUSZABET DB2DROP: ABB-FILLER ABB-ABTEILUNG ABB-BENUTZER-CODE ABB-SACHBEARBEITER-CODE
        
File: D:\db123\samples\old_pebsy\CVPEB001.cbl closed. (2815 code lines)

DB123 summary (vs. 14.05):
==========================

        1 DOWNLOAD programs generated to: D:\db123\samples\old_pebsy

       no errors detected.
       no warnings.

DB123 used:    2.55 seconds for the actions above.

Important Notes:

.4.04 Generating the NEW Data Dictionary

Now, as the next step, we have to Define the NEW Data-Dictionary, where the single DL/I Segments are converted to logically equivalent DB2  Tables.

In Order to do so, each HIERARCHIC DL/I Segment (DL/I record structure) is prepended with the proper PARENT keys, i.e. the keys of all PARENT-records specified in the PARENT-clauses of the  (old) Data Dictionary.

We may use the DB123 ACTION NEWDICT to perform this task, which will generate a new relational database Data-Dictionary, as follows:

cmd>DB123 NEWDICT PEBSY

db123.newdict.pebsy.log      2004-02-26 11:33:14

DB123 vs 14.06: NEWDICT PEBSY
=============================

... Loading Dictionary: PEBSY options: ADD

List of defined records of Data Dictionary: PEBSY
=================================================

    File          Record   Logical         Prefix
 ## Class    Type Name     Parent   CBL  DL/I DB2  DB2-Table / Physical Filename
--- -------- ---- -------- -------- ---- ---- ---- -----------------------------
  1 PEBSY    DL/I AGENTUR           AST  AST  AGN  PEBSY
  2 PEBSY    DL/I BETREUER AGENTUR  BET  BET  BET  PEBSY
  3 PEBSY    DL/I BINFIX   AGENTUR  BIF  BIF  BIF  PEBSY
  4 PEBSY    DL/I BINVAR   AGENTUR  BIV  BIV  BIV  PEBSY
  5 PEBSY    DL/I LIEFSOFO AGENTUR  ALS  ALS  ALS  PEBSY
  6 PEBSY    DL/I LIEFTERM AGENTUR  ALT  ALT  ALT  PEBSY
  7 PEBSY    DL/I UMSPRO   BETREUER BUP  BUP  BUP  PEBSY
  8 PEBSY    DL/I GUTBEL1  BETREUER GUB  GUB  GUB  PEBSY
  9 PEBSY    DL/I GUTBEL2  BETREUER GU2  GU2  GU2  PEBSY
 10 PEBSY    DL/I REMISS   BETREUER REM  REM  RMS  PEBSY
 11 PEBSY    DL/I BANKBET  BETREUER BBB  BBB  BBB  PEBSY
 12 PEBSY    DL/I AUSZABET BANKBET  ABB  ABB  ABB  PEBSY
 

Processing Data-Dictionary: PEBSY
=================================


Generating new Data Dictionary: D:\DB123\samples\new_pebsy\PEBSY.newdict
========================================================================

   item: AST-AGENTUR-NR cloned to: BET-AGENTUR-NR
   Logical parent of: BETREUER is: AGENTUR
      WITH AST-AGENTUR-NR=BET-AGENTUR-NR
Build full key-list of: BETREUER for DB2 usage:
   Parent keys are: AST-AGENTUR-NR
   Record Keys are: BET-BETREUER-NR
   Full key-list of: BETREUER is: BET-AGENTUR-NR BET-BETREUER-NR
   item: AST-AGENTUR-NR cloned to: BIF-AGENTUR-NR
   Logical parent of: BINFIX is: AGENTUR
      WITH AST-AGENTUR-NR=BIF-AGENTUR-NR
Build full key-list of: BINFIX for DB2 usage:
   Parent keys are: AST-AGENTUR-NR
   Record Keys are: BIF-SATZ-INDEX
   Full key-list of: BINFIX is: BIF-AGENTUR-NR BIF-SATZ-INDEX
   item: AST-AGENTUR-NR cloned to: BIV-AGENTUR-NR
   Logical parent of: BINVAR is: AGENTUR
      WITH AST-AGENTUR-NR=BIV-AGENTUR-NR
Build full key-list of: BINVAR for DB2 usage:
   Parent keys are: AST-AGENTUR-NR
   Record Keys are: BIV-SEITE
   Full key-list of: BINVAR is: BIV-AGENTUR-NR BIV-SEITE
   item: AST-AGENTUR-NR cloned to: ALS-AGENTUR-NR
   Logical parent of: LIEFSOFO is: AGENTUR
      WITH AST-AGENTUR-NR=ALS-AGENTUR-NR
Build full key-list of: LIEFSOFO for DB2 usage:
   Parent keys are: AST-AGENTUR-NR
   Record Keys are: ALS-TITEL-NR
   Full key-list of: LIEFSOFO is: ALS-AGENTUR-NR ALS-TITEL-NR
   item: AST-AGENTUR-NR cloned to: ALT-AGENTUR-NR
   Logical parent of: LIEFTERM is: AGENTUR
      WITH AST-AGENTUR-NR=ALT-AGENTUR-NR
Build full key-list of: LIEFTERM for DB2 usage:
   Parent keys are: AST-AGENTUR-NR
   Record Keys are: ALT-TITEL-NR
   Full key-list of: LIEFTERM is: ALT-AGENTUR-NR ALT-TITEL-NR
   item: BET-AGENTUR-NR cloned to: BUP-AGENTUR-NR
   item: BET-BETREUER-NR cloned to: BUP-BETREUER-NR
   Logical parent of: UMSPRO is: BETREUER
      WITH BET-AGENTUR-NR=BUP-AGENTUR-NR,  BET-BETREUER-NR=BUP-BETREUER-NR
Build full key-list of: UMSPRO for DB2 usage:
   Parent keys are: BET-AGENTUR-NR BET-BETREUER-NR
   Record Keys are: 
   Full key-list of: UMSPRO is: BUP-AGENTUR-NR BUP-BETREUER-NR
   item: BET-AGENTUR-NR cloned to: GUB-AGENTUR-NR
   item: BET-BETREUER-NR cloned to: GUB-BETREUER-NR
   Logical parent of: GUTBEL1 is: BETREUER
      WITH BET-AGENTUR-NR=GUB-AGENTUR-NR,  BET-BETREUER-NR=GUB-BETREUER-NR
Build full key-list of: GUTBEL1 for DB2 usage:
   Parent keys are: BET-AGENTUR-NR BET-BETREUER-NR
   Record Keys are: 
   Full key-list of: GUTBEL1 is: GUB-AGENTUR-NR GUB-BETREUER-NR
   item: BET-AGENTUR-NR cloned to: GU2-AGENTUR-NR
   item: BET-BETREUER-NR cloned to: GU2-BETREUER-NR
   Logical parent of: GUTBEL2 is: BETREUER
      WITH BET-AGENTUR-NR=GU2-AGENTUR-NR,  BET-BETREUER-NR=GU2-BETREUER-NR
Build full key-list of: GUTBEL2 for DB2 usage:
   Parent keys are: BET-AGENTUR-NR BET-BETREUER-NR
   Record Keys are: 
   Full key-list of: GUTBEL2 is: GU2-AGENTUR-NR GU2-BETREUER-NR
   Logical parent of: REMISS is: BETREUER
      WITH BET-AGENTUR-NR=REM-AGENTUR-NR,  BET-BETREUER-NR=REM-BETREUER-NR
Build full key-list of: REMISS for DB2 usage:
   Parent keys are: BET-AGENTUR-NR BET-BETREUER-NR
   Record Keys are: REM-RETOUR-NR REM-AGENTUR-NR REM-BETREUER-NR
Warning: key: REM-AGENTUR-NR already in KEY LIST: REM-RETOUR-NR REM-AGENTUR-NR REM-BETREUER-NR
duplicate item: REM-AGENTUR-NR ignored!!!
Warning: key: REM-BETREUER-NR already in KEY LIST: REM-RETOUR-NR REM-AGENTUR-NR REM-BETREUER-NR
duplicate item: REM-BETREUER-NR ignored!!!
   Full key-list of: REMISS is:  REM-RETOUR-NR REM-AGENTUR-NR REM-BETREUER-NR
   item: BET-AGENTUR-NR cloned to: BBB-AGENTUR-NR
   item: BET-BETREUER-NR cloned to: BBB-BETREUER-NR
   Logical parent of: BANKBET is: BETREUER
      WITH BET-AGENTUR-NR=BBB-AGENTUR-NR,  BET-BETREUER-NR=BBB-BETREUER-NR
Build full key-list of: BANKBET for DB2 usage:
   Parent keys are: BET-AGENTUR-NR BET-BETREUER-NR
   Record Keys are: 
   Full key-list of: BANKBET is: BBB-AGENTUR-NR BBB-BETREUER-NR
   item: BBB-AGENTUR-NR cloned to: ABB-AGENTUR-NR
   item: BBB-BETREUER-NR cloned to: ABB-BETREUER-NR
   Logical parent of: AUSZABET is: BANKBET
      WITH BBB-AGENTUR-NR=ABB-AGENTUR-NR,  BBB-BETREUER-NR=ABB-BETREUER-NR
Build full key-list of: AUSZABET for DB2 usage:
   Parent keys are: BBB-AGENTUR-NR BBB-BETREUER-NR
   Record Keys are: ABB-JJMMTT-BUCHUNG
   Full key-list of: AUSZABET is: ABB-AGENTUR-NR ABB-BETREUER-NR ABB-JJMMTT-BUCHUNG
       87 lines generated to new dictionary: D:\DB123\samples\new_pebsy\PEBSY.newdict
        2 warnings
       no errors encountered

As the immediate next step, we also generate the NEW COPY-books for the NEW DB2 Database,
using the NEWCOPY action of DB123.

cmd>DB123 NEWCOPY PEBSY

db123.newcopy.pebsy.log      2004-02-26 11:37:35

DB123 vs 14.06: NEWCOPY PEBSY
=============================

... Loading Dictionary: PEBSY options: ADD

List of defined records of Data Dictionary: PEBSY
=================================================

    File          Record   Logical         Prefix
 ## Class    Type Name     Parent   CBL  DL/I DB2  DB2-Table / Physical Filename
--- -------- ---- -------- -------- ---- ---- ---- -----------------------------
  1 PEBSY    DL/I AGENTUR           AST  AST  AGN  PEBSY
  2 PEBSY    DL/I BETREUER AGENTUR  BET  BET  BET  PEBSY
  3 PEBSY    DL/I BINFIX   AGENTUR  BIF  BIF  BIF  PEBSY
  4 PEBSY    DL/I BINVAR   AGENTUR  BIV  BIV  BIV  PEBSY
  5 PEBSY    DL/I LIEFSOFO AGENTUR  ALS  ALS  ALS  PEBSY
  6 PEBSY    DL/I LIEFTERM AGENTUR  ALT  ALT  ALT  PEBSY
  7 PEBSY    DL/I UMSPRO   BETREUER BUP  BUP  BUP  PEBSY
  8 PEBSY    DL/I GUTBEL1  BETREUER GUB  GUB  GUB  PEBSY
  9 PEBSY    DL/I GUTBEL2  BETREUER GU2  GU2  GU2  PEBSY
 10 PEBSY    DL/I REMISS   BETREUER REM  REM  RMS  PEBSY
 11 PEBSY    DL/I BANKBET  BETREUER BBB  BBB  BBB  PEBSY
 12 PEBSY    DL/I AUSZABET BANKBET  ABB  ABB  ABB  PEBSY
 


Processing Data-Dictionary: PEBSY
=================================


Generate NEW COBOL copies for file class: PEBSY
===============================================

record: AGENTUR DB2DROP: AST-FILLER AST-ABTEILUNG AST-BENUTZER-CODE AST-SACHBEARBEITER-CODE
Record: AGENTUR DLM: AST-UPDATE-DATE generated.
Record: AGENTUR level: 0 UID: AST-UPDATE-ID generated.
Record: AGENTUR converting old-copy: D:\db123\samples\old_pebsy\.\includes\AGENTUR.cpy to new copy: D:\db123\samples\old_pebsy\AGENTUR.cpy
first_detail_lno= 14
Generating Parent key decl
record: BETREUER DB2DROP: BET-ABTEILUNG BET-BENUTZER-CODE BET-SACHBEARBEITER-CODE
Record: BETREUER DLM: BET-UPDATE-DATE generated.
Record: BETREUER level: 0 UID: BET-UPDATE-ID generated.
Record: BETREUER converting old-copy: D:\db123\samples\old_pebsy\.\includes\BETREUER.cpy to new copy: D:\db123\samples\old_pebsy\BETREUER.cpy
first_detail_lno= 16
Generating Parent key decl
   Logical DL/I Parent Key: BET-AGENTUR-NR added.
record: BINFIX DB2DROP: BIF-FILLER BIF-ABTEILUNG BIF-BENUTZER-CODE BIF-SACHBEARBEITER-CODE
Record: BINFIX DLM: BIF-UPDATE-DATE generated.
Record: BINFIX level: 0 UID: BIF-UPDATE-ID generated.
Record: BINFIX converting old-copy: D:\db123\samples\old_pebsy\.\includes\BINFIX.cpy to new copy: D:\db123\samples\old_pebsy\BINFIX.cpy
first_detail_lno= 13
Generating Parent key decl
   Logical DL/I Parent Key: BIF-AGENTUR-NR added.
record: BINVAR DB2DROP: BIV-FILLER BIV-ABTEILUNG BIV-BENUTZER-CODE BIV-SACHBEARBEITER-CODE
Record: BINVAR DLM: BIV-UPDATE-DATE generated.
Record: BINVAR level: 0 UID: BIV-UPDATE-ID generated.
Record: BINVAR converting old-copy: D:\db123\samples\old_pebsy\.\includes\BINVAR.cpy to new copy: D:\db123\samples\old_pebsy\BINVAR.cpy
first_detail_lno= 13
Generating Parent key decl
   Logical DL/I Parent Key: BIV-AGENTUR-NR added.
Record: LIEFSOFO DLM: ALS-UPDATE-DATE generated.
Record: LIEFSOFO level: 0 UID: ALS-UPDATE-ID generated.
Record: LIEFSOFO converting old-copy: D:\db123\samples\old_pebsy\.\includes\LIEFSOFO.cpy to new copy: D:\db123\samples\old_pebsy\LIEFSOFO.cpy
first_detail_lno= 12
Generating Parent key decl
   Logical DL/I Parent Key: ALS-AGENTUR-NR added.
record: LIEFTERM DB2DROP: ALT-FILLER
Record: LIEFTERM DLM: ALT-UPDATE-DATE generated.
Record: LIEFTERM level: 0 UID: ALT-UPDATE-ID generated.
Record: LIEFTERM converting old-copy: D:\db123\samples\old_pebsy\.\includes\LIEFTERM.cpy to new copy: D:\db123\samples\old_pebsy\LIEFTERM.cpy
first_detail_lno= 13
Generating Parent key decl
   Logical DL/I Parent Key: ALT-AGENTUR-NR added.
record: UMSPRO DB2DROP: BUP-FILLER BUP-ABTEILUNG BUP-BENUTZER-CODE BUP-SACHBEARBEITER-CODE
Record: UMSPRO DLM: BUP-UPDATE-DATE generated.
Record: UMSPRO level: 0 UID: BUP-UPDATE-ID generated.
Record: UMSPRO converting old-copy: D:\db123\samples\old_pebsy\.\includes\UMSPRO.cpy to new copy: D:\db123\samples\old_pebsy\UMSPRO.cpy
first_detail_lno= 13
Generating Parent key decl
   Logical DL/I Parent Key: BUP-AGENTUR-NR added.
   Logical DL/I Parent Key: BUP-BETREUER-NR added.
record: GUTBEL1 DB2DROP: GUB-FILLER GUB-ABTEILUNG GUB-BENUTZER-CODE GUB-SACHBEARBEITER-CODE
Record: GUTBEL1 DLM: GUB-UPDATE-DATE generated.
Record: GUTBEL1 level: 0 UID: GUB-UPDATE-ID generated.
Record: GUTBEL1 converting old-copy: D:\db123\samples\old_pebsy\.\includes\GUTBEL.cpy to new copy: D:\db123\samples\old_pebsy\GUTBEL.cpy
first_detail_lno= 13
Generating Parent key decl
   Logical DL/I Parent Key: GUB-AGENTUR-NR added.
   Logical DL/I Parent Key: GUB-BETREUER-NR added.
record: GUTBEL2 DB2DROP: GU2-FILLER GU2-ABTEILUNG GU2-BENUTZER-CODE GU2-SACHBEARBEITER-CODE
Record: GUTBEL2 DLM: GU2-UPDATE-DATE generated.
Record: GUTBEL2 level: 0 UID: GU2-UPDATE-ID generated.
Record: GUTBEL2 converting old-copy: D:\db123\samples\old_pebsy\.\includes\GUTBEL2.cpy to new copy: D:\db123\samples\old_pebsy\GUTBEL2.cpy
first_detail_lno= 14
Generating Parent key decl
   Logical DL/I Parent Key: GU2-AGENTUR-NR added.
   Logical DL/I Parent Key: GU2-BETREUER-NR added.
record: REMISS DB2DETAIL: REM-RETOURWAREN AS REMRET PREFIX RMT
Record: REMISS DLM: REM-UPDATE-DATE generated.
Record: REMISS level: 0 UID: REM-UPDATE-ID generated.
Record: REMISS converting old-copy: D:\db123\samples\old_pebsy\.\includes\REMISS.cpy to new copy: D:\db123\samples\old_pebsy\REMISS.cpy
first_detail_lno= 13
Generating Parent key decl
   Logical DL/I Parent Key: REM-AGENTUR-NR added.
   Logical DL/I Parent Key: REM-BETREUER-NR added.
record: BANKBET DB2DROP: BBB-FILLER BBB-ABTEILUNG BBB-BENUTZER-CODE BBB-SACHBEARBEITER-CODE
Record: BANKBET DLM: BBB-UPDATE-DATE generated.
Record: BANKBET level: 0 UID: BBB-UPDATE-ID generated.
Record: BANKBET converting old-copy: D:\db123\samples\old_pebsy\.\includes\BANKBET.cpy to new copy: D:\db123\samples\old_pebsy\BANKBET.cpy
first_detail_lno= 11
Generating Parent key decl
   Logical DL/I Parent Key: BBB-AGENTUR-NR added.
   Logical DL/I Parent Key: BBB-BETREUER-NR added.
record: AUSZABET DB2DROP: ABB-FILLER ABB-ABTEILUNG ABB-BENUTZER-CODE ABB-SACHBEARBEITER-CODE
Record: AUSZABET DLM: ABB-UPDATE-DATE generated.
Record: AUSZABET level: 0 UID: ABB-UPDATE-ID generated.
Record: AUSZABET converting old-copy: D:\db123\samples\old_pebsy\.\includes\AUSZABET.cpy to new copy: D:\db123\samples\old_pebsy\AUSZABET.cpy
first_detail_lno= 11
Generating Parent key decl
   Logical DL/I Parent Key: ABB-AGENTUR-NR added.
   Logical DL/I Parent Key: ABB-BETREUER-NR added.

DB123 summary (vs. 14.06):
==========================

       12 NEW copy-books generated to: D:\db123\samples\old_pebsy
 
       no errors detected.
       no warnings.
 
DB123 used:    1.86 seconds for the actions above.

cmd>DB123 NEWCOPY PEBSY

Some additional notes :

  1. in the DONAULAND Database, the former DL/I copies did NOT contain any PARENT keys in the detail records.
    Therefore this parent key information needs to be added in the DB2-version.
    These
    logical parent keys are added in FRONT of the old COBOL data-items belongig to this record.
  2. If the DL/I key has been a MULTIPLE key or did have NO KEY at all, a sequence field is added.
  3. As a standard, each record in the NEW database has 2 new fields added at the end of each record:

As already noted, a SEQUENCE-field is added for the following cases:

Therefore ANY new record of our new DB2-database does now have a UNIQUE key
(this property of DB2-databases is called 3
rd Normal form, as you probably know)

 

4.05 Defining the NEW database Data Dictionary (relational DB)

You should now list the generated NEW Dictionary and also the NEW copy-books, and review it, discussing open issues with your collegues and database administrator.
If changes are necessary, you might change the new Dictionary until it fits you requirements.
When you do have changes, you have two choices:

In our sample, we did not need any changes, except to make proper decisions for the REPEATED fields in our COBOL record structure.

For each REPEATED field, you have 2 choices:

Note that the latter approach is also appropriate, when the total DB2 binary length is greater than 4000 bytes, which is a reasonable limit for a single DB2 Table. You may therefore use the DB2DETAIL-clause to push details of 1 COBOL structure to a separate DB2 Table.

The NEW Data Dictionary for our Database does now look like:

pebsy.dict      2004-02-26 14:49:04

* PEBSY Stamm-Datenbank
* 03.05.2003: must introduce DLIPREFIX, as it may be different from PREFIX
* 05.05.2003: DLIPREFIX upgedatet.
* 08.05.2003: reichtiges DLIPREFIX für LIEFSOFO ist 'LSO' (siehe PY0101EK)
* 03.06.2003: BINFIX. BIF-LFD-NR Bestandteil des Keys !
* 17.06.2003: REMRET: REMR-ZEILEN-NR korrigiert.
* 11.07.2003: RESISSIONEN: DL/I Segmentname ist REMISSI (statt REMISS!)
* 17.02.2004: DBSPACE added 
********************************************************************************
********************************************************************************
* ... NEW DB2 Dictionary for PEBSY (DB Persönliche Betreuung)
SCHEMA=COMPUSER
DBSPACE=DB_PEBSY
OWNER=DL

FILECLASS=AGENTUR, FILETYPE=SQL, TABLE=AGENTUR
   RECORD=AGENTUR, COPY=AGENTUR
   DESCR=Agentur-Stammsatz
   PREFIX=AST, DLIPREFIX=AST, DB2PREFIX=AGN
   KEY=AST-AGENTUR-NR
FILECLASS=BETREUER, FILETYPE=SQL, TABLE=BETREUER
   RECORD=BETREUER, COPY=BETREUER
   DESCR=Betreuer-Stammsatz
   PREFIX=BET, DB2PREFIX=BET
   LPARENT=AGENTUR WITH AST-AGENTUR-NR=BET-AGENTUR-NR
   KEY=BET-AGENTUR-NR BET-BETREUER-NR
FILECLASS=BINFIX, FILETYPE=SQL, TABLE=BINFIX
   RECORD=BINFIX, COPY=BINFIX
   DESCR=Betreuer-Info fix
   PREFIX=BIF, DB2PREFIX=BIF
   LPARENT=AGENTUR WITH AST-AGENTUR-NR=BIF-AGENTUR-NR
   KEY=BIF-AGENTUR-NR BIF-SATZ-INDEX BIF-LFD-NR
   ORDER=BIF-AGENTUR-NR BIF-SATZ-INDEX BIF-LFD-NR
FILECLASS=BINVAR, FILETYPE=SQL, TABLE=BINVAR
   RECORD=BINVAR, COPY=BINVAR
   DESCR=Betreuer-Info variabel
   PREFIX=BIV, DB2PREFIX=BIV
   LPARENT=AGENTUR WITH AST-AGENTUR-NR=BIV-AGENTUR-NR
   KEY=BIV-AGENTUR-NR BIV-SEITE
FILECLASS=LIEFSOFO, FILETYPE=SQL, TABLE=LIEFSOFO
   RECORD=LIEFSOFO, COPY=LIEFSOFO
   DESCR=Lieferung sofort
   PREFIX=ALS, DLIPREFIX=LSO, DB2PREFIX=ALS
   LPARENT=AGENTUR WITH AST-AGENTUR-NR=ALS-AGENTUR-NR
   KEY=ALS-AGENTUR-NR ALS-TITEL-NR ALS-LFD-NR
   SEQUENCE=ALS-LFD-NR
FILECLASS=LIEFTERM, FILETYPE=SQL, TABLE=LIEFTERM
   RECORD=LIEFTERM, COPY=LIEFTERM
   DESCR=Lieferung Termin
   PREFIX=ALT, DLIPREFIX=LIT, DB2PREFIX=ALT
   LPARENT=AGENTUR WITH AST-AGENTUR-NR=ALT-AGENTUR-NR
   KEY=ALT-AGENTUR-NR ALT-TITEL-NR ALT-LFD-NR
   SEQUENCE=ALT-LFD-NR
FILECLASS=UMSPRO, FILETYPE=SQL, TABLE=UMSPRO
   RECORD=UMSPRO, COPY=UMSPRO
   DESCR=Betreuer-Historie
   PREFIX=BUP, DLIPREFIX=UMS, DB2PREFIX=BUP
   LPARENT=BETREUER WITH BET-AGENTUR-NR=BUP-AGENTUR-NR,  BET-BETREUER-NR=BUP-BETREUER-NR
   KEY=BUP-AGENTUR-NR BUP-BETREUER-NR
FILECLASS=GUTBEL, FILETYPE=SQL, TABLE=GUTBEL1
   RECORD=GUTBEL, COPY=GUTBEL
   DESCR=Gutschrift/Belastung 1
   PREFIX=GUB, DB2PREFIX=GUB, DLIPREFIX=GUB
   LPARENT=BETREUER WITH BET-AGENTUR-NR=GUB-AGENTUR-NR,  BET-BETREUER-NR=GUB-BETREUER-NR
   KEY=GUB-AGENTUR-NR GUB-BETREUER-NR GUB-LFD-NR
   SEQUENCE=GUB-LFD-NR
FILECLASS=GUTBEL2, FILETYPE=SQL, TABLE=GUTBEL2
   RECORD=GUTBEL2, COPY=GUTBEL2
   DESCR=Gutschrift/Belastung 2
   PREFIX=GU2, DB2PREFIX=GU2
   LPARENT=BETREUER WITH BET-AGENTUR-NR=GU2-AGENTUR-NR,  BET-BETREUER-NR=GU2-BETREUER-NR
   KEY=GU2-AGENTUR-NR GU2-BETREUER-NR GU2-LFD-NR
   SEQUENCE=GU2-LFD-NR
FILECLASS=BANKBET, FILETYPE=SQL, TABLE=BANKBET
   RECORD=BANKBET, COPY=BANKBET
   DESCR=Bank-Betreuer
   PREFIX=BBB, DLIPREFIX=BAN, DB2PREFIX=BBB
   LPARENT=BETREUER WITH BET-AGENTUR-NR=BBB-AGENTUR-NR,  BET-BETREUER-NR=BBB-BETREUER-NR
   KEY=BBB-AGENTUR-NR BBB-BETREUER-NR
FILECLASS=AUSZABET, FILETYPE=SQL, TABLE=AUSZABET
   RECORD=AUSZABET, COPY=AUSZABET
   DESCR=Auszahlung für Brutto-Betreuer
   PREFIX=ABB, DLIPREFIX=AUS, DB2PREFIX=ABB
   LPARENT=BANKBET WITH BBB-AGENTUR-NR=ABB-AGENTUR-NR,  BBB-BETREUER-NR=ABB-BETREUER-NR
   KEY=ABB-AGENTUR-NR ABB-BETREUER-NR ABB-JJMMTT-BUCHUNG ABB-LFD-NR
   SEQUENCE=ABB-LFD-NR
FILECLASS=REMISSI, FILETYPE=SQL, TABLE=REMISS
   RECORD=REMISSI, COPY=REMISS
   DESCR=Remission
   PREFIX=REM, DLIPREFIX=REM, DB2PREFIX=REM
   LPARENT=BETREUER WITH BET-AGENTUR-NR=REM-AGENTUR-NR,  BET-BETREUER-NR=REM-BETREUER-NR
   KEY= REM-AGENTUR-NR REM-BETREUER-NR REM-RETOUR-NR
   DB2DETAIL=REM-RETOURWAREN AS REMRET
* .. detailsatz fuer Retour-Waren   
FILECLASS=REMRET, FILETYPE=SQL, TABLE=REMISS_RETOUR
   RECORD=REMRET, COPY=REMRET
   DESCR=Remission
   PREFIX=RMR, DB2PREFIX=RMR
   LPARENT=REMISSI WITH REM-AGENTUR-NR=RMR-AGENTUR-NR,  REM-BETREUER-NR=RMR-BETREUER-NR,
            REM-RETOUR-NR = RMR-RETOUR-NR
   KEY= RMR-AGENTUR-NR RMR-BETREUER-NR RMR-RETOUR-NR RMR-ZEILEN-NR
 

You will find the listing of the NEW copy-books (DB2-Version) in APPENDIX B

After making those adaptions, we define our new database again with the DBDICT program as follows:

cmd>CD new_pebsy

cmd>DBDICT pebsy.dict

DBDICT.pebsy.log      2004-02-26 11:42:04


DBDICT processing of Data Dictionary: PEBSY with DBDICT vs. 14.06
=================================================================

File Class: AGENTUR
===================

   Record: AGENTUR
    relevant copy is: \DB123\samples\new_pebsy\includes\AGENTUR.cpy
Date-Form Table: DATEFORM.TABLE used.

File: \DB123\samples\new_pebsy\includes\AGENTUR.cpy
===================================================

Parsing Decls of file: \DB123\samples\new_pebsy\includes\AGENTUR.cpy with cob_decl.nrx vs 14.00 26.02.2004 11:40:12
    corresponding IDECL-file is: AGENTUR.IDECL
         copy: AGENTUR COBOL-structure: AGENTUR-STAMM-SATZ
   first item: AST-AGENTUR-NR level: 5 default prefix: AST
    record-prefix is: AST
DLI-record-prefix is: AST
DB2-record-prefix is: AGN
         key-loc:1 key-size: 5
      file-class: AGENTUR max. thread-length: 5

File Class: BETREUER
====================

   Record: BETREUER
    relevant copy is: \DB123\samples\new_pebsy\includes\BETREUER.cpy
Date-Form Table: DATEFORM.TABLE used.

File: \DB123\samples\new_pebsy\includes\BETREUER.cpy
====================================================

Parsing Decls of file: \DB123\samples\new_pebsy\includes\BETREUER.cpy with cob_decl.nrx vs 14.00 26.02.2004 11:40:17
    corresponding IDECL-file is: BETREUER.IDECL
         copy: BETREUER COBOL-structure: BETREUER-STAMM-SATZ
   first item: BET-AGENTUR-NR level: 5 default prefix: BET
    record-prefix is: BET
DB2-record-prefix is: BET
  logical parent: AGENTUR
    fetch-clause: AST-AGENTUR-NR=BET-AGENTUR-NR
         key-loc:1 key-size: 6
      file-class: BETREUER max. thread-length: 6

File Class: BINFIX
==================

   Record: BINFIX
    relevant copy is: \DB123\samples\new_pebsy\includes\BINFIX.cpy
Date-Form Table: DATEFORM.TABLE used.

File: \DB123\samples\new_pebsy\includes\BINFIX.cpy
==================================================

Parsing Decls of file: \DB123\samples\new_pebsy\includes\BINFIX.cpy with cob_decl.nrx vs 14.00 26.02.2004 11:40:27
    corresponding IDECL-file is: BINFIX.IDECL
         copy: BINFIX COBOL-structure: BETREUER-INFO-SATZ-FIX
   first item: BIF-AGENTUR-NR level: 5 default prefix: BIF
    record-prefix is: BIF
DB2-record-prefix is: BIF
  logical parent: AGENTUR
    fetch-clause: AST-AGENTUR-NR=BIF-AGENTUR-NR
         key-loc:1 key-size: 13
ordered by: BIF-AGENTUR-NR BIF-SATZ-INDEX BIF-LFD-NR
      file-class: BINFIX max. thread-length: 13

File Class: BINVAR
==================

   Record: BINVAR
    relevant copy is: \DB123\samples\new_pebsy\includes\BINVAR.cpy
Date-Form Table: DATEFORM.TABLE used.

File: \DB123\samples\new_pebsy\includes\BINVAR.cpy
==================================================

Parsing Decls of file: \DB123\samples\new_pebsy\includes\BINVAR.cpy with cob_decl.nrx vs 14.00 26.02.2004 11:40:31
    corresponding IDECL-file is: BINVAR.IDECL
         copy: BINVAR COBOL-structure: BETREUER-INFO-SATZ-VARIABLE
   first item: BIV-AGENTUR-NR level: 5 default prefix: BIV
    record-prefix is: BIV
DB2-record-prefix is: BIV
  logical parent: AGENTUR
    fetch-clause: AST-AGENTUR-NR=BIV-AGENTUR-NR
         key-loc:1 key-size: 6
      file-class: BINVAR max. thread-length: 6

File Class: LIEFSOFO
====================

   Record: LIEFSOFO
    relevant copy is: \DB123\samples\new_pebsy\includes\LIEFSOFO.cpy
Date-Form Table: DATEFORM.TABLE used.

File: \DB123\samples\new_pebsy\includes\LIEFSOFO.cpy
====================================================

Parsing Decls of file: \DB123\samples\new_pebsy\includes\LIEFSOFO.cpy with cob_decl.nrx vs 14.00 26.02.2004 11:40:34
    corresponding IDECL-file is: LIEFSOFO.IDECL
         copy: LIEFSOFO COBOL-structure: LIEFERUNG-SOFORT-SATZ
   first item: ALS-AGENTUR-NR level: 5 default prefix: ALS
    record-prefix is: ALS
DLI-record-prefix is: LSO
DB2-record-prefix is: ALS
  logical parent: AGENTUR
    fetch-clause: AST-AGENTUR-NR=ALS-AGENTUR-NR
         key-loc:1 key-size: 15
      file-class: LIEFSOFO max. thread-length: 15

File Class: LIEFTERM
====================

   Record: LIEFTERM
    relevant copy is: \DB123\samples\new_pebsy\includes\LIEFTERM.cpy
Date-Form Table: DATEFORM.TABLE used.

File: \DB123\samples\new_pebsy\includes\LIEFTERM.cpy
====================================================

Parsing Decls of file: \DB123\samples\new_pebsy\includes\LIEFTERM.cpy with cob_decl.nrx vs 14.00 26.02.2004 11:40:37
    corresponding IDECL-file is: LIEFTERM.IDECL
         copy: LIEFTERM COBOL-structure: LIEFERUNG-TERMIN-SATZ
   first item: ALT-AGENTUR-NR level: 5 default prefix: ALT
    record-prefix is: ALT
DLI-record-prefix is: LIT
DB2-record-prefix is: ALT
  logical parent: AGENTUR
    fetch-clause: AST-AGENTUR-NR=ALT-AGENTUR-NR
         key-loc:1 key-size: 15
      file-class: LIEFTERM max. thread-length: 15

File Class: UMSPRO
==================

   Record: UMSPRO
    relevant copy is: \DB123\samples\new_pebsy\includes\UMSPRO.cpy
Date-Form Table: DATEFORM.TABLE used.

File: \DB123\samples\new_pebsy\includes\UMSPRO.cpy
==================================================

Parsing Decls of file: \DB123\samples\new_pebsy\includes\UMSPRO.cpy with cob_decl.nrx vs 14.00 26.02.2004 11:40:40
    corresponding IDECL-file is: UMSPRO.IDECL
         copy: UMSPRO COBOL-structure: UMSATZ-PROVISIONS-SATZ
   first item: BUP-AGENTUR-NR level: 5 default prefix: BUP
    record-prefix is: BUP
DLI-record-prefix is: UMS
DB2-record-prefix is: BUP
  logical parent: BETREUER
    fetch-clause: BET-AGENTUR-NR=BUP-AGENTUR-NR,  BET-BETREUER-NR=BUP-BETREUER-NR
         key-loc:1 key-size: 6
      file-class: UMSPRO max. thread-length: 6

File Class: GUTBEL
==================

   Record: GUTBEL
    relevant copy is: \DB123\samples\new_pebsy\includes\GUTBEL.cpy
Date-Form Table: DATEFORM.TABLE used.

File: \DB123\samples\new_pebsy\includes\GUTBEL.cpy
==================================================

Parsing Decls of file: \DB123\samples\new_pebsy\includes\GUTBEL.cpy with cob_decl.nrx vs 14.00 26.02.2004 11:40:43
    corresponding IDECL-file is: GUTBEL.IDECL
         copy: GUTBEL COBOL-structure: GUTSCHRIFT-BELASTUNG
   first item: GUB-AGENTUR-NR level: 5 default prefix: GUB
    record-prefix is: GUB
DB2-record-prefix is: GUB
DLI-record-prefix is: GUB
  logical parent: BETREUER
    fetch-clause: BET-AGENTUR-NR=GUB-AGENTUR-NR,  BET-BETREUER-NR=GUB-BETREUER-NR
         key-loc:1 key-size: 10
      file-class: GUTBEL max. thread-length: 10

File Class: GUTBEL2
===================

   Record: GUTBEL2
    relevant copy is: \DB123\samples\new_pebsy\includes\GUTBEL2.cpy
Date-Form Table: DATEFORM.TABLE used.

File: \DB123\samples\new_pebsy\includes\GUTBEL2.cpy
===================================================

Parsing Decls of file: \DB123\samples\new_pebsy\includes\GUTBEL2.cpy with cob_decl.nrx vs 14.00 26.02.2004 11:40:46
    corresponding IDECL-file is: GUTBEL2.IDECL
         copy: GUTBEL2 COBOL-structure: GUTSCHRIFT-BELASTUNG-2
   first item: GU2-AGENTUR-NR level: 5 default prefix: GU2
    record-prefix is: GU2
DB2-record-prefix is: GU2
  logical parent: BETREUER
    fetch-clause: BET-AGENTUR-NR=GU2-AGENTUR-NR,  BET-BETREUER-NR=GU2-BETREUER-NR
         key-loc:1 key-size: 10
      file-class: GUTBEL2 max. thread-length: 10

File Class: BANKBET
===================

   Record: BANKBET
    relevant copy is: \DB123\samples\new_pebsy\includes\BANKBET.cpy
Date-Form Table: DATEFORM.TABLE used.

File: \DB123\samples\new_pebsy\includes\BANKBET.cpy
===================================================

Parsing Decls of file: \DB123\samples\new_pebsy\includes\BANKBET.cpy with cob_decl.nrx vs 14.00 26.02.2004 11:40:49
    corresponding IDECL-file is: BANKBET.IDECL
         copy: BANKBET COBOL-structure: BANK-BRUTTO-BETREUER
   first item: BBB-AGENTUR-NR level: 5 default prefix: BBB
    record-prefix is: BBB
DLI-record-prefix is: BAN
DB2-record-prefix is: BBB
  logical parent: BETREUER
    fetch-clause: BET-AGENTUR-NR=BBB-AGENTUR-NR,  BET-BETREUER-NR=BBB-BETREUER-NR
         key-loc:1 key-size: 6
      file-class: BANKBET max. thread-length: 6

File Class: AUSZABET
====================

   Record: AUSZABET
    relevant copy is: \DB123\samples\new_pebsy\includes\AUSZABET.cpy
Date-Form Table: DATEFORM.TABLE used.

File: \DB123\samples\new_pebsy\includes\AUSZABET.cpy
====================================================

Parsing Decls of file: \DB123\samples\new_pebsy\includes\AUSZABET.cpy with cob_decl.nrx vs 14.00 26.02.2004 11:40:51
    corresponding IDECL-file is: AUSZABET.IDECL
         copy: AUSZABET COBOL-structure: AUSZAHLUNG-BRUTTO-BETREUER
   first item: ABB-AGENTUR-NR level: 5 default prefix: ABB
    record-prefix is: ABB
DLI-record-prefix is: AUS
DB2-record-prefix is: ABB
  logical parent: BANKBET
    fetch-clause: BBB-AGENTUR-NR=ABB-AGENTUR-NR,  BBB-BETREUER-NR=ABB-BETREUER-NR
         key-loc:1 key-size: 10
      file-class: AUSZABET max. thread-length: 10

File Class: REMISSI
===================

   Record: REMISSI
    relevant copy is: \DB123\samples\new_pebsy\includes\REMISS.cpy
Date-Form Table: DATEFORM.TABLE used.

File: \DB123\samples\new_pebsy\includes\REMISS.cpy
==================================================

Parsing Decls of file: \DB123\samples\new_pebsy\includes\REMISS.cpy with cob_decl.nrx vs 14.00 26.02.2004 11:40:54
    corresponding IDECL-file is: REMISS.IDECL
         copy: REMISS COBOL-structure: REMISSIONS-SATZ
   first item: REM-AGENTUR-NR level: 5 default prefix: REM
    record-prefix is: REM
DLI-record-prefix is: REM
DB2-record-prefix is: REM
  logical parent: BETREUER
    fetch-clause: BET-AGENTUR-NR=REM-AGENTUR-NR,  BET-BETREUER-NR=REM-BETREUER-NR
         key-loc:1 key-size: 11
      file-class: REMISSI max. thread-length: 11

File Class: REMRET
==================

   Record: REMRET
    relevant copy is: \DB123\samples\new_pebsy\includes\REMRET.cpy
Date-Form Table: DATEFORM.TABLE used.

File: \DB123\samples\new_pebsy\includes\REMRET.cpy
==================================================

Parsing Decls of file: \DB123\samples\new_pebsy\includes\REMRET.cpy with cob_decl.nrx vs 14.00 26.02.2004 11:40:57
    corresponding IDECL-file is: REMRET.IDECL
         copy: REMRET COBOL-structure: REMISS-RETOUREN-SATZ
   first item: RMR-AGENTUR-NR level: 5 default prefix: RMR
    record-prefix is: RMR
DB2-record-prefix is: RMR
  logical parent: REMISSI
    fetch-clause: REM-AGENTUR-NR=RMR-AGENTUR-NR,  REM-BETREUER-NR=RMR-BETREUER-NR, REM-RETOUR-NR = RMR-RETOUR-NR
         key-loc:1 key-size: 13
      file-class: REMRET max. thread-length: 13

       no warnings
       no errors encountered

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

       13 File Classes defined.
Data Dictionary for: 'PEBSY' saved in: PEBSY.DD
 

4.06 Defining the SQL tables (CREATE TABLE)

As the next step, we do CREATE our DB2-tables with the CREATE action of DB123.

cmd>DB123 CREATE PEBSY

db123.create.pebsy.log      2004-02-26 14:53:40

DB123 vs 14.06: CREATE PEBSY
============================

... Loading Dictionary: PEBSY options: ADD

List of defined records of Data Dictionary: PEBSY
=================================================

    File          Record   Logical         Prefix
 ## Class    Type Name     Parent   CBL  DL/I DB2  DB2-Table / Physical Filename
--- -------- ---- -------- -------- ---- ---- ---- -----------------------------
  1 AGENTUR  SQL  AGENTUR           AST  AST  AGN  AGENTUR
  2 BETREUER SQL  BETREUER AGENTUR  BET  BET  BET  BETREUER
  3 BINFIX   SQL  BINFIX   AGENTUR  BIF  BIF  BIF  BINFIX
  4 BINVAR   SQL  BINVAR   AGENTUR  BIV  BIV  BIV  BINVAR
  5 LIEFSOFO SQL  LIEFSOFO AGENTUR  ALS  LSO  ALS  LIEFSOFO
  6 LIEFTERM SQL  LIEFTERM AGENTUR  ALT  LIT  ALT  LIEFTERM
  7 UMSPRO   SQL  UMSPRO   BETREUER BUP  UMS  BUP  UMSPRO
  8 GUTBEL   SQL  GUTBEL   BETREUER GUB  GUB  GUB  GUTBEL1
  9 GUTBEL2  SQL  GUTBEL2  BETREUER GU2  GU2  GU2  GUTBEL2
 10 BANKBET  SQL  BANKBET  BETREUER BBB  BAN  BBB  BANKBET
 11 AUSZABET SQL  AUSZABET BANKBET  ABB  AUS  ABB  AUSZABET
 12 REMISSI  SQL  REMISSI  BETREUER REM  REM  REM  REMISS
 13 REMRET   SQL  REMRET   REMISSI  RMR  RMR  RMR  REMISS_RETOUR
 

Processing Data-Dictionary: PEBSY
=================================


Generate CREATE SQL-Tables for file class: AGENTUR
==================================================

   ... after load: D:\db123\samples\new_pebsy\AGENTUR.SQLMAP: columns are: 1 thru 163 (163 columns)
      163 SQL-columns loaded from: D:\db123\samples\new_pebsy\AGENTUR.SQLMAP

generating SQLMAP-files
=======================

SQLMAP-file: D:\db123\samples\new_pebsy\AGENTUR.SQLMAP does already exist, record: AGENTUR skipped
Line 164: Warning: SQL-Table: \DB123\samples\new_pebsy\SQLTables\AGENTUR.SQLTABLE already exists
May it be overwritten (Yes/No/All/Stop): All

Generating SQL-Table definition for table: AGENTUR
==================================================

UNIQUE KEY of record AGENTUR is: AST-AGENTUR-NR
      184 SQL-lines generated into: \DB123\samples\new_pebsy\SQLTables\AGENTUR.SQLTABLE

Generate CREATE SQL-Tables for file class: BETREUER
===================================================

   ... after load: D:\db123\samples\new_pebsy\BETREUER.SQLMAP: columns are: 164 thru 234 (71 columns)
       71 SQL-columns loaded from: D:\db123\samples\new_pebsy\BETREUER.SQLMAP

generating SQLMAP-files
=======================

SQLMAP-file: D:\db123\samples\new_pebsy\BETREUER.SQLMAP does already exist, record: BETREUER skipped

Generating SQL-Table definition for table: BETREUER
===================================================

UNIQUE KEY of record BETREUER is: AST-AGENTUR-NR
       92 SQL-lines generated into: \DB123\samples\new_pebsy\SQLTables\BETREUER.SQLTABLE

Generate CREATE SQL-Tables for file class: BINFIX
=================================================

   ... after load: D:\db123\samples\new_pebsy\BINFIX.SQLMAP: columns are: 235 thru 272 (38 columns)
       38 SQL-columns loaded from: D:\db123\samples\new_pebsy\BINFIX.SQLMAP

generating SQLMAP-files
=======================

SQLMAP-file: D:\db123\samples\new_pebsy\BINFIX.SQLMAP does already exist, record: BINFIX skipped

Generating SQL-Table definition for table: BINFIX
=================================================

UNIQUE KEY of record BINFIX is: AST-AGENTUR-NR
       59 SQL-lines generated into: \DB123\samples\new_pebsy\SQLTables\BINFIX.SQLTABLE

Generate CREATE SQL-Tables for file class: BINVAR
=================================================

   ... after load: D:\db123\samples\new_pebsy\BINVAR.SQLMAP: columns are: 273 thru 293 (21 columns)
       21 SQL-columns loaded from: D:\db123\samples\new_pebsy\BINVAR.SQLMAP

generating SQLMAP-files
=======================

SQLMAP-file: D:\db123\samples\new_pebsy\BINVAR.SQLMAP does already exist, record: BINVAR skipped

Generating SQL-Table definition for table: BINVAR
=================================================

UNIQUE KEY of record BINVAR is: AST-AGENTUR-NR
       59 SQL-lines generated into: \DB123\samples\new_pebsy\SQLTables\BINVAR.SQLTABLE

Generate CREATE SQL-Tables for file class: LIEFSOFO
===================================================

   ... after load: D:\db123\samples\new_pebsy\LIEFSOFO.SQLMAP: columns are: 294 thru 308 (15 columns)
       15 SQL-columns loaded from: D:\db123\samples\new_pebsy\LIEFSOFO.SQLMAP

generating SQLMAP-files
=======================

SQLMAP-file: D:\db123\samples\new_pebsy\LIEFSOFO.SQLMAP does already exist, record: LIEFSOFO skipped

Generating SQL-Table definition for table: LIEFSOFO
===================================================

UNIQUE KEY of record LIEFSOFO is: AST-AGENTUR-NR
       36 SQL-lines generated into: \DB123\samples\new_pebsy\SQLTables\LIEFSOFO.SQLTABLE

Generate CREATE SQL-Tables for file class: LIEFTERM
===================================================

   ... after load: D:\db123\samples\new_pebsy\LIEFTERM.SQLMAP: columns are: 309 thru 319 (11 columns)
       11 SQL-columns loaded from: D:\db123\samples\new_pebsy\LIEFTERM.SQLMAP

generating SQLMAP-files
=======================

SQLMAP-file: D:\db123\samples\new_pebsy\LIEFTERM.SQLMAP does already exist, record: LIEFTERM skipped

Generating SQL-Table definition for table: LIEFTERM
===================================================

UNIQUE KEY of record LIEFTERM is: AST-AGENTUR-NR
       32 SQL-lines generated into: \DB123\samples\new_pebsy\SQLTables\LIEFTERM.SQLTABLE

Generate CREATE SQL-Tables for file class: UMSPRO
=================================================

   ... after load: D:\db123\samples\new_pebsy\UMSPRO.SQLMAP: columns are: 320 thru 363 (44 columns)
       44 SQL-columns loaded from: D:\db123\samples\new_pebsy\UMSPRO.SQLMAP

generating SQLMAP-files
=======================

SQLMAP-file: D:\db123\samples\new_pebsy\UMSPRO.SQLMAP does already exist, record: UMSPRO skipped

Generating SQL-Table definition for table: UMSPRO
=================================================

UNIQUE KEY of record UMSPRO is: AST-AGENTUR-NR
      100 SQL-lines generated into: \DB123\samples\new_pebsy\SQLTables\UMSPRO.SQLTABLE

Generate CREATE SQL-Tables for file class: GUTBEL
=================================================

   ... after load: D:\db123\samples\new_pebsy\GUTBEL1.SQLMAP: columns are: 364 thru 377 (14 columns)
       14 SQL-columns loaded from: D:\db123\samples\new_pebsy\GUTBEL1.SQLMAP

generating SQLMAP-files
=======================

SQLMAP-file: D:\db123\samples\new_pebsy\GUTBEL1.SQLMAP does already exist, record: GUTBEL skipped

Generating SQL-Table definition for table: GUTBEL1
==================================================

UNIQUE KEY of record GUTBEL is: AST-AGENTUR-NR
       35 SQL-lines generated into: \DB123\samples\new_pebsy\SQLTables\GUTBEL1.SQLTABLE

Generate CREATE SQL-Tables for file class: GUTBEL2
==================================================

   ... after load: D:\db123\samples\new_pebsy\GUTBEL2.SQLMAP: columns are: 378 thru 391 (14 columns)
       14 SQL-columns loaded from: D:\db123\samples\new_pebsy\GUTBEL2.SQLMAP

generating SQLMAP-files
=======================

SQLMAP-file: D:\db123\samples\new_pebsy\GUTBEL2.SQLMAP does already exist, record: GUTBEL2 skipped

Generating SQL-Table definition for table: GUTBEL2
==================================================

UNIQUE KEY of record GUTBEL2 is: AST-AGENTUR-NR
       35 SQL-lines generated into: \DB123\samples\new_pebsy\SQLTables\GUTBEL2.SQLTABLE

Generate CREATE SQL-Tables for file class: BANKBET
==================================================

   ... after load: D:\db123\samples\new_pebsy\BANKBET.SQLMAP: columns are: 392 thru 408 (17 columns)
       17 SQL-columns loaded from: D:\db123\samples\new_pebsy\BANKBET.SQLMAP

generating SQLMAP-files
=======================

SQLMAP-file: D:\db123\samples\new_pebsy\BANKBET.SQLMAP does already exist, record: BANKBET skipped

Generating SQL-Table definition for table: BANKBET
==================================================

UNIQUE KEY of record BANKBET is: AST-AGENTUR-NR
       38 SQL-lines generated into: \DB123\samples\new_pebsy\SQLTables\BANKBET.SQLTABLE

Generate CREATE SQL-Tables for file class: AUSZABET
===================================================

   ... after load: D:\db123\samples\new_pebsy\AUSZABET.SQLMAP: columns are: 409 thru 421 (13 columns)
       13 SQL-columns loaded from: D:\db123\samples\new_pebsy\AUSZABET.SQLMAP

generating SQLMAP-files
=======================

SQLMAP-file: D:\db123\samples\new_pebsy\AUSZABET.SQLMAP does already exist, record: AUSZABET skipped

Generating SQL-Table definition for table: AUSZABET
===================================================

UNIQUE KEY of record AUSZABET is: AST-AGENTUR-NR
       34 SQL-lines generated into: \DB123\samples\new_pebsy\SQLTables\AUSZABET.SQLTABLE

Generate CREATE SQL-Tables for file class: REMISSI
==================================================

   ... after load: D:\db123\samples\new_pebsy\REMISS.SQLMAP: columns are: 422 thru 430 (9 columns)
        9 SQL-columns loaded from: D:\db123\samples\new_pebsy\REMISS.SQLMAP

generating SQLMAP-files
=======================

record: REMISSI DB2DETAIL: REM-RETOURWAREN AS REMRET
SQLMAP-file: D:\db123\samples\new_pebsy\REMISS.SQLMAP does already exist, record: REMISSI skipped
record: REMISSI DB2DETAIL: REM-RETOURWAREN AS REMRET

Generating SQL-Table definition for table: REMISS
=================================================

UNIQUE KEY of record REMISSI is: AST-AGENTUR-NR
       30 SQL-lines generated into: \DB123\samples\new_pebsy\SQLTables\REMISS.SQLTABLE

Generate CREATE SQL-Tables for file class: REMRET
=================================================

   ... after load: D:\db123\samples\new_pebsy\REMISS_RETOUR.SQLMAP: columns are: 431 thru 451 (21 columns)
       21 SQL-columns loaded from: D:\db123\samples\new_pebsy\REMISS_RETOUR.SQLMAP

generating SQLMAP-files
=======================

SQLMAP-file: D:\db123\samples\new_pebsy\REMISS_RETOUR.SQLMAP does already exist, record: REMRET skipped

Generating SQL-Table definition for table: REMISS_RETOUR
========================================================

UNIQUE KEY of record REMRET is: AST-AGENTUR-NR
       42 SQL-lines generated into: \DB123\samples\new_pebsy\SQLTables\REMISS_RETOUR.SQLTABLE

DB123 summary (vs. 14.06):
==========================

       13 DB2(SQL) Table declarations generated to: \DB123\samples\new_pebsy\SQLTables
 
       no errors detected.
        1 warnings.
 
DB123 used:   13.91 seconds for the actions above.

We are showing here only one created SQL-TABLE, for instance the BETREUER TABLE (customer contacts).

  BETREUER.SQLTABLE      2004-02-26 14:55:57

------------------------------------------------------------------------
-- SQL-TABLE: BETREUER
--   Last modified: 2004-02-26 14:52:47
--   Data Dictionary: PEBSY
--   Record: BETREUER
--   Record Prefix: BET
--   DB2 Prefix: BET
--   Schema: COMPUSER
--   Owner : DL
--   Generated by DB123 vs. 14.06 at: 2004-02-26 14:52:47
--   corresponding COPY-book: BETREUER (COBOL)
------------------------------------------------------------------------
DROP TABLE COMPUSER.BETREUER;
CREATE TABLE COMPUSER.BETREUER (
-- columns
   BET_AGENTUR_NR INTEGER KEY NOT NULL ,
   BET_BETREUER_NR SMALLINT KEY NOT NULL ,
   BET_BETREUER_STATUS SMALLINT NOT NULL ,
   BET_ANREDE SMALLINT NOT NULL ,
   BET_ADR_TITEL CHAR(10) NOT NULL ,
   BET_ADR_VORNAME CHAR(30) NOT NULL ,
   BET_ADR_ZUNAME CHAR(30) NOT NULL ,
   BET_ADR_NAMENSZUSATZ CHAR(40) NOT NULL ,
   BET_ADR_STRASSE CHAR(35) NOT NULL ,
   BET_ADR_ORT CHAR(35) NOT NULL ,
   BET_ADR_ORTSZUSATZ CHAR(35) NOT NULL ,
   BET_PLZ SMALLINT NOT NULL ,
   BET_TELEFON CHAR(14) NOT NULL ,
   BET_WERKVERTRAG_KZ CHAR(1) NOT NULL ,
   BET_ZWEIT_VERTRAG CHAR(1) NOT NULL ,
   BET_TAET_BEGINN_JJMMTT DATE NOT NULL ,
   BET_TAET_ENDE_JJMMTT DATE NOT NULL ,
   BET_AUSSCHEIDUNGS_GRUND SMALLINT NOT NULL ,
   BET_WERBUNGSART SMALLINT NOT NULL ,
   BET_PROVISIONS_KZ SMALLINT NOT NULL ,
   BET_FAMILIEN_STAND SMALLINT NOT NULL ,
   BET_REKLAMATION_QU1 SMALLINT NOT NULL ,
   BET_REKLAMATION_QU2 SMALLINT NOT NULL ,
   BET_REKLAMATION_QU3 SMALLINT NOT NULL ,
   BET_REKLAMATION_QU4 SMALLINT NOT NULL ,
   BET_ZAHL_MAHNUNG_QU1 SMALLINT NOT NULL ,
   BET_ZAHL_MAHNUNG_QU2 SMALLINT NOT NULL ,
   BET_ZAHL_MAHNUNG_QU3 SMALLINT NOT NULL ,
   BET_ZAHL_MAHNUNG_QU4 SMALLINT NOT NULL ,
   BET_ABR_MAHNUNG_QU1 SMALLINT NOT NULL ,
   BET_ABR_MAHNUNG_QU2 SMALLINT NOT NULL ,
   BET_ABR_MAHNUNG_QU3 SMALLINT NOT NULL ,
   BET_ABR_MAHNUNG_QU4 SMALLINT NOT NULL ,
   BET_MAHN_ZAEHLER SMALLINT NOT NULL ,
   BET_FIX_PROVISION DECIMAL(9,2) NOT NULL ,
   BET_PROVISION_LFD DECIMAL(9,2) NOT NULL ,
   BET_BRUTTO_PR_MON DECIMAL(9,2) NOT NULL ,
   BET_BRUTTO_PR_LFD DECIMAL(9,2) NOT NULL ,
   BET_ALTER_SALDO DECIMAL(9,2) NOT NULL ,
   BET_NEUER_SALDO DECIMAL(9,2) NOT NULL ,
   BET_NEUER_SALDO_ALT DECIMAL(9,2) NOT NULL ,
   BET_BEREITS_FAELLIG DECIMAL(9,2) NOT NULL ,
   BET_ALTER_LFD_FAELLIG DECIMAL(9,2) NOT NULL ,
   BET_UMSATZ_KOST_1 DECIMAL(9,2) NOT NULL ,
   BET_UMSATZ_KOST_2 DECIMAL(9,2) NOT NULL ,
   BET_UMSATZ_KOST_3 DECIMAL(9,2) NOT NULL ,
   BET_UMSATZ_KOST_4 DECIMAL(9,2) NOT NULL ,
   BET_UMSATZ_KOST_5 DECIMAL(9,2) NOT NULL ,
   BET_UMSATZ_KOST_6 DECIMAL(9,2) NOT NULL ,
   BET_UMSATZ_KOST_7 DECIMAL(9,2) NOT NULL ,
   BET_AKTUELLE_GUTBEL SMALLINT NOT NULL ,
   BET_KONTO_NR SMALLINT NOT NULL ,
   BET_KINDER SMALLINT NOT NULL ,
   BET_INFORMATION_1 CHAR(73) NOT NULL ,
   BET_INFORMATION_2 CHAR(73) NOT NULL ,
   BET_INFORMATION_3 CHAR(73) NOT NULL ,
   BET_FELD_1 DECIMAL(9,2) NOT NULL ,
   BET_FELD_2 DECIMAL(7,2) NOT NULL ,
   BET_FELD_3 INTEGER NOT NULL ,
   BET_TEAM_SONDER_PROV CHAR(1) NOT NULL ,
   BET_FILLER_2 CHAR(1) NOT NULL ,
   BET_GEBURT_JJMMTT DATE NOT NULL ,
   BET_LIEFERADRESSE CHAR(1) NOT NULL ,
   BET_MAHNUNG_ALT CHAR(1) NOT NULL ,
   BET_MAHNUNG CHAR(1) NOT NULL ,
   BET_TEAM_LEITER CHAR(1) NOT NULL ,
   BET_TEAM_BEGINN_JJQ INTEGER NOT NULL ,
   BET_PROVISION_DIFFERENZ DECIMAL(7,2) NOT NULL ,
   BET_SV_NR INTEGER NOT NULL ,
   BET_UPDATE_DATE DATE  ,
   BET_UPDATE_ID CHAR(8) NOT NULL ,
-- end columns of table: BETREUER
PRIMARY KEY (
   AST_AGENTUR_NR ) )
IN DB_PEBSY;
-- end of table: BETREUER
-- ************************************************** --
 

You will find a listing of all Tables in Appendix C.

4.08 The SQL Maps

As you can see from the log-file above, there is also a SQLMAP-file created for each DB2-Table.
This file does contain the actual mappings of the
COBOL variable names with their PICTURE, USAGE and IMAGE (for Date items) against the separate columns of the DB2 Table definition.

For our BETREUER example record, the mapping does now look as follows:

BETREUER.SQLMAP      2004-02-23 17:59:14

"SQL-Schema";"SQL-Table";"SQL-Column-No";"SQL-Column";"Type";"Attributes";"COPY-Name";"Line-No";"COBOL-Name";"Picture";"Usage";"Class";"Image";
;"BETREUER";1;"BET_BETREUER_STATUS";"SMALLINT";"NOT
NULL";"BETREUER";17;"BET-BETREUER-STATUS";"9";"X";;;
;"BETREUER";2;"BET_ANREDE";"SMALLINT";"NOT NULL";"BETREUER";22;"BET-ANREDE";"9";"X";;;
;"BETREUER";3;"BET_ADR_TITEL";"CHAR(10)";"NOT NULL";"BETREUER";28;"BET-ADR-TITEL";"X(10)";"X";;;
;"BETREUER";4;"BET_ADR_VORNAME";"CHAR(30)";"NOT NULL";"BETREUER";29;"BET-ADR-VORNAME";"X(30)";"X";;;
;"BETREUER";5;"BET_ADR_ZUNAME";"CHAR(30)";"NOT NULL";"BETREUER";30;"BET-ADR-ZUNAME";"X(30)";"X";;;
;"BETREUER";6;"BET_ADR_NAMENSZUSATZ";"CHAR(40)";"NOT NULL";"BETREUER";31;"BET-ADR-NAMENSZUSATZ";"X(40)";"X";;;
;"BETREUER";7;"BET_ADR_STRASSE";"CHAR(35)";"NOT NULL";"BETREUER";32;"BET-ADR-STRASSE";"X(35)";"X";;;
;"BETREUER";8;"BET_ADR_ORT";"CHAR(35)";"NOT NULL";"BETREUER";33;"BET-ADR-ORT";"X(35)";"X";;;
;"BETREUER";9;"BET_ADR_ORTSZUSATZ";"CHAR(35)";"NOT NULL";"BETREUER";34;"BET-ADR-ORTSZUSATZ";"X(35)";"X";;;
;"BETREUER";10;"BET_PLZ";"SMALLINT";"NOT NULL";"BETREUER";35;"BET-PLZ";"9(4)";"P";;;
;"BETREUER";11;"BET_TELEFON";"CHAR(14)";"NOT NULL";"BETREUER";37;"BET-TELEFON";"X(14)";"X";;;
;"BETREUER";12;"BET_WERKVERTRAG_KZ";"CHAR(1)";"NOT NULL";"BETREUER";40;"BET-WERKVERTRAG-KZ";"X";"X";;;
;"BETREUER";13;"BET_ZWEIT_VERTRAG";"CHAR(1)";"NOT NULL";"BETREUER";46;"BET-ZWEIT-VERTRAG";"X";"X";;;
;"BETREUER";14;"BET_TAET_BEGINN_JJMMTT";"DATE";"NOT NULL";"BETREUER";50;"BET-TAET-BEGINN-JJMMTT";"9(6)";"P";"Date";"YYMMDD";
;"BETREUER";15;"BET_TAET_ENDE_JJMMTT";"DATE";"NOT NULL";"BETREUER";55;"BET-TAET-ENDE-JJMMTT";"9(6)";"P";"Date";"YYMMDD";
;"BETREUER";16;"BET_AUSSCHEIDUNGS_GRUND";"SMALLINT";"NOT NULL";"BETREUER";57;"BET-AUSSCHEIDUNGS-GRUND";"9";"X";;;
;"BETREUER";17;"BET_WERBUNGSART";"SMALLINT";"NOT NULL";"BETREUER";69;"BET-WERBUNGSART";"9";"X";;;
;"BETREUER";18;"BET_PROVISIONS_KZ";"SMALLINT";"NOT NULL";"BETREUER";77;"BET-PROVISIONS-KZ";"9";"X";;;
;"BETREUER";19;"BET_FAMILIEN_STAND";"SMALLINT";"NOT NULL";"BETREUER";84;"BET-FAMILIEN-STAND";"9";"X";;;
;"BETREUER";20;"BET_REKLAMATION_QU1";"SMALLINT";"NOT NULL";"BETREUER";93;"BET-REKLAMATION-QU1";"9";"X";;;
;"BETREUER";21;"BET_REKLAMATION_QU2";"SMALLINT";"NOT NULL";"BETREUER";94;"BET-REKLAMATION-QU2";"9";"X";;;
;"BETREUER";22;"BET_REKLAMATION_QU3";"SMALLINT";"NOT NULL";"BETREUER";95;"BET-REKLAMATION-QU3";"9";"X";;;
;"BETREUER";23;"BET_REKLAMATION_QU4";"SMALLINT";"NOT NULL";"BETREUER";96;"BET-REKLAMATION-QU4";"9";"X";;;
;"BETREUER";24;"BET_ZAHL_MAHNUNG_QU1";"SMALLINT";"NOT NULL";"BETREUER";101;"BET-ZAHL-MAHNUNG-QU1";"9";"X";;;
;"BETREUER";25;"BET_ZAHL_MAHNUNG_QU2";"SMALLINT";"NOT NULL";"BETREUER";102;"BET-ZAHL-MAHNUNG-QU2";"9";"X";;;
;"BETREUER";26;"BET_ZAHL_MAHNUNG_QU3";"SMALLINT";"NOT NULL";"BETREUER";103;"BET-ZAHL-MAHNUNG-QU3";"9";"X";;;
;"BETREUER";27;"BET_ZAHL_MAHNUNG_QU4";"SMALLINT";"NOT NULL";"BETREUER";104;"BET-ZAHL-MAHNUNG-QU4";"9";"X";;;
;"BETREUER";28;"BET_ABR_MAHNUNG_QU1";"SMALLINT";"NOT NULL";"BETREUER";109;"BET-ABR-MAHNUNG-QU1";"9";"X";;;
;"BETREUER";29;"BET_ABR_MAHNUNG_QU2";"SMALLINT";"NOT NULL";"BETREUER";110;"BET-ABR-MAHNUNG-QU2";"9";"X";;;
;"BETREUER";30;"BET_ABR_MAHNUNG_QU3";"SMALLINT";"NOT NULL";"BETREUER";111;"BET-ABR-MAHNUNG-QU3";"9";"X";;;
;"BETREUER";31;"BET_ABR_MAHNUNG_QU4";"SMALLINT";"NOT NULL";"BETREUER";112;"BET-ABR-MAHNUNG-QU4";"9";"X";;;
;"BETREUER";32;"BET_MAHN_ZAEHLER";"SMALLINT";"NOT NULL";"BETREUER";116;"BET-MAHN-ZAEHLER";"9(2)";"X";;;
;"BETREUER";33;"BET_FIX_PROVISION";"DECIMAL(9,2)";"NOT NULL";"BETREUER";120;"BET-FIX-PROVISION";"S9(7)V99";"P";;;
;"BETREUER";34;"BET_PROVISION_LFD";"DECIMAL(9,2)";"NOT NULL";"BETREUER";122;"BET-PROVISION-LFD";"S9(7)V99";"P";;;
;"BETREUER";35;"BET_BRUTTO_PR_MON";"DECIMAL(9,2)";"NOT NULL";"BETREUER";123;"BET-BRUTTO-PR-MON";"S9(7)V99";"P";;;
;"BETREUER";36;"BET_BRUTTO_PR_LFD";"DECIMAL(9,2)";"NOT NULL";"BETREUER";125;"BET-BRUTTO-PR-LFD";"S9(7)V99";"P";;;
;"BETREUER";37;"BET_ALTER_SALDO";"DECIMAL(9,2)";"NOT NULL";"BETREUER";128;"BET-ALTER-SALDO";"S9(7)V99";"P";;;
;"BETREUER";38;"BET_NEUER_SALDO";"DECIMAL(9,2)";"NOT NULL";"BETREUER";129;"BET-NEUER-SALDO";"S9(7)V99";"P";;;
;"BETREUER";39;"BET_NEUER_SALDO_ALT";"DECIMAL(9,2)";"NOT NULL";"BETREUER";130;"BET-NEUER-SALDO-ALT";"S9(7)V99";"P";;;
;"BETREUER";40;"BET_BEREITS_FAELLIG";"DECIMAL(9,2)";"NOT NULL";"BETREUER";132;"BET-BEREITS-FAELLIG";"S9(7)V99";"P";;;
;"BETREUER";41;"BET_ALTER_LFD_FAELLIG";"DECIMAL(9,2)";"NOT NULL";"BETREUER";133;"BET-ALTER-LFD-FAELLIG";"S9(7)V99";"P";;;
;"BETREUER";42;"BET_UMSATZ_KOST_1";"DECIMAL(9,2)";"NOT NULL";"BETREUER";136;"BET-UMSATZ-KOST-1";"S9(7)V99";"P";;;
;"BETREUER";43;"BET_UMSATZ_KOST_2";"DECIMAL(9,2)";"NOT NULL";"BETREUER";137;"BET-UMSATZ-KOST-2";"S9(7)V99";"P";;;
;"BETREUER";44;"BET_UMSATZ_KOST_3";"DECIMAL(9,2)";"NOT NULL";"BETREUER";138;"BET-UMSATZ-KOST-3";"S9(7)V99";"P";;;
;"BETREUER";45;"BET_UMSATZ_KOST_4";"DECIMAL(9,2)";"NOT NULL";"BETREUER";139;"BET-UMSATZ-KOST-4";"S9(7)V99";"P";;;
;"BETREUER";46;"BET_UMSATZ_KOST_5";"DECIMAL(9,2)";"NOT NULL";"BETREUER";140;"BET-UMSATZ-KOST-5";"S9(7)V99";"P";;;
;"BETREUER";47;"BET_UMSATZ_KOST_6";"DECIMAL(9,2)";"NOT NULL";"BETREUER";141;"BET-UMSATZ-KOST-6";"S9(7)V99";"P";;;
;"BETREUER";48;"BET_UMSATZ_KOST_7";"DECIMAL(9,2)";"NOT NULL";"BETREUER";142;"BET-UMSATZ-KOST-7";"S9(7)V99";"P";;;
;"BETREUER";49;"BET_AKTUELLE_GUTBEL";"SMALLINT";"NOT NULL";"BETREUER";146;"BET-AKTUELLE-GUTBEL";"9";"X";;;
;"BETREUER";50;"BET_KONTO_NR";"SMALLINT";"NOT NULL";"BETREUER";150;"BET-KONTO-NR";"9(3)";"P";;;
;"BETREUER";51;"BET_KINDER";"SMALLINT";"NOT NULL";"BETREUER";151;"BET-KINDER";"9";"X";;;
;"BETREUER";52;"BET_INFORMATION_1";"CHAR(73)";"NOT NULL";"BETREUER";155;"BET-INFORMATION-1";"X(73)";"X";;;
;"BETREUER";53;"BET_INFORMATION_2";"CHAR(73)";"NOT NULL";"BETREUER";156;"BET-INFORMATION-2";"X(73)";"X";;;
;"BETREUER";54;"BET_INFORMATION_3";"CHAR(73)";"NOT NULL";"BETREUER";157;"BET-INFORMATION-3";"X(73)";"X";;;
;"BETREUER";55;"BET_FELD_1";"DECIMAL(9,2)";"NOT NULL";"BETREUER";159;"BET-FELD-1";"S9(7)V99";"P";;;
;"BETREUER";56;"BET_FELD_2";"DECIMAL(7,2)";"NOT NULL";"BETREUER";160;"BET-FELD-2";"S9(5)V99";"P";;;
;"BETREUER";57;"BET_FELD_3";"INTEGER";"NOT NULL";"BETREUER";161;"BET-FELD-3";"S9(5)";"P";;;
;"BETREUER";58;"BET_TEAM_SONDER_PROV";"CHAR(1)";"NOT NULL";"BETREUER";163;"BET-TEAM-SONDER-PROV";"X";"X";;;
;"BETREUER";59;"BET_FILLER_2";"CHAR(1)";"NOT NULL";"BETREUER";166;"BET-FILLER-2";"X";"X";;;
;"BETREUER";60;"BET_GEBURT_JJMMTT";"DATE";"NOT NULL";"BETREUER";167;"BET-GEBURT-JJMMTT";"9(6)";"P";"Date";"YYMMDD";
;"BETREUER";61;"BET_LIEFERADRESSE";"CHAR(1)";"NOT NULL";"BETREUER";169;"BET-LIEFERADRESSE";"X";"X";;;
;"BETREUER";62;"BET_MAHNUNG_ALT";"CHAR(1)";"NOT NULL";"BETREUER";173;"BET-MAHNUNG-ALT";"X";"X";;;
;"BETREUER";63;"BET_MAHNUNG";"CHAR(1)";"NOT NULL";"BETREUER";174;"BET-MAHNUNG";"X";"X";;;
;"BETREUER";64;"BET_TEAM_LEITER";"CHAR(1)";"NOT NULL";"BETREUER";180;"BET-TEAM-LEITER";"X";"X";;;
;"BETREUER";65;"BET_TEAM_BEGINN_JJQ";"INTEGER";"NOT NULL";"BETREUER";182;"BET-TEAM-BEGINN-JJQ";"9(3)";"P";"Date";"YYQ";
;"BETREUER";66;"BET_PROVISION_DIFFERENZ";"DECIMAL(7,2)";"NOT NULL";"BETREUER";184;"BET-PROVISION-DIFFERENZ";"S9(5)V99";"P";;;
;"BETREUER";67;"BET_SV_NR";"INTEGER";"NOT NULL";"BETREUER";186;"BET-SV-NR";"9(05)";"P";;;
;"BETREUER";68;"BET_UPDATE_DATE";"DATE";;"BETREUER";189;"BET-UPDATE-DATE";"9(8)";"X";"DLM";"CCYYMMDD";
;"BETREUER";69;"BET_UPDATE_ID";"CHAR(8)";"NOT NULL";"BETREUER";191;"BET-UPDATE-ID";"X(8)";"X";;;
 

These SQLMAPs are used by the various program generators and pre-processors to perform the actual MOVES etc from the DB2 columns to the COBOL Variables and vice versa. You also will note that the SQLMAP-files may be easily imported into an Excel spreadsheet or MS ACCESS or DB2 Database for documentation purposes.

4.09 Generating the COBOL HOST variable Copy-Books

In most cases, your COBOL programs will NOT use item-types directly compatible to the "imbedded SQL" language.
While character items usually are no problem, numeric fields usually have different representations in the
DB2 database and your COBOL program.

While DB2 uses field-types like SMALLINT, INT, BIGINT and DECIMAL; your COBOL programs may have numeric data represented as ZONED, PACKED or BINARY fields.

You most probably are representing Date fields as Integers with 6 (YYMMDD) or 8 digits (YYYYMMDD) in your COBOL Program, but SQL must store them in YYYY-MM-DD format when you would like to take advantage of the DATE-arithmetic supported within SQL.

In order to overcome these incompatibilities, a separate (temporary) data structure known as HOST-variables is used, which may be used within EXEC SQL ... END-EXEC statements.

DB123 should be used to GENERATE those Host-Variable copy books as follows:

cmd>DB123 HOSTVAR dictionary

db123.hostvar.pebsy.log      2004-02-26 12:01:41


DB123 vs 14.06: HOSTVAR PEBSY
=============================

... Loading Dictionary: PEBSY options: ADD

List of defined records of Data Dictionary: PEBSY
=================================================

    File          Record   Logical         Prefix
 ## Class    Type Name     Parent   CBL  DL/I DB2  DB2-Table / Physical Filename
--- -------- ---- -------- -------- ---- ---- ---- -----------------------------
  1 AGENTUR  SQL  AGENTUR           AST  AST  AGN  AGENTUR
  2 BETREUER SQL  BETREUER AGENTUR  BET  BET  BET  BETREUER
  3 BINFIX   SQL  BINFIX   AGENTUR  BIF  BIF  BIF  BINFIX
  4 BINVAR   SQL  BINVAR   AGENTUR  BIV  BIV  BIV  BINVAR
  5 LIEFSOFO SQL  LIEFSOFO AGENTUR  ALS  LSO  ALS  LIEFSOFO
  6 LIEFTERM SQL  LIEFTERM AGENTUR  ALT  LIT  ALT  LIEFTERM
  7 UMSPRO   SQL  UMSPRO   BETREUER BUP  UMS  BUP  UMSPRO
  8 GUTBEL   SQL  GUTBEL   BETREUER GUB  GUB  GUB  GUTBEL1
  9 GUTBEL2  SQL  GUTBEL2  BETREUER GU2  GU2  GU2  GUTBEL2
 10 BANKBET  SQL  BANKBET  BETREUER BBB  BAN  BBB  BANKBET
 11 AUSZABET SQL  AUSZABET BANKBET  ABB  AUS  ABB  AUSZABET
 12 REMISSI  SQL  REMISSI  BETREUER REM  REM  REM  REMISS
 13 REMRET   SQL  REMRET   REMISSI  RMR  RMR  RMR  REMISS_RETOUR
 

Processing Data-Dictionary: PEBSY
=================================


Generate HOST-variable copies for file class: AGENTUR
=====================================================

   ... after load: D:\db123\samples\new_pebsy\AGENTUR.SQLMAP: columns are: 1 thru 163 (163 columns)
      163 SQL-columns loaded from: D:\db123\samples\new_pebsy\AGENTUR.SQLMAP
... generating AGNHOST to: \DB123\samples\new_pebsy\includes\AGNHOST.cpy

Generate HOST-variable copies for file class: BETREUER
======================================================

   ... after load: D:\db123\samples\new_pebsy\BETREUER.SQLMAP: columns are: 164 thru 234 (71 columns)
       71 SQL-columns loaded from: D:\db123\samples\new_pebsy\BETREUER.SQLMAP
... generating BETHOST to: \DB123\samples\new_pebsy\includes\BETHOST.cpy

Generate HOST-variable copies for file class: BINFIX
====================================================

   ... after load: D:\db123\samples\new_pebsy\BINFIX.SQLMAP: columns are: 235 thru 272 (38 columns)
       38 SQL-columns loaded from: D:\db123\samples\new_pebsy\BINFIX.SQLMAP
... generating BIFHOST to: \DB123\samples\new_pebsy\includes\BIFHOST.cpy

Generate HOST-variable copies for file class: BINVAR
====================================================

   ... after load: D:\db123\samples\new_pebsy\BINVAR.SQLMAP: columns are: 273 thru 293 (21 columns)
       21 SQL-columns loaded from: D:\db123\samples\new_pebsy\BINVAR.SQLMAP
... generating BIVHOST to: \DB123\samples\new_pebsy\includes\BIVHOST.cpy

Generate HOST-variable copies for file class: LIEFSOFO
======================================================

   ... after load: D:\db123\samples\new_pebsy\LIEFSOFO.SQLMAP: columns are: 294 thru 308 (15 columns)
       15 SQL-columns loaded from: D:\db123\samples\new_pebsy\LIEFSOFO.SQLMAP
... generating ALSHOST to: \DB123\samples\new_pebsy\includes\ALSHOST.cpy

Generate HOST-variable copies for file class: LIEFTERM
======================================================

   ... after load: D:\db123\samples\new_pebsy\LIEFTERM.SQLMAP: columns are: 309 thru 319 (11 columns)
       11 SQL-columns loaded from: D:\db123\samples\new_pebsy\LIEFTERM.SQLMAP
... generating ALTHOST to: \DB123\samples\new_pebsy\includes\ALTHOST.cpy

Generate HOST-variable copies for file class: UMSPRO
====================================================

   ... after load: D:\db123\samples\new_pebsy\UMSPRO.SQLMAP: columns are: 320 thru 363 (44 columns)
       44 SQL-columns loaded from: D:\db123\samples\new_pebsy\UMSPRO.SQLMAP
... generating BUPHOST to: \DB123\samples\new_pebsy\includes\BUPHOST.cpy

Generate HOST-variable copies for file class: GUTBEL
====================================================

   ... after load: D:\db123\samples\new_pebsy\GUTBEL1.SQLMAP: columns are: 364 thru 377 (14 columns)
       14 SQL-columns loaded from: D:\db123\samples\new_pebsy\GUTBEL1.SQLMAP
... generating GUBHOST to: \DB123\samples\new_pebsy\includes\GUBHOST.cpy

Generate HOST-variable copies for file class: GUTBEL2
=====================================================

   ... after load: D:\db123\samples\new_pebsy\GUTBEL2.SQLMAP: columns are: 378 thru 391 (14 columns)
       14 SQL-columns loaded from: D:\db123\samples\new_pebsy\GUTBEL2.SQLMAP
... generating GU2HOST to: \DB123\samples\new_pebsy\includes\GU2HOST.cpy

Generate HOST-variable copies for file class: BANKBET
=====================================================

   ... after load: D:\db123\samples\new_pebsy\BANKBET.SQLMAP: columns are: 392 thru 408 (17 columns)
       17 SQL-columns loaded from: D:\db123\samples\new_pebsy\BANKBET.SQLMAP
... generating BBBHOST to: \DB123\samples\new_pebsy\includes\BBBHOST.cpy

Generate HOST-variable copies for file class: AUSZABET
======================================================

   ... after load: D:\db123\samples\new_pebsy\AUSZABET.SQLMAP: columns are: 409 thru 421 (13 columns)
       13 SQL-columns loaded from: D:\db123\samples\new_pebsy\AUSZABET.SQLMAP
... generating ABBHOST to: \DB123\samples\new_pebsy\includes\ABBHOST.cpy

Generate HOST-variable copies for file class: REMISSI
=====================================================

   ... after load: D:\db123\samples\new_pebsy\REMISS.SQLMAP: columns are: 422 thru 430 (9 columns)
        9 SQL-columns loaded from: D:\db123\samples\new_pebsy\REMISS.SQLMAP
record: REMISSI DB2DETAIL: REM-RETOURWAREN AS REMRET
... generating REMHOST to: \DB123\samples\new_pebsy\includes\REMHOST.cpy

Generate HOST-variable copies for file class: REMRET
====================================================

   ... after load: D:\db123\samples\new_pebsy\REMISS_RETOUR.SQLMAP: columns are: 431 thru 451 (21 columns)
       21 SQL-columns loaded from: D:\db123\samples\new_pebsy\REMISS_RETOUR.SQLMAP
... generating RMRHOST to: \DB123\samples\new_pebsy\includes\RMRHOST.cpy

DB123 summary (vs. 14.06):
==========================

       13 COBOL HOST-Variable copies generated to: \DB123\samples\new_pebsy\includes
      790 total COBOL source lines generated.
 
       no errors detected.
       no warnings.
 
DB123 used:    9.72 seconds for the actions above.
 
 

Note, that the DB2PREFIX as specified in the DATA DICTIONARY is used to create the HOST-Variable copy book names.

Therefore record AGENTUR goes to file AGNHOST.cpy and BINFIX goes to BIFHOST.cpy.

Also note, that the respective directory name as specified in the GLOBALS-file is used to store the HOSTVAR copy-books. Appendix D contains a sample listing of the generated HOSTVAR-copies of our example database.

4.07 Generating the SQL ACCESS functions

As the next step, we can now directly generate all the necessary SQL access functions. Two major approaches are available:

In our application we decided to use INLINE Code (i.e. the generated functions are accessed by COBOL PERFORM xxxx thru xxxx-EX statements (and the code is INCLUDED in the programs where necessary).

cmd> DB123 SQLACC dictionary , for example

cmd>DB123 SQLACC PEBSY

db123.sqlacc.pebsy.log      2004-02-26 11:54:36


DB123 vs 14.06: SQLACC PEBSY
============================

... Loading Dictionary: PEBSY options: ADD

List of defined records of Data Dictionary: PEBSY
=================================================

    File          Record   Logical         Prefix
 ## Class    Type Name     Parent   CBL  DL/I DB2  DB2-Table / Physical Filename
--- -------- ---- -------- -------- ---- ---- ---- -----------------------------
  1 AGENTUR  SQL  AGENTUR           AST  AST  AGN  AGENTUR
  2 BETREUER SQL  BETREUER AGENTUR  BET  BET  BET  BETREUER
  3 BINFIX   SQL  BINFIX   AGENTUR  BIF  BIF  BIF  BINFIX
  4 BINVAR   SQL  BINVAR   AGENTUR  BIV  BIV  BIV  BINVAR
  5 LIEFSOFO SQL  LIEFSOFO AGENTUR  ALS  LSO  ALS  LIEFSOFO
  6 LIEFTERM SQL  LIEFTERM AGENTUR  ALT  LIT  ALT  LIEFTERM
  7 UMSPRO   SQL  UMSPRO   BETREUER BUP  UMS  BUP  UMSPRO
  8 GUTBEL   SQL  GUTBEL   BETREUER GUB  GUB  GUB  GUTBEL1
  9 GUTBEL2  SQL  GUTBEL2  BETREUER GU2  GU2  GU2  GUTBEL2
 10 BANKBET  SQL  BANKBET  BETREUER BBB  BAN  BBB  BANKBET
 11 AUSZABET SQL  AUSZABET BANKBET  ABB  AUS  ABB  AUSZABET
 12 REMISSI  SQL  REMISSI  BETREUER REM  REM  REM  REMISS
 13 REMRET   SQL  REMRET   REMISSI  RMR  RMR  RMR  REMISS_RETOUR
 

Processing Data-Dictionary: PEBSY
=================================


Generate SQL-ACCESS-Routines for file class: AGENTUR
====================================================

   ... after load: D:\db123\samples\new_pebsy\AGENTUR.SQLMAP: columns are: 1 thru 163 (163 columns)
      163 SQL-columns loaded from: D:\db123\samples\new_pebsy\AGENTUR.SQLMAP
... generating AGNHOST to: \DB123\samples\new_pebsy\includes\AGNHOST.cpy
... generating AGNH2C to: \DB123\samples\new_pebsy\includes\AGNH2C.cpy
... generating AGNC2H to: \DB123\samples\new_pebsy\includes\AGNC2H.cpy
... generating AGNGET to: \DB123\samples\new_pebsy\includes\AGNGET.cpy
... generating AGNCURN to: \DB123\samples\new_pebsy\includes\AGNCURN.cpy
... generating AGNNEXT to: \DB123\samples\new_pebsy\includes\AGNNEXT.cpy
... generating AGNISRT to: \DB123\samples\new_pebsy\includes\AGNISRT.cpy
... generating AGNDELE to: \DB123\samples\new_pebsy\includes\AGNDELE.cpy
... generating AGNREPL to: \DB123\samples\new_pebsy\includes\AGNREPL.cpy

Generate SQL-ACCESS-Routines for file class: BETREUER
=====================================================

   ... after load: D:\db123\samples\new_pebsy\BETREUER.SQLMAP: columns are: 164 thru 234 (71 columns)
       71 SQL-columns loaded from: D:\db123\samples\new_pebsy\BETREUER.SQLMAP
... generating BETHOST to: \DB123\samples\new_pebsy\includes\BETHOST.cpy
... generating BETH2C to: \DB123\samples\new_pebsy\includes\BETH2C.cpy
... generating BETC2H to: \DB123\samples\new_pebsy\includes\BETC2H.cpy
... generating BETGET to: \DB123\samples\new_pebsy\includes\BETGET.cpy
... generating BETCURN to: \DB123\samples\new_pebsy\includes\BETCURN.cpy
... generating BETNEXT to: \DB123\samples\new_pebsy\includes\BETNEXT.cpy
... generating BETCURP to: \DB123\samples\new_pebsy\includes\BETCURP.cpy
... generating BETNWP to: \DB123\samples\new_pebsy\includes\BETNWP.cpy
... generating BETISRT to: \DB123\samples\new_pebsy\includes\BETISRT.cpy
... generating BETDELE to: \DB123\samples\new_pebsy\includes\BETDELE.cpy
... generating BETREPL to: \DB123\samples\new_pebsy\includes\BETREPL.cpy

Generate SQL-ACCESS-Routines for file class: BINFIX
===================================================

   ... after load: D:\db123\samples\new_pebsy\BINFIX.SQLMAP: columns are: 235 thru 272 (38 columns)
       38 SQL-columns loaded from: D:\db123\samples\new_pebsy\BINFIX.SQLMAP
... generating BIFHOST to: \DB123\samples\new_pebsy\includes\BIFHOST.cpy
... generating BIFH2C to: \DB123\samples\new_pebsy\includes\BIFH2C.cpy
... generating BIFC2H to: \DB123\samples\new_pebsy\includes\BIFC2H.cpy
... generating BIFGET to: \DB123\samples\new_pebsy\includes\BIFGET.cpy
... generating BIFCURN to: \DB123\samples\new_pebsy\includes\BIFCURN.cpy
... generating BIFNEXT to: \DB123\samples\new_pebsy\includes\BIFNEXT.cpy
... generating BIFCURP to: \DB123\samples\new_pebsy\includes\BIFCURP.cpy
... generating BIFNWP to: \DB123\samples\new_pebsy\includes\BIFNWP.cpy
... generating BIFISRT to: \DB123\samples\new_pebsy\includes\BIFISRT.cpy
... generating BIFDELE to: \DB123\samples\new_pebsy\includes\BIFDELE.cpy
... generating BIFREPL to: \DB123\samples\new_pebsy\includes\BIFREPL.cpy

Generate SQL-ACCESS-Routines for file class: BINVAR
===================================================

   ... after load: D:\db123\samples\new_pebsy\BINVAR.SQLMAP: columns are: 273 thru 293 (21 columns)
       21 SQL-columns loaded from: D:\db123\samples\new_pebsy\BINVAR.SQLMAP
... generating BIVHOST to: \DB123\samples\new_pebsy\includes\BIVHOST.cpy
... generating BIVH2C to: \DB123\samples\new_pebsy\includes\BIVH2C.cpy
... generating BIVC2H to: \DB123\samples\new_pebsy\includes\BIVC2H.cpy
... generating BIVGET to: \DB123\samples\new_pebsy\includes\BIVGET.cpy
... generating BIVCURN to: \DB123\samples\new_pebsy\includes\BIVCURN.cpy
... generating BIVNEXT to: \DB123\samples\new_pebsy\includes\BIVNEXT.cpy
... generating BIVCURP to: \DB123\samples\new_pebsy\includes\BIVCURP.cpy
... generating BIVNWP to: \DB123\samples\new_pebsy\includes\BIVNWP.cpy
... generating BIVISRT to: \DB123\samples\new_pebsy\includes\BIVISRT.cpy
... generating BIVDELE to: \DB123\samples\new_pebsy\includes\BIVDELE.cpy
... generating BIVREPL to: \DB123\samples\new_pebsy\includes\BIVREPL.cpy

Generate SQL-ACCESS-Routines for file class: LIEFSOFO
=====================================================

   ... after load: D:\db123\samples\new_pebsy\LIEFSOFO.SQLMAP: columns are: 294 thru 308 (15 columns)
       15 SQL-columns loaded from: D:\db123\samples\new_pebsy\LIEFSOFO.SQLMAP
... generating ALSHOST to: \DB123\samples\new_pebsy\includes\ALSHOST.cpy
... generating ALSH2C to: \DB123\samples\new_pebsy\includes\ALSH2C.cpy
... generating ALSC2H to: \DB123\samples\new_pebsy\includes\ALSC2H.cpy
... generating ALSGET to: \DB123\samples\new_pebsy\includes\ALSGET.cpy
... generating ALSCURN to: \DB123\samples\new_pebsy\includes\ALSCURN.cpy
... generating ALSNEXT to: \DB123\samples\new_pebsy\includes\ALSNEXT.cpy
... generating ALSCURP to: \DB123\samples\new_pebsy\includes\ALSCURP.cpy
... generating ALSNWP to: \DB123\samples\new_pebsy\includes\ALSNWP.cpy
... generating ALSISRT to: \DB123\samples\new_pebsy\includes\ALSISRT.cpy
... generating ALSDELE to: \DB123\samples\new_pebsy\includes\ALSDELE.cpy
... generating ALSREPL to: \DB123\samples\new_pebsy\includes\ALSREPL.cpy

Generate SQL-ACCESS-Routines for file class: LIEFTERM
=====================================================

   ... after load: D:\db123\samples\new_pebsy\LIEFTERM.SQLMAP: columns are: 309 thru 319 (11 columns)
       11 SQL-columns loaded from: D:\db123\samples\new_pebsy\LIEFTERM.SQLMAP
... generating ALTHOST to: \DB123\samples\new_pebsy\includes\ALTHOST.cpy
... generating ALTH2C to: \DB123\samples\new_pebsy\includes\ALTH2C.cpy
... generating ALTC2H to: \DB123\samples\new_pebsy\includes\ALTC2H.cpy
... generating ALTGET to: \DB123\samples\new_pebsy\includes\ALTGET.cpy
... generating ALTCURN to: \DB123\samples\new_pebsy\includes\ALTCURN.cpy
... generating ALTNEXT to: \DB123\samples\new_pebsy\includes\ALTNEXT.cpy
... generating ALTCURP to: \DB123\samples\new_pebsy\includes\ALTCURP.cpy
... generating ALTNWP to: \DB123\samples\new_pebsy\includes\ALTNWP.cpy
... generating ALTISRT to: \DB123\samples\new_pebsy\includes\ALTISRT.cpy
... generating ALTDELE to: \DB123\samples\new_pebsy\includes\ALTDELE.cpy
... generating ALTREPL to: \DB123\samples\new_pebsy\includes\ALTREPL.cpy

Generate SQL-ACCESS-Routines for file class: UMSPRO
===================================================

   ... after load: D:\db123\samples\new_pebsy\UMSPRO.SQLMAP: columns are: 320 thru 363 (44 columns)
       44 SQL-columns loaded from: D:\db123\samples\new_pebsy\UMSPRO.SQLMAP
... generating BUPHOST to: \DB123\samples\new_pebsy\includes\BUPHOST.cpy
... generating BUPH2C to: \DB123\samples\new_pebsy\includes\BUPH2C.cpy
... generating BUPC2H to: \DB123\samples\new_pebsy\includes\BUPC2H.cpy
... generating BUPGET to: \DB123\samples\new_pebsy\includes\BUPGET.cpy
... generating BUPCURN to: \DB123\samples\new_pebsy\includes\BUPCURN.cpy
... generating BUPNEXT to: \DB123\samples\new_pebsy\includes\BUPNEXT.cpy
... generating BUPCURP to: \DB123\samples\new_pebsy\includes\BUPCURP.cpy
... generating BUPNWP to: \DB123\samples\new_pebsy\includes\BUPNWP.cpy
... generating BUPISRT to: \DB123\samples\new_pebsy\includes\BUPISRT.cpy
... generating BUPDELE to: \DB123\samples\new_pebsy\includes\BUPDELE.cpy
... generating BUPREPL to: \DB123\samples\new_pebsy\includes\BUPREPL.cpy

Generate SQL-ACCESS-Routines for file class: GUTBEL
===================================================

   ... after load: D:\db123\samples\new_pebsy\GUTBEL1.SQLMAP: columns are: 364 thru 377 (14 columns)
       14 SQL-columns loaded from: D:\db123\samples\new_pebsy\GUTBEL1.SQLMAP
... generating GUBHOST to: \DB123\samples\new_pebsy\includes\GUBHOST.cpy
... generating GUBH2C to: \DB123\samples\new_pebsy\includes\GUBH2C.cpy
... generating GUBC2H to: \DB123\samples\new_pebsy\includes\GUBC2H.cpy
... generating GUBGET to: \DB123\samples\new_pebsy\includes\GUBGET.cpy
... generating GUBCURN to: \DB123\samples\new_pebsy\includes\GUBCURN.cpy
... generating GUBNEXT to: \DB123\samples\new_pebsy\includes\GUBNEXT.cpy
... generating GUBCURP to: \DB123\samples\new_pebsy\includes\GUBCURP.cpy
... generating GUBNWP to: \DB123\samples\new_pebsy\includes\GUBNWP.cpy
... generating GUBISRT to: \DB123\samples\new_pebsy\includes\GUBISRT.cpy
... generating GUBDELE to: \DB123\samples\new_pebsy\includes\GUBDELE.cpy
... generating GUBREPL to: \DB123\samples\new_pebsy\includes\GUBREPL.cpy

Generate SQL-ACCESS-Routines for file class: GUTBEL2
====================================================

   ... after load: D:\db123\samples\new_pebsy\GUTBEL2.SQLMAP: columns are: 378 thru 391 (14 columns)
       14 SQL-columns loaded from: D:\db123\samples\new_pebsy\GUTBEL2.SQLMAP
... generating GU2HOST to: \DB123\samples\new_pebsy\includes\GU2HOST.cpy
... generating GU2H2C to: \DB123\samples\new_pebsy\includes\GU2H2C.cpy
... generating GU2C2H to: \DB123\samples\new_pebsy\includes\GU2C2H.cpy
... generating GU2GET to: \DB123\samples\new_pebsy\includes\GU2GET.cpy
... generating GU2CURN to: \DB123\samples\new_pebsy\includes\GU2CURN.cpy
... generating GU2NEXT to: \DB123\samples\new_pebsy\includes\GU2NEXT.cpy
... generating GU2CURP to: \DB123\samples\new_pebsy\includes\GU2CURP.cpy
... generating GU2NWP to: \DB123\samples\new_pebsy\includes\GU2NWP.cpy
... generating GU2ISRT to: \DB123\samples\new_pebsy\includes\GU2ISRT.cpy
... generating GU2DELE to: \DB123\samples\new_pebsy\includes\GU2DELE.cpy
... generating GU2REPL to: \DB123\samples\new_pebsy\includes\GU2REPL.cpy

Generate SQL-ACCESS-Routines for file class: BANKBET
====================================================

   ... after load: D:\db123\samples\new_pebsy\BANKBET.SQLMAP: columns are: 392 thru 408 (17 columns)
       17 SQL-columns loaded from: D:\db123\samples\new_pebsy\BANKBET.SQLMAP
... generating BBBHOST to: \DB123\samples\new_pebsy\includes\BBBHOST.cpy
... generating BBBH2C to: \DB123\samples\new_pebsy\includes\BBBH2C.cpy
... generating BBBC2H to: \DB123\samples\new_pebsy\includes\BBBC2H.cpy
... generating BBBGET to: \DB123\samples\new_pebsy\includes\BBBGET.cpy
... generating BBBCURN to: \DB123\samples\new_pebsy\includes\BBBCURN.cpy
... generating BBBNEXT to: \DB123\samples\new_pebsy\includes\BBBNEXT.cpy
... generating BBBCURP to: \DB123\samples\new_pebsy\includes\BBBCURP.cpy
... generating BBBNWP to: \DB123\samples\new_pebsy\includes\BBBNWP.cpy
... generating BBBISRT to: \DB123\samples\new_pebsy\includes\BBBISRT.cpy
... generating BBBDELE to: \DB123\samples\new_pebsy\includes\BBBDELE.cpy
... generating BBBREPL to: \DB123\samples\new_pebsy\includes\BBBREPL.cpy

Generate SQL-ACCESS-Routines for file class: AUSZABET
=====================================================

   ... after load: D:\db123\samples\new_pebsy\AUSZABET.SQLMAP: columns are: 409 thru 421 (13 columns)
       13 SQL-columns loaded from: D:\db123\samples\new_pebsy\AUSZABET.SQLMAP
... generating ABBHOST to: \DB123\samples\new_pebsy\includes\ABBHOST.cpy
... generating ABBH2C to: \DB123\samples\new_pebsy\includes\ABBH2C.cpy
... generating ABBC2H to: \DB123\samples\new_pebsy\includes\ABBC2H.cpy
... generating ABBGET to: \DB123\samples\new_pebsy\includes\ABBGET.cpy
... generating ABBCURN to: \DB123\samples\new_pebsy\includes\ABBCURN.cpy
... generating ABBNEXT to: \DB123\samples\new_pebsy\includes\ABBNEXT.cpy
... generating ABBCURP to: \DB123\samples\new_pebsy\includes\ABBCURP.cpy
... generating ABBNWP to: \DB123\samples\new_pebsy\includes\ABBNWP.cpy
... generating ABBISRT to: \DB123\samples\new_pebsy\includes\ABBISRT.cpy
... generating ABBDELE to: \DB123\samples\new_pebsy\includes\ABBDELE.cpy
... generating ABBREPL to: \DB123\samples\new_pebsy\includes\ABBREPL.cpy

Generate SQL-ACCESS-Routines for file class: REMISSI
====================================================

   ... after load: D:\db123\samples\new_pebsy\REMISS.SQLMAP: columns are: 422 thru 430 (9 columns)
        9 SQL-columns loaded from: D:\db123\samples\new_pebsy\REMISS.SQLMAP
record: REMISSI DB2DETAIL: REM-RETOURWAREN AS REMRET
record: REMISSI DB2DETAIL: REM-RETOURWAREN AS REMRET
record: REMISSI DB2DETAIL: REM-RETOURWAREN AS REMRET
... generating REMHOST to: \DB123\samples\new_pebsy\includes\REMHOST.cpy
record: REMISSI DB2DETAIL: REM-RETOURWAREN AS REMRET
record: REMISSI DB2DETAIL: REM-RETOURWAREN AS REMRET
... generating REMH2C to: \DB123\samples\new_pebsy\includes\REMH2C.cpy
record: REMISSI DB2DETAIL: REM-RETOURWAREN AS REMRET
record: REMISSI DB2DETAIL: REM-RETOURWAREN AS REMRET
... generating REMC2H to: \DB123\samples\new_pebsy\includes\REMC2H.cpy
record: REMISSI DB2DETAIL: REM-RETOURWAREN AS REMRET
record: REMISSI DB2DETAIL: REM-RETOURWAREN AS REMRET
... generating REMGET to: \DB123\samples\new_pebsy\includes\REMGET.cpy
record: REMISSI DB2DETAIL: REM-RETOURWAREN AS REMRET
must extend: GET-UNIQUE--REMISSI by READ REM-RETOURWAREN AS REMRET
record: REMISSI
db2_detail: REM-RETOURWAREN AS REMRET
  parent_struct: REM-RETOURWAREN (item #: 951)
      n-details: 14 ( occurs: 14)
     detail_rec: REMRET (rec #: 13)
record: REMISSI DB2DETAIL: REM-RETOURWAREN AS REMRET
record: REMISSI DB2DETAIL: REM-RETOURWAREN AS REMRET
record: REMISSI DB2DETAIL: REM-RETOURWAREN AS REMRET
... generating REMCURN to: \DB123\samples\new_pebsy\includes\REMCURN.cpy
record: REMISSI DB2DETAIL: REM-RETOURWAREN AS REMRET
... generating REMNEXT to: \DB123\samples\new_pebsy\includes\REMNEXT.cpy
must extend: GET-NEXT--REMISSI by READ REM-RETOURWAREN AS REMRET
record: REMISSI
db2_detail: REM-RETOURWAREN AS REMRET
  parent_struct: REM-RETOURWAREN (item #: 951)
      n-details: 14 ( occurs: 14)
     detail_rec: REMRET (rec #: 13)
record: REMISSI DB2DETAIL: REM-RETOURWAREN AS REMRET
record: REMISSI DB2DETAIL: REM-RETOURWAREN AS REMRET
record: REMISSI DB2DETAIL: REM-RETOURWAREN AS REMRET
... generating REMCURP to: \DB123\samples\new_pebsy\includes\REMCURP.cpy
record: REMISSI DB2DETAIL: REM-RETOURWAREN AS REMRET
... generating REMNWP to: \DB123\samples\new_pebsy\includes\REMNWP.cpy
must extend: GET-NEXT-WLP--REMISSI by READ REM-RETOURWAREN AS REMRET
record: REMISSI
db2_detail: REM-RETOURWAREN AS REMRET
  parent_struct: REM-RETOURWAREN (item #: 951)
      n-details: 14 ( occurs: 14)
     detail_rec: REMRET (rec #: 13)
record: REMISSI DB2DETAIL: REM-RETOURWAREN AS REMRET
record: REMISSI DB2DETAIL: REM-RETOURWAREN AS REMRET
record: REMISSI DB2DETAIL: REM-RETOURWAREN AS REMRET
... generating REMISRT to: \DB123\samples\new_pebsy\includes\REMISRT.cpy
must extend: INSERT--REMISSI by INSERT REM-RETOURWAREN AS REMRET
record: REMISSI
db2_detail: REM-RETOURWAREN AS REMRET
  parent_struct: REM-RETOURWAREN (item #: 951)
      n-details: 14 ( occurs: 14)
     detail_rec: REMRET (rec #: 13)
record: REMISSI DB2DETAIL: REM-RETOURWAREN AS REMRET
record: REMISSI DB2DETAIL: REM-RETOURWAREN AS REMRET
record: REMISSI DB2DETAIL: REM-RETOURWAREN AS REMRET
... generating REMDELE to: \DB123\samples\new_pebsy\includes\REMDELE.cpy
must extend: DELETE--REMISSI by DELETE REM-RETOURWAREN AS REMRET
record: REMISSI
db2_detail: REM-RETOURWAREN AS REMRET
  parent_struct: REM-RETOURWAREN (item #: 951)
      n-details: 14 ( occurs: 14)
     detail_rec: REMRET (rec #: 13)
record: REMISSI DB2DETAIL: REM-RETOURWAREN AS REMRET
record: REMISSI DB2DETAIL: REM-RETOURWAREN AS REMRET
record: REMISSI DB2DETAIL: REM-RETOURWAREN AS REMRET
... generating REMREPL to: \DB123\samples\new_pebsy\includes\REMREPL.cpy
must extend: REPLACE--REMISSI by REPLACE REM-RETOURWAREN AS REMRET
record: REMISSI
db2_detail: REM-RETOURWAREN AS REMRET
  parent_struct: REM-RETOURWAREN (item #: 951)
      n-details: 14 ( occurs: 14)
     detail_rec: REMRET (rec #: 13)
record: REMISSI DB2DETAIL: REM-RETOURWAREN AS REMRET

Generate SQL-ACCESS-Routines for file class: REMRET
===================================================

   ... after load: D:\db123\samples\new_pebsy\REMISS_RETOUR.SQLMAP: columns are: 431 thru 451 (21 columns)
       21 SQL-columns loaded from: D:\db123\samples\new_pebsy\REMISS_RETOUR.SQLMAP
... generating RMRHOST to: \DB123\samples\new_pebsy\includes\RMRHOST.cpy
... generating RMRH2C to: \DB123\samples\new_pebsy\includes\RMRH2C.cpy
... generating RMRC2H to: \DB123\samples\new_pebsy\includes\RMRC2H.cpy
... generating RMRGET to: \DB123\samples\new_pebsy\includes\RMRGET.cpy
... generating RMRCURN to: \DB123\samples\new_pebsy\includes\RMRCURN.cpy
... generating RMRNEXT to: \DB123\samples\new_pebsy\includes\RMRNEXT.cpy
... generating RMRCURP to: \DB123\samples\new_pebsy\includes\RMRCURP.cpy
... generating RMRNWP to: \DB123\samples\new_pebsy\includes\RMRNWP.cpy
... generating RMRISRT to: \DB123\samples\new_pebsy\includes\RMRISRT.cpy
... generating RMRDELE to: \DB123\samples\new_pebsy\includes\RMRDELE.cpy
... generating RMRREPL to: \DB123\samples\new_pebsy\includes\RMRREPL.cpy

DB123 summary (vs. 14.06):
==========================

       13 COBOL HOST-Variable copies generated to: \DB123\samples\new_pebsy\includes
      128 SQL access routines / copy books generated
     9124 total COBOL source lines generated.
 
       no errors detected.
       no warnings.
 
DB123 used:   13.03 seconds for the actions above.

For each Record-Type (SQL-Table) a set of declarations / procedures are generated as follows:  

 

xxxHOST.cpy 

The COBOL HOST Variable declarations, which will be used in the EXEC SQL BEGIN DECLARE --..END DECLARE section.

xxxC2H.cpy

the COBOL to HOSTVAR conversion procedure

xxxH2C.cpy

the HOSTVAR to COBOL conversion procedure

xxxGET.cpy 

the KEYED RETRIEVAL (SELECT UNIQUE KEY) procedure

xxxCURN.cpy

the SQL CURSOR definition for SEQUENTIAL access

xxxNEXT.cpy 

the SQL GET NEXT record (within CURSOR) procedure

xxxCURP.cpy

the SQL CURSOR definition for NEXT WITHIN PARENT

xxxNWP.cpy

the SQL GET NEXT WITHIN LOGICAL PARENT procedure

xxxISRT.cpy 

the SQL INSERT procedure

xxxDELE.cpy

the SQL DELETE procedure

xxxREPL.cpy 

the SQL REPLACE (UPDATE all fields) procedure

 

As already noted, the DB2PREFIX (normally identical to the record-prefix) is used to generate UNIQUE copy book names
(xxx in the example above).

4.08 Using the generated access-functions

4.09 Documenting the Data Dictionary

4.10 Summary