DB2® High Performance Design and Tuning

RICHARD YEVICH is an internationally recognized consultant and teacher and principal of YL&A, Inc., a firm specializing in consulting, performance audits, performance publications, and Web database software development which assists businesses in exploiting e-commerce technologies. An IBM Gold Consultant for DB2 and S/390, he co-authored Data Warehouse Practical Advice from the Experts (Prentice Hall PTR) and DB2 Answers, and is widely published in trade journals and magazines. SUSAN LAWSON, Principal Consultant with YL&A, has worked with DB2 for over a dozen years. She has served as an IBM Data Sharing advocate, supporting many large DB2 customers and providing technical expertise for migration to DB2 Data Sharing in Parallel Sysplex environments. Now a Director of the Midwest DB2 Users Group and IBM Gold Consultant for DB2 and S/390, she is Technical Editor of the IDUG Solutions Journal.

Setting the Stage.


The Legacy.


Performance Does Not Come Easy.


The Realization.


Why the Myths Arose.


Top 20 Myths.
Section 1. Environment and Tuning.

1. I/O and Storage Management.


Data Facility Storage Management Subsystem (DFSMS). Data Classes. Storage Classes. Management Classes. Storage Groups. Reclaiming Space. Disk Storage. Tape versus Disk. Data-Set Allocation and Placement. I/O Subsystem. Understanding I/O. I/O Scheduling Priority. Cache Considerations. SEQCACH. Enterprise Storage Server (ESS). Features Benefiting DB2. Tuning I/O and Caching. I/O and Caching Summary. Compression. DB2 Compression Methods. What Is and Is Not Compressed. Performance Issues. Logging. Log Reads. Log Writes. Active Logs. Archive Logs.

2. CPU.


CPU and SQL Design Issues. Number of SQL Statements. Number of Rows Retrieved. Number of Columns Retrieved. Unnecessary Repetitive Processes. Referential Integrity. Sorting. Physical Design Issues. Application Design Issues. I/O Layers. Benefits of SQL Tuning for CPU. Life of xPROCs. Address Space Priority. Mixed Workloads and WLM. Test and Production Mix. WLM. OLTP and Warehouses. IRLM. Thread Reuse. CICS. IMS. Traces.

3. Memory.


Virtual Storage. Address Spaces. Below the 16MB Line. Buffer Pools. Overview. Pages. Virtual Buffer Pools and Hiperpools. Buffer Pool Queue Management. I/O Requests and Paging. Page Externalization. Buffer Pool Parameters. Internal Thresholds. Buffer Pool 0 (BP0). Virtual-Pool Design Strategies. DSNDB07: A Different Kind of Buffer Pool. Code, Decode, Reference, and Lookup Tables. The Hit Ratio. Tuning with the DISPLAY BUFFERPOOL Command. RID Pool. Size. Statistics to Monitor. Effects on Optimization. SORT Pool. DB2 and Sorting. Size. DSNDB07 Work Files. Application Impacts. EDM Pool. Size. EDM Pool Efficiency. EDM Pool Issues.

Section 2. Database Design and Tuning.

4. Catalog and Directory.


Overview. Catalog. Storage and Maintenance. Directory. SPT01 Skeleton Package Table (SKPT) and SCT02 Skeleton Cursor Table (SKCT). DBD01 DBDs. SYSLGRNX and SYSUTILX. Shadow Catalog. ODBC Catalog. Reducing Catalog Contention. Manually Updating Catalog Statistics. Key Correlation Statistics. Catalog Queries. Queries to Use with EXPLAIN Output. Finding Extraneous Authorizations. Identifying Partitioning Keys. Determining When to REORG an Index. Determining Foreign Keys without Indexes. Showing Foreign Keys Fully Supported by Indexes. Identifying Index Columns. Primary Key Information. Tables to Index to Keys to Columns. Determining RI Relationships. Synonyms Not Used. Displaying Multicolumn Cardinalities. Validating Manually Updated Multicolumn Cardinalities. Re-engineering DDL from the Catalog.

5. Physical Database Objects.


Guidelines for Optimal Design. Table Spaces. Table Spaces per Database. Segmented Table Spaces. SEGSIZE. Tables per Table Space. Partitioning. Table Space Compression. Free Space. Data-Set Closure. LOCKSIZE. Lock Escalation. Selective Partition Locking. Reorganizing Table Spaces. Tables. Normalization. Denormalization. Splitting Tables Based on Usage. Auditing. Columns. Ordering. VARCHAR Columns. Determining the Row Length. COMMENT ON Clause. NULL Usage. Distinct Data Types. Identity Columns. Nonattribute Columns. Referential Integrity. Mapping Logical Relationships to Physical RI Relationships. When to Use Declarative RI. Table Check Constraints. RI or Table Check Constraints. Indexes. Clustering Index. Primary-Key Indexes. Surrogate-Key Indexes. Indexes on Foreign Keys. Nonpartitioning Indexes. Reorganizing Indexes. Views. Base Table Views. Views versus Direct Table Access. Schemas.

6. VLDBs, VLTBs, and Warehouses.


Very Large Databases (VLDBs). Very Large Tables (VLTBs). Nonpartitioning Indexes. Warehouses. Operational Data Warehouses. Data Marts. VLDB Warehouses. Data Warehouse and OLTP. Designing Warehouses.

7. Large Objects (LOBs).


LOB Data Types. LOB Support for Multimedia Objects. LOB Table Spaces. Page Sizes. LOB Tables. Indicator Columns. ROWID. LOB Indexes. LOB Implementation. Automatic Creation of LOBs. LOB Restrictions. Inserting and Loading LOBs. Buffer Pool Impacts. Logging Options. Locking Options. LOBs and Data Sharing. LOBs and User-Defined Types. LOBs and User-Defined Functions.

8. Special Tables.


Code, Reference, and Decode Tables. Table Design. Locking. Buffer Pools. Logical Locking and Checkout Tables. Number Control and Sequencing. Heuristic and Dynamic Control Tables. Control and Restart Tables. Partition Availability and Access. Historical Tables. Applications. Physical Design. SQL Access. Restoring Archived Data.

9. Roles in Database Implementation.


The Changing Role of Data Administrators. Data Administrators versus Database Administrators. Process-Driven Legacy. Logical Modeling. Life-Cycle Development. Database Administrators. DBA Ratio. Impacts of Warehouses. Impacts of E-Business. Database Procedural Programmers.

Section 3. Application Design and Tuning.

10. General Application Design.


Program Structure. Putting the Logic in the SQL. Retrieving Only What Is Needed. I/O Modules. Cursors. Application-Enforced Referential Integrity. Plans and Packages. When to Rebind Packages. Dynamic SQL. Dynamic SQL Cache. KEEPDYNAMIC and MAXKEEPD. Monitoring the Dynamic SQL. Program Functionality. Retry Logic. Commit Strategies. Heuristic Control Tables. Binding Programs and Commit Considerations. Rollbacks. Savepoints. Establishing a Savepoint. Restoring to a Savepoint. Releasing a Savepoint. Isolation Levels. Logging. What Gets Logged.

11. Version 6 Late Additions and Version 7.


Version 6 Late Additions. External Savepoints. Star Join. Declared Temporary Tables. Identity Columns. LOG SUSPEND and RESUME. Defer Definition. IFI Consolidation. DDF SUSPEND and RESUME. Update with Subselect. REXX Language Support and Stored Procedures. Up to 225 Tables. Version 7. Applications. Scalability. Availability. Management. Control Center. Access. Resources for Latest DB2 Information from IBM.

12. Program Design and Processing.


Batch Processing Issues. Cursors and Parallelism. Sequential Numbering. Mass Inserts. Star Joins. Transaction Processing Issues. General Index Design. Browsing Data. Cursor Repositioning. Transaction Design Issues. CICS Considerations. Reverse-Order Retrieval. Dependent Table Cascade.

13. Locking and Concurrency.


Programming for Concurrency. Row-Level Locking. Unique Generated Numbers and Identity Columns. Releasing Locks. DSNZPARMs of Interest. IRLMRWT. RECURHL. XLKUPDLT. NUMLKTS. NUMLKUS. LOCK TABLE Statement. Deadlocks and Retry Logic. Lock Escalation. Monitoring Lock Escalation. Selective Partition Locking. Monitoring Locking. Monitoring Time-outs and Deadlocks. Lock Wait Time. Recommendations for Lock Performance. DISPLAY DATABASE LOCKS. Lock Avoidance. Bind Option. Using Uncommitted Read. Determining Whether Lock Avoidance Is Used.

14. Temporary Tables.


Global Temporary Tables. Creating a Global Temporary Table. Determining How Often Global Temporary Tables Are Materialized. Declared Temporary Tables. Defining. Referencing. Usage.

15. Enterprise Resource Planning.


ERP/DB2 Usage Overview. Separate DB2 Subsystem. Database Objects. Tables and Table Spaces. Indexes. Dynamic SQL. Dynamic SQL Cache. SQL and Optimization. Key Correlation Statistics. List Prefetch. Uncommitted Read. PREPARE and EXECUTE Messages. Releasing Locks. Batch Workload Location. Row-Level Locks. Lock Escalation. LOGLOAD Monitoring. Buffer Pool Tuning. Keep the DB2 Catalog Clean. DB2 as an ASCII Server. Conclusion.

Section 4. SQL and Advanced Function Tuning.

16. SQL.


DB2 SQL Engine Review. General Recommendations. Retrieve the Fewest Rows. Retrieve Only the Columns Needed. Reduce the Number of SQL Statements. Code Predicates Based on Selectivity. Use Stage 1 Predicates. Never Use Generic SQL Statements. Avoid Unnecessary Sorting. Sort Only Necessary Columns. Use the ON Clause for All Join Predicates. Avoid UNIONs. Use Joins Instead of Subqueries. Code the Most Selective Predicates First. Use the Proper Method for Existence Checking. Avoid Anything Unnecessary. Special Techniques. CASE in Predicates. GROUP BY to Allow Single Pass. GROUP BY to Work Both Sides.

17. Triggers and User-Defined Functions.


Triggers. Trigger Definitions. Creating and Adding Triggers. Triggers versus Table Check Constraints. Triggers and Declarative RI. Trigger Invalidations. Performing Actions outside a Database. Performance Issues. Monitoring and Controlling Triggers. User-Defined Functions. Sourced Scalar Functions. External Functions. Examples of UDFs That Come with DB2. UDF in a Trigger. UDF Restrictions. UDF Performance Considerations. Monitoring and Controlling UDFs.

18. LOBs and Extenders.


Extenders. LOB and Extender Usage. Enabling Extenders. Text Extenders. Image, Audio, and Video Extenders. XML Extenders.

19. Stored Procedures.


Stored Procedures. Language Support. Performance Considerations. Reentrant Code. Fenced and Nonfenced Procedures. Limiting Resources Used. Workload Manager. CICS EXCI. Programming Considerations. LE/370 Required. COMMIT ON RETURN. Return of Columns. Result Sets. Nesting. Stored Procedure Builder. SQL Procedures and the Stored Procedure Builder. Java and the Stored Procedure Builder. DB2-Delivered Stored Procedures. DSNWZP. DSNWSPM. DSNACCMG. DSNACCAV. DSNUTILS. Commands and Monitoring.

20. REXX, Java, and SQL Procedure Language.


REXX. Java and DB2. JDBC and SQLJ. Java. SQL Procedure Language.

21. Parallelism.


Query Parallelism. When Parallelism Is Chosen. Degree of Parallelism. Queries Best Suited for Parallelism. Achieving Parallelism for Nonpartitioned Table Spaces. Parallelism on Nonpartitioning Indexes. Parallelism Impacts on Buffer Pools. Parallelism and Hiperpools. Parallelism and I/O. Trace Records for Parallelism. EXPLAINing Parallelism. Sysplex Query Parallelism. Controlling Sysplex Query Parallelism. Buffer Pool and Work File Impacts. IRLM Storage Impact. Monitoring Sysplex Query Parallelism. Utilities and Parallelism.

22. Predictive Analysis, Governing and Tuning.


EXPLAIN. EXPLAIN Explained. The Plan Table. The Statement Table. The Function Table. Effective Use of EXPLAIN's Output. Important Information Not in EXPLAIN. Developing a Methodology. Exception Reporting. EXPLAIN Interpretation. Predictive Governing. RLST. DSN_STATEMNT_TABLE. Monitoring and Tracing. DB2 Tracing. DB2PM. IFCID and Trace Field Descriptions.

23. Influencing the Optimizer.


The DB2 Optimizer. Access Path Selection Statistics. Global Temporary Table Statistics. User-Defined Table Function Statistics. Key Correlation Statistics. DSTATS. Updating Catalog Statistics. Influencing Access Path Selection. OPTIMIZE FOR n ROWS. Manipulating Index Selection. Forcing Partition-Range Scanning. Direct Row Access. Access Path Hints. Reoptimization at Run Time. Version and Release Upgrades.

24. E-Business, the Web, and Networks.


E-Business and the Web. DB2 Web Application Performance. Java. Net.Data. Workload Management. XML. Connection Performance Issues. Stored Procedures, User-Defined Functions, and Triggers.

Section 5. Data Sharing Design and Tuning.

25. Data Sharing Overview and Processing Costs.


Data Sharing Benefits. Performance. Processing Costs. Movement to Data Sharing. Application Analysis. Migration Considerations. Current Environment Evaluation.

26. Hardware and Configuration Considerations.


Coupling Facility. Dedicated Coupling Facility versus ICMF. Storage. Links. Cycles. Internal Coupling Facilities. Geographically Dispersed Parallel Sysplex. Cross-Site Reconfiguration. GDPS Configuration Options. GDPS Implementation. GDPS Impacts on DB2 Data Sharing.

27. Locking.


Overview of Locking in a Data-Sharing Environment. Explicit Hierarchical Locking. Lock Management. Lock Avoidance and Data Sharing. Global Locking Considerations. Lock Structure. Locking Support by Various Data-Sharing Components. Locking Contention. Deadlock Considerations.

28. Group Buffer Pools.


Page Registration. Registration. Deregistration. Sizing. Generic Guidelines and Rules of Thumb. GBP Sizing for Cached and Changed, Noncached, and LOB Data. Tuning. Ratio. Castout and Checkpoint. GBPCACHE Option. Group Buffer Pool Dependency. PCLOSEN and PCLOSET. Group Buffer Pool Duplexing.

29. Migration.


Sharing of Data. Policies and Structures. CFRM. SFM. ARM. Lock, SCA, and GBP Structures. Naming Conventions. Workload Distribution Planning. CICS Workload Distribution. Distributed Workload. Migration Considerations. Install or Merge. Originating Member. Migration of Catalog and User Data. Measuring the Migration.

30. Application Tuning.


Lock Avoidance. Bind Parameters. RELEASE. CURRENTDATA. ISOLATION. IMMEDWRITE.

31. Physical Design.


Partitioning. Selective Partition Locking. Type 2 Indexes. Row-Level Locking versus MAXROWS = 1. Multiple Page Sizes. Member Clustering. Space Map Page Tracking. Nonpartitioning Indexes.

32. Problem Diagnosing.


System Hangs. Application Hangs. Deadlocks and Time-Outs. Inconsistency and Incoherency. Application Error Checking.

33. Long-Term Monitoring.


Group Buffer Pools. DISPLAY GROUPBUFFERPOOL Command. Statistics Report. Locking. Activity. Contention. Coupling Facility Resources. Conclusion.

Index.