Transcript DB-123 Powerpoint

Introducing DB-123
A New Approach to Database
Management Systems
Thomas Schneider
February 2004
Goals
Upwards compatible database management
· Minimize costs for migrations
· Maximize security for users
· Do not loose already existing
Meta-Information like
Hierarchies, Repetitions, …
a practical example –
the Donauland Database…
consider the following structures …
29
75
10
6
10
DL/I Databases with
DL/I Segments and
permanent VSAM KSDS Files
permanent VSAM ESDS Files
SQL-Tables
which have to be migrated to a different Operating System
and a different Database Management System
a practical example –
the Donauland Database…
A „classical“ Database-Migration of
the described Databases to
another System would imply :
55
95
Download-Programs necessary
DB2-LOAD-Files !!!
without regarding the problems of Data Integrity
as well as 600+ Application Programs to be adapted ….
DL/I --> DB2 Migration
A „classical“ Database-Migration to
another System would imply :
• 75 new DB2-Tables necessary only for DL/I
Migration (excluding VSAM-Files) !!!
• all 75 DB2-Tables have to be downloaded from
the HOST and uploaded to the new system at
once.!
the DL/I
DB123 –
DB2 Migration-Tool
• Performs the Database DESIGN automatically
• manual adaption of DESIGN possible
• automatic insertion of new fields possible –
for instance USER-ID and UPDATE-Date
in all new tables
the DL/I
DB123 –
DB2 Migration-Tool [2]
• generates DOWNLOAD Programs
[COBOL SOURCE and VSE JCL!]
for unloading the DL/I databases
• performs rigorous checks of all values
– check NUMERIC fields for valid entries
– check all DATE-fields for valid Dates
• 000000 --> SQL NULL
• 999999 --> SQL NULL
– Character-Fields: no imbedded quotes allowed
the DL/I
DB123 –
DB2 Migration-Tool [3]
• generates COBOL HOST-Variables for
SQL-interface
• automatic update of the
„old“ DL/I record COPY-books
…add parent keys, USER-ID, LASTUPDATE, …
the DL/I
DB123 –
DB2 Migration-Tool [4]
• generates COBOL procedures (inline code) for
the varios access methods:
– xxxGET - for keyed access (unique key given)
– xxxNEXT - for SQL Cursor access (alle seq.)
– xxxNWP - ‚next within parent‘
[Cursor defined by ‚logical parent‘]
– xxxISRT - for INSERTs
– xxxDELE - for DELETEs (with given key)
– xxxUPDT - for Updates
DB123: the advantages
Take again the Donauland example –
for 95 DB2-Tables in the Application
• There are 95 * 6 = 570
subroutines/procedures
• which have NOT to be developed manually
and tested, but are generated automatically !
DB-123 DESIGN:
The DB2-Data-Base Designer
• for each DL/I Segment and each VSAM
KSDS-File a DB2-Table has to be created
• following the rules of the ‚3rd Normal Form‘
The rules of the 3rd Normal Form are very simple
– read on …
DB-123 DESIGN:
The First Normal Form
• All parent DL/I primary keys must be added to
the child records (in front of the existing fields)
• All unique DL/I primary keys become primary
keys of the DB2-table as well.
DB-123 DESIGN:
the Second Normal Form
• If the key is still not unique,
a sequence field (trigger) or
a so called TIMESTAMP
is added
• Each DB2 table has now a unique key !
Is the DESIGN now complete ?
• NO !
• The DL/I Databases may contain
OCCURS-items in the COBOL record
structures!
Is the DESIGN now complete ?
…continued
• Cobol-OCCURS-items are NOT supported by
DB2!
Solution with DB123 :
either new, higher level TABLES (DB2DETAIL)
or NUMBERING the respective DB2 columns
(both Alternatives may be performed
automatically, driven by proper clauses in the
Data Dictionary).
DB-123 DESIGN:
the Third Normal Form
• All Tables are flat tables (including logical
parent keys)
• Hierarchic relationship retained thru
„Logical Parent“ relationships
(LPARENT-clause) in data Dictionary.
DB-123 DESIGN:
the Third Normal Form
• Repeated COBOL structures resolved thru
DBDETAIL clause in Data Dictionary
• New Data Dictionary used to GENERATE
ACCESS functions and needed COPYbooks.
Thank You !
For all inquiries and more information please contact
[email protected]