DB2 Database Administration

Duration

Overview

Audience

Prerequisites

Objectives

• Set up the DB2 Database Administration function in his/her organization
• Participate in the installation or migration of DB2
• Understand the basics of Data Normalization
• Create DB2 objects: Storage Groups, databases, tablespaces, tables, view, indexes, and set up referential integrity
• Alter DB2 tables
• Compare DB2 structures
• Set up and execute DB2 Utilities
• Understand DB2 locking
• Set up DB2 security
• Interpret DB2 EXPLAIN output

Course Content

Module 1 INTRODUCTION TO DB2 DATABASE ADMINISTRATION

• Introduction
• Software Support
• Development Support
• QA Support
• Production Support
• Backup and Recovery
• Security
• Database Connectivity
• Performance Management
• Consulting

Module 2 SOFTWARE SUPPORT

• Storage Estimates
• Virtual and Real Storage
• DASD Requirements
• Distributed Processing
• Parallel Sysplex
• SMP/E
• ODBC
• Service Levels
• Installation jobs
• Migration
• Fallback
• Other software

Module 3 DEVELOPMENT SUPPORT: LOGICAL DESIGN

• Logical Design
• Why Normalization
• Steps in Normalization
• Un-Normalized Relations
• First Normal Form
• Second Normal Form
• Third Normal Form

Module 4 DEVELOPMENT SUPPORT: PHYSICAL DESIGN

• Physical design process
• Object Design
• Storage Groups
• Databases
• Simple Tablespace
• Segmented Tablespace
• Partitioned tablespace
• Tables
• Data Types
• Indexes
• View
• Constraints
• Primary Key
• Foreign Key

Module 5 DEVELOPMENT SUPPORT: OBJECT MAINTENANCE

• Developer Support overview
• Adding Columns
• Renaming Columns
• Deleting columns
• Changing nulls
• Object versioning

Module 6 QA SUPPORT

• Change Requests
• DBA Checklist
• Listing missing objects
• Object comparison – tablespace
• Object comparison – tables
• Object comparison – columns
• Object comparison – indexes
• Impact analysis

Module 7 PRODUCTION SUPPORT: DATA AVAILABILITY

• Data availability
• Backup
• Storage Management
• Database Performance
• REORG
• Defect support
• Defect support for DB2
• Defect support for applications

Module 8 PRODUCTION SUPPORT: SECURITY AND AUTOMATION

• Continuous availability
• Database descriptors
• COPY SHRLEVEL CHANGE
• REORG SHRLEVEL CHANGE
• Auditing
• Security
• Automation
• Automation Levels Characteristics

Module 9 PRODUCTION SUPPORT: LOCKING AND CONCURRENCY

• Locking
• Locking hierarchy
• Transaction locks
• Intent Locks
• Tablespace locks
• Releasing locks
• Releasing Page/Row Locks
• Concurrency Options

Module 10 BACKUP AND RECOVERY

• Backup and recovery definition
• Copy Utility
• QUIESCE utility
• Report utility
• Data set retention
• Recover utility
• Rebuild utility
• Check Data Utility
• Modify utility
• The BSDS

Module 11 SECURITY

• Security definition
• Privileges
• Administrative authorities
• Secondary AUTH IDS
• Security Exits
• CICS Attachment facility options
• IMS Attachment facility options

Module 12 DATABASE CONNECTIVITY

• Connectivity definition
• DRDA
• DB2 Connect
• DRDA Levels
• CLI and ODBC
• JDBC
• DB2 Connect Personal edition
• DB2 Connect Enterprise edition

Module 13 PERFORMANCE MANAGEMENT: COMPONENTS AND FACILITIES

• Performance Management definition
• DB2 Architecture
• Optimizer statistics
• Default statistics
• DB2 Visual Explain
• DB2 Instrumentation Facility
• DB2 Estimator

Module 14 PERFORMANCE MANAGEMENT: DB2 ACCESS PATHS & EXPLAIN

• DB2 Explain
• PLAN_TABLE Usage
• Creating the PLAN_TABLE
• Populating the PLAN_TABLE
• DB2 Access Path Overview
• Tablespace Scan
• Matching Index Scan
• Index Screening
• Non-Matching Index Scan
• In-List Index Scan
• Multiple Index Access
• One Fetch Index Access
• Index Only Access
• Equal Unique Index
• Nested Loop Join
• Merge Scan Join
• Hybrid Join
• 12 Questions to ask

Module 15 CONSULTING AND PROJECTS

• Definition
• Reasons for Conversion
• Parallel Sysplex
• OO Programming (UDT)
• OO Programming (UDF)
• OO Programming (Triggers)
• Multimedia
• Client Server
• Data Replication