Objectives

On successful completion of this course attendees will be able to:

  • develop DB2 applications with optimised performance attributes
  • maintain and tune applications for optimised performance.
  • describe effective techniques for advanced database management

Audience

Systems Administrators, Database Administrators and technical personnel who are involved in designing, planning, implementing and/or maintaining DB2 UDB Databases, up to version 9.7. within a Linux, UNIX, or Windows environment.

Prerequisites

A sound practical understanding of DB2 UDB.

Duration

3 days

Outline for DB2 UDB: Performance & Tuning in Linux/UNIX/Windows Environments

1. DB2 UDB Review

  • Introduction
  • The DB2 Family.

2. Advanced Database Management

  • Advanced database management ( up to DB2 9.7)
  • Tuning The Database
  • Database Configuration File
  • Partitioned Database
  • Advanced Tablespace Management
  • Advanced Table Management
  • Table Partitioning Feature.

3. MQT

  • Generating DDL
  • Set Integrity
  • Deep Compression.

4. DB2 System Catalog

  • Attributes, including changes up to version 9.7
  • Consolidation of DDL with regard to efficiency and Version 9.
  • Practical Exercise 1.

5. Locking Strategy

  • What resources can be locked
  • How To Set The Locking
  • Locking Configuration Parameters
  • Locking Terms
  • Duration of a lock
  • Commit/Rollback
  • Logging
  • How to find out what Locks are held
  • Savepoints.
  • Practical Exercise 2.

6. Indexing

  • Physical data management
  • Page
  • Indexes
  • Type 1/ 2 Indexes
  • Design Advisor
  • Utilities
  • Runstats
  • Reorg
  • Autonomic Utilities
  • Reorgchk
  • Access Strategy
  • Single Table Access Paths
  • Multi table access path.

7. EXPLAIN

  • Introduction
  • Explain Tables
  • Executing from the CLP
  • Executing from the Command Centre
  • DB2EXPLN
  • Visual Explain

8. Clustering

  • Multidimensional Clustering
  • Range Clustered Tables
  • The Organize by Key Sequence Clause
  • Statistical View.

9. Triggers

  • Stored Procedures
  • Development center
  • Types of stored procedure
  • An external procedure
  • The catalog
  • SPL
  • Stored procedure builder
  • User Defined Functions.

10. Security

  • Security levels
  • Security administrator authority level
  • Label based access contro
  • Security label component
  • Security structure examples.

11. Utilities

  • BACKUP
  • RESTORE
  • LOAD
  • IMPORT/EXPORT
  • Introduction to Automatic Computing
  • Configuration Parameters
  • Collecting Catalog Statistics
  • Health Center
  • Remote Administration
  • DB2 Administration Server.

12. Logs

  • Logs, Snapshot ,Event Monitor, Db2pd And Trace
  • Types of Logging
  • Configuring Database
  • Logging Information
  • DB2DIAG
  • Manual Reading of the Log
  • Snapshot & Event Monitor
  • DB2PD
  • TRACE DB2TRC.