IBM DB2 Universal database 重重 Administration guide performance Version 8 sc09-4821-00
IBM® DB2 Universal Database™ Administration Guide: Performance Version 8 SC09-4821-00
IBM DB2 Universal database 重重 Administration guide performance Version 8 sc09-4821-00
IBM® DB2 Universal Database™ Administration Guide: Performance Version 8 SC09-4821-00
efore using this information and the product it supports. be sure to read the general information under Notices. This document contains proprietary information of IBM. It is provided under a license agreement and is protected by copyright law. The information contained in this publication does not include any product warranties, and any tatements provided in this manual should not be interpreted as such. You can order IBM publications online or through your local IBM representative ToorderpublicationsonlinegototheIbMPublicationsCenteratwww.ibm.com/shop/publications/order To find your local IBM representative go to the IBM Directory of Worldwide Contacts at www.ibm.com/planetwide o order DB2 publications from DB2 Marketing and Sales in the United States or Canada, call 1-800-IBM-4YOU (426-4968) When you send information to IBM, you grant IBM a nonexclusive right to use or distribute the information in any way it believes appropriate without incurring any obligation to you. o Copyright International Business Machines Corporation 1993-2002. All rights US Government Users Restricted Rights- Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp
Before using this information and the product it supports, be sure to read the general information under Notices. This document contains proprietary information of IBM. It is provided under a license agreement and is protected by copyright law. The information contained in this publication does not include any product warranties, and any statements provided in this manual should not be interpreted as such. You can order IBM publications online or through your local IBM representative. v To order publications online, go to the IBM Publications Center at www.ibm.com/shop/publications/order v To find your local IBM representative, go to the IBM Directory of Worldwide Contacts at www.ibm.com/planetwide To order DB2 publications from DB2 Marketing and Sales in the United States or Canada, call 1-800-IBM-4YOU (426-4968). When you send information to IBM, you grant IBM a nonexclusive right to use or distribute the information in any way it believes appropriate without incurring any obligation to you. © Copyright International Business Machines Corporation 1993 - 2002. All rights reserved. US Government Users Restricted Rights – Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp
Contents About this book Memory management Who should use this book How this book is structured a brief overview of the other administration Part 2. Tuning application Guide volumes performance Administration Guide: Planning Administration Guide: Implementation Chapter 3 Application considerations . 51 Concurrency control and isolation levels Part 1. Introduction to performance 51 Performance impact of isolation levels.. 52 Chapter 1 Introduction to performance ..3 Concurrency control and locking Elements of performance Locks and concurrency control Performance-tuning guidelines Lock attributes The performance-tuning process Locks and performance Developing a performance-improvement Guidelines for locking 5 Correcting lock escalation problen Performance information that users can Lock type compatibility Lock modes and access paths for standard Performance-tuning limits 7 7391880247 Quick-start tips for performance tuning Lock modes for table and rid index scans of mDC tables Chapter 2. Architecture and processes Locking for block index scans for MDC DB2 architecture and process overview Deadlocks between application 14 Factors that affect locking Disk ste 15 Factors that affect locking Disk-storage performance factors Database directories and files Locks and data-access methods Table space overview 19 Index types and next-key locking Table spaces Optimization factors SMS table spaces Optimization class guidelines DMs table spaces Optimization classes 70888588803 Locks and types of application processing 84 llustration of the DMS table-space address Setting the optimization cla map. Tuning applications Tables and indexes Guidelines for restricting select statements 95 Table and index management for standard Specifying row blocking to reduce Table and index management for MDC Query tuning guidelines Efficient Select statements 101 Compound SQl guidelines 103 Processes Character-conversion guidelin Insert process Parallel processing for applications Update process Client-server processing model Chapter 4. Environmental considerations 111 o Copyright IBM Corp. 1993-2002
Contents About this book . . . . . . . . . . ix Who should use this book. . . . . . . .x How this book is structured . . . . . . .x A brief overview of the other Administration Guide volumes . . . . . . . . . . . xi Administration Guide: Planning . . . . xi Administration Guide: Implementation . . xii Part 1. Introduction to performance 1 Chapter 1. Introduction to performance . . 3 Elements of performance . . . . . . . .3 Performance-tuning guidelines . . . . . .4 The performance-tuning process . . . . .5 Developing a performance-improvement process . . . . . . . . . . . . .5 Performance information that users can provide . . . . . . . . . . . . .6 Performance-tuning limits . . . . . . .7 Quick-start tips for performance tuning . . . 7 Chapter 2. Architecture and processes . . 11 DB2 architecture and process overview . . . 11 Deadlocks between applications . . . . . 14 Disk storage overview . . . . . . . . 15 Disk-storage performance factors . . . . 15 Database directories and files . . . . . 16 Table space overview . . . . . . . . . 19 Table spaces . . . . . . . . . . . 19 SMS table spaces . . . . . . . . . 19 DMS table spaces . . . . . . . . . 20 Illustration of the DMS table-space address map. . . . . . . . . . . . . . 22 Tables and indexes . . . . . . . . . . 23 Table and index management for standard tables . . . . . . . . . . . . . 23 Table and index management for MDC tables . . . . . . . . . . . . . 28 Index structure . . . . . . . . . . 31 Processes . . . . . . . . . . . . . 32 Logging process. . . . . . . . . . 33 Insert process . . . . . . . . . . 34 Update process . . . . . . . . . . 35 Client-server processing model. . . . . 36 Memory management. . . . . . . . 44 Part 2. Tuning application performance . . . . . . . . . . 49 Chapter 3. Application considerations . . 51 Concurrency control and isolation levels . . 51 Concurrency issues. . . . . . . . . 51 Performance impact of isolation levels . . 52 Specifying the isolation level . . . . . 57 Concurrency control and locking . . . . . 59 Locks and concurrency control . . . . . 59 Lock attributes . . . . . . . . . . 61 Locks and performance . . . . . . . 63 Guidelines for locking. . . . . . . . 68 Correcting lock escalation problems . . . 70 Lock type compatibility . . . . . . . 72 Lock modes and access paths for standard tables . . . . . . . . . . . . . 74 Lock modes for table and RID index scans of MDC tables . . . . . . . . . . 77 Locking for block index scans for MDC tables . . . . . . . . . . . . . 80 Factors that affect locking . . . . . . . 83 Factors that affect locking . . . . . . . 84 Locks and types of application processing 84 Locks and data-access methods . . . . 85 Index types and next-key locking . . . . 86 Optimization factors . . . . . . . . . 88 Optimization class guidelines . . . . . 88 Optimization classes . . . . . . . . 90 Setting the optimization class . . . . . 93 Tuning applications . . . . . . . . . 95 Guidelines for restricting select statements 95 Specifying row blocking to reduce overhead . . . . . . . . . . . . 99 Query tuning guidelines . . . . . . 101 Efficient SELECT statements . . . . . 101 Compound SQL guidelines . . . . . 103 Character-conversion guidelines . . . . 105 Guidelines for stored procedures. . . . 106 Parallel processing for applications . . . 107 Chapter 4. Environmental considerations 111 © Copyright IBM Corp. 1993 - 2002 iii
Database partition group impact on query Compiler rewrite example: implied predicates 173 Table space impact on query optimization 111 Column correlation for multiple rvel options affecting fede era predicates 174 Data access methods Chapter 5. System catalog statistics 117 Data-access methods and concepts Catalog statistics 117 Data access through index scans Collecting and analyzing catalog statistics 119 Types of index access 181 Guidelines for collecting and updating Index access and cluster ratios statistics Predicate terminology Collecting catalog statistics Join methods and strategies Collecting distribution statistics for Joins Join methods Collecting index statistics Strategies for selecting optimal joins 191 Statistics collected Replicated materialized-query tables in Catalog statistics tables artitioned database Statistical information that is collected 131 Join strategies in partitioned databases 196 Distribution statistics Join methods in partitioned databases.. 198 Optimizer use of distribution statistics Effects of sorting and grouping Extended examples of Optimization strategies distribution-statistics use Optimization strategies for intra-partition Detailed index statistics 142 parallelism Sub-element statistics Optimization strategies for MDC tables 209 sers can Automatic summary tables Statistics for user-defined function 146 Federated database query-compiler phases 213 Catalog statistics for modeling and Federated database pushdown analysis 213 what-if planning 147 Guidelines for analyzing where a Statistics for modeling production federated query is evaluated databases Remote SQL generation and global General rules for updating catalog mization in federated databases 220 statistics manually al analysis of federated database Rules for updating column statistics manually 153 Rules for updating distribution statistics Chapter 7. SQL Explain facility manually SOL explain facilit 227 Rules for updating table and nickname Tools for collecting and analyzing explain statistics manually information Rules for updating index statistics Explain tools 228 Guidelines for using explain information 230 Explain information collected 32 Chapter 6. Understanding the SQL The explain tables and organization of The SQL compiler process Explain information for data objects Configuration parameters that affect query Explain information for data operators 234 Explain information for instances Query rewriting Guidelines for capturing explain Query rewriting methods and examples 166 information Guidelines for analyzing explain information 24 Compiler rewrite example: DISTINCT The Design Advisor Iv Administration Guide: Performance
Database partition group impact on query optimization . . . . . . . . . . . 111 Table space impact on query optimization 111 Server options affecting federated databases 115 Chapter 5. System catalog statistics. . . 117 Catalog statistics . . . . . . . . . . 117 Collecting and analyzing catalog statistics 119 Guidelines for collecting and updating statistics . . . . . . . . . . . . 119 Collecting catalog statistics. . . . . . 120 Collecting distribution statistics for specific columns . . . . . . . . . 121 Collecting index statistics . . . . . . 123 Statistics collected. . . . . . . . . . 124 Catalog statistics tables . . . . . . . 124 Statistical information that is collected 131 Distribution statistics. . . . . . . . 134 Optimizer use of distribution statistics 136 Extended examples of distribution-statistics use . . . . . . 141 Detailed index statistics . . . . . . . 142 Sub-element statistics . . . . . . . 144 Catalog statistics that users can update . . 145 Statistics for user-defined functions . . . 146 Catalog statistics for modeling and what-if planning . . . . . . . . . 147 Statistics for modeling production databases . . . . . . . . . . . 149 General rules for updating catalog statistics manually . . . . . . . . 152 Rules for updating column statistics manually . . . . . . . . . . . 153 Rules for updating distribution statistics manually . . . . . . . . . . . 153 Rules for updating table and nickname statistics manually . . . . . . . . 154 Rules for updating index statistics manually . . . . . . . . . . . 155 Chapter 6. Understanding the SQL compiler . . . . . . . . . . . . 159 The SQL compiler process . . . . . . . 159 Configuration parameters that affect query optimization . . . . . . . . . . . 163 Query rewriting . . . . . . . . . . 166 Query rewriting methods and examples 166 Compiler rewrite example: view merges 168 Compiler rewrite example: DISTINCT elimination . . . . . . . . . . . 171 Compiler rewrite example: implied predicates . . . . . . . . . . . 173 Column correlation for multiple predicates . . . . . . . . . . . 174 Data access methods . . . . . . . . . 176 Data-access methods and concepts . . . 176 Data access through index scans . . . . 177 Types of index access . . . . . . . 181 Index access and cluster ratios . . . . 183 Predicate terminology . . . . . . . . 184 Join methods and strategies . . . . . . 186 Joins . . . . . . . . . . . . . 187 Join methods . . . . . . . . . . 190 Strategies for selecting optimal joins . . 191 Replicated materialized-query tables in partitioned databases . . . . . . . 194 Join strategies in partitioned databases 196 Join methods in partitioned databases . . 198 Effects of sorting and grouping . . . . . 204 Optimization strategies . . . . . . . . 206 Optimization strategies for intra-partition parallelism . . . . . . . . . . . 206 Optimization strategies for MDC tables 209 Automatic summary tables. . . . . . . 210 Federated database query-compiler phases 213 Federated database pushdown analysis 213 Guidelines for analyzing where a federated query is evaluated . . . . . 218 Remote SQL generation and global optimization in federated databases. . . 220 Global analysis of federated database queries . . . . . . . . . . . . 223 Chapter 7. SQL Explain facility . . . . 227 SQL explain facility . . . . . . . . . 227 Tools for collecting and analyzing explain information . . . . . . . . . . . . 228 Explain tools . . . . . . . . . . 228 Guidelines for using explain information 230 Explain information collected . . . . . . 232 The explain tables and organization of explain information . . . . . . . . 232 Explain information for data objects . . 234 Explain information for data operators 234 Explain information for instances . . . 235 Guidelines for capturing explain information . . . . . . . . . . . 239 Guidelines for analyzing explain information 241 The Design Advisor . . . . . . . . . 242 iv Administration Guide: Performance
Part 3. tuning and configuring Database-agent management 310 your system 249 Configuration parameters that affect the number of agent 311 Chapter 8 Operational performance Connection-concentrator improvements for client connections Memory usage 251 Organization of memory use Agents in a partitioned database Database manager shared memo The database system-monitor information 316 The FCM buffer pool and memory Chapter 9. Using the governor 319 The governor util Global memory and parameters that control it Governor startup and shutdown Guidelines for tuning parameters that Starting and stopping the governor...320 The Governor daemon affect memory usage Governor configuration Buffer pools 263 Buffer-pool management Configuring the Governor condary buffer pools in extended The Governor configurat memory on 32-bit platforms Governor rule elements 326 Buffer-pool management of data pages 267 Example of a Governor configuration file 331 Illustration of buffer-pool data-page Governor log-file use 269 Governor log file management Management of multiple database buffer Governor log-file queries ls 271 oncepts Chapter 10. Scaling your configurate 337 Prefetching data into the buffer pool Management of database server capacity Sequential prefetching Partitions in a partitioned database Block-based buffer pools for improved Adding a partition to a running database sequential prefetching system 339 List prefetching Adding a partition to a stopped database I/O management system on wir 1/0 server configuration for prefetching Adding a partition to a stopped database llustration of prefetching with parallel.279 system on UNIX and parallelism Node-addition error recovery I/O Parallel I/O management 282 Guidelines for sort performance Chapter 11. Redistributing Data Across Table management Database Partitions 347 Table reorganization Data redistribution Determining when to reorganize tables 28 Determining whether to redistribute data 349 Choosing a table reorganization method 291 Redistributing data across partitio 294 Log space requirements for data Advantages and disadvantages of indexes 294 redistribution Index planning tips Redistribution-error recovery 353 Index performance tips amud a Chapter 12 Benchmark testing Benchmark testing Online index defragmentation Benchmark preparation 5568 dMS device considerations Benchmark test creation Agent managem 308 Examples of db2batch tests Database agents Benchmark test execution Benchmark test analysis example
Part 3. Tuning and configuring your system. . . . . . . . . . 249 Chapter 8. Operational performance. . . 251 Memory usage . . . . . . . . . . . 251 Organization of memory use . . . . . 251 Database manager shared memory . . . 254 The FCM buffer pool and memory requirements . . . . . . . . . . 256 Global memory and parameters that control it. . . . . . . . . . . . 258 Guidelines for tuning parameters that affect memory usage . . . . . . . . 261 Buffer pools. . . . . . . . . . . . 263 Buffer-pool management . . . . . . 264 Secondary buffer pools in extended memory on 32-bit platforms . . . . . 266 Buffer-pool management of data pages 267 Illustration of buffer-pool data-page management . . . . . . . . . . 269 Management of multiple database buffer pools . . . . . . . . . . . . . 271 Prefetching concepts . . . . . . . . . 274 Prefetching data into the buffer pool . . 274 Sequential prefetching . . . . . . . 275 Block-based buffer pools for improved sequential prefetching . . . . . . . 277 List prefetching . . . . . . . . . 278 I/O management . . . . . . . . . . 279 I/O server configuration for prefetching and parallelism . . . . . . . . . 279 Illustration of prefetching with parallel I/O . . . . . . . . . . . . . 280 Parallel I/O management . . . . . . 282 Guidelines for sort performance . . . . 284 Table management . . . . . . . . . 287 Table reorganization . . . . . . . . 287 Determining when to reorganize tables 288 Choosing a table reorganization method 291 Index management . . . . . . . . . 294 Advantages and disadvantages of indexes 294 Index planning tips . . . . . . . . 296 Index performance tips . . . . . . . 299 Index cleanup and maintenance . . . . 302 Index reorganization . . . . . . . . 303 Online index defragmentation . . . . 305 DMS device considerations . . . . . . 307 Agent management . . . . . . . . . 308 Database agents . . . . . . . . . 308 Database-agent management . . . . . 310 Configuration parameters that affect the number of agents . . . . . . . . . 311 Connection-concentrator improvements for client connections . . . . . . . 312 Agents in a partitioned database . . . . 315 The database system-monitor information 316 Chapter 9. Using the governor. . . . . 319 The Governor utility . . . . . . . . . 319 Governor startup and shutdown . . . . . 320 Starting and stopping the governor . . . 320 The Governor daemon . . . . . . . 321 Governor configuration . . . . . . . . 322 Configuring the Governor . . . . . . 323 The Governor configuration file . . . . 324 Governor rule elements . . . . . . . 326 Example of a Governor configuration file 331 Governor log-file use . . . . . . . . 333 Governor log files. . . . . . . . . 333 Governor log-file queries . . . . . . 334 Chapter 10. Scaling your configuration 337 Management of database server capacity . . 337 Partitions in a partitioned database . . . . 338 Adding a partition to a running database system . . . . . . . . . . . . . 339 Adding a partition to a stopped database system on Windows NT . . . . . . . 341 Adding a partition to a stopped database system on UNIX . . . . . . . . . . 342 Node-addition error recovery . . . . . . 344 Dropping a database partition . . . . . 346 Chapter 11. Redistributing Data Across Database Partitions . . . . . . . . 347 Data redistribution . . . . . . . . . 347 Determining whether to redistribute data 349 Redistributing data across partitions . . . 350 Log space requirements for data redistribution . . . . . . . . . . . 352 Redistribution-error recovery . . . . . . 353 Chapter 12. Benchmark testing . . . . 355 Benchmark testing . . . . . . . . . 355 Benchmark preparation . . . . . . . . 356 Benchmark test creation. . . . . . . . 358 Examples of db2batch tests . . . . . . 360 Benchmark test execution . . . . . . . 364 Benchmark test analysis example . . . . 366 Contents v
e configuring DB2 Configuration parameter onfiguration parameter tuning m Name of the DB2 Server Syste onfiguration parameter-db2system ..530 DAS Administration Authority Group Configuring DB2 with configuration dasadm_group Configuration parameters summary. Scheduler Mode configuration parameter Database Manager Configuration sched enable Parameter St 376 Tools Catalog Database Instance Database Configuration Parameter onfiguration parameter-toolscat_inst.. 532 Tools Catalog Database configuration DB2 Administration Server(DAS) Configuration Parameter Summary Tools Catalog Database Schema Parameter Details by Function 390 configuration parameter-toolscat_schema 53 Capacity Management SMTP Server configuration parameter Database Shared Memory smtp_server 534 Application Shared Memory Java Development Kit Installation Path Agent Private Memor DAS configuration parameter-jdk_path. 535 Agent/Application Communication Execute Expired Tasks configuration arameter-exec_exp_task Database Manager Instance Memory Scheduler User ID configuration parameter-sched userid 1/0 and Storage Location of Contact List configuration parameter-contact_host Stored Procedures and User Defined Authentication Type DAS configuration DAS Code Page configuration parameter Database Log Files das_codepage 537 Database Log Activity DAS Territory configuration parameter das_territory istributed Unit of Work Recovery Database Management Query Enabler Part 4. Appendixes .539 Attributes DB2 Data Links manager Appendix A DB2 Registry and Environment variables DB2 registry and enviro Communications Registry and environment variables by Communication Protocol Setup category DB2 Discovery General registry variables 542 Partitioned Database environment System environment variables Communications Communications variables Parallel Processing Instance Management 157 Command-line variable MPP configuration variables 507 SQL compiler variables Database System Monitor Parameters Performance variables System Management Data-links variables stance administration Miscellaneous variables DB2 Administration Server DAS Discovery Mode configuration Appendix B. Explain tables parameter-discover 30 Explain tables 577 vi Administration Guide: Performance
Chapter 13. Configuring DB2 . . . . . 369 Configuration parameters . . . . . . . 369 Configuration parameter tuning . . . . . 371 Configuring DB2 with configuration parameters . . . . . . . . . . . . 372 Configuration parameters summary. . . . 376 Database Manager Configuration Parameter Summary . . . . . . . . 376 Database Configuration Parameter Summary . . . . . . . . . . . 382 DB2 Administration Server (DAS) Configuration Parameter Summary . . . 388 Parameter Details by Function . . . . . 390 Capacity Management . . . . . . . . 391 Database Shared Memory . . . . . . 391 Application Shared Memory . . . . . 401 Agent Private Memory . . . . . . . 405 Agent/Application Communication Memory . . . . . . . . . . . . 416 Database Manager Instance Memory . . 421 Locks . . . . . . . . . . . . . 427 I/O and Storage . . . . . . . . . 431 Agents . . . . . . . . . . . . 438 Stored Procedures and User Defined Functions . . . . . . . . . . . 450 Logging and Recovery . . . . . . . . 454 Database Log Files . . . . . . . . 454 Database Log Activity . . . . . . . 464 Recovery. . . . . . . . . . . . 469 Distributed Unit of Work Recovery . . . 476 Database Management . . . . . . . . 480 Query Enabler . . . . . . . . . . 480 Attributes . . . . . . . . . . . 481 DB2 Data Links Manager . . . . . . 483 Status . . . . . . . . . . . . . 486 Compiler Settings . . . . . . . . . 489 Communications . . . . . . . . . . 496 Communication Protocol Setup . . . . 496 DB2 Discovery . . . . . . . . . . 498 Partitioned Database Environment . . . . 501 Communications . . . . . . . . . 501 Parallel Processing . . . . . . . . 505 Instance Management . . . . . . . . 507 Diagnostic . . . . . . . . . . . 507 Database System Monitor Parameters . . 511 System Management . . . . . . . . 512 Instance Administration. . . . . . . 520 DB2 Administration Server. . . . . . . 529 DAS Discovery Mode configuration parameter - discover . . . . . . . . 530 Name of the DB2 Server System configuration parameter - db2system . . 530 DAS Administration Authority Group Name configuration parameter - dasadm_group. . . . . . . . . . 531 Scheduler Mode configuration parameter - sched_enable . . . . . . . . . . 532 Tools Catalog Database Instance configuration parameter - toolscat_inst. . 532 Tools Catalog Database configuration parameter - toolscat_db . . . . . . . 533 Tools Catalog Database Schema configuration parameter - toolscat_schema 533 SMTP Server configuration parameter - smtp_server. . . . . . . . . . . 534 Java Development Kit Installation Path DAS configuration parameter - jdk_path . 535 Execute Expired Tasks configuration parameter - exec_exp_task . . . . . . 535 Scheduler User ID configuration parameter - sched_userid . . . . . . 536 Location of Contact List configuration parameter - contact_host . . . . . . 536 Authentication Type DAS configuration parameter - authentication . . . . . . 537 DAS Code Page configuration parameter - das_codepage . . . . . . . . . . 537 DAS Territory configuration parameter - das_territory . . . . . . . . . . 538 Part 4. Appendixes . . . . . . . 539 Appendix A. DB2 Registry and Environment Variables . . . . . . . 541 DB2 registry and environment variables . . 541 Registry and environment variables by category . . . . . . . . . . . . . 542 General registry variables . . . . . . 542 System environment variables . . . . 546 Communications variables . . . . . . 548 Command-line variables . . . . . . 553 MPP configuration variables . . . . . 554 SQL compiler variables . . . . . . . 556 Performance variables . . . . . . . 562 Data-links variables . . . . . . . . 569 Miscellaneous variables . . . . . . . 571 Appendix B. Explain tables . . . . . . 577 Explain tables . . . . . . . . . . . 577 vi Administration Guide: Performance
EXPLAIN ARGUMENT table 578 Appendix E DB2 Universal Database EXPLAIN INSTANCE table 582 technical information 647 EXPLAIN OBJECT table Overview of DB2 Universal Database EXPLAIN OPERATOR table 588 technical information EXPLAIN PREDICATE table Categories of DB2 technical information 648 EXPLAIN STATEMENT table 592 Printing DB2 books from PDF files EXPLAIN STREAM table 595 Ordering printed DB2 books ADVISE INDEX table Accessing online help ADVISE WORKLOAD table 600 Finding topics by accessing the DB2 Information Center from a browser Appendix C. SQL explain tools Finding product information by accessing SQL explain tools 601 the DB2 Information Center from the db2expln. administration tools db2expln syntax and parameters 602 Viewing technical documentation online Usage notes for db2expln 608 directly from the DB2 HTML Documentation dynex CD Explain output information 610 Updating the hTMl documentation installed Description of db2expln and dynexpli on your machine 610 Copying files from the DB2 HTML Table access information Documentation CD to a Web server Temporary table information 617 Troubleshooting DB2 documentation search Join information with Netscape 4.x Data stream information 622 Searching the DB2 documentation rma Online DB2 troubleshooting information Block and row identifier preparation information 24 Keyboard Input and Navigation Aggregation information Accessible Display Parallel processing information 626 Alternative alert cues Federated query information Compatibility with Assistive Technologies 668 Miscellaneous information Accessible documentation Examples of db2expln and dynexpln Output 633 DB2 tutorials Examples of db2expln and dynexpln DB2 Information Center for topics Example one: no parallelism 3 Appendix F Notices ample two: single-partition plan with Trademarks intra-partition parallelism Example three: multipartition plan with Inde 677 ter-partition parallelism Example four: multipartition plan with Conta inter-partition and intra-partition Product information parallelism Example five: federated database plan 642 Appendix D. db2exfmt-Explain table-format tool
EXPLAIN_ARGUMENT table . . . . . . 578 EXPLAIN_INSTANCE table . . . . . . 582 EXPLAIN_OBJECT table . . . . . . . 585 EXPLAIN_OPERATOR table . . . . . . 588 EXPLAIN_PREDICATE table . . . . . . 590 EXPLAIN_STATEMENT table. . . . . . 592 EXPLAIN_STREAM table . . . . . . . 595 ADVISE_INDEX table . . . . . . . . 597 ADVISE_WORKLOAD table . . . . . . 600 Appendix C. SQL explain tools . . . . 601 SQL explain tools . . . . . . . . . . 601 db2expln. . . . . . . . . . . . . 602 db2expln syntax and parameters . . . . 602 Usage notes for db2expln . . . . . . 608 dynexpln . . . . . . . . . . . . 610 Explain output information . . . . . . 610 Description of db2expln and dynexpln output . . . . . . . . . . . . 610 Table access information . . . . . . 611 Temporary table information . . . . . 617 Join information . . . . . . . . . 620 Data stream information . . . . . . 622 Insert, update, and delete information 623 Block and row identifier preparation information . . . . . . . . . . . 624 Aggregation information . . . . . . 625 Parallel processing information . . . . 626 Federated query information . . . . . 629 Miscellaneous information . . . . . . 630 Examples of db2expln and dynexpln Output 633 Examples of db2expln and dynexpln output . . . . . . . . . . . . 633 Example one: no parallelism . . . . . 633 Example two: single-partition plan with intra-partition parallelism . . . . . . 635 Example three: multipartition plan with inter-partition parallelism . . . . . . 637 Example four: multipartition plan with inter-partition and intra-partition parallelism . . . . . . . . . . . 640 Example five: federated database plan 642 Appendix D. db2exfmt - Explain table-format tool. . . . . . . . . . 645 Appendix E. DB2 Universal Database technical information . . . . . . . . 647 Overview of DB2 Universal Database technical information . . . . . . . . 647 Categories of DB2 technical information 648 Printing DB2 books from PDF files . . . . 655 Ordering printed DB2 books . . . . . . 656 Accessing online help . . . . . . . . 656 Finding topics by accessing the DB2 Information Center from a browser . . . . 658 Finding product information by accessing the DB2 Information Center from the administration tools . . . . . . . . . 660 Viewing technical documentation online directly from the DB2 HTML Documentation CD. . . . . . . . . . . . . . . 661 Updating the HTML documentation installed on your machine . . . . . . . . . . 662 Copying files from the DB2 HTML Documentation CD to a Web Server. . . . 664 Troubleshooting DB2 documentation search with Netscape 4.x . . . . . . . . . . 664 Searching the DB2 documentation . . . . 665 Online DB2 troubleshooting information . . 666 Accessibility . . . . . . . . . . . 667 Keyboard Input and Navigation . . . . 667 Accessible Display . . . . . . . . 668 Alternative Alert Cues . . . . . . . 668 Compatibility with Assistive Technologies 668 Accessible Documentation . . . . . . 668 DB2 tutorials . . . . . . . . . . . 668 DB2 Information Center for topics . . . . 669 Appendix F. Notices . . . . . . . . 671 Trademarks . . . . . . . . . . . . 674 Index . . . . . . . . . . . . . 677 Contacting IBM . . . . . . . . . . 691 Product information . . . . . . . . . 691 Contents vii
About this book The Administration Guide in its three volumes provides information necessary o use and administer the dB2 relational database management system (RDBMS) products, and includes: Information about database design(found in Administration Guide: Planning Information about implementing and managing databases(found in Administration Guide: Implementation Information about configuring and tuning your database environment to improve performance(found in Administration Guide: Performance Many of the tasks described in this book can be performed using different interfaces. The Command Line Processor, which allows you to access and manipulate databases from a graphical interface. From this interface, you can also execute SQL statements and DB2 utility functions. Most examples in this book illustrate the use of this interface. For more information about using the command line processor, see the Command Reference. The application programming interface, which allows you to execute DB2 utility functions within an application program. For more information about using the application programming interface, see the Administrative API The Control Center, which allows you to use a graphical user interface to perform administrative tasks such as configuring the system, managing directories, backing up and recovering the system, scheduling jobs, and managing media. The Control Center also contains Replication systems. Further, the Control Center allows you to execute DB2 utility Administration, which allows you set up the replication of data betwe functions through a graphical user interface. There are different methods to invoke the Control Center depending on your platform. For example, use the db2cc command on a command line, select the control center icon from the DB2 folder, or use the Start menu on Windows platforms. For introductory help, select Getting started from the Help pull-down of the Control Center window. The Visual Explain and Performance Monitor tools are invoked from the Control center There are other tools that you can use to perform administration tasks. They The Script Center to store small applications called scripts. These scripts may contain SQL statements, DB2 commands, as well as operating system o Copyright IBM Corp. 1993-2002 IX
About this book The Administration Guide in its three volumes provides information necessary to use and administer the DB2 relational database management system (RDBMS) products, and includes: v Information about database design (found in Administration Guide: Planning) v Information about implementing and managing databases (found in Administration Guide: Implementation) v Information about configuring and tuning your database environment to improve performance (found in Administration Guide: Performance) Many of the tasks described in this book can be performed using different interfaces: v The Command Line Processor, which allows you to access and manipulate databases from a graphical interface. From this interface, you can also execute SQL statements and DB2 utility functions. Most examples in this book illustrate the use of this interface. For more information about using the command line processor, see the Command Reference. v The application programming interface, which allows you to execute DB2 utility functions within an application program. For more information about using the application programming interface, see the Administrative API Reference. v The Control Center, which allows you to use a graphical user interface to perform administrative tasks such as configuring the system, managing directories, backing up and recovering the system, scheduling jobs, and managing media. The Control Center also contains Replication Administration, which allows you set up the replication of data between systems. Further, the Control Center allows you to execute DB2 utility functions through a graphical user interface. There are different methods to invoke the Control Center depending on your platform. For example, use the db2cc command on a command line, select the Control Center icon from the DB2 folder, or use the Start menu on Windows platforms. For introductory help, select Getting started from the Help pull-down of the Control Center window. The Visual Explain and Performance Monitor tools are invoked from the Control Center. There are other tools that you can use to perform administration tasks. They include: v The Script Center to store small applications called scripts. These scripts may contain SQL statements, DB2 commands, as well as operating system commands. © Copyright IBM Corp. 1993 - 2002 ix
The alert Center to monitor the messages that result from other dB2 The Health Center provides a tool to assist DBAs in the resolution of performance and resource allocation problems The Tools Settings to change the settings for the Control Center, Alert Center, and Replication. The Journal to schedule jobs that are to run unattended The Data Warehouse Center to manage warehouse objects. Who should use this book This book is intended primarily for database administrators, system administrators, security administrators and system operators who need to design, implement and maintain a database to be accessed by local or remote clients. It can also be used by programmers and other users who require an understanding of the administration and operation of the DB2 relational database management system How this book is structured This book contains information about the following major topics ntroduction to performance Chapter 1, "Introduction to performance", introduces concepts and considerations for managing and improving DB2 UDB performance Chapter 2, " Architecture and processes", introduces underlying DB2 Universal Database architecture and processes Tuning Application Performance Chapter 3, Application considerations", describes some techniques for improving database performance when designing your applications. Chapter 4, "Environmental considerations", describes some techniques for improving database performance when setting up your database environment Chapter 5, "System catalog statistics", describes how statistics about your data can be collected and used to ensure optimal performa Chapter 6, Understanding the SQL compiler", describes what happens to an SQL statement when it is compiled using the SQL compiler. Chapter 7, "SQL Explain facility", describes the Explain facility, which allows you to examine the choices the sQL compiler has made to access your data Tuning and Configuring Your System x Administration Guide: Performance
v The Alert Center to monitor the messages that result from other DB2 operations. v The Health Center provides a tool to assist DBAs in the resolution of performance and resource allocation problems. v The Tools Settings to change the settings for the Control Center, Alert Center, and Replication. v The Journal to schedule jobs that are to run unattended. v The Data Warehouse Center to manage warehouse objects. Who should use this book This book is intended primarily for database administrators, system administrators, security administrators and system operators who need to design, implement and maintain a database to be accessed by local or remote clients. It can also be used by programmers and other users who require an understanding of the administration and operation of the DB2 relational database management system. How this book is structured This book contains information about the following major topics: Introduction to Performance v Chapter 1, “Introduction to performance”, introduces concepts and considerations for managing and improving DB2 UDB performance. v Chapter 2, “Architecture and processes”, introduces underlying DB2 Universal Database architecture and processes. Tuning Application Performance v Chapter 3, “Application considerations”, describes some techniques for improving database performance when designing your applications. v Chapter 4, “Environmental considerations”, describes some techniques for improving database performance when setting up your database environment. v Chapter 5, “System catalog statistics”, describes how statistics about your data can be collected and used to ensure optimal performance. v Chapter 6, “Understanding the SQL compiler”, describes what happens to an SQL statement when it is compiled using the SQL compiler. v Chapter 7, “SQL Explain facility”, describes the Explain facility, which allows you to examine the choices the SQL compiler has made to access your data. Tuning and Configuring Your System x Administration Guide: Performance