Top 10 considerations for DB2 Performance Optimization

The selection of DB2 performance optimization policies determines the effect of database optimization. The following describes the considerations for optimizing the performance of ten DB2 databases. I hope this will help you learn about the performance optimization of DB2.

1. Provide appropriate statistics
For the DB2 optimization manager otimizer), how to execute SQL statements more effectively is determined by the statistical information that exists in the DB2 catalog. The optimizer determines the optimal path using the information.
Therefore, in order to ensure that the system can make the best choice, it is necessary to run the Runstats command frequently to keep the system statistics timely and effective. In a production environment with a large workload, it is necessary to run Runstats frequently. To reduce the impact of statistics on the system, Sampling technology can be used.

2. Try to use Stage 1 predicates in SQL statements.
SQL predicates belonging to Stage 1 are processed by Data Manager, while SQL statements belonging to Stage 2 are executed by Relational Data Server. Data Manager is more advantageous in terms of processing efficiency. Therefore, if possible, try to write SQL statements that can be executed in Stage 1. If you can use Index to determine the predicates in an SQL statement, the SQL Execution efficiency will be improved. Therefore, when writing an SQL query statement, you should combine the definition of Index and the choice of predicate description to make the SQL statement run in Stage 1 as much as possible.

3. Select only required columns in SQL
In SQL statements, specifying only the columns required by the program can improve the performance of SQL operations. If the Select * mode is used, it will bring additional processing work to the client application and require more memory and other resources. On the server side, extra columns increase the Sort working file during the sorting process, which leads to a decrease in the sorting speed and requires more storage resources. During network transmission, unnecessary data will be transmitted to reduce network efficiency.

4. select only the required rows.
The fewer rows to query, the faster the query speed and response speed. Each row of data obtained by the query goes through a series of processes, such as from the storage device> buffer pool> sorting and conversion> network transmission> application. In the Data Query Process, the database manager will filter a lot of data. If there are a lot of unnecessary rows to retrieve from the database, this will waste a lot of unnecessary processing time. Therefore, in the SQL statement writing process, you should specify the query conditions in detail and query only the required rows.

5. If the data in the database remains unchanged for a long time, use constants or characters as much as possible in SQL.
Host variables are used in SQL to adapt the system to the changing external environment. Using host variables in SQL programs allows applications to execute new SQL definitions without re-binding. However, this flexibility is at the cost of reducing the performance of the DB2 optimization manager. In SQL statements that contain host variables, the DB2 optimization manager selects the SQL path based on the default estimated value instead of the statistics in Catalog. This selection is not optimal in many cases. Therefore, replacing host variables with constants or characters will be more advantageous in a long and fixed environment than using host variables.

6. Match data types such as numbers and dates
Before DB2 V7, if the length of the number is different during the data comparison process, DB2 will put the comparison and matching of the number in Stage 2, this reduces the processing efficiency. Since DB2 V7, the system has provided the Cast method, which can manually change the numerical length and avoid processing in Stage 2 in some cases.

7. Restrictions on Sorting SQL statements
The DB2 system classifies SQL statements according to the categories of predicates. For example, Stage 1 and Indexable belong to the first category, and Stage 1 and On Index screening) belongs to the second category, and so on. In the case of the same category, the system processes the data in the defined physical order. Therefore, SQL statements should be written in the key order of constraints. The advantage of doing so is that the system can first scan data based on the most restrictive predicate definitions, effectively reducing the data processing pressure in the second or third predicate scan process, it also improves the overall SQL statement retrieval efficiency.

8. Delete unnecessary predicate Conditions
The predicate condition in each SQL statement is processed by the system and occupies system resources. Therefore, if a condition is unnecessary, delete it from the SQL statement, to avoid unnecessary resource overhead.

9. Restrict returned result sets
If you know the number of returned result sets, you can use the fetch first n rows only parameter to limit the number of returned results sets and quickly release related resources, for example: pages in the buffer pool can be quickly released after the result set is returned, and some lock and Cusor resources can also be released. This can save system resources and improve the utilization of system resources.

10. Analyze and optimize the Access path Access Paths)
Generally, the access path automatically generated by the system is not necessarily optimal. to further optimize the performance of SQL Execution, we can use tools such as Explain to obtain and interpret the output information of Explain, verify that the access path is suitable for the current environment. Especially in the production environment, the access path of each SQL query statement should be checked in detail. Optimization query mainly includes the following technologies: optimize for n ROWS
, Fetch first n rows only, Table expressions with DISTINCT, REOPT (VARS), AND Index Optimization.

Connect SQL SERVER to the DB2 database

Learn about the DB2 Index Structure

Provides you with an in-depth understanding of the DB2 materialized query table.

Two Methods for restoring DB2 partitioned Database

Three types of DB2 database backup solutions