DB2 LUW Performance Tuning and Monitoring for Single and Multiple Partition-SPVC

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
• utilize the enhanced relational monitoring interfaces with SQL in reporting and monitoring of the database system, data objects, and the package cache to help you quickly identify issues that might be causing problems
• configure the DB2 Database configuration options that control the collection of request, activity and object level metrics on the entire database
• compare the enhanced monitoring features with the snapshot monitoring facilities provided by previous DB2 releases

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
• describe how scan sharing can reduce the I/O workload for accessing large tables

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
• describe how DB2's Index compression option can reduce disk storage requirements for indexes
• explain how DB2 can compress temporary data to improve application performance
• utilize the DB2 provided tools and functions to estimate compression results for Indexes and XML data

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
• enable the statement concentrator using the stmt_conc database manager configuration parameter to reduce SQL compilation overhead for dynamic SQL statements
• utilize the db2look utility to extract catalog statistics from existing tables to mimic an existing database environment
• create an optimizer profile to influence the access plan selected by the DB2 optimizer

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
• use the MON_GET_INDEX table function to monitor index usage
• 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

(to be continued in the Remarks section)