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 #:WA2162

DB2 for z/OS Database Administrators - Boot Camp Training

This comprehensive ten-day 'Boot Camp' course provides attendees with an accelerated learning approach to the DBA role in a DB2 for z/OS environment. The course is ideal for anyone new to the DBA role or those who coming to DB2 for z/OS from another database environment.  The regular, hands-on lab exercises give students the opportunity to try out their newly-gained skills immediately. On successfully completing this boot camp, attendees will have reached the skill level needed to enable them to efficiently and effectively manage their DB2 for z/OS database environment.


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

  • describe the relational concepts and theory, and the advantages of a RDBMS
  • list the components of the DB2 system, their function, and how they are controlled
  • describe the internal structure and components of DB2 UDB for z/OS databases
  • utilise all the functions available with DB2 Interactive (DB2I
  • 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.
  • describe how large object and XML data is held, and its implications on data recovery
  • design and use materialized query tables, clone tables and temporal tables and the additional SQL statements associated with them
  • explain the use of distinct data types and user-defined functions within DB2 for z/OS
  • understand how and when triggers may be used and code them
  • understand and control the locking strategy used by DB2 to maintain data integrity and concurrency
  • assess security and related requirements
  • describe the concept of online schema management and versioning
  • use DB2 commands to monitor and control DB2 objects, processes and operations
  • describe and use the full complement of DB2 online data and recovery utilities
  • understand and use the DB2 stand-alone utilities
  • explain the program preparation process for DB2 applications
  • understand the DB2 optimizer and the use of EXPLAIN in determining access path, statement efficiency and choosing optimal database and code design
  • plan for application monitoring and tuning
  • use DB2 traces to record performance monitoring information to identify problem areas and identify tuning opportunities
  • understand and interpret information in DB2PE accounting and statistics reports.


New z/OS Database Administrators and/or those coming new to DB2 for z/OS from another database environment.


The abitiy to use TSO/ISPF and a working knowledge of z/OS JCL.


10 days

Outline of DB2 for z/OS Database Administrators - Boot Camp Training

Day 1

DBMS Overview

What is a database?; comparison of hierarchical, network & relational database management systems; a brief history of DB2.

Relational Theory & Concepts

Relational theory; relational model; relational algebra; entity, referential and user-defined integrity.

DB2 System & Operations

DB2 address spaces; DB2 attachments; storage pools; logging methodology; basic operations; DB2 commands.

Day 2

DB2 Interactive (DB2I)

DB2I panels; SPUFI; other functions.

DB2 Data Objects

DB2 objects and the relationships between them; DB2 catalog & directory; interrogating the DB2 catalog.

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.

Day 3

DB2 Index Design & Usage

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.

DB2 Physical Database Design & DDL

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

Using Views & Temporary Tables

Views; Nested table expressions; Common table expressions; Recursive SQL; View options; Created temporary tables; Declared temporary tables; Table comparisons.

Day 4

Materialized Query, Clone & Temporal Tables

Materialized query tables; maintaining data in MQTs; Using MQTs; Automatic Query Rewrite (AQR); Clone tables; Using clone tables; Exchanging data; System period temporal tables; Application period temporal tables; Using temporal tables; FROM period specification.

Distinct Data Types & User Defined Functions

Distinct data types; Casting data; Sourced user defined function; External user defined function; User defined SQL functions; Table functions; Inline and non-inline scalar functions; Invoking functions; Identifying functions; Function resolution.


Triggers; Trigger components; Trigger options; Trigger body statements; BEFORE, AFTER & INSTEAD OF triggers; Trigger examples; Trigger performance.

Day 5

Online Schema Management & Versioning

Enhanced ALTER statement; altering data types; altering index keys; altering tablespace types and attributes; restrictions on altering objects; impact of altering objects; pending definition changes; versioning; reclaiming versions.

Partition Management

Index defined vs table defined partitioning; adding new partitions; rotating partitions; changing partition boundaries; rebalancing partitions.

DB2 Locking & Concurrency

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

DB2 Security & DCL

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.

Day 6

DB2 Pagesets

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

DB2 Utilities Overview

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

Online Data Utilities


Day 7

Online Recovery Utilities


Stand-Alone Utilities


Catalog & Directory Recovery

Catalog / Directory recovery implications; Recovery sequence; Catalog & Directory point-in-time recovery; Recovery implications - DEFINE option; Recovery implications - FASTSWITCH; Recovery implications - identity columns & sequences; System-wide points of consistency.

Day 8

Program Preparation & Execution

Precompile, bind, program preparation and execution; plans and packages; BIND PLAN; BIND PACKAGE; BIND QUERY; invoking TSO attachment; CICS attachment; Resource Control Table; DB2CONN using CEDA; DB2ENTRY using CEDA; DB2TRAN using CEDA; controlling CICS attachment; IMS attachment; IMS Subsystem Member (SSM); Subsystem Member relationships; Resource Translation Table (RTT).

The DB2 Optimizer

Input to the Optimizer; Catalog statistics; Filter factors; Filter factor and clustering; Filter factor examples; Influencing the Optimizer; Influencing the Optimizer by manually adjusting statistics, modelling production values & using optimization hints'; Catalog statistics; RUNSTATS options; Statistics columns; RUNSTATS - examples.


EXPLAIN; PLAN_TABLE; PLAN_TABLE additions; DSN_STATEMNT_TABLE; DSN_FUNCTION_TABLE; EXPLAIN: basic access paths, multi-index access, nested queries examples.

Day 9

DB2 Performance Overview

What is 'performance'?; Performance objectives; Workload categories; Service Level Agreements; Performance factors; System parameters; EDM pool size; Buffer pool sizes; Bufferpool thresholds; Bufferpool development; Database design factors; Application design factors; DB2 traces.

Database Design Factors Review

Normalisation forms; De-normalisation considerations; Table creation; Null attributes; Variable length columns; Column considerations; Constraint and domain management; Tablespace considerations; Space allocation and free space; Work file considerations; Data set placement; Index structure; Creating indexes; When and when not to index; Indexes and clustering; non-key columns in unique indexes; Composite keys; Reorganising indexes.

Application Design Factors Review

Predicates; Access paths; Indexable and non-indexable predicates; SQL statement processing; Stage 1 and stage 2 predicates; Summary of predicate processing; Predicate evaluation sequence; Sequential prefetch; List prefetch; Index lookaside; Joins; Join transformation; Join method; Subqueries; Non-correlated vs. correlated subqueries; UNION; Case expressions; Union and case performance; Table expressions; Temporary tables; Table comparisons.

Day 10

DB2 Traces

Trace types; Trace destination; Accounting trace classes; Audit trace classes; Statistics trace classes; Performance trace classes; Monitor trace classes; Global trace classes; IFCID types; Other trace options; Controlling traces.

Monitoring Application Performance

Monitoring response times; Suspensions; The accounting reports; Elapsed and class 2 time distribution; Highlights of application performance; Class 1, class 2, and class 5 times; Wait times (class 3 suspensions); Package times and suspensions (class 7 and class 8); Application termination data; Locking activity; Buffer pool activity; Group buffer pool activity; SQL activity; RID list and ROWID processing; Stored procedures, UDFs and triggers; Dynamic SQL and LOB storage; Query parallelism; DDF activity; Data sharing locking.

Performance Analysis

EXPLAIN information; DB2PM reports; short accounting reports; long accounting reports; report usage summary; a general approach to problem analysis in DB2.

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