What you will learn
  At the end of this course, students will be able to:
  • Code SQL to improve performance.
  • Use 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.
  • 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 for automatic maintenance.
  • Use and analyse benefits of advanced table design like row compression.
  Database administrators and those senior support persons who are responsible for tuning DB2 for performance.
  Relevant Operating Systems experience (Linux, UNIX or Windows) along with knowledge of relational database theory and SQL. Previous experience of using DB2 in a support roles that includes some database administration.
  Four days.

Outline for DB2 System Performance and Tuning for Linux, Unix and Windows

1. 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

2. 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

3. SQL performance

  • Hints and tips for effectively using SQL

4. Data Access and EXPLAIN

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

5. The DB2 Optimizer

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

6. Event and snapshot Monitors

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

7. 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

8. 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

9. 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.

10. Advanced table options

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

11. Configuring for self tuning of DB2

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