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

ExitCertified US

ExitCertified Canada English

ExitCertified Canada French



	start > courses and registration > training feature sheet

DB2 LUW Performance Tuning and Monitoring for Single and Multiple Partition DBs

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

code.

CF444

length.

5 days

type.

Instructor-Led

partner.

IBM

price.

$3,250

If you are in a single partition environment you should enroll in (CF413).

Learn how to tune for optimum performance the IBM DB2 9 for Linux, UNIX, and Windows relational database management system andassociated 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..

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

course schedule

Phoenix, AZ	feb.9.2009 - feb.13.2009	register
Phoenix, AZ	feb.23.2009 - feb.27.2009	register
Phoenix, AZ	apr.6.2009 - apr.10.2009	register
Phoenix, AZ	apr.27.2009 - may.1.2009	register
El Segundo/Los Angeles, CA	dec.15.2008 - dec.19.2008	register
Sacramento/Elk Grove, CA	dec.15.2008 - dec.19.2008	register
Santa Clara, CA	dec.15.2008 - dec.19.2008	register
Sacramento/Elk Grove, CA	feb.9.2009 - feb.13.2009	register
El Segundo/Los Angeles, CA	feb.9.2009 - feb.13.2009	register
San Francisco, CA	feb.9.2009 - feb.13.2009	register
Sacramento/Elk Grove, CA	apr.6.2009 - apr.10.2009	register
El Segundo/Los Angeles, CA	apr.6.2009 - apr.10.2009	register
San Francisco, CA	apr.6.2009 - apr.10.2009	register
Englewood/Denver, CO	feb.9.2009 - feb.13.2009	register
Englewood/Denver, CO	feb.23.2009 - feb.27.2009	register
Englewood/Denver, CO	apr.27.2009 - may.1.2009	register
Atlanta, GA	dec.1.2008 - dec.5.2008	register
Overland Park, KS	feb.23.2009 - feb.27.2009	register
Overland Park, KS	apr.27.2009 - may.1.2009	register
Boston, MA	apr.13.2009 - apr.17.2009	register
St louis/Maryland Heights, MO	feb.23.2009 - feb.27.2009	register
St louis/Maryland Heights, MO	apr.27.2009 - may.1.2009	register
Durham/Raleigh, NC	mar.9.2009 - mar.13.2009	register
New York/Downtown, NY	feb.2.2009 - feb.6.2009	register
Dublin, OH	feb.23.2009 - feb.27.2009	register
Dublin, OH	apr.27.2009 - may.1.2009	register
Bellevue/Seattle, WA	dec.15.2008 - dec.19.2008	register
Bellevue/Seattle, WA	feb.9.2009 - feb.13.2009	register
Bellevue/Seattle, WA	apr.6.2009 - apr.10.2009	register

who can benefit

This is an advanced course for Database administrators, application developers, vendors, and consultants concerned with the performance aspects of planning, implementing, and maintaining DB2 UDB in a multipartition environment.

prerequisites

You should complete:

	DB2 UDB Multi Partition Database Administration Workshop for UNIX (CF241) or
	DB2 UDB Multi Partition Environment for Single Partition DBAs (CG241)
	and be able to administer a DB2 UDB partitioned database

skills gained

After completing this course, you should be able to:

	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.

ibm education advantage program eligibility:

Yes - IBM Education Pack - online account

related courses, exams and materials

	before related training and products

CG241 DB2 UDB Multi Partition Environment for Single Partition DBAs

course content details

	Monitoring the Database

	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 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 Design for Performance

	Select appropriate values for tablespace page size and extent size to support application performance requirements
	Choose a prefetch size for a tablespace or select automatic adjustment of prefetch size
	List the advantages of selecting Database Managed Storage (DMS) or System Managed Storage (SMS) tablespace management as well as using Automatic Storage managed tablespaces
	Set file system caching options for tablespaces to optimize tablespace performance
	Describe the various row insertion algorithms for tables for tables based on the APPEND option or a clustering index
	Implement Large Row Identifier (RID) support for selected tablespaces and tables
	Plan and Implement Row Compression to reduce disk and memory requirements and improve application performance

	Database 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

	Automatic 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
	Use the db2expln explain tool to analyze an access plan for status or dynamic SQL
	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 DB2 Optimizer

	Describe the stages of the SQL compiler
	Choose the right optimization level
	Explain the effects of database configuration including CPUSPEED, Tablespace OVERHEAD and TRANSFERRATE or optimizer access plans and costs
	Apply strategies to facilitate the optimizer to produce better access plans
	Use the RUNSTATS UTILITY options to collect accurate statistics to improve access plan selection
	Implement a Statistical View and collect statistics with RUNSTATS to improve the result cardinality estimates and generate more efficient access plans

	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 Multiple Dimensional Clustering (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

	Describe the types of parallel sort strategies that can be used when Intra-partition parallelism is used to improve sort performance
	Explain the processing for Nested Loop joins, Merge Scan joins and Hash joins
	Plan and implement a Materialized Query Table to improve query performance
	Utilize the design advisor to predict performance improvements for implementation of Materialized Query Table (MQTs)
	Describe the use of multiple table spaces for the data and indexes of a Range Partitioned Table.
	Plan the implementation of a Range Partitioned Table to support Roll-in and Roll-out of data into a large table using the ALTER TABLE ATTACH and DETACH options

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

	DB2 Application Considerations

	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 Database Health and Activity

	Monitor database health using health snapshot commands, administrative table functions or the Health Center graphical tool to ensure that applications are running as efficiently as possible
	Monitor tablespace disk utilization and container status for SMS, DMS and Automatic Storage tablespaces
	Use the Activity Monitor to analyze various performance problems in an active DB2 database

	Event Monitoring

	Create event monitors to collect database performance statistics that write event monitor data to files, pipes, or DB2 tables
	Use an event monitor to collect information about database deadlocks
	Evaluate event monitor data using the event analyzer, the db2evmon text based tool or using SQL queries

	DPF Partitioned Database Performance Considerations

	Monitor a DPF partitioned database using the DB2 snapshot commands, administrative views, event monitors and the db2pd command
	Configure database memory and buffer pools using a partitioned database
	Implement the Self Tuning Memory Management in a partitioned database system
	Analyze db2expln and db2exfmt explain reports for SQL running in a DPF partitioned database including the use of subsections and table queues
	Plan and execute DB2 utilities like RUNSTATS and REORG to support application performance in a partitioned database
	Utilize the db2batch utility to benchmark to execute SQL statements and collect performance statistics in a partitioned database

	Row Relocation and Additional Performance topics

	Identify the types of table queues that may be used by DB2
	List the row relocation techniques that DB2 may use, including collocation, directed and broadcast
	Identify the Fast Communication Manager (FCM) configuration parameters and how they can be monitored using the snapshot commands, administrative views or using db2pd commands
	Implement check constraints to define a UNION ALL VIEW to simply access and improve performance to large amounts of data
	Utilize Referential Integrity constraints that are NOT ENFORCED to improve the access plans and performance for SQL statements

	Agenda - Day 1

	Welcome
	Basic monitoring
	Lab 1 Basic Monitoring
	Database I/O Management
	Tablespace Design for Performance
	Lab 2 I/O Management and Tablespace Design

	Day 2

	DB2 Memory Management
	Lab 3 Memory Management
	Lab 4 Section 1 - Setup for STMM lab
	Automatic Memory Management
	Lab 4 Sections 2-3 Using STMM to Manage Memory
	Using Explain Tools
	Lab 5 Using Explain Tools

	Day 3

	The DB2 Optimizer
	Lab 6 DB2 Optimizer
	Using Indexes for Performance
	Lab 7 Index Performance
	Complex SQL Performance
	Lab 8 Complex SQL Performance

	Day 4

	Tools and Utilities for Performance
	Lab 9 Tools and utilities
	Monitoring Database Health and Activity
	Lab 10 Health and Activity monitors
	DB2 Application Performance Considerations
	Lab 11 Application Performance
	Event Monitoring
	Lab 12 Event Monitors

	Day 5

	DPF Partitioned Database Performance Considerations
	Row Relocation and Additional Performance topics
	Lab 13 Row Relocation

Free Chumby with IBM Training

Save on IBM Training