|
|
DB2 Application Design and Tuning
(Version 6)
| Duration: 3 days |
| Participants: Application programmers and developers involved in designing and tuning DB2 applications and database admin-is-trators who assist programmers in tuning applications. Experience designing and coding DB2 applications in a procedural language such as COBOL, C/370, or PL/I and an understanding of DB2 data definition concepts are required. |
|
Objectives:
Upon successful completion of this course you will be able to:
- Design, implement, and tune physical DB2 databases for optimal performance and concurrency while maintaining the integrity of the logical relational design. - Minimize cost, response time, and locking contention by using a proven methodology to design and implement both online and batch DB2 applications. - Analyze catalog and EXPLAIN reports to determine DB2 optimizer access path selection, and tune SQL statements to use optimal access paths. - Analyze DB2 accounting and performance trace reports to resolve performance and locking problems. |
| Overview: This course focuses on tuning individual applications within the DB2 environment. Students run EXPLAINs, then analyze 12 different EXPLAIN outputs in order to identify ways to improve performance and resolve problems. We recommend a design-monitor-tune cycle to guide the application development process and, to reinforce this strategy, we have organized this course around the same cycle. |
| Prerequisites: Experience designing and coding DB2 applications in a procedural language such as COBOL, C/370, or PL/I and an understanding of DB2 data definition concepts are required. |
| Format: Lecture and discussion with hands-on workshops with special focus on tuning individual applications within the DB2 environment. |
|
Topic Outline:
- Introduction Relational vs hierarchical databases Interdependence of system and application tuning Location of design and tuning expertise in the organization Cost of not designing for performance Importance of monitoring and tuning in production - Methodology Design/monitoring/tuning cycle Techniques - efficiency and expense Knowledge of application (technical vs application tuning) Overview of monitoring tools - Physical Database Design Review of database definition language Resource usage, concurrency, and availability Normalization and denormalization Tablespace design Datasets and storage groups Segmented, simple, and partitioned Freespace Lock sizes Column considerations Variable-length fields Nulls Decimal values Date and timestamp Additional considerations Views Referential integrity performance considerations Index design Costs and benefits Clustering Unique and compound keys Definition parameters Utility considerations - Application Design Techniques Application vs DB2 processing Efficient transactions Thread creation and reuse Authorization checking SQL Commits Locking Sequences Isolation levels Timeout and deadlock scenarios Update sequences Joins and subqueries Tuning for concurrency Avoiding timeouts and deadlocks Update contention Insert contention Retry logic Browse transactions Batch processing Commits Checkpoint/restart Alternatives to mass update/delete QMF ad hoc query applications Shadow environments and contention Indexes Governor - DB2 Architecture Components Stage 1 vs stage 2 predicates Transaction flow Dynamic query flow Other processes Access types Scans (tablespace, clustered and non-clustered index) Matching index (unique, duplicate, index-only) Multiple index access ("ANDing" and "ORing") Sorts Sequential and list prefetch - Access Path Selection Joins: nested loop, merge scan, hybrid Subqueries: non-correlated and correlated Group by and order by Scalar and column functions Cursors Predicate processing Predicate types Sequence Filtering RUNSTATS Catalog tables and columns EXPLAIN Analysis Access path selection process - Monitoring Monitoring tools (traces, program shells, catalog EXPLAIN, etc.) DB2PM or INSIGHT overview Developing a strategy Cost reports, response time, and contention drive application and SQL tuning Catalog and VSAM reports drive REORGS, space allocation, and dataset placement Plan table and catalog reports drive index evaluation Anomaly reports (locking escalation, prefetch, buffer usage) Using traces DB2 trace types and commands Standard output destination Products Accounting trace summary and detail Performance trace SQL summary to identify high-cost cursors SQL detail: DML flows - Tuning SQL Cost factors: CPU, I/O, and SORT SQL coding considerations by release (tricks of the trade) Summary of index usage Update of catalog statistics Altering and adding indexes - Closing Considerations Common bottlenecks in different environments Trade-offs Ease of development vs performance Contention for resources among applications Batch and online coexistence Ad hoc environments Tuning environment Development support, design reviews, code walkthroughs Prototyping (identify objectives and requirements) Production tuning Production DDL and data in the tuning environment |
|
|