Pro koho je kurz určen
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.
Co Vás naučíme
- 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
Požadované vstupní znalosti
- DB2 9 Database Administration Workshop for Linux (CL201) or
- DB2 9 Database Administration Workshop for UNIX (CL211) or
- DB2 9 Database Administration Workshop for Windows (CL231) or
- Fast Path to DB2 9 for Experienced Relational DBAs (CL281) or
- DB2 9 for Linux, UNIX, and Windows Quickstart for Experienced Relational
DBAs (CL481) or
- 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
Metody výuky
Professional explanation with practical samples and examples.
Studijní materiály
Studijní materiál IBM
Osnova kurzu
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
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
- 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
- use 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