DB2 Database Design

Course Objectives
The Course looks at the wider and more advanced issues applicable to efficient DB2 design, including Logical and Physical Database design issues, locking strategies, and the definition of database constraints. The Course includes many practical sessions, designing, implementing, and refining a DB2 system. On completion of this Course the student will be able to:

• use design techniques such as Normalisation
• understand the performance issues involved in constructing a system
• implement an efficient tablespace, table and index design
• choose the correct locking strategy
• implement referential and table check constraints
• use distinct types
• design and create triggers
• understand the performance issues involving the use of large objects

Course Details

• THE DESIGN PROCESS
• Introduction - The Basic Elements of a Database
• The Flat File Database
• The Hierarchical Database
• The Relational Database
• Relational Database Advantages
• Database Design Phases
• Key Roles in the Design Process
• CONCEPTUAL DESIGN
• Why Analyse?
• Who Will be Involved in the Analysis?
• Entities
• Entity Relationships - A Worked Example
• Entity Relationship Diagrams
• Relationship Types
• Many to Many Relationships - Association Entities
• Common Parentage
• Attributes
• LOGICAL DATABASE DESIGN
• Overview of Normalisation
• Benefits of Normalisation
• Primary and Foreign Keys
• Primary Key Considerations
• The Normalisation Process
• First Normal Form
• Second Normal Form
• Third Normal Form
• Fourth Normal Form
• Fifth Normal Form
• Normalisation Check - Homonyms and Synonyms
• Conversion of Entities to Base Tables
• Attributes
• Identification of Keys
• PHYSICAL DATABASE DESIGN
• Introduction
• Gathering of Required Information
• Data Flow Diagrams
• Access Path Requirements
• Entity Life Histories
• Data And Relationship Volumes
• Denormalisation Considerations
• Denormalisation Techniques
• RI Requirements
• Index Requirements
• Locking Requirements
• Locking Considerations
• View Requirements
• PHYSICAL DATABASE REQUIREMENTS
• The Structure of DB2 Objects
• Definition of DB2 Objects - DDL
• Storage Groups
• Databases
• Tablespaces
• Pages
• Tablespace Organisation
• Page and Row Organisation
• The Simple Tablespace
• The Segmented Tablespace
• The Partitioned Tablespace
• The Lob Tablespace
• Create Tablespace Parameters
• Alter Tablespace
• Large Tablespaces (EA Enabled)
• Creating Large Tablespaces
• Altering DSSIZE
• Tablespace Thresholds
• Tablespace Row Placement Rules
• Tables
• Copying Table Definitions
• Rename Table
• DB2 Column Types
• Design Tips for Columns
• Null Values
• User Defined Default Values
• Nulls - Design Tips
• Identity Columns
• Identity Columns - Examples
• Identity Columns - Considerations
• Identity Columns - Restrictions
• Identity_Val_Local Function
• Global Temporary Tables
• Declared Temporary Tables
• Declared Temporary Table Considerations
• Declared Temporary Tables - Comparisons
• Synonyms
• Aliases
• Indexes
• Index Organisation - The B Tree Index
• Index Clustering
• Non-Unique Indexes
• Defining An Index
• A Partitioning Index
• Create Index Paramaters
• Indexes of Large / EA Enabled Tablespaces
• Index Design Considerations
• Alter Index
• Views
• Read Only Views
• Views - With Check Option
• Creating A View of Two Tables
• View Materialisation
• View Design Considerations
• The Alter Statements
• The Drop Statements
• LARGE OBJECTS
• Large Objects (LOBs)
• LOB Definition And Manipulation
• Base Table Definition
• Altering A Table to Add LOB Data
• ROWID Data Type
• LOB Tablespace and Auxiliary Table Requirements
• LOB Tablespace Considerations
• LOB Tablespace Definition
• Auxiliary Table Definition
• Auxiliary Table - Index Definition
• LOB Authorisations
• Dropping LOB Tables
• LOB Data Manipulation
• LOB Tablespace Logging
• Load Utility - LOB Data
• Loading ROWIDs
• LOCKING
• Implications of Concurrent Processing
• IMS Resource Lock Manager (IRLM)
• DB2 Locking Methods
• The Lock Table Statement
• Lock Modes
• When Locks Are Acquired
• When Locks Are Released
• Isolation Levels
• Controlling The Isolation Level At SQL Level
• Keep Update Locks
• Unit of Work In TSO
• Commit And Rollback
• Declaring Cursors With Hold
• CICS Issues
• IMS Issues
• Savepoints
• Savepoint Definition
• Savepoints - Considerations and Restrictions
• Transaction Deadlocks
• Design Considerations
• Commit, Rollback And Restart
• Unit of Work In TSO
• The Need For Application Restart
• Recommendations for Application Design
• SCHEMAS
• Schemas
• The Grant Schema Statement
• Schema Path - Bind Option
• Current Path - Special Register
• Overriding the Search Path
• DISTINCT TYPES
• Distinct Types (User-defined Data Types)
• Create Distinct Type Statement
• UDT Allowable Operations
• Generated Cast Functions
• Using Cast Functions
• Defining a Sourced Function with Distinct Types
• Defining a Sourced Function for an Operation
• CAST Specifications
• Cast Specifications in Application Programs
• Distinct Type Privileges
• Catalog Information
• Dropping Distinct Types
• TRIGGERS
• Triggers
• Trigger Parts
• Before and After Triggers
• Trigger Examples
• Allowable Combinations
• Error Handling
• Trigger Cascading
• Ordering of Multiple Triggers
• Trigger Authorisation
• Catalog Information for Triggers
• Removing Triggers
• REFERENTIAL INTEGRITY
• What Is Referential Integrity?
• Parent and Dependent Tables
• The Primary Key
• The Foreign Key
• Referential Constraint Rules
• Constraint Names
• More Complex Referential Structures
• Check Pending Status
• Resetting Check Pending Status
• Referential Integrity Access
• Design Considerations
• TABLE CHECK CONSTRAINTS
• Overview of Check Constraints
• Constraint Syntax
• Allowable Constraints
• When are Constraints Enforced?
• Current Rules
• When Is Check Pending Set
• Catalog Changes
• Authority Changes
• SECURITY
• Using DB2 Views
• Controlling Resource Access - Data Control Language
• DB2 Special Users
• Database Privileges
• Table Privileges
• Authorisation of Plans
• Plan / Package Privileges
• Retain Execution Authority
• Primary, Secondary And Current AUTHIDs
• Grant Examples
• Revoke Examples
• EXPLAIN OVERVIEW
• The Explain Function
• SQL Explain Statement
• Plan Table Layout
• Plan Table Column Definitions
• Predicate Evaluation
• Indexable Predicates
• Stage 1 and Stage 2 Predicates

Course Environment The Course runs in the following environment:

• IBM Mainframe

Course Format: Practical sessions make up a large part of the course, allowing delegates to demonstrate and reinforce the lectures given. During these sessions the delegate will follow a case study to design and build an efficient database application system. The comprehensive Student Guide supplied is fully indexed serving as a useful reference tool long after the course has finished. Delegates will also be able to access a free help-line with technical questions relating to topics covered on the course.