Database Modernization / SQL DDL

The course does not design a new database, but rather assumes that most companies have grown database concepts, physical logical files with lots of redundancies. Furthermore, it is shown how it is possible not only to convert the files in an existing application, but also to extend them ... and, last but not least, to redesign them.

RPG IV and SQL developers

The participants should be familiar with database concepts, and at least know physical and logical files.

DDS # SQL

• SQL/DDS Terminology
• SQL versus System Naming Conventions
• Architectural Differences (invalid numeric Values)

Schema

Tables

• Definition
• Data Types
• Unique Identifier (Identiy Columns)
• Other Auditing Columns
• CREATE OR REPLACE ... convert from DDS to DDL
• Row and Column Access Control (RCAC)

Constraints

• Primary / Unique Key Constraints
• Check Constraints
• Referential Integrities
• Trigger

Journaling and Commitment Control

Temporal Tables

Materialized Query Tables

Reverse Engineering

Views

• Data Centric Thinking
• Definition
• Instead Of Triggers

Indices

• Binary Radix Tree Indexes
• Encoded Vector Indexes (incl. INCLUDE Clause)
• Derived and Sparse Indexes

IBM i Access Client Solutions - Index Analysis

• Index Advisor
• Index Evaluator
• Index Condenser