Front cover -- Contents -- Figures -- Tables -- Examples -- Notices -- Trademarks -- Preface -- The team that wrote this redbook -- Become a published author -- Comments welcome -- Chapter 1. Introduction to performance management -- 1.1 Introduction -- 1.2 Performance management -- 1.3 Types of monitoring -- 1.3.1 Routine monitoring -- 1.3.2 Online/realtime event monitoring -- 1.3.3 Exception monitoring -- 1.4 Problem determination methodology -- Chapter 2. DB2 UDB architecture overview -- 2.1 Introduction -- 2.2 Main components of DB2 -- 2.2.1 High level overview of main DB2 components -- 2.2.2 High level overview of DB2 architecture and processes -- 2.2.3 Process model -- 2.2.4 Memory model -- 2.3 Single user transaction/query flow -- 2.3.1 Transaction flow with connection concentrator enabled -- 2.3.2 Transaction flow with connection concentrator disabled -- 2.4 Multi-user (concurrent) transaction/query flow -- 2.5 Key performance knobs -- 2.5.1 Configuration Advisor and AUTOCONFIGURE -- 2.5.2 Database Manager (DBM) configuration parameters -- 2.5.3 Database (DB) configuration parameters -- 2.5.4 DB2 registry and environment variables -- 2.6 Performance monitoring facilities -- 2.6.1 CLI/ODBC/JDBC trace -- 2.6.2 Database System Monitor -- 2.6.3 DB2 administration notification log -- 2.6.4 db2batch -- 2.6.5 db2diag.log -- 2.6.6 DB2 Performance Expert -- 2.6.7 Design Advisor -- 2.6.8 Explain and Visual Explain -- 2.6.9 Heath Monitor and Health Center -- 2.6.10 Memory Tracker -- 2.6.11 Memory Visualizer -- Chapter 3. Application design and system performance considerations -- 3.1 OLTP and BI characteristics -- 3.1.1 OLTP characteristics -- 3.1.2 BI characteristics -- 3.2 Key performance drivers -- 3.3 Application design considerations -- 3.3.1 Table design -- 3.3.2 MDC design considerations -- 3.3.3 MQT/AST design considerations.

3.3.4 Index design -- 3.3.5 Table space design -- 3.3.6 Writing efficient SQL -- 3.3.7 Concurrency -- 3.4 System environment considerations -- 3.4.1 I/O placement considerations -- 3.4.2 Log considerations -- 3.4.3 Monitor switch settings -- 3.4.4 Connection considerations -- 3.4.5 Buffer pool considerations -- 3.4.6 Locking considerations -- 3.4.7 Package cache considerations -- 3.4.8 Catalog cache considerations -- 3.4.9 Sort considerations -- 3.4.10 Other memory considerations -- 3.4.11 Miscellaneous considerations -- Chapter 4. Command and utility considerations -- 4.1 Introduction -- 4.2 Backup -- 4.2.1 Brief description -- 4.2.2 Performance considerations -- 4.2.3 Best practices -- 4.3 Export -- 4.3.1 Brief description -- 4.3.2 Performance considerations -- 4.3.3 Best practices -- 4.4 Import -- 4.4.1 Brief description -- 4.4.2 Performance considerations -- 4.4.3 Best practices -- 4.5 Load -- 4.5.1 Brief description -- 4.5.2 Performance considerations -- 4.5.3 Best practices -- 4.6 Reorg -- 4.6.1 Brief description -- 4.6.2 Performance considerations -- 4.6.3 Best practices -- 4.7 Restore -- 4.7.1 Brief description -- 4.7.2 Performance considerations -- 4.7.3 Best practices -- 4.8 Runstats -- 4.8.1 Brief description -- 4.8.2 Performance considerations -- 4.8.3 Best practices -- Chapter 5. Operating system considerations -- 5.1 Introduction -- 5.2 AIX platform -- 5.2.1 Operating system considerations -- 5.2.2 Memory considerations -- 5.2.3 Disk and filesystem considerations -- 5.2.4 Monitoring and problem determination tools -- 5.3 Windows platform -- 5.3.1 Operating system considerations -- 5.3.2 Memory considerations -- 5.3.3 Disk and filesystem considerations -- 5.3.4 Monitoring and problem determination tools -- Chapter 6. Problem determination scenarios -- 6.1 Introduction -- 6.2 DB2 hypotheses hierarchy.

6.2.1 DB2 database server system resource constraints -- 6.2.2 DB2 system resource constraints -- 6.3 Exception event scenarios -- 6.3.1 Lock waits due to default LOCKTIMEOUT value (OLTP) -- 6.3.2 Poor SQL performance due to missing indexes (OLTP) -- 6.3.3 Poor SQL performance due to unused MQTs (BI) -- 6.4 Routine monitoring scenarios -- 6.4.1 Deteriorating space utilization conditions (BI) -- 6.4.2 Deteriorating buffer pool hit ratios (OLTP) -- 6.5 Online/Realtime monitoring scenarios -- 6.5.1 Lock contention (OLTP) -- Appendix A. DB2 UDB ESE Version 8 performance enhancements -- A.1 Introduction -- A.2 Application-related performance enhancements -- A.2.1 Multidimensional clustering -- A.2.2 MQT enhancements -- A.2.3 Compression of NULLS and DEFAULT -- A.2.4 Load enhancements -- A.3 System-related performance enhancements -- A.3.1 Prefetching enhancements -- A.3.2 Faster page cleaners -- A.3.3 Connection concentrator -- A.3.4 Type 2 indexes -- A.3.5 Stored procedures and UDFs thread-based model -- A.3.6 DMS container enhancements -- A.3.7 RUNSTATS enhancements -- A.3.8 Logging enhancements -- A.3.9 Manageability enhancements -- A.4 DB2 UDB Version 8.1.4 -- A.4.1 Backup compression -- A.4.2 Range-clustered tables -- A.4.3 Direct I/O support on AIX -- A.4.4 Asymmetric index splitting -- A.4.5 Buffer pool memory allocation -- A.4.6 Page cleaning enhancements -- A.4.7 Lock deferral -- A.4.8 Improved sort performance -- Appendix B. Workloads used in the scenarios -- B.1 Introduction -- B.2 DTW workload -- B.3 EBIZ database -- B.4 Trade2 database and application -- B.5 WebSphere Performance Tools (WPT) -- Related publications -- IBM Redbooks -- Other publications -- Online resources -- How to get IBM Redbooks -- Help from IBM -- Index -- Back cover.