DB2 for z/OS: Database Design & Implementation Training

Course #:WA2274

DB2 for z/OS: Database Design & Implementation Training

This course provides a detailed explanation of the methodology to be followed in order to design and implement efficient DB2 for z/OS databases. The course discusses all aspects of database design from the logical data model and implementation of the physical design, through to ongoing performance monitoring of the resultant database.

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 of DB2 for z/OS: Database Design & Implementation Training

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.
We regularly offer classes in these and other cities. Atlanta, Austin, Baltimore, Calgary, Chicago, Cleveland, Dallas, Denver, Detroit, Houston, Jacksonville, Miami, Montreal, New York City, Orlando, Ottawa, Philadelphia, Phoenix, Pittsburgh, Seattle, Toronto, Vancouver, Washington DC.