Introducing DB123 A New Approach to Database Management

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 DB 2 -LOAD-Files !!! without regarding the problems of Data Integrity as well as 600+ Application Programs to be adapted ….

DL/I --> DB 2 Migration A „classical“ Database-Migration to another System would imply : • 75 new DB 2 -Tables necessary only for DL/I Migration (excluding VSAM-Files) !!! • all 75 DB 2 -Tables have to be downloaded from the HOST and uploaded to the new system at once. !

the DL/I DB 123 – DB 2 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 DB 123 – DB 2 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 DB 123 – DB 2 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 DB 123 – DB 2 Migration-Tool [4] • generates COBOL procedures (inline code) for the varios access methods: – xxx. GET - for keyed access (unique key given) – xxx. NEXT - for SQL Cursor access (alle seq. ) – xxx. NWP - ‚next within parent‘ [Cursor defined by ‚logical parent‘] – xxx. ISRT - for INSERTs – xxx. DELE - for DELETEs (with given key) – xxx. UPDT - for Updates

DB 123: the advantages Take again the Donauland example – for 95 DB 2 -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 DB 2 -Data-Base Designer • for each DL/I Segment and each VSAM KSDS -File a DB 2 -Table has to be created • following the rules of the ‚ 3 rd Normal Form‘ The rules of the 3 rd 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 DB 2 -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 DB 2 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 DB 2! Solution with DB 123 : either new, higher level TABLES (DB 2 DETAIL) or NUMBERING the respective DB 2 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 office@db-123. com