DB2 9 for Linux, UNIX, and Windows Performance Tuning and Monitoring Workshop (CF413)
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 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 9, develop your ability to use monitoring tools, explain tools and DB2 utilities, such as RUNSTATS , REORG and db2batch, to tune a database running on your local Linux workstation.
Skills Gained
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
Identify and use the tools that assist in monitoring and tuning of a database
Who Can Benefit
This is an advanced course for database designers, database administrators, and application developers working with DB2 for Linux, UNIX, and Windows who are concerned about performance.
Prerequisites
You should complete:
DB2 Database Administration Workshop for Linux (CF202) or
DB2 Database Administration Workshop for UNIX (CF212) or
DB2 Database Administration Workshop for Windows (CF232) or
Fast Path to DB2 for Experienced Relational DBAs (CF282) or
DB2 Advanced Administration for Experienced Relational DBAs (CF482)
or have equivalent experience
Course Materials
The course materials cover DB2 9 for Linux, UNIX, and Windows.
Hands-On Labs
The twelve labs use the DB2 9 Linux, UNIX, and Windows tools and commands to monitor, configure and analyze performance. The labs are performed using one DB2 9 for Linux system per student.
Code:
CF413
Length:
4 days
Type:
Instructor-Led
Certified By:
IBM
Tuition:
$2,600
This course is taught by Certified IBM instructors. There is a difference. Learn More
This course is in the following categories. Click the categories to find similar courses and topics of interest.
There are currently no scheduled dates for this course. If you are interested in this course, request a course date with the links below. We can also contact you when the course is scheduled in your area.
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 design for performance
select appropiate 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 statis 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
index freespace
describe the block indexing capability for Multidimensional 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 intrapartition 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 Tables (MQT) to improve query performance
utilize the design advisor to predict performance improvements for implementation of 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
Daily agenda: Day 1
welcome
basic monitoring
lab: basic monitoring
database I/O management
tablespace 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
event monitoring
lab: event monitors
DB2 application considerations
lab: application performance
monitoring database health and activity
lab: health and activity monitors
When you take a certified course with ExitCertified, you are learning from
the creators of the products you use. Our commitment to your IT
community, along with our authorization to deliver certified courses,
ensures you receive a premium training experience.
