Audience:
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 9 Database Administration Workshop for Linux (CF202) or (CF20G) or
DB2 9 Database Administration Workshop for UNIX (CF212) or (CF21G) or
DB2 9 Database Administration Workshop for Windows (CF232) or (CF23G) or
Fast Path to DB2 9 for Experienced Relational DBAs (CF282) or (CF28G) or
DB2 9 for Linux, UNIX, and Windows Quickstart for Experienced Relational DBAs (CF484) or (CF48G)
or have equivalent experience.
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:
- 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
For more information /quotation / training details please dial 1-630-355-6292 (Monday - Friday: 8AM to 6PM),alternatively you can email us at info@royalcyber.com or Contact us and our team of consultants will assist you further. |
