DB20 - DB2 z/OS Database Design - 3 Days

Course Description

This course provides advanced DB2 training, and is aimed at staff requiring a detailed knowledge of the DB2 Logical and Physical design issues, and training in how efficient DB2 applications are implemented and maintained.


Familiarity with using DB2 on a z/OS platform is advantageous but not essential.


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 Identity Columns & Sequences
  • design and create Materialized Query Tables
  • design and create triggers
  • understand the performance issues involving the use of large objects


The course runs in the following environment:

  • IBM Mainframe
  • DB2 for z/OS Versions 10, 11 or 12


For on-site courses (i.e. at your premises), we are more than happy to tailor the course agenda to suit your exact requirements. In many cases, we are able to build your in-house standards and naming conventions into the delivered course.

Course Details

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

Why Analyse?
Who Will be Involved in the Analysis?
Entity Relationships - A Worked Example
Entity Relationship Diagrams
Relationship Types
Many to Many Relationships - Association Entities
Common Parentage

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
Identification of Keys

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

The Structure Of Db2 Objects
Definition Of Db2 Objects - Ddl
Storage Groups

Tablespace Organisation
Page And Row Organisation
The Simple Tablespace
The Segmented Tablespace
The Partitioned Tablespace
Maximum Number Of Partitions
Create Tablespace Parameter Reference
Version 9 - Universal Tablespaces
Universal Tablespace Benefits
Partition-By-Growth Tablespace
Creating A Partition-By-Growth Tablespace
Partition-By-Growth Tablespaces - Behaviour
Partition-By-Growth - Partition Allocation
Partition-By-Growth Tablespaces - Insert
Partition-By-Growth Tablespaces - Reorg
Partition-By-Growth Tablespaces - Other Utilities
Partition-By-Range Tablespace
Partition-By-Range Tablespaces - Considerations
Universal Tablespaces - Catalog Information
Storing Large Objects
The Lob Tablespace
LOB Base Table Definition
LOB Tablespace Definition
LOB Auxiliary Table Definition
LOB Auxiliary Table - Index Definition
LOB Enhancements – Inline LOBS
Data Compression
Compress on Insert / Merge / Load
Pending Changes

Copying Table Definitions
Rename Table
Db2 Column Types
Design Tips for Columns
Implicitly Hidden Columns
Reordered Row Format
Moving to Reordered Row Format
Null Values
Nulls - Design Tips
Variable Timestamp Precision
Variable Timestamp Precision – Current Timestamp
Variable Timestamp Precision – Row Change Timestamps
Implicit Time Zone
Timestamp with Time Zone – Assignment and Comparison
Global Temporary Tables
Declared Temporary Tables
Declared Temporary Table Considerations
Declared Temporary Tables - Comparisons
Db2 Synonym
Db2 Alias
Read Only Views
Views - With Check Option
Creating A View Of Two Tables
View Materialisation
View Design Considerations
Altering a Table
Altering Tables and Indexes
Data Type Changes
Alter Data Type - Performance Implications
Column Renaming
Column Renaming Restrictions
The Drop Statements
Create Table - Implicit Database Creation
Implicit Database Creation - Considerations
Create Table - Implicit Tablespace Creation
The Truncate Statement
Truncate Examples
Hash Tables
Tablespaces for Hash Tables
Creating a Hash Table

Clone Tables - Fast Data Replacement
Why Use Clone Tables?
Creating A Clone Table
Dropping A Clone Table
Clone Table Considerations
Creating a Clone - Catalog Activity
Creating a Clone - Index and Lob Catalog Activity
Clone Tables - The Exchange Command
Clone Tables - Exchange Considerations
Clone Tables - Locking Considerations
Clone Tables - DB2 Commands
Clone Tables - Authority

Db2 Indexes
Index Organisation - The B Tree Index
Backwards Index Scan
Index Clustering
Changing the Clustering Sequence
Non-Unique Indexes
Defining An Index
Index Definition - Parameter Reference
Partitioning Indexes
Index Page Size
Index Page Size Specification
Index Compression
Index Compression Considerations
Index Compression - Estimation
Index Compression - Dsn1 Sample Output
Table vs. Index Compression Comparisons
Index On Expression
Index On Expression Considerations
Index Design Considerations
Altering a Column's Data Type - Impact Upon Indexes
Alter Data Type - Index Availability
Index Renaming

Increased Number Of Partitions
Maximum Number Of Partitions
Considerations when Partitioning
Data Set Names
Table-Controlled Partitioning
Table-Controlled Partitioning Example
Altering a Table to Add Table Partitioning
Converting from Index to Table Partitioning
Table-controlled Partitioning Catalog Changes
Index-controlled Partitioning Terminology
Table-controlled Partitioning Terminology
Index classification
Partitioned / Non-Partitioned Example
Partitioned / Partitioning Example
Clustering Within Partition
Changing the Clustering Sequence
Data Partitioned Secondary Indexes
Creating a DPSI
Design Considerations - Why Partition At All?
Design Considerations - Non-Partitioned Index Problems
Design Considerations - DPSI Benefits
DPSI Benefits - Partition Pruning
Design Considerations - DPSI Problems
DPSIs and Utilities
DPSIs and Planning
Partition Management
Adding Partitions
Adding Partitions - Considerations
Index-controlled to Table-controlled partitioning
Rotating Partitions
Rotate Partition Syntax
Rotating Partitions - Considerations
Altering Partition Boundaries
Rebalancing Partitions using Reorg
Rebalancing Partitions - Considerations
Considerations for User Applications
Display Database Command - Increased Partition Support
Display Database Examples

Temporal Tables and Versioning
Temporal Tables – Versioning Example
System Temporal Tables – Data Access
Temporal Tables – Considerations
Temporal Tables – Application Controlled
Business Temporal Tables – Data Selection
Business Temporal Tables – Update and Delete
Business Temporal Tables – Update Example
Business Temporal Tables – Delete Example

Identity Columns
Identity Columns - Examples
Altering Identity Columns
Altering Identity Columns - Parameters
Identity Columns - Retrieving the Generated Number
Identity Columns - Data Sharing Implications
Using Identity Columns with the Load Utility
Create Sequence Syntax
Sequence Ordering
Altering Sequences
Dropping Sequences
Using Sequences in Applications
Sequences - Considerations and Restrictions
Sequence Application Examples
Sequences and Identity Columns Comparison

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

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

Overview of Check Constraints
Constraint Syntax
Allowable Constraints
When are Constraints Enforced?
Current Rules
When Is Check Pending Set
Catalog Changes
Authority Changes

What Are Materialized Query Tables?
MQT Features
Creating an MQT
Create MQT Example
Altering an MQT
Alter MQT Example
MQT Fullselect Features / Restrictions
Refresh Table
Populating User Maintained MQTs
Automatic Query Rewrite using MQT
Enabling Automatic Query Rewrite
Enabling Automatic Query Rewrite - DDL Options
Enabling Automatic Query Rewrite - Special Registers
AQR - Using Both Registers
Enabling Automatic Query Rewrite - System Properties
Enabling Automatic Query Rewrite - Query Properties
Determining if Query Rewrite Occurred
MQTs and Referential Integrity
MQTs and RI - Informational Constraints

System Privileges
Database Privileges
Use Privileges
Table Privileges
Plan / Package Privileges
Primary, Secondary And Current Authids
Implicit Privileges Of Object Owners
Revoking Privileges
DB2 Multilevel Security - Seclabel Definition
Seclabel Behaviour
Seclabel Behaviour with Sql
The Existing 3 Tier Security Model
3 Tier Problems
Security Enhancement - Trusted Contexts / Roles
Trusted Contexts
Creating Trusted Contexts
Trusted Context / Role Examples
Row and Column Access Control
Scalar Functions for Row / Column Permissions
Row Access Control - Defining Row Permissions
Activating Row Permissions
Column Access Control - Defining Column Masks
Activating Column Masks
Row and Column Access – Restrictions
Row and Column Access – Explain Output
Row and Column Access – Catalog Changes

The Explain Function
SQL Explain Statement
Plan Table Layout
Plan Table Column Definitions
Predicate Evaluation
Indexable Predicates
Stage 1 and Stage 2 Predicates

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.

Course Enquiry