Db2 for z/OS: Database Design, Implementation & 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.

The comprehensive and challenging practical exercises in this course complement the formal tuition sessions, ensuring full understanding.

This course is also available for one-company, on-site presentations and for live presentation over the Internet, via the Virtual Classroom Environment service.

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

• describe the internal structure and components of Db2 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.

DBMS Overview

Database Management Systems; Hierarchical database structure; Network database structure; Relational database structure; Database comparison; Table structure; A brief history of Db2.

Relational Theory & Concepts

Dr. E F Codd's 12 rules; The relational model; Structure - tables; Structure - rules for columns and rows; Structure - candidate keys; Structure - primary and alternate keys; Structure - foreign keys; Manipulation - relational algebra; Manipulation: projection, restriction, join, union, intersection, difference, product; Integrity - domain and user-defined integrity; Integrity - entity and referential integrity; Referential integrity - terminology; Tablespace set; Referential integrity - defining constraint; Constraint definition restrictions; Data manipulation restrictions; Catalog entries; Referential Integrity and INSERT; Referential Integrity and UPDATE; Referential Integrity and DELETE; Referential Integrity review.

Table Design

Conventional file systems; Database systems; Data concepts; Entity-relationship diagram; Functional dependence; Normalisation; First normal form; Second normal form; Third normal form; Normalisation summary; De-normalisation; De-normalisation integrity exposures; Table creation; DB2 data types; Null attributes; Variable length columns; Variable length row formats; Large object data; XML data; Identity columns; GENERATED options; Column considerations; Constraint and domain management; Constraint catalog tables; Temporary tables; Created temporary tables; Declared temporary tables; Table comparisons.

Db2 Index Usage & Design

Predicates; Accessing data - table or tablespace scan; Sequential prefetch; Index structure; Index page splits; Using index - matching index scan; Using index - non-matching index scan; Using direct row access; Indexable and non-indexable predicates; Statement processing; Stage 1 and stage 2 predicates; Summary of predicate processing; Predicate evaluation sequence; List prefetch; Index lookaside; Index considerations; Creating indexes; Composite keys; Clustering; Variable length index keys; Index compression; Index key randomisation; Indexes on expressions; Non-key columns in unique index; Further considerations; Reorganising indexes; Hash access; Hash table structure; Defining a hash table.

Physical Database Design & Data Definition Language

Pagesets; Pageset structure; Tablespace types; Non-segmented tablespace; Segmented tablespace; Partitioned tablespace; Universal tablespace; Large object tablespace; XML tablespace; Data compression; Db2 data objects overview; Storage group; Database; Tablespace; Determining tablespace type; Table; Indexspace / index; Views; Synonym; Alias; Deleting objects; Performance considerations: space allocations, data set placement, free space, statistics, buffer pools.

Db2 Locking & Concurrency

Controlling concurrent access; Claims and drains; Claim classes; Drains; Utility restrictive states; Transaction locking; Reasons for locking - preventing lost updates; Reasons for locking - preventing reads of uncommitted data; Reasons for locking - allowing repeatable reads of data; Lock control; Lock options - installation parameters; Lock options - tablespace creation; Lock options - SQL statements; Locking hierarchy; Lock compatibility - row and page locks; Lock compatibility - table and tablespace locks; Lock options - BIND parameters; BIND - ACQUIRE and RELEASE parameters; BIND - ISOLATION; BIND - CURRENTDATA; Lock avoidance; Lock avoidance example; Locking and concurrency recommendations; Monitoring locking - DB2 commands; Monitoring locking - LOCKINFO; Monitoring locking - Db2PM / Db2PE reports.

Db2 EXPLAIN

EXPLAIN; EXPLAIN table enhancements; EXPLAIN syntax enhancements; PLAN_TABLE (Db2 version 1); PLAN_TABLE (Versions 2 to 6); PLAN_TABLE (Db2 versions 7 to 11); DSN_STATEMNT_TABLE; DSN_FUNCTION_TABLE; EXPLAIN example 1 (basic access paths); EXPLAIN example 2 (multi-index access); EXPLAIN example 3 (nested queries).

Db2 System Architecture

Architecture overview; Working Storage Areas; Buffer, Sort, RID & EDM pools; Buffer Pools - 64 bit addressing; EDM pool; Sort pool; RID pool; Db2 Attachments; System data sets & databases; Db2 objects; Object characteristics; SQL overview; Db2 Interactive (Db2i); Basic Db2 operations; Db2 commands; Db2i commands panel.

Db2 Pagesets

Pagesets; Pageset structure; Page structure; Space map pages; Tablespace data pages; Row header information; Tablespace types; LOB and XML tablespaces; Determining tablespace type; Changing tablespace type; Index structure; Data compression.

Db2 Security & Data Control Language

Authorisation identifiers; Controlling DB2 access; GRANT / REVOKE overview; Controlling cascading REVOKE; System privileges; Usage privileges; Database privileges; Table & view privileges; Package, collection & plan privileges; Distinct type or JAR privileges; Function & procedure privileges; Schema privileges; Administrative privileges; Additional administrative privileges; Install SYSADM, SYSOPR & SECADM; Using RACF for Db2 security; Mapping Db2 authorisation to RACF; Db2 objects and RACF classes; RACF profiles for Db2; Multi-level security; Security labels; Row and column access control; Defining row permissions; Defining column masks.

Managing Data with Db2 Utilities - Overview

Db2 utilities; Data backup & recovery utilities; System backup & recovery utilities; Data integrity & consistency utilities; Other online utilities; Stand-alone utilities; Using online utilities; Utility control statements; Db2i utilities - main menu; Db2i utilities - Data Set Names panel; Db2i utilities - Control Statement Data Set Names panel; Controlling utilities; Using LISTDEF & TEMPLATE; Using pattern matching; LISTDEF syntax; TEMPLATE ; Data set sizing; Dataset naming variables; TEMPLATE syntax; OPTIONS syntax.

Online Data Utilities

RUNSTATS utility; LOAD utility; LOAD utility - SHRLEVEL; LOAD pending states; LOAD examples; UNLOAD utility; CHECK DATA utility; CHECK DATA - SHRLEVEL; CHECK DATA - SCOPE; CHECK INDEX utility; CHECK LOB utility; REORG utility; REORG utility - SHRLEVEL; REORG utility - BUILD2 phase (DB2 Version 8); REORG utility - BUILD2 phase (DB2 Version 9); REORG utility - FASTSWITCH; STOSPACE utility; Repair utility.

Online Recovery Utilities

Recovery components - historic; Recovery components - enhancements; COPY utility; COPYTOCOPY utility; MERGECOPY utility; REPORT utility; QUIESCE utility; RECOVER utility; Partial recovery - historic; Partial recovery - BACKOUT YES; Recovery and referential integrity; Recovery with large object or XML data; REBUILD INDEX utility; MODIFY utility; BACKUP SYSTEM; RESTORE SYSTEM utility.

Stand-alone Utilities

Stand-alone utilities; =DSN1COPY utility; DSN1PRNT utility; DSN1LOGP utility; DSN1COMP utility; DSN1CHKR utility; DSNJU004 utility; DSNJU003 utility; DSNJLOGF utility.

Performance Tuning

Trace types; Trace destination; Trace classes; IFCIDs activated; Constraints and filters; Controlling traces; Using trace data; Reports and traces (short / long); Monitoring response times; Elapsed and class 2 time distribution; Class 3 suspensions; Locking activity; Data sharing locking activity; Buffer pool activity; Group buffer pool activity; SQL activity; ROWID and RID list activity; Stored procedure, UDF and trigger activity; EDM pool activity; Subsystem services.

Practical Exercises

Using data utilities; using backup & recovery utilities; using stand-alone utilities.