Profience

DB2 Database Administration Workshop for LUW

Why take this course?

After completing this course, you will be able to
  • describe the major editions and components of the DB2 LUW product
  • use Data Studio to create and maintain DB2 objects.
  • implement a DB2 database.
  • use stored procedures and triggers.
  • use DB2's security mechanisms to control access to DB2 data.
  • use DB2's Explain tools to view and optimize DB2 data access paths.
  • use the DB2 Catalog and other resources to monitor performance.
  • create effective backup and recovery strategies for applications.
You will also be familiar with
  • DB2's data integrity control mechanisms.
  • Database administration related utilities.
This course provides comprehensive and practical training for database administrators working with DB2 for LUW. You will learn about Database Design, data modelling, logical and physical design and Administration, including data integrity, backup and recovery, security, performance and application support. We will review the DB2 product from a DBA perspective and give you extensive hands-on exercises so you leave with a ready-to-use, working knowledge of the different components of a DBA's toolkit.
Who should attend?
This course is best for database administrators who have less than a year of working experience. Application and technical support personnel will also find many of the topics directly relevant to their jobs.Any prerequisites?You should have a good understanding of DB2 concepts and facilities. Ideally, you have at least 3 months of practical DB2 experience in application development or database administration.

Dates and Fees

5 Days   ? - $ 1,747 (CAD) ? Up to 3 - $ 3,494 Up to 6 - $ 5,244   ? Plus - $ 874
 
Sorry, no classes are currently scheduled, please contact us to request a class
Contact us to arrange your preferred date and/or in-class training

What Topics are covered?

Introduction
  • Database Administration
  • Database Life Cycle
  • DBA Tasks
  • DBA Deliverables

DB2 Overview

  • What is DB2
  • DB2 LUW Editions
  • The DB2 Environment
  • Architectures
  • Accessing DB2
  • Managing DB2
  • Access to DB2
  • DB2 Clients and drivers

DB2 Objects

  • Instance
  • Database
  • Storage Group
  • Table space
  • Schema
  • Table
  • Column
  • Datatypes
  • Attributes
  • Index
  • View
  • Nickname
  • Alias
  • Package
  • Module
  • Buffer Pool
  • SQL Limits

Designing DB2 Databases - Logical

  • Database Design Process
  • Initial Logical Design
  • Modelling Entity Types
  • The Primary Key
  • Modelling Relationships
  • One-to-One Relationship
  • One-to-Many Relationship
  • Many-to-Many Relationship
  • Modelling Attributes
  • Single Valued Attribute
  • Multi Valued Attribute
  • Initial Logical Design
  • Refined Logical Design
  • Transaction Analysis
  • Logical Design

Physical Database Design

  • Storage Management
  • DMS, SMS and Automatic Storage Management
  • Partitioning
  • Multidimensional Clustering
  • Table space Options
  • Column Attributes
  • Indexing Strategies
  • Using Views

Implementing the Database

  • Data Definition Language
  • Naming rules
  • DATABASE
  • STOGROUP
  • BUFFER POOL
  • SCHEMA
  • TABLE SPACE
  • TABLE
  • INDEX
  • VIEW
  • ALIAS

Implementing Declarative Constraints

  • Table Constraints
  • NOT NULL Constraint
  • UNIQUE Constraint
  • PRIMARY KEY Constraint
  • CHECK Constraint
  • Referential Constraint
  • Basic Concepts
  • Additional Terms
  • Defining Referential Integrity
  • Design Considerations
  • Operational Implications

Implementing Procedural Constructs

  • User Defined Functions
  • Stored Procedures
  • Triggers

Implementing Security

  • Security aspects      
  • Authorization ID      
  • Sources of permission      
  • Levels of authorization      
  • Types of permissions      
  • Groups and roles      
  • Application of permission
  • Administrative authoritiies
  • Database authorities      
  • Privileges    
  • Object ownership and LBAC
  • Role hierarchies      
  • Access to DB2 data      
  • Privileges and authorities
  • Data Control Language    
  • Application Packages

The DB2 Catalog and Directory

  • DB2 Directory Structure
  • DB2 Catalog
  • Using the catalog
  • Maintaining catalog statistics

Managing DB2 Performance

  • What is Performance?
  • DB2 vs. System Performance
  • Performance Factors
  • Performance Tuning
  • Monitoring and tools
  • Implementing Changes

Concurrency Control

  • Why Concurrency Control?
  • The Performance Dilemma
  • DB2 Locking Strategy
  • Lock Size
  • Lock Duration
  • Lock Modes
  • Lock conversion
  • Claims and Drains
  • Lock deferral
  • Reducing Contention
  • Deadlock

DB2 Access Paths

  • What is an Access Path?
  • Types of Access Paths
  • Matching Index Scan
  • Non-Matching Index Scan
  • Range Limit index scan
  • Jump Scan
  • Table Scan
  • Partition Scan
  • Multiple Index Access
  • Join Methods
  • Nested Loop Join
  • Merge Scan Join
  • Hash Join
  • Using Multiple Indexes in Joins
  • Combining Join Method

DB2 Explain Facility

  • The Optimizer
  • The EXPLAIN Facility
  • The EXPLAIN Tables
  • Invoking EXPLAIN
  • Simulating Host Variable Usage
  • The EXPLAIN statement
  • Explain output
  • Tools and Techniques for Improving Performance

Backup and Recovery

  • Concepts
  • Basic Terminology
  • Point of Consistency
  • Direction of Recovery
  • Logs
  • Design Considerations
  • Backup Command
  • Restore Command
  • Rollforward Command