Optimization of DB2 SQL statements

DB2 SQL statements are familiar to everyone. Good DB2 SQL statements make the database performance more stable. So how should we optimize DB2 SQL statements? The following sections will show you the answer.

The operations performed by the application on the DB2 database are ultimately performed by SQL statements on the database. A good DB2 SQL statement can be used repeatedly by the database to reduce analysis time, improve the performance of a system, increase the hit rate of the database memory area, and reduce I/O access..

In the DB2 data statistics and analysis system, the physical location of the data is more important than the logical location, because the database must find the data to return it to the query user. Therefore, the key to adjusting SQL statements is how to simplify the data search path. Generally, the following methods can be used to optimize the performance of SQL statements on data operations.

1) Reduce the number of queries to the DB2 database, that is, reduce requests to system resources.

2) use temporary tablespaces to sort the subsets of tables in temporary tables, which helps avoid multiple sorting operations. At least one tablespace should be created for temporary segments to accelerate SQL queries.

3) restrict the use of dynamic SQL statements. Although dynamic SQL statements are useful, dynamic SQL performs syntax analysis again even if there is a completely identical query value in the SQL sharing pool.

4) Avoid executing SQL statements without any conditions. Full table scanning is required for SQL statements without any conditions during execution. Such scanning usually takes a long time, which greatly reduces the retrieval efficiency.

5) To avoid the tags of a field in a subquery that appear in both the primary query and the where clause, it is very likely that after the field value in the primary query changes, the subquery must be re-queried. The more nested query layers, the lower the efficiency. Therefore, try to avoid subqueries. If the subquery is unavoidable, filter as many rows as possible in the subquery.

6) try to use the same or very similar SQL statements for queries. This not only makes full use of the analyzed syntax tree in the memory, it also makes the data to be queried more likely to hit in the buffer pool.

Implementation of DB2 date format change

Tips for DB2 data deletion

DB2 event monitoring command

Implementation Process of batch execution of DB2 SQL scripts

DB2 Online Export Method