Main Image

PoS(CENet2017)092

Query Optimization Ways of DB2 to Improve Database Performance

J. Tang, Z. Hua, J. Feng

in The 7th International Conference on Computer Engineering and Networks

Contribution: pdf

Abstract

How to write the query that meets the requirements of DB2 optimizer is the most concerns of programmers and form partial ability of the programmer to directly control the query performance. In this paper, we analyse how to improve the performance of DB2 from the point of view of Query. The research of this paper includes: (1) how to optimize the query, (2) run
RUNSTATS tool to collect statistical object information, (3) the influence of FF (Filter Factor) and statistical information on query efficiency, (4) how to use EXPLAIN tool to view the access
path, and (5) discuss the effect of join mode on performance. In this paper, we draw the following conclusions:(1) One of the most important factors affecting the performance of SQL is the choice of access path. The basic generation access path comes from the statistical information of query table, the filter factor of predicate, etc. (2) Before we do the query tuning, the first operation is to run the RUNSTATS program on related tables and indexes. (3) Different join methods under different situations can have different impacts on performance.