Objectives

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.

Audience

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

Prerequisites

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

Duration

10 days

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

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

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

RUNSTATS; LOAD; UNLOAD; CHECK DATA; CHECK INDEX; CHECK LOB; REORG; STOSPACE; REPAIR.

Day 7

Online Recovery Utilities

Recovery components; COPY; COPYTOCOPY; MERGECOPY; REPORT; QUIESCE; RECOVER; REBUILD; MODIFY; BACKUP SYSTEM; RESTORE SYSTEM.

Stand-Alone Utilities

DSN1COPY; DSN1PRNT, DSN1LOGP; DSN1COMP; DSN1CHKR; DSNJU004; DSNJU003; DSNJLOGF.

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.

DB2 EXPLAIN

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.