Objectives

On successful completion of this course attendees will be able to:

  • describe the internal structure and components of DB2 UDB for z/OS databases
  • design and assess a logical database model
  • design and implement efficient physical databases from the logical model using relevant options to create and amend storage groups, databases, table spaces, tables, indexes, aliases, synonyms, etc.
  • understand and implement an effective locking strategy and use DB2 commands and other facilities to monitor locking for performance
  • use EXPLAIN to evaluate the effectiveness of the database design.

Audience

Those responsible for the design and implementation of DB2 for z/OS databases.

Prerequisites

Attendees should be familiar with the DB2 environment and have knowledge of SQL or have attended RSM's courses DB2 for z/OS: Introduction, Concepts & Facilities and DB2 for z/OS: SQL for Query Users.

Duration

2 days

Outline for DB2 for z/OS: Database Design & Implementation

1. The Relational Concepts Overview

  • Data structure
  • Data manipulation
  • Data integrity.

2. Table Design

  • Data concepts
  • E-R diagrams
  • Normalisation
  • Table creation
  • DB2 data types and attributes
  • Cconstraint & domain management
  • Temporal tables
  • System period data versioning
  • Temporary tables.

3. Index Design

  • Data access paths
  • Predicates, index usage and I/O types
  • Index structure
  • When to index
  • Composite keys
  • Clustering
  • Index key randomisation
  • Indexes on expressions
  • Non-key columns in unique indexes
  • Creating indexes.

4. Physical Database Design & Data Definition Language

  • Pageset structure
  • Tablespace types and defaults
  • DB2 object definition
  • Understanding and choosing options for performance.

5. DB2 Locking

  • Resource serialisation
  • Claims and drain
  • Concurrent applications and utility processing
  • Lock control
  • Locking options
  • Lock compatibility
  • Bind options
  • Lock avoidance
  • Monitoring locking.

6. Evaluating Database Design

  • The DB2 Optimizer
  • Input to the Optimizer
  • statistics
  • Filter factors and column distribution
  • EXPLAIN tables
  • Using EXPLAIN to evaluate access paths
  • Influencing the Optimizer
  • Optimisation hints.