DB2 explain

Introduction to DB2 explain.

DB2 provides different kinds of services to the user, in which that user can perform the different operations as per their requirement. The DB2 explanation is one of the functions, or we can say that it is a service. By using DB2 explain, we can determine the access path of the select statement. Basically, DB2 explain is related to query performance and query optimization. By using DB2 explain, we can analyze the performance of query statements, and after that, we can make the performance improvement. DB2 explains uses the index for accessing the data as well as we can use sort to increase the performance of the query statement.

Syntax

explain plan set query_no = 1 for [Specified sql statement that we need to execute]

Explanation

In the above syntax, we use the explain plan keyword to see the performance of the SQL statement. After that, we need to retrieve the row from the specified table to understand the access path. Here specified sql statement means SQL statement that we need to execute.

How explain command works in DB2?

Now let’s see how to explain command works in DB2 as follows.

The DB2 explain command contains some important parameters as follows.

Important fields for significant data as follows.

PLANNO – Number of steps important to handle the query shown in QBLOCKNO.

STRATEGY – Indicate joins technique utilized for the progression (PLANNO).

ACCESTYPE – Method used to get to the specified table.

MATCHCOLS – Number of list keys utilized for list filters (when ACCESTYPE is I, IN, M, MX).

ACCESSNAME – Name of the list utilized for list filter (when ACCESTYPE is I, IN, M, MX).

INDEXONLY – Indicates if the index file alone is sufficient to complete the progression.

PREFETCH – Indicates if information pages can be perused ahead of time by prefetch.

COLUMN_FN_EVAL – Indicates when total capacities that are aggregate functions are assessed.

After that, we need to analyze the final result by using the following parameters as follows.

ACCESSTYPE:

I – Index- This is one of the best accesses for the path after the one-get list. It utilizes the index to recover rows. The quantity of index columns utilized for coordinating is addressed in MATCHCOLS.

I1 – One-get file access that we also call one fetch indexing addressing. Is the ideal access as it requires recovering just one row? Be that as it may, it applies just to proclamation with a MAX or MIN work.

N – Index examines with IN catchphrase in the predicate. In the model: Z (AB1, AB2, AB3, and AB4). Order: Select * from Z where AB1 = 1 AND AC2 (in 1, 2, 3) AND AB3 > 0 and AB4 = 1. When MATCHCOLS is 3 at that time, ACCESSTYPE will be N. The IN-indexing sweep will be preceded as three coordinating with record filter: (AB=1, AB2=1, AB3>0), (AB=1, AB2=2, AB3>0) and (AB=1, AB2=3, AB3>0). On the off chance that parallelism is upheld, they will execute in equal.

MX – It is used for multiple indexing examinations. More than one index is utilized to get to a specified table. It is a productive access way when no single index is effective, and a mix of records gives proficient access.

R – It is used for tablespace examinations. This is the most noticeably terrible sort of access as the whole table will be looked to handle the question.

MATCHCOLS: The quantity of index columns coordinated on a record examines.

1. In the event that it is 0, all index keys and RIDs are perused, or we can say read.
2. On the off chance that one of the coordinating with predicates is a reach, there will be not any more coordinating with sections. Model for the record on Z(AB1, AB2, AB3, AB4) for the accompanying order the AB3 predicate will not be utilized: Select * Z where AB1=1 and AB2 > 1 and AB3 = 1. The situation of the segments in the file is utilized to conclude that AB3 will not be utilized.

INDEXONLY: If the specified column required for a SQL proclamation can be found in the index of DB2 won’t get to the table. INDEXONLY execution is extremely high.

PREFETCH: Perfecting decides ahead of time, assuming many information pages will be utilized and peruses the whole set into a cushion with a solitary offbeat I/O activity.

S – Sequential prefetch: information pages read ahead of time is gotten consecutively. Table space examination consistently utilizes consecutive prefetch.

L – List prefetch: At least one list is utilized to choose the RIDs list ahead of time.

D -Dynamic prefetch: the pages to be gotten to will be nonsuccessive.

Clear – Prefetch not anticipated.

SORTs: They add an additional progression to the got to information.

METHOD=3 – These sorts are utilized for ORDER BY, GROUP BY, SELECT DISTINCT, or UNION.

Examples

Now let’s see the different examples of explain commands as follows.

First, we need to create the new table by using the following create table statement as follows.

create table company_1 (Comp_Id int(20), comp_name varchar(30),
comp_address varchar(30));

Explanation

By using the above statement, we create a new table name as company_1. Now insert some records into a company_1 table by using insert into statement.

Now perform the explain command as follows.

EXPLAIN ANALYZE SELECT * FROM company_1;

Explanation

In the above example, we use the explain keyword that is a command. The end output of the above statement we illustrate by using the following screenshot.

We have some records in the company_1 table. Now use the explain command with where clause as follows.

explain select Comp_Id, comp_name from company_1 where comp_name='Dell';

Explanation

In the above example, we use the explain command with where clause as shown in the above statement. The end output of the above statement we illustrate by using the following screenshot.

So in this way, we can use the explain command with different clauses as per the requirement of the user.

Conclusion

We hope from this article you have understood about the DB2 Explain Command. From the above article, we have learned the basic syntax of Explain Command, and we also see different examples of Explain Command. From this article, we learned how and when we use the DB2 Explain Command.

Recommended Articles

This is a guide to DB2 explain. Here we discuss the basic syntax of Explain Command, and we also see different examples of Explain Command. You may also have a look at the following articles to learn more –

1. DB2 LISTAGG
2. DB2 INSERT
3. DB2 Interview Questions
4. MySQL InnoDB