Five best practices for in-depth optimization of DB2 databases

Structured Query Language (SQL) is a double-edged sword for relational DBMS, whether DB2 or Oracle, SQL Server database, and has mixed advantages and disadvantages. Because SQL is required to retrieve any data from a relational database, the topic of this article is how they will access a relational database, whether they are end users or developers or database administrators (DBAs). When efficient SQL is used, the system becomes easy to upgrade, flexible, and manageable. When inefficient SQL is used, the response time and the program run time are extended, and the application system interrupt is also generated. Given that a typical database system typically spends 90% of its processing time retrieving data from a database, it is clear how important it is to ensure that SQL is as efficient as possible. To examine the usual SQL statement issues such as "Select*from" is only the tip of the iceberg, we will explore other common problems that are easy to determine in this article. It is to be remembered that there are many ways to find the SQL statement that retrieves the same data, so there is no good query or bad query, and only the query statement that satisfies the appropriate needs. Each relational database has its own way to optimize and execute query statements. As a result, each DBMS has its own best performance query techniques. This article will use the examples and overviews of QuestCentralforDB2 in the Quest software to focus on db2foros/390 and z/0s.

17 years ago, the tip would be longer and would include corrective methods for minimizing the select scenario. Each new version of DB2 will add thousands of lines of new code to extend intelligent optimization, and query rewriting and execution. For example, a component called a data manager for many years is usually provided as a "first-stage processing" to increase its filtering capacity by 100 times times. Another component is a relational data server, which is often provided as a "second-stage process" for query rewriting and optimization of its main function. Another key component is based on the current SQL and uses the access path to determine the DB2 optimizer that retrieves the data. The DB2 optimizer improves each DB2 version, and allows for new and improved access paths, taking into account the statistics in other DB2 directories. Figure 1 shows these components and more, and describes how DB2 handles data or SQL requests. This is the source of the following Db2sql performance tips.

Figure 1:db2engine and some of the components introduced

In this article, we'll review some of the more representative SQL issues, and there are more SQL performance techniques that are beyond the scope of this article. Like all guidelines, there are exceptions to all these techniques.

Tip 1: Verify that the appropriate statistics are available:

For the DB2 optimizer, the most important resource, in addition to the SELECT statement itself, is the statistics created in the DB2 directory. The optimizer uses these statistics based on a multitude of choices. The main reason why the DB2 Optimizer chooses a non-optimal access path for queries is due to invalid or missing statistics. The DB2 optimizer uses the following directory statistics:

Figure 2:DB2 the validated columns and the access paths used to determine