DB2 LUW Performance Tuning and Monitoring for Single and Multiple Partition DBs (CF444)
If you are in a single partition environment you should enroll in (CF413).
Learn how to tune for optimum performance the IBM DB2 9 for Linux, UNIX, and Windows relational database management system andassociated applications written for this environment. Learn about DB2 9 for Linux, UNIX, and Windows in support of single partition and multiple partition (DPF) database environments.
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 operating systems environments. During labs running on DB2 9, develop your ability to use monitoring tools, explain tools and DB2 utilities like RUNSTATS, REORG and db2batch to tune a database running on your local LINUX workstation using single and multiple partition DB2 databases..
The course materials cover DB2 9 for Linux, UNIX, and Windows.
Skills Gained
After completing this course, you should be able to:
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 for Online Transaction processing (OLTP) or Data Warehouse environments
Identify and use the tools that assist in monitoring and tuning of single partition and multiple partition (DPF) databases
Analyze explain reports to identify the access strategies selected by the DB2 optimizer for execution of SQL statements including the selection of indexes, join techniques, sorts and table queues.
Who Can Benefit
This is an advanced course for Database administrators, application developers, vendors, and consultants concerned with the performance aspects of planning, implementing, and maintaining DB2 UDB in a multipartition environment.
Prerequisites
You should complete:
DB2 UDB Multi Partition Database Administration Workshop for UNIX (CF241) or
DB2 UDB Multi Partition Environment for Single Partition DBAs (CG241)
and be able to administer a DB2 UDB partitioned database
IBM Education Advantage Program eligibility:
Yes - IBM Education Pack - online account
Code:
CF444
Length:
5 days
Type:
Instructor-Led
Certified By:
IBM
Tuition:
$3,250
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 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 appropriate 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 status 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, and Index Freespace
Describe the Block Indexing capability for Multiple Dimensional 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 Intra-partition 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 Table to improve query performance
Utilize the design advisor to predict performance improvements for implementation of Materialized Query Table (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
Monitor a DPF partitioned database using the DB2 snapshot commands, administrative views, event monitors and the db2pd command
Configure database memory and buffer pools using a partitioned database
Implement the Self Tuning Memory Management in a partitioned database system
Analyze db2expln and db2exfmt explain reports for SQL running in a DPF partitioned database including the use of subsections and table queues
Plan and execute DB2 utilities like RUNSTATS and REORG to support application performance in a partitioned database
Utilize the db2batch utility to benchmark to execute SQL statements and collect performance statistics in a partitioned database
Row Relocation and Additional Performance topics
Identify the types of table queues that may be used by DB2
List the row relocation techniques that DB2 may use, including collocation, directed and broadcast
Identify the Fast Communication Manager (FCM) configuration parameters and how they can be monitored using the snapshot commands, administrative views or using db2pd commands
Implement check constraints to define a UNION ALL VIEW to simply access and improve performance to large amounts of data
Utilize Referential Integrity constraints that are NOT ENFORCED to improve the access plans and performance for SQL statements
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.
