Skip to main content

Course description: DB2 LUW Performance Tuning and Monitoring for Single and Multiple Partition DBs

Interested in bringing a class to you? Onsite training

Overview

List of course details in a data table
Course code CL441 Skill level Advanced
Duration 5.0 days Delivery type Classroom
(Hands-on labs)
Course type Public or Private on-site    
Public price USD $3,250.00 plus tax  

Learn how to tune for optimum performance the IBM DB2 9 for Linux, UNIX, and Windows relational database management system and associated applications written for this environment. Learn about DB2 9 for Linux, UNIX, and Windows in support of single partition and multiple partition (DPF) database environments.

Explore performance issues affecting the design of the database and applications using the database, the major database performance parameters, and the different tools that assist in performance monitoring and tuning.

Use tools in class that are common across the Linux, UNIX, and Windows operating systems environments. During labs running on DB2 9, develop your ability to use monitoring tools, Explain tools and DB2 utilities like RUNSTATS, REORG and db2batch to tune a database running on your local LINUX workstation using single and multiple partition DB2 databases.

Course Materials

The course materials cover DB2 9.5 for Linux, UNIX, and Windows.

Hands-On Labs

The thirteen labs use the DB2 9.5 Linux, UNIX, and Windows tools and commands to monitor, configure and analyze performance. The labs are performed using one DB2 9.5 for Linux system per student.

View this course in other countries


Training Paths that reference this course are:

Back to top

Special note

IBM Education Advantage Program Eligibility:

  • Yes - IBM Education Pack - online account

Back to top

Audience

This is an advanced course for database designers, database administrators, and application developers working with DB2 9 for Linux, UNIX, and Windows who are concerned about performance in both single and multiple partition databases.

Back to top

Prerequisites

You should complete:

  • DB2 9 for LUW Multiple Partition DBA Workshop (CF24) or
  • DB2 9 for LUW Multiple Partition Environment for Single Partition DBAs (CG24)

or have equivalent experience.

Back to top

Skills taught

  • Define the impact of database design (tables, indexes, and data placement) on database performance
  • Describe database application programming considerations and how they affect performance
  • Identify and describe the parameters (database and non-database) that affect performance
  • Tune parameters to achieve optimum performance for Online Transaction processing (OLTP) or Data Warehouse environments
  • Identify and use the tools that assist in monitoring and tuning of single partition and multiple partition (DPF) databases
  • Analyze Explain reports to identify the access strategies selected by the DB2 Optimizer for execution of SQL statements including the selection of indexes, join techniques, sorts and table queues.

Back to top

Course outline

Database monitoring

  • Describe the basic principles in monitoring a DB2 database
  • List the tools for monitoring database and application activity
  • Use GET SNAPSHOT commands to produce reports for analysis of database performance
  • Utilize the administrative routines and views provided by to DB2 to simplify application access to database performance statistics
  • Use the db2pd to perform performance analysis or problem determination for a DB2 database

Database Input/Output (I/O) management

  • Describe processing for Reading Database Pages into Buffer Pools
  • Describe processing for Writing Database Pages from Buffer Pools
  • Monitor Database Read and Write Activity using GET SNAPSHOT commands or Administrative Routines and Views
  • Monitor Database Logging Activity and select appropriate values for SOFTMAX and MINCOMMIT
  • Implement and monitor use of a block based buffer pool to improve table scan performance
  • Describe the alternate page cleaning processing associated with the DB2 Registry variable DB2_USE_ALTERNATE_PAGE_CLEANING

Tablespace and Table design for performance

  • Select appropriate values for table space page size and extent size to support application performance requirements
  • Describe the calculation of prefetch size when PREFETCHSIZE is set to AUTOMATIC
  • List the advantages of selecting DMS or SMS table space management as well as using Automatic Storage managed table spaces
  • Set file system caching options for table spaces to optimize table space performance
  • Describe the various row insertion algorithms for tables that are based on using the APPEND option or defining a clustering index
  • Plan and Implement Row Compression to reduce disk and memory requirements and improve application performance

DB2 memory management

  • Describe memory heap usage for instance memory, database shared memory, and agent private memory
  • Explain the management of database shared memory based on setting the configuration option database_MEMORY to AUTOMATIC, COMPUTED or a specific number of pages
  • Select the mode for managing data sort memory using SHEAPTHRES, SORTHEAP, and SHEAPTHRES_SHR
  • Monitor DB2 memory usage using the db2mtrk command or the graphical application Memory Visualizer.
  • Utilize the db2pd for monitoring current database memory usage
  • Use AUTOCONFIGURE to set database configuration defaults when a new database is created or after a database workload changes

Automated memory management

  • Describe how Self-Tuning Memory Manager (STMM) can be used to automatically manage database shared memory heaps
  • Explain the differences in STMM processing based on the setting of database_MEMORY
  • Plan and configure a database for self-tuning memory
  • Activate or deactivate STMM for selected memory heaps
  • Describe the management of sortheap memory based on the configuration of sheapthres and sheapthres_shr
  • Explain how DB2 can automatically increase or decrease database memory for multiple DB2 databases running on the same server

Using explain tools

  • Describe the advantages of using Visual Explain
  • Describe the advantages of using db2exfmt
  • Create special tables used by Visual Explain and db2exfmt
  • Identify how to set the Explain snapshot and Explain mode registers to capture the information of interest
  • Differentiate between the different methods of viewing Explain information

The optimizer

  • Describe the stages of the SQL compiler
  • Choose the appropriate optimization class
  • Describe the catalog statistics and database configuration options impact on access plan selection
  • Implement a statistical view to provide better cardinality estimates for complex queries
  • Utilize the db2look utility to extract catalog statistics from existing tables to mimic an existing database environment

Using indexes for performance

  • Describe the Indexing options that can be used to improve performance including: Index Only Access, Clustered Index, Reverse Scans, Include Columns, and Index Freespace
  • Describe the Block Indexing capability for MDC tables
  • Explain how multiple indexes can be combined using Index ORing and Dynamic Bitmap Index ANDing
  • Use the Design Advisor to predict performance gains from adding new indexes

Complex SQL performance

  • Review Explain reports for costly sort operations
  • Describe the differences between Nested Loop, Merge Scan and Hash Joins
  • Plan the implementation of Refresh Immediate or Refresh Deferred Materialized Query Tables to improve query performance
  • Utilize the design advisor to analyze SQL statements and recommend new MQTs
  • Describe the features of Range Partitioned Tables to support large DB2 tables using multiple tablespaces, including the roll-in and roll-out of data ranges
  • Use the DB2 Explain tools to determine if partition elimination is being used to improve access performance to large range partitioned tables

Tools and utilities for performance

  • Use the RUNSTATS utility to collect table and index statistics to enable the DB2 Optimizer to generate efficient access strategies
  • Select appropriate RUNSTATS options to collect Distribution Statistics or Column Group Statistics to improve cardinality estimates during SQL compilation
  • Utilize the table and indexes statistics to plan for table and index reorganization using the REORG utility
  • Set the policies and options for automation of catalog statistics collection.
  • Monitor the activity associated with implementing real time statistics collection
  • Use the db2batch utility to run SQL workloads and collect performance statistics that can be used to benchmark database and application changes

Database Health and Activity Monitoring

  • Monitor the database using Health Monitor and Health Center to ensure that applications are running as efficiently as possible
  • Use the Activity Monitor to generate reports regarding Database and application Performance and locate potential performance issues that require additional analysis.

Application Performance Considerations

  • List examples of application coding techniques that can effect performance
  • Describe the performance advantages of using stored procedures
  • Design applications to maximize record blocking, minimize sort operations and minimize lock contention
  • Monitor application performance and lock waits using GET SNAPSHOT commands and SQL queries
  • Set the DB2 registry variables DB2_EVALUNCOMMITTED, DB2_SKIPINSERTED and DB2_SKIPDELETED to reduce lock contention based on application requirements

Event Monitor

  • Create Event Monitors to collect performance statistics at the database, application or SQL statement level.
  • Implement simple Workload Management definitions to utilize the Workload Manager-based event monitoring including activities, statistics and threshold violations.
  • Evaluate Event Monitor data using the Event Analyzer tool, the db2evmon text-based tool or using SQL queries.

DPF Partitioned Database Performance Tuning Considerations

  • Use GET SNAPSHOT commands and SNAPSHOT views to monitor performance for a DPF-partitioned database
  • Configure and monitor database shared memory for multiple partition databases
  • Utilize the Self Tuning Memory Management option to automatically configure memory options for a DPF database
  • Review Explain reports or use Visual Explain to analyze access to tables in a DPF-partitioned database
  • Collect RUNSTATS statistics for DPF-partitioned tables
  • Implement Informational RI and Check Constraints to improve SQL performance
  • Create Event monitors for a partitioned database
  • Use the dp2pd problem determination tool to obtain statistics from one or all database partitions

Row Relocation Strategies and FCM Performance Considerations

  • Identify the types of table queues that may be used by DB2 and describe the differences between directed and broadcast table queues
  • Examine DB2 Explain reports to determine the row relocation techniques utilized by the DB2 Optimizer
  • Implement a Replicated MQT to achieve collocation for table join operations
  • Identify the FCM configuration parameters and how they can be monitored using GET SNAPSHOT and db2pd commands
  • Use the GET SNAPSHOT FOR APPLICATIONS report to review the subsection status information processing SQL statements

(see Remarks for continuation of topics)

Back to top

Remarks

Daily agenda

Day 1

  • welcome
  • database monitoring
  • lab: database monitoring
  • database I/O management
  • tablespace and table design for performance
  • lab: I/O management and tablespace design

Day 2

  • DB2 memory management
  • lab: memory management
  • automatic memory management
  • lab: using STMM to manage memory
  • using explain tools
  • lab: using explain tools

Day 3

  • the DB2 optimizer
  • lab: DB2 opimizer
  • using indexes for performance
  • lab: index performance
  • complex SQL performance
  • lab: complex SQL performance

Day 4

  • tools and utilities for performance
  • lab: tools and utilities
  • DB2 application considerations
  • Optional lab: application performance
  • monitoring database health and activity
  • Optional lab: health and activity monitors
  • event monitoring
  • lab: event monitors

Day 5

  • DPF Partitioned Database Performance Considerations
  • Row Relocation and Additional Performance topics
  • lab Row Relocation

Back to top

My IBM

We're here to help

Easy ways to get the answers you need.

or call us at
Call 1-800-426-8322

Open M-F 9AM-7PM ET.

Student center

Find the right course

Offers and more