DB2 for z/OS: Application & Database Design Training

Course #:WA2159

DB2 for z/OS: Application & Database Design Training

This course teaches the skills required by Applications Developers and Data Base Administrators to develop and design efficient DB2 applications and databases in all DB2 for z/OS environments.


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

  • normalise data and know when to denormalise
  • understand how and when to implement constraints
  • design and implement physical databases covering: storage groups, databases, table spaces, tables, indexes, aliases, synonyms, etc.
  • design and implement efficient indexes
  • design efficient SQL
  • understand the differences in design for online and batch tasks
  • assess security and related requirements
  • be aware of the implication of locking, commit frequency, etc., on concurrency
  • use EXPLAIN to evaluate design performance.


IT developers and designers, DB2 database administrators and application programmers.


Attendees should have a basic knowledge of DB2 concepts (as provided by the RSM course DB2 for z/OS: Introduction, Features & Facilities).


4 days


Outline of DB2 for z/OS: Application & Database Design Training

1. Logical Database Design

  • Data concepts
  • E-R diagrams
  • Normalisation
  • Denormalisation.

2. Table Design

  • Data types, Null attributes
  • Column considerations
  • Constraint and domain management
  • Temporal tables
  • System period data versioning
  • Temporary tables
  • Views, synonyms and aliases.

3. Index Design and Usage

  • When to index
  • Index structure
  • Clustering; Composite keys
  • Indexes on expressions
  • Non-key columns in unique indexes
  • Access paths
  • Predicate evaluation
  • Indexable and non-indexable predicates
  • Stage 1 and Stage 2 predicates
  • Predicate evaluation sequence.

3. Physical Database Design

  • DB2 objects including storage groups, databases, tablespaces, etc.
  • Performance considerations
  • Accessing currently committed data.

4. DB2 Optimiser & EXPLAIN

  • Optimiser input
  • Catalog statistics
  • Using EXPLAIN
  • Optimisation hints.

5. SQL Coding Techniques

  • Single row vs cursors
  • Scrollable cursors
  • Subqueries
  • joins
  • Nested table expressions
  • Common table expressions
  • Unions vs case expressions

4. Object Oriented SQL Extensions

  • Distinct types
  • Triggers
  • User-defined functions

5. Locking & Concurrency

  • Lock control
  • locking options
  • lock compatibility
  • bind options
  • lock avoidance

6. DB2 Security and DCL

  • The grant and revoke statements
  • Explicit and implicit privileges
  • Administrative authorities
  • Using RACF profiles
  • Multilevel security
  • Row & column level security

7. Online Application Design

  • Hot spots
  • Concurrency
  • Deadlocks
  • Save points
  • Thread reuse

8. Batch Application Design

  • Caching data
  • Commit frequency and performance
  • Commit and cursors
  • Utilities vs application programs

9. Program Preparation & Execution

  • Precompiler
  • Bind options
  • Package options
  • Versions
  • Program execution

10. Practicals

  • Normalisation & Table design
  • Table creation
  • Using EXPLAIN
  • Triggers
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.