Tuning IBM DB2 UDB SQL Access Path

Brief introduction

Visual Explain is an outstanding tool in ibm®db2®universal database™ that programmers and DBAs use to specify the access path that the DB2 Optimizer chooses for SQL statements. In fact, Explain should be a key component of your performance monitoring strategy. Explain provides invaluable information for solving many types of performance problems, because it provides such details:

DB2 the work done behind the scenes to implement the data requirements for SQL requests

DB2 whether to use available indexes, and DB2 how to use them if used

Order of access to DB2 tables to meet connection conditions

Implementing locking requirements for SQL statements

Performance of SQL statements based on the selected access path

For borland®delphi™7 programmers, Visual Explain is a fantastic resource for discovering how DB2 executes SQL requests. Delphi interacts with DB2 using the CLI native interface. As a result, Delphi uses dynamic SQL. When the SQL statement is submitted to DB2 execution, DB2 will "live" to design the access path for the dynamic SQL statement. For the analyst, you cannot check the access path that DB2 has chosen for these statements before executing each SQL statement. Therefore, it makes sense to use Visual Explain to periodically check the access path that DB2 has chosen for all Delphi SQL statements. This allows you to observe which statements consume most of the resources. In addition, you can instruct you how to tune your SQL to achieve better performance.

But before delving into the usage of explain, I first need to look at what explain exactly "illustrates". The answer is simple, it shows the DB2 access path. The access path is an algorithm used by DB2 to satisfy the requirements of SQL statements. But there are plenty of different types of access paths that need to be mastered.

Types of DB2 access paths and their components

When the DB2 optimizer creates an optimized access path for each SQL statement, it can pick a variety of different technologies. These techniques include reading from a simple sequence of sequences to more complex policies (for example, using multiple indexes to access data). Let's take a look at some of the most common techniques that the optimizer uses to design DB2 access paths.

Among the many decisions that the optimizer must make, the most important decision may be whether to use an index to implement the query. Before the optimizer makes this decision, it must first determine whether an index exists. Keep in mind that you can query any column in any table, but you can't expect to do this by simply indexing. Therefore, the optimizer must be able to access data that is not indexed, and it can use scans to do so.

In most cases, the DB2 optimizer prefers to use indexes. This is true, because indexes can greatly optimize data retrieval. However, if an index is not present, it cannot be used. And in some cases, just using a full scan of the data can be a great way to implement certain types of SQL statements. For example, consider the following SQL statement:

SELECT * FROM EMP;

Why is DB2 trying to use an index in this statement? There is no WHERE clause, so the full scan is the best. Even if a WHERE clause is specified, the optimizer may determine that the sequential scan of the page is better than indexed retrieval-so you may not choose an indexed retrieval method.

The primary reason for indexing is that it can improve performance, so why are non indexed access better than indexed access? Well, indexed access can be slower than a simple scan. For example, a very small table might have only a few pages. Reading all the pages may be faster than reading the index page and then reading the data page. Even for larger tables, in some cases, an organization index may require additional I/O to implement the query. When you do not use an index to implement a query, the resulting access path takes a table scan (or a tablespace scan) method.

Table scans usually read each page in the table. But in some cases, DB2 will be very smart, and it will limit the pages to be scanned. In addition, DB2 can call sequential prefetching to read pages before requesting some pages. Sequential prefetching is particularly useful when SQL requests require sequential access to multiple rows of data in the order in which they are stored on disk. When the optimizer determines that the query will read the data page in order, it notifies you that sequential prefetching should be enabled. Table scans often benefit from the early reading of sequential prefetching, because when a query requests data, the data is already in memory.