Db2 for z/OS: Database Administrator Boot Camp

What you will learn

On successful completion of this course you 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 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 DBb 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.

Who Should Attend

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

Prerequisites

The ability to use TSO/ISPF and a working knowledge of z/OS JCL. These skills can be gained by attending the RSM courses TSO/ISPF Users' Workshop and z/OS JCL Workshop Part 1 - Foundation Skills.

Duration

9 days

Fee (per attendee)

P.O.A.

This includes free online 24/7 access to course notes.

Hard copy course notes are available on request from rsmshop@rsm.co.uk

at £50.00 plus carriage per set.

Course Code

DBBC

Contents

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 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 datasets & databases; Db2 objects; Object characteristics; SQL overview; Db2 Interactive (Db2i); Basic Db2 operations; Db2 commands; Db2i commands panel.

Day 2

Introduction to Db2 Interactive (Db2i)

Db2i primary option menu; Db2i option D - Db2i defaults; Db2i option 1 - SPUFI; Current SPUFI defaults; SPUFI SQL statement creation; Browsing SPUFI output; SPUFI commit or rollback panel; SPUFI AUTOCOMMIT options; Db2i option 2 - DCLGEN (Declarations Generator); DCLGEN output; Db2i option 3 - Program Preparation;Db2i option 4 - Precompile; DB2I option 5 - Bind / Rebind / Free; DB2I option 6 - Run;Db2i option 7 - Db2 commands; Db2i option 8 - Db2 utilities; Query Management Facility; Reporting options - SPUFI versus QMF 154; SPUFI report; QMF report.

Db2 Data Objects

Db2 objects; The Db2 database; Tablespace; Determining tablespace type; Table; Synonym & alias; View; Index; Index structure; Storage group; User defined datasets; Schema; Trigger; Db2 catalog & directory.

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.

Temporary Tables & Views

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

Day 4

Specialised Table Types

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

Need for distinct types; defining and using distinct types; distinct type security.

User-defined Functions

User-defined functions; User-defined function types; Sourced user-defined function; External user-defined scalar function; External user-defined table functions; User-defined SQL scalar functions; User-defined SQL scalar functions; User-defined SQL table functions; Identifying functions; Invoking functions; Function resolution; Function Security; Controlling User Defined Functions.

Triggers

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

Day 5

Online Schema Maintenance & Versions

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; impact on user tasks.

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 Security & DCL

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; 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 (DB2 10); Defining row permissions; Defining column masks.

Day 6

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 ; Dataset sizing; Dataset naming variables; TEMPLATE syntax; OPTIONS syntax.

Online Data Utilities

RUNSTATS utility; LOAD utility; LOAD utility - SHRLEVEL; LOAD pending states; 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), BUILD2 phase (Db2 Version 9); REORG utility - FASTSWITCH; STOSPACE utility; REPAIR utility.

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

Problem Determination

Basic code structure; Db2 subcomponents; Db2 control block structure; Db2 page sets; Page set structure; Index structure; Index keys and pointers; Data navigation; RIDs, map ids, pointers and holes; Row headers; Row data format; Versions and system pages; OBDREC entries; Version examples; Identifying and handling problems; Basic dump analysis; Diagnosis tools: traces, utilities; Possible problem areas; Active log out of space; Active log I/O errors; Archive log I/O errors; BSDS errors; Forced single BSDS mode; Recovering BSDS; Data errors; Down level datasets.

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.

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.

Day 9

Db2 EXPLAIN

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

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.

Performance Analysis

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

Day 10

Program Preparation & Execution

Processing SQL statements; Program preparation overview; Db2 precompile; Db2 BIND; BIND alternatives - plan only; BIND alternatives - packages; Packages & collections; BIND parameters; Plan management & access path stability; Plan management (RE)BIND parameters; Packages - varying BIND options; Packages - using mirrored tables; Packages - using versions; Program execution - TSO; Program execution - IMS; Program execution - CICS.

Wrap-up Session