Achieving maximum performance from DB2 requires a different approach and stricter methodologies than simply getting a DB2 application to perform well. In today’s high volume/high performance environments the rules have changed and we must find every tuning knob in order to get another transaction through the system. This seminar focuses on specific issues of system design, physical object design, application design and tuning methods used to achieve high performance. Topics such as table and index design, large tables and NPSI’s, DPSIs, pushing logic into SQL, control and concurrency, enterprise wide performance topics are examined for OLTP, batch, and data warehousing. Topics such as buffer pool design and utilization, concurrency and locking, DB2 compression, EDM pool sizing, RID pool sizing, Sort work and work file tuning, and parallelism are examined, and it is shown how poor memory tuning directly effects application performance. Focus is on the complete set of design points of DB2, and how they will affect performance of your applications. Discover why many old guidelines no longer apply, how old standards can actually hurt us and learn the approaches to systems management and tuning for the DB2 environment. This course gives new ideas on achieving the new demands placed on us in today’s high volume/high availability environments. The rules have changed and certainly in DB2 10 there are many more features to take advantage of and to be careful with!
The case study examples and stories told are from actual performance audits and design/development efforts.
All topics and examples are current with DB2 z/OS 10.
System Overview
– Some Internals for Discussion, Discussion of All Performance Opportunities
Physical Database Design Issues
– Table Design, Non-attribute Columns, Code Tables and Reference Tables, Materialized Query Tables, Denormalization, Column Placement, Reordered Record Format, Large Tables and their indexes, Partitioning for size and performance, Universal Table spaces, Clone Tables, Index Design, Clustering versus non-clustering indexes, Append processing, Partitioning index design, Referential integrity and index design, NPSIs and Pieces, DPSIs,Hash access, Temporal Tables, Compression (tablespace and index), Designing for 24×7, Large table case study
Concurrency
- Locking issues, Coding for minimal locking overhead, Impacts of row level locking, Lock avoidance, Lock monitoring – proactive vs reactive, Use of optimistic locking
Bufferpools
- Buffer manager overview, Impacts on application performance, Buffer pool parameters, Bufferpool use, thresholds, and performance, Sequential prefetch impact, List prefetch impact, Checkpoint impacts, Virtual pool design strategies
RID Pool
- List prefetch impact, Application impacts, Statistics detail/failure types, Work file usage for RID overflows
Sort Pool
– Correct sizing/workfiles, Work file users, Potential performance problems, Application performance detail
EDM Pool
Impacts of new features, Monitoring efficiency, EDM pool and I/O activity, Dynamic SQL caching
Application Design Issues
– Load Reduction by Application Transaction Re-design, Uncommitted Read, Skipping Locked Data, Currently Committed Data, RELEASE(DEALLOCATE), DEGREE(ANY), Plan Stability
Parallelism
– Query CPU Parallelism, How the Degree is determined, Buffer Pool Impacts on Parallelism, Basic Trace Records for Parallelism, IFCID 221 &222
SQL Performance Issues
– Code for Reduced CPU, Pushing Logic into the SQL, Binding and Reoptimization Issues, Filtering Data, Dynamic SQL Cache Usage, Explain Tables and best usage
OLTP
– SQL Transaction Cost, Sequential Numbering Issues
Batch
– Reducing CPU and I/O, Checkpoint/Restart, Commits and Savepoints, Heuristic control tables, Browsing Performance
Scrollable Cursors, Mass updates, deletes and inserts
Final Guidelines
HR
EN
SL