Web Age Solutions Inc
Providing Technology Training and Mentoring For Modern Technology Adoption
Web Age Aniversary Logo
US Inquiries / 1.877.517.6540
Canadian Inquiries / 1.877.812.8887
Course #:WA2238

DB2 for z/OS: Database Design, Implementation and Administration Training

This is the definitive course for DB2 DBAs. It teaches the skills that are essential for administrators, enabling them to provide effective and efficient database design, implementation and support. It explains the database design process, from the logical database model right through to the implementation of the physical database. The system architecture, operational facilities and utilities used in a DB2 for z/OS environment are fully explained and demonstrated. In addition, monitoring, security and recovery techniques are also covered.

This course is suitable for all environments up to DB2 10.

The comprehensive and challenging practical exercises complement the formal tuition sessions.

What you will Learn

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
  • describe the DB2 environment, and understand the use of key DB2 components such as buffer pools, the DB2 catalog and directory, logging data sets, etc.
  • understand and assess security and related requirements
  • understand and assess security and related requirementsuse DB2 commands to monitor and control DB2 objects, processes and operations
  • understand and use the full complement of DB2 online data and recovery utilities
  • understand and use the DB2 stand-alone utilities
  • use DB2 traces to record performance monitoring information
  • understand and interpret information in DB2PM accounting and statistics reports.


DB2 database administrators.


Attendees should be familiar with the DB2 environment and have knowledge of SQL or have attended the DB2 concepts and SQL courses.


5 days

Outline of DB2 for z/OS: Database Design, Implementation and Administration Training

1. Relational Concepts Overview

  • Data structure
  •  Data manipulation
  •  Data integrity.

2. DB2 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. DB2 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.

7. The DB2 Environment

  • DB2 address spaces
  •  Attachments
  •  Log data sets
  •  Catalog
  •  Directory
  •  Buffer pools
  •  bufferpool thresholds
  •  bufferpool hit ratio
  •  usage considerations.

8. DB2 Pagesets

  • Pageset structure
  •  Tablespace types and defaults
  •  Index structure
  •  Data compression.

9. DB2 Security & Data Control Language

  • The grant and revoke statements
  •  Explicit and implicit privileges
  •  Object privileges
  •  Administrative privileges
  •  Install SYSADM/SYSOPR
  •  Using RACF profiles
  •  Multilevel security
  •  row & column level security.

10. DB2 Utilities Overview

  • Online utilities
  •  stand-alone utilities
  •  control statements
  •  DB2I Utilities option
  •  using LISTDEF
  •  using TEMPLATE
  •  OPTIONS statement.

11. Online Data Utilities

  •  LOAD
  •  REORG
  •  REPAIR.

12. Online Recovery Utilities

  • Recovery components
  •  COPY

13. Stand-Alone Utilities

  •  DSNJU004
  •  DSNJU003

14. Catalog & Directory Recovery

  • Catalog / Directory recovery
  •  Recovery sequence
  •  Point-in-time recovery implications.

15. Performance Monitoring

  • DB2 traces
  •  Controlling traces
  •  DB2PM Accounting reports
  •  DB2PM Statistics reports.

16. Practical Exercises

  • Using data utilities
  •  using backup & recovery utilities
  •  using stand-alone utilities


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.
US Inquiries / 1.877.517.6540
Canadian Inquiries / 1.877.812.8887