DB2 database performance optimization

For the DB2 database, this is all based on a deep understanding of the DB2 lock mechanism and concurrency mechanism, indexing principle, database parameters, optimizer principle, SQL script optimization, and other technologies.

For the DB2 database, this is all based on a deep understanding of the DB2 lock mechanism and concurrency mechanism, indexing principle, database parameters, optimizer principle, SQL script optimization, and other technologies.

Some time ago, I got this book DB2 database performance tuning and optimization (version 2nd). This is a book about performance tuning of DB2 databases, this book covers all the knowledge and tools required for performance tuning of DB2 databases, and provides a large number of practical cases for performance tuning, DB2 is all in the grasp of "pride. As we all know, performance tuning is a system engineering. Through comprehensive real-time monitoring and analysis of OS, I/O performance, and memory usage, DBAs can identify whether the database program is faulty, or the application running on the server has a problem.

DB2 database performance adjustment and optimization (version 1st and version 2)

For DB2 databases, all of these are based on understanding DB2's locking mechanism, concurrency mechanism, indexing principle, database parameters, optimizer principle, and SQL script optimization, if you have a deep understanding of these technical points, you can quickly estimate and judge the problems and propose solutions.

For DBAs, the following tools must be mastered:
1) snapshots
2) db2pd
3) db2expln
4) event monitor

To make friends who are not familiar with DB2 have a correct understanding of DB2, I will introduce the background information of DB2.

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

DB2 uses data classification technology to easily download mainframe data to the LAN Database Server, so that client/server users and LAN-based applications can access mainframe data, and make the database localization and remote connection transparent. It is famous for having a very complete query optimizer. Its external connection improves the query performance and supports multi-task parallel query. DB2 has excellent network support capabilities. Each subsystem can connect to tens of thousands of distributed users and activate thousands of active threads at the same time. This is especially suitable for large-scale distributed application systems.

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

1) DB2 Everyplace
It is 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 functions of DB2 Express.

4) DB2 Express Edition
DB2 Express Edition is a fully functional Web-enabled client/server RDBMS. It provides a low-cost entry-level server for computing tasks in small businesses and departments.

5) DB2 Workgroup Edition
DB2 Workgroup Edition has the same functionality as DB2 Express, and only differs in the number 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 provided for application developers and has been deprecated.

After understanding the background information of DB2, what is performance?
Performance is the processing capability of business application systems under specific hardware resources and workloads.
Performance is measured by the system response, throughput, availability (resource utilization), and so on.

So how can we maintain good system performance?
We will mainly consider the following aspects:

1) physical resources are sufficient and can be used properly
The system must be configured with sufficient CPU, memory, high-speed disks, and network bandwidth, and allocated and used properly.

2) good storage I/O Design
Sufficient disk devices are required to ensure sufficient I/O concurrency to support large concurrent transactions.

3) Reasonable database configuration parameters
This affects database resource allocation, resource advantages, and database operation optimization.

4) Make sure that the application holds the lock for 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 transaction duration is as short as possible.

5) create the most reasonable index for the table
Make sure that all the columns for the connection operation in the query have indexes.

6) use SQL statements efficiently

7) analyze the SQL Execution Plan
Find the SQL running bottleneck and make adjustments.

8) physical and logical design of databases
The most rational database technology is used to implement our business logic. A good and reasonable design is necessary for the efficient operation of the entire system.

9) Statistics Update and fragment
Regularly update statistics so that the optimizer can make the most appropriate execution plan for SQL at all times.

10) familiar with the working principle of the DB2 Optimizer
Optimizer is the soul and heart of DB2.