About this Course

This course provides guidance information for Database and System Administration personnel responsible for the DB2 production and test Data Bases. The course covers creation, utility support, recovery, monitoring and management of DB2 Databases. Physical Database Implementation and control are the key issues addressed by this course.

Audience Profile

DB2 Database and System Administrators who will be designing and managing DB2 Databases

At Course Completion

Upon successful completion of this course, the student will be able to:

• Understand The DB2 System and Data Objects
• How to Create the DB2 Data Objects
• DB2 Naming Conventions
• The DB2 System Objects impacted when creating Data Objects
• DB2 Tablespace and Index Space Management
• How to use the DB2 Catalog to monitor your DB2 Databases
• How to Alter your DB2 Data Objects for performance and usability
• How to build and maintain DB2 Databases using the DB2 utilities
• How to prepare for Recovery
• DB2 Database Recovery
• How to use DB2 commands to monitor DB2 databases

Prerequisites

Introduction to DB2 & SQL coding basics; MVS JCL, TSO, ISPF, EDIT.

Course Outline

1. Introduction

• DB2 Database Objects
• System Structure
• DB2’s Operating Environment
  • DB2 Threads
  • DB2 and TSO
  • DB2 and CICS
  • DB2 and IMS
  • DB2 and DDF
• DB2 Support Structure
• SQL Language
• Utilities overview
• DB2 Command Overview
• Data Base Administrator’s Role

2. Database Object Creation

• Database Definition Language (DDL)
• DB2 Objects Dependencies
• DB2 Object Naming Conventions
• DATABASES
• Storage Groups
  • Storage Management
• Tablespaces
  • Tablespace Types
  • Table Row Format
  • DASD Tablespace Overhead
  • Tablespace Set
  • Create Tablespace
  • Alter Tablespace
  • Dropping Objects
• Partitioning Choices
• “Classic” Partitioning
• Universal Partitioning
  • Partition by Range
  • Partition by Growth
• DB2 Locking
• Row Locks or Page Locks?
  • Controlling Lock Escalation by Tablespace
• Display Database
• Catalog Queries

3. Basic Table Creation

• DB2 Tables
  • Create Table
  • Table Column Formats
  • DB2 Tables
• Alter Tables
  • Referential Integrity
  • Foreign Keys
  • Table Check Constraints
  • Potential Problems
• Table Partitioning

4. Indexes

• INDEX STRUCTURE
  • Space Management in an Index Leaf Page
  • Space Management in an Index Nonleaf Page
  • Index Processing
• INDEXES
  • Create INDEX
  • Clustering Index
  • Partitioned vs. Non-Partitioning
  • Secondary Indexes
    • Non-Partitioned (NPI)
    • Partitioned (PI)
    • Data Partitioned (DPSI)
  • Alter INDEX
• Recommendations

5. Populating Your Database

• DB2 Utilities Overview
  • Utility Groups
  • Invoking Outline Utilities
  • DSNUPROC
  • Utility commands
  • Restricted States
• Load Utility
  • Loading empty tables
  • Load refresh
  • Load append
  • RI Impact
  • Loading partitions
• Best Practices

6. Preparing Your Database for Use

• Verifying Integrity with CHECK Utility
• Preparing for Recovery
  • DB2 Recovery Environment
  • COPY Utility
  • LISTDEF & TEMPLATEs
  • MERGECOPY Utility
  • QUIESCE Utility
  • MODIFY Utility
  • Backup Considerations
• RUNSTATS
  • Purpose
  • Basic Tablespace statistics
  • Table Statistics
  • Index Statistics
  • History Statistics
  • Distribution Statistics
  • Stats and Access Path Selection
• Catalog Queries

7. REORG Utility

• Basic Syntax
• REORG Phases
• SHRLEVEL NONE
• Online Reorg:
  • SHRLEVEL REFERENCE
  • SHRLEVEL CHANGE
• Purpose and Examples
  • Reorganize an entire table space
  • Reorganize partition
  • Rebalance partitions
  • Reorganize one or more indexes of a table space
  • Convert a table space from BRF and RRF and vice versa
  • Apply Pending DDL
• REORG Best Practices

8. Altering your Tablespace Objects

• Alter Tablespace Object for performance
• Immediate changes
• Pending changes
• ALTER Syntax and Examples
  • Altering the page size of a table space
  • Altering the data set size of a table space
  • Altering the segment size of a table space
  • Altering the table space type
  • Alter to minimize Contention
  • Compressing Data
  • Altering the index page size
  • ARORP status set
  • REORG to apply pending charges
• Recommendations

9. Altering Tables and Indexes

• Alter Tables for Usability and new requirements
  • Adding and Changing Columns
  • Hash Access
  • Inline Lobs
  • Key Performance Options
• Alter Index to improve performance
  • Adding Columns
  • Compressions
  • Impact
• Identify impact of altering your objects
• Manage Tablespace and Index Versions
• Recommendations

10. Advanced Object Creation

• Create and Manage:
  • Views
  • Materialize Query Tables (MQTs)
  • Clone Tables
  • Triggers
  • Temporal Tables
  • Global Temporary tables
  • Working with Large Objects (LOBs)
• Making informed advanced feature selection choices
• Identify key strengths and weaknesses in selected features

11. Introductions to Recovery

• Recovery Environment
• RECOVER Utility
• Recovering a Tablespace
  • RECOVER TOCOPY, TORBA, TOLOGPOINT
  • LOGONLY Recovery
  • BACKOUT Recovery
  • Recovery Information
  • Rebuilding an Index
• Rebuilding an Index
• MODIFY Utility
• Recommendations

12. Introduction to Performance

• Access Path Selection
• Predicate Processing
• Stage 1 and Stage 2 Predicates
• Predicate Types and Processing
• Efficient Use of SQL
• Using Explain
• Filter Factors in Compound Predicates
• Tuning Multi-Table  Queries
• Visual Explain in Data Studio