Common methods to improve DB2 query performance

Brief introduction

With the increasing application of DB2, more and more database developers are confronted with the problem that the query is too complicated and the performance is difficult to be accepted in the process of project development. This article will mainly from a database developer's point of view introduces several commonly used methods to improve the performance of DB2 query, and does not discuss how to improve the performance of the whole database by configuring DB2 parameters and adjusting the server environment. Such work as System configuration is part of the DBA's work, and is transparent to developers in general project development. This paper first introduces several related tools and commands that DB2 provides to improve query performance, then introduces some common techniques and methods to improve query performance according to the author's working experience. Mainly focused on how to create and maintain the index, rewrite the query and change the implementation of the query, the relevant content will be explained through examples.

Several related tools and commands provided by DB2

We will focus on using visual explain and DB2EXPLN to view the access plans for dynamic queries. Readers can refer to DB2 Info Center for information about viewing the static query access plan.

DB2 Visual Explain

DB2 provides a very intuitive and efficient way to view the query's access plan. DB2 Visual Explain can get a visual query plan, while the DB2EXPLN command can get a query plan in text form. With the query plan, we can optimize the query for. Find the highest-cost scans (table scans, index scans, etc.) and operations (Join,filter,fetch, etc.) based on the query plan, and then refine the query by overwriting the query or by creating an index to eliminate costly scans or operations.

DB2 provides a variety of ways to get a visual query plan.

Get a visual query plan through DB2 control center. As shown in Figure 1:

Figure 1. Visual query plan

Click "Explain SQL" after the input to the analysis of the query and the query label and label, click the OK button can be visualized query plan. At this point, the query plan is stored in the system's explain table. The user can obtain all the query plans stored in the explain table by using the show explained Statements History command in Figure 1.

Obtain a visual query plan through command Editor (called Command Center before the DB2 8.2 version). As shown in Figure 2:

Figure 2. Get a visual query plan

After entering the query and connecting to the database in the main window, click on the button shown in the diagram to get a visual query plan, as in Figure 3:

Figure 3. Query plan Results

In the query plan shown in Figure 3, you can also click on each node in the diagram to see detailed statistics. For example, clicking the node "FETCH (13) 21,959.75" will pop up the dialog box shown in Figure 4:

Figure 4. Detailed statistical information