DB2 Universal Database Performance Tuning and Monitoring Workshop

start > courses and registration > training feature sheet

DB2 Universal Database Performance Tuning and Monitoring Workshop

course schedule			who can benefit
prerequisites			skills gained
other notes			course content
related solutions

code.

CF412

length.

4 days

type.

Instructor-Led

partner.

IBM

price.

$2,500

IACET credits 3.2

Learn how to tune for optimum performance the IBM DB2 UDB for Linux, UNIX, and Windows relational database management system and associated applications written for this environment. Learn about DB2 UDB for Linux, UNIX, and Windows in a single partition database environment. 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 environments. During labs running on DB2 UDB V8, develop your ability to use the common tools to independently tune a database running on your local Windows workstation.

There are currently no scheduled dates for this course. If you are interested in this course, request a course date with the links below.

who can benefit

This is an advanced course for experienced database administrators, and technical individuals, who plan, implement and maintain recovery and high availability for DB2 UDB for UNIX, Windows, and Linux single partition databases.

prerequisites

You should complete:

	DB2 Universal Database Administration Workshop for Linux (CF201) or
	DB2 Universal Database Administration Workshop for UNIX (CF211) or
	DB2 Universal Database Administration Workshop for Windows NT (CF231) or
	DB2 UDB Administration Workshop for Sun Solaris (CF271) or
	Fast Path to DB2 UDB for Experienced Relational DBAs (CF281) or
	DB2 UDB Advanced Administration for Experienced Relational DBAs (CF482)
	OR have equivalent experience

skills gained

	Gain a better understanding of the buffer pools, sort heap, and other memory areas used by various components of DB2 UDB Enterprise Server Edition (ESE) Version 8 databases, and the impact on performance
	Explore the DB2 UDB database configuration options that affect database and application performance
	Identify and use the standard tools that assist in monitoring and tuning of a database including snapshot and event monitoring
	Define the impact of database design on database performance, including selecting the appropriate space management Data Managed Storage (DMS) versus System Managed Storage (SMS), and data placement consderations
	Using the DB2 UDB design advisor to evaluate and suggest indexes that would reduce cost of running SQL queries
	Analyze the access plans selected by the DB2 optimizer using visual explain and the text explain facilities like db2exfmt and db2expln
	Utilize the DB2 REORG, RUNSTATS, DB2BATCH AND DB2LOOK utilities to analyze and improve database performance
	Monitor the operational status of a DB2 UDB database using the health center and memory visualizer tools

course materials

The course materials cover DB2 UDB Version 8.1 for Linux, UNIX, and Windows.

hands-on labs

Ten labs are included to address using DB2 UDB Version 8.1 for Linux, UNIX, and Windows.

certification

Prepare for IBM Certification Test 704: DB2 UDB V8.1 for Linux, UNIX and Windows Advanced Database Administration.

training path

This course is part of an IBM Training Path. Taking this course in the recommended sequence allows you to maximize the benefits from your education.

ibm education advantage program eligibility:

	Yes - IBM Education Card
	Yes - IBM Education Pack
	Yes - IBM Corporate Education Pass
	Yes - IBM Education Pack - online account

skill level

Advanced

course content details

	Database performance and monitoring

	describe the principles in monitoring
	identify the memory requirements for DB2
	state basic performance principles
	identify how the database system monitor can be used
	use the snapshot monitor

	DB2 operational model

	describe how DB2 manages the database buffer pools
	identify the purpose of the buffer pool descriptor area
	describe the major flow of data to and from DB2 buffer pools, Input/Output (I/O) servers and page cleaners
	describe how DB2 uses and how to monitor the database heap, catalog cache, package cache, (and dynamic SQL cache), sort areas, and log buffers

	DB2 design for performance

	describe the strategies that may be used to improve performance, including I/O parallelism, appropriate media/placement, database design for access optimization, minimizing I/Os, and use of asynchronous I/O
	apply these strategies to your database design

	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 right optimization level
	describe the information stored in packages
	apply strategies to facilitate the optimizer to produce better access plans
	prototype non-existent conditions (for example: a production database and its environment or a proposed index) by changing the statistic values

	Optimizer strategies

	describe the information stored in packages
	look at the access plans produced by the optimizer and describe what steps will be performed to execute the query
	take actions to improve the access plans

	Tools and utilities for performance

	utilize the RUNSTATS utility to collect accurate statistics from a DB2 database
	plan the use of the REORG utility to improve the efficiency of access to a tablep
	use the db2look utility to capture the statistics the DB2 catalog tables
	setup and invoke the db2batch utility to support benchmark tests of SQL statements

	DB application design and programming

	apply the appropriate SQL programming strategies to improve performance
	use joins properly
	minimize sorts
	use stored procedures to improve performance
	maximize the performance of User-Defined Functions (UDF)
	exploit record blocking for performance
	use the correct strategies for Large Objects (LOB) to improve performance
	apply the programming strategies to maximize concurrency and improve performance

	Monitoring using the health center

	monitor the database using health monitor and health center to ensure that applications are running as efficiently as possible
	set thresholds and actions to perform when those thresholds are reached
	monitor memory utilization using memory visualizer
	manage storage with storage management tool
	automatically tune and configure using configuration advisor