DB2 Performance and Tuning for UNIX and Windows

Course:  DB2PTUW
Duration:  3 Days
Level:  II
Course Summary

This course is designed to review many aspects of performance and tuning of DB2 version 9.7, for Linux, UNIX and Windows (LUW). DB2 commands and tools created by IBM for DB2, will be used to support workshop activities. Performance and tuning varies with type of user to be supported. We will review this topic for support of SQL users, programmers, data designers and database administrators.

« Hide The Details
Topics Covered In This Course

Creating tables and tablespaces

  • Review concepts of SMS and DMS tablespaces
  • show table options like identity columns and generated columns
  • introduction to data row compression
  • basic design using data range partitioning

Advanced SQL techniques

  • how to use different outer join techniques
  • using OLAP functions
  • review Nested and common table expressions
  • using recursion with common table expressions

SQL performance

  • Hints and tips for effectively using SQL

Data Access and EXPLAIN

  • Review of Data access techniques
  • How to use various explain tools and interpret results

The DB2 Optimizer

  • Review of strategy to improve optimization strategy
  • Design of indexes for optimum performance

Event and snapshot Monitors

  • How to use event monitors
  • options for creating and using event monitors

Multi Dimensional Clustering (MDC)

  • the terminology of multi dimensional data.
  • Know why and how to create a table that is clustered in multiple dimensions.
  • Create generated columns to be used for dimension columns.
  • Understand the concept of monotonicity associated with generated dimension columns.

Advanced table options

  • Using table range partitions
  • table row compression
  • advanced options for REORG, RUNSTATS utilities
  • Supporting identity columns in utilities

Configuring for self tuning of DB2

  • automatic maintenance with utilities
  • Self tuning memory
  • database global memory
  • automatic tuning

DB2 Operational Model

  • Review the architectural concepts for DB2
  • look at the behavior of DB2, with respect to I/O operations
  • identify important cache and monitoring elements

The Health Monitor and Activity Monitor

  • Using commands and the Health Center to monitor DB2 ???health???
  • Monitor DB2 memory resource usage with the Activity Monitor
  • Various tools, like DB2PD will be used to monitor memory utilization
  • Analyzing performance with snapshot functions and administrative views

DB2 Configuration Parameters

  • review important configuration parameters for the a database and the database manager
  • Introduce auto configuration
  • identify the concepts of the Design Advisor
What You Can Expect

After completing this course the student should be able to:

  • Code SQL to improve performance.
  • Use various DB2 EXPLAIN tools to evaluate performance expectations.
  • Design application programs to improve performance and review with EXPLAIN tools.
  • Use the tool DB2BATCH to benchmark applications.
  • Use various tools and snapshot functions and administrative views to evaluate DB2 performance and operational behaviour of applications
  • Improve performance through parallelism
  • Review data placement to improve performance.
  • Understand row compression and range partitioning on tables to improve performance
  • Consider Multi-Dimensional Clustering as a design option, by analysing data and application needs.
  • Tune registry variables and configuration parameters to effect performance.
  • Use DB2 monitor tools to assist in performance tuning.
  • Use performance improving options for DB2 utilities
  • Configure DB2 for self tuning
  • Use advanced features of utilities including support for identity columns.
  • Set DB2 for automatic maintenance.
  • Use and analyse benefits of advanced table design like row compression.
Who Should Take This Course

Database administrators and those senior support persons, who are responsible for tuning DB2 for performance or analyzing application performance.

Recommended Prerequisites

Relevant Operating Systems experience (Linux, UNIX or Windows) along with knowledge of relational database theory and SQL. Previous experience of using DB2 that includes either some database administration or application support.

« Hide The Details
Related Courses
Code Course Title Duration Level
DB2UDBI
DB2 for LUW (Linux, UNIX, Windows)
2 Days
I
Details
DB2LUW
DB2 LUW (Linux, UNIX, Windows) FOR PROGRAMMERS
4 Days
I
Details
DB2U
DB2 SQL Application Programming
5 Days
I
Details

Every student attending a Verhoef Training class will receive a certificate good for $100 toward their next public class taken within a year.

You can also buy "Verhoef Vouchers" to get a discounted rate for a single student in any of our public or web-based classes. Contact your account manager or our sales office for details.

Schedule For This Course
There are currently no public sessions scheduled for this course. We can schedule a private class for your organization just a couple of weeks from now. Or we can let you know the next time we do schedule a public session.
Notify me the next time this course is confirmed!
Can't find the course you want?
Call us at 800.533.3893, or
email us at [email protected]