Based On The Db2 Relational Database Query Performance Tuning

With the development of the globalized economy, the scale of company has expanded rapidly and the requirements for processing massive data are more and more complicated, and this requires a high-performance database support. IBM's DB2 database is a high performance relational database system, which is mainly used in large-scale applications, such as banking, securities, aviation and other fields, has good scalability, supports from the mainframe to a single user environment and is used in OS/2, OS/400, OS/390, z/OS, UNIX, AIX, Windows and other platforms, so the performance tuning of DB2 database has a very important significance.Database tuning is often a challenge, usually the most effective tuning methods: the first is to optimize the the database query performance through efficient access path algorithm and the connection method to achieve performance tuning; another method is to get the problem SQL by looking for which SQL statements consume the most resources, and then decide to take certain SQL tuning measures to reduce resource consumption. Writing concise, efficient SQL statement is not an easy thing, in practice, the cause of leading to the database performance bottlenecks is some severe SQL statements, SQL tuning is not a simple matter. It requires a good test strategy, which has the good planning and design, careful observation and in-depth analysis, and different operating platforms may produce different results, therefore SQL statement tuning is an important part of database performance tuning. This theory is mainly on performance tuning of SQL statements.At first, this theory introduces DB2 features and architecture, then focus on the introduction of predicate property features and applications, predicate has a significant impact to the performance of SQL statement, and study the predicate performance assessment principles, and then research the SQL query performance optimization method separately from the access path tuning, predicate transaction and other aspects. In the fourth chapter, through the deep study and research of the DB2, I proposed the dynamic programming algorithm for query performance tuning, which based on the partition idea and avoiding double counting idea, in addition, using the hash connection method, which can greatly improve the performance of complex SQL statements. Through an example, show that the algorithm can greatly improve query performance, and can generate optimal SQL statement access paths, reducing running time. In the fifth chapter, mainly research the performance issues about a problem query, through the analysis and study to issues, raise query optimization solutions, and through the OSC tool, contrast the actual data before and after the implementation of the solutions, realized the optimization for the problem query. And propose the SQL query optimization method, which can greatly improve the efficiency of query optimization. Finally, it's the summary for this research and prospect to the next step.