DB2 database performance optimization

For the DB2 database, all of this is based on a deep understanding of DB2's lock mechanism and concurrency mechanism, indexing principles, database parameters, optimizer principles, SQL script optimization, etc.

Some time ago, I got this book "DB2 Database Performance Tuning and Optimization (Second Edition)", this is a book introducing DB2 database performance tuning, this book covers all the DB2 database performance tuning required Knowledge and tools, but also provides a large number of practical examples of performance tuning, there is quite a pride of "a book in hand, DB2 is in control". Everyone knows that performance tuning is a system project. Through comprehensive real-time monitoring and analysis of OS, I / O performance, and memory usage, the DBA can find out whether it is a problem with the database itself or running on the server. Problem with the application.

DB2 database performance tuning and optimization (versions 1 and 2) see

For the DB2 database, all this is based on a deep understanding of DB2's lock mechanism and concurrency mechanism, indexing principles, database parameters, optimizer principles, optimization of SQL scripts and other technical points, mastering these technical points The deeper the problem, the quicker it is to estimate and judge after a problem occurs, and propose a solution to the problem.

For the DB2 database DBA, the following tools must be mastered:
1) Snapshot
2) db2pd
3) db2expln
4) Event monitor

In order to let friends who are not familiar with DB2 have a correct understanding of DB2, let me introduce the background information of DB2.

DB2 is a large-scale relational database system issued by IBM. It is mainly used in large-scale application systems, and has good scalability, supporting from the mainframe environment to a single-user server environment, can be applied to OS / 2, Windows and other operating system environments. The DB2 system provides high-level data utilization, integrity, security, recoverability and other characteristics, as well as the execution and support capabilities of applications from small to large scale, and has basic platform-independent functions and SQL commands.

DB2 uses data grading technology, which can easily download mainframe data to LAN database server, so that client / server users and LAN-based applications can access mainframe data, and localize the database and remote Transparency. It is famous for having a very complete query optimizer, its external connection improves query performance, and supports multi-task parallel query. DB2 has good network support capabilities. Each subsystem can connect hundreds of thousands of distributed users and can activate thousands of active threads at the same time, which is especially suitable for large-scale distributed application systems.

The DB2 database family is very large, it has multiple versions, such as:

1) DB2 Everyplace
Mainly used for mobile computing.

2) DB2 Personal Edition
DB2 Personal Edition is a single-user RDBMS that runs on low-cost commercial hardware desktop computers.

3) DB2 Express-C
DB2 Express-C is not actually a version of the DB2 series, but it provides most of the features of DB2 Express.

4) DB2 Express Edition
DB2 Express Edition is a full-featured client / server RDBMS that supports the Web. It provides a low-cost entry-level server, mainly used for computing tasks of small businesses and departments.

5) DB2 Workgroup Edition
DB2 Workgroup Edition has the same functions as DB2 Express, except for the amount of memory and value units that can be installed on the server.

6) DB2 Enterprise Edition
DB2 Enterprise Edition is a full-featured RDBMS. It supports all UNIX, Linux and Windows, and is suitable for large and medium-sized department servers.

7) Data Enterprise Developer Edition
Data Enterprise Developer Edition is a special version for application developers and has been abandoned.

After understanding the background information of DB2, we need to clarify what is performance?
Performance is the processing power that a business application system exhibits under specific hardware resources and workloads.
Performance is mainly measured by system response, throughput, availability (resource utilization), etc.

So, how to keep the system good performance?
Mainly consider from the following aspects:

1) The physical resources are sufficient and can be fully and reasonably used
The system needs to be configured with sufficient CPU, memory, high-speed hard disk, and network bandwidth, and it must also be properly allocated for use.

2) Good storage I / O design
There must be enough disk devices to ensure sufficient I / O parallelism to support large-volume concurrent transactions.

3) Reasonable database configuration parameters
This will affect the allocation of database resources, the play of resource advantages, and whether the database runs optimally.

4) Make sure that the application holds the lock as short as possible
Start the first SQL statement of the transaction as late as possible, and make the update of the transaction as close as possible to the commit phase, so that the duration of the transaction is as short as possible.

5) Create the most reasonable index for the table
Make sure that the columns that are joined in the query have indexes.

6) Use SQL statements efficiently

7) Analysis of SQL execution plan
Identify the operating bottleneck of SQL and make adjustments.

8) Physical and logical design of the database
Using the most reasonable database technology to realize our business logic, a good and reasonable design is a necessary condition for the efficient operation of the entire system.

9) Statistics update and defragmentation
Regularly update statistical information, so that the optimizer always formulates the most reasonable execution plan for SQL.

10) Familiar with the working principle of DB2 optimizer
The optimizer is the soul and heart of DB2.