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