AO Advanced Modernization (RENOVATION) Workshop Session 03

Normalisation Theory and Metadata Repository (Data Dictionary)
The DB2 relational database definition, contents and structure are the basis of all systems development.
0:01:24 Contact Information
0:02:30 Revision of Session 2
0:10:32 Second Normal Form (2NF)
0:14:33 Third Normal Form (3NF)
0:22:04 Normalizing Data: Summary
0:25:22 “Final Statement”
0:39:37 Q and A
0:50:59 Data Dictionary
0:59:50 Data Dictionary - Benefits
1:08:50 AO Data Dictionary - Review
1:59:27 End
Codd’s 12 Rules for Relational Databases
Review and discuss the 12 rules defined by Edgar F. Codd which need to be considered for the successful construction of a relational database.
Normalizing Data
DB2 on IBM i is an integrated relational database and to gain the best results the data in the database should be properly normalized and relationships between files fully defined.
Normalization Theory
Discuss and review the generally accepted theories of normalization in a relational database.
Practical Normalization
Examine the differences between theory and practice in the normalization of data and discuss the reasons for the differences in practical applications.
Data Dictionary
What is a data dictionary? Why is it necessary and how does one go about implementing one.
Additional advantages of a properly constituted and managed Data Dictionary.
Database Components
Taking a look at all the different components which go to making up a DB2 Normalized, Relational Database.
The Physical Database
The “Data Store” is the heart of any database as it is the definition of the structures which contain the company’s data. This is the single most valuable asset of any company no matter how big or how small.
We examine the objects making up the physical database and the key role these objects play in the modernized database and associated applications.
For customers with AO installed the workshop covers the details of how the physical database objects are defined and maintained from within the AO workbench.
DDS Physical Files
DDS is the original definition tool for “Physical Files” in the DB2 database. Access from application code is by ISAM (Indexed Sequential Access Method).
DDL Tables
DDL is the modern way to define “Tables” for the DB2 database. This uses the SQL engine for table access.
DDS vs. DDL
Discuss the pro’s and con’s of DDS as opposed to DDL.
Constraints
Primary Keys, Unique Keys, Check Constraints and Referential Constraints
In a DB2 relational database, the “relational” components are those elements which provide the relationship management and other powerful functionality to the IBM i DB2 database.
This section discusses the what, why and how of the various “constraints”, illustrating the enormous benefits obtained from the use of these components for a very small investment in time and effort.
Journals and Receivers
Journals and their associated receivers are an absolutely mandatory requirement when constructing a DB2 Relational database. Many other benefits are available from the use of these journals.
This section looks at the setup and usage of journals in the context of a modernized DDL/SQL database.
Commitment Control
As with journals, commitment control is required for a correctly functioning DB2 relational database.
We look at the setup and usage of commitment control and the “commit” cycle within a modernized database and also within its associated applications.
Trigger Programs
Although not mandatory, trigger programs are certainly considered “best practice” when it comes to DB2 databases.
Trigger programs are conventional programs written in any ILE language, as well as SQL, and attached to an event occurring on a “physical” database construct. A new trigger event has just been made available for SQL logical files.
This section discusses how to create trigger programs and the benefits of triggers in modernizing applications.
External (RPG) vs. SQL
We will also investigate and discuss the differences, advantages and disadvantages of SQL triggers as opposed to RPGILE (External) triggers.
I/O Servers
I/O Servers, although not an official IBM i DB2 construct, are considered “best practice” in the process of application modernization around an already modernized DB2 database.
We discuss the principles and creation of the I/O Server programs and/or procedures and how these entities form the “bridge” between the application and the database, reducing application requirements in the process.
Please see www.adsero-opt... and www.adsero-opt... for more details.