DB2 for z/OS: Database Administrator Boot Camp


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 are 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.

This course is also available 'on demand' (minimum 2 students) for additional public presentations or for one-company, on-site presentations.

Objectives

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

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

10 days

Fee (per attendee)

£4250 (ex VAT)

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 data sets & 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 data sets; 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 ; Data set sizing; Data set 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 data sets.

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


© RSM Technology 2017