DB2 for Linux, UNIX, and Windows Performance Tuning and Monitoring Workshop

M/UX
Atlanta, Boston, Denver, KC, Los Angeles, New York, Raleigh, San Francisco, Sacramento, Seattle, United States

Course Format: Classroom | E-learning | Virtual Class | Online | On-site | Blended | Self-paced

Course Description:

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

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.


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
Identify and use the tools that assist in monitoring and tuning of a database


Course outline

--------------------------------------------------------------------------------

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

mo ...''

Phone:866-700-9811

School Address:

pSeries/AIX, CISCO, Linux, Microsoft, Professional Skills, Project Management, RedHat LINUX Enrollments 866-700-9811 (Toll Free) or 916-226-2537

CICS, iSeries/OS400, Enterprise/zSeries and WebSphere Enrollments 888-689-3326 (Toll Free) or 916-226-2511

DB2 UDB, Rational Software, and Tivoli Enrollments 866-700-9817 (Toll Free) or 916-226-2533

M/UX
9245 Laguna Springs Drive
Suite 315
Elk Grove, CA 95758 USA

Jobs & Resumes: Atlanta, Boston, Denver, KC, Los Angeles, New York, Raleigh, San Francisco, Sacramento, Seattle
Houses & Roommates: Atlanta, Boston, Denver, KC, Los Angeles, New York, Raleigh, San Francisco, Sacramento, Seattle

Relational Database Design
Using Rational Data Architect
DB2 9 Database Administration Workshop for Linux/UNIX/Windows
DB2 Universal Database Administration Workshop for Windows
DB2 Universal Database Administration Workshop for UNIX
DB2 UDB Multi Partition Database Administration Workshop for UNIX
DB2 9 for LUW Multiple Partition DBA Workshop
DB2 UDB for z/OS and OS/390 Data Sharing Implementation
DB2 for z/OS and OS/390 Data Sharing Recovery and Restart
DB2 9 for LUW Advanced Database Recovery
DB2 Stored Procedures Programming Workshop
DB2 UDB Application Programming Workshop
DB2 UDB for z/OS System/Disaster Recovery Workshop
Query and Manage XML data with DB2 9
DB2 UDB for z/OS Stored Procedures using COBOL
DB2 and SQL Programming Workshop
iSeries RPG IV Version 5 Programming Fundamentals Workshop
iSeries RPG IV Version 5 Programming Advanced Workshop

Notice: The course description on this page was captured from the Internet as historical reference or submitted by visitors. It was archived statically and not updated from day to day.
 

Tips: Want to find a place to stay close to the school? Or homestay in a different country? Try the house rental message board where you can search for roommates and post shared rooms or apartments for rent.