Achieving maximum performance from DB2 requires a different approach and stricter methodologies than simply getting a DB2 application to perform well. In today’s web and SOA 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 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 9 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 and we will cover migration considerations for 9 and 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
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
Usage
List prefetch impact
Correct sizing
Performance problems
Application impacts
Statistics detail/failure types
Sort Pool
Usage
Correct sizing/workfiles
Potential performance problems
Statistics detail
Application performance detail
EDM Pool
Correct sizing
Impacts of new features
Monitoring its 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