EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials DB2 Tutorial DB2 explain
Secondary Sidebar
DB2 Tutorial
  • DB2 Tutorial
    • What is DB2?
    • DB2? current date
    • DB2 purescale
    • DB2 backup
    • DB2 restore
    • DB2 C Express
    • DB2 Version
    • DB2? Architecture
    • DB2? Data Types
    • DB2? load
    • DB2? order by
    • DB2 date
    • DB2 NVL
    • DB2? update
    • DB2 warehouse
    • DB2 grant
    • DB2 database
    • DB2 VARCHAR
    • DB2? INSERT
    • DB2 LISTAGG
    • DB2 LIKE
    • DB2 TRUNCATE TABLE
    • DB2 LIST TABLES
    • DB2 between
    • DB2? current timestamp
    • DB2? length
    • DB2? bind
    • DB2 limit rows
    • DB2? export
    • DB2 with
    • DB2 Create Table
    • DB2 case statement
    • DB2 CAST
    • DB2 Functions
    • DB2 Date Functions
    • DB2? row_number
    • DB2 trim
    • DB2? Translate
    • DB2 UNION
    • DB2 timestamp
    • DB2? TIMESTAMPDIFF
    • DB2? replace
    • DB2 merge
    • DB2 COALESCE
    • DB2 ISNULL
    • DB2? explain
    • DB2 Join
    • DB2 alter column
    • DB2 rename column
    • DB2? Describe Table
    • DB2? rename table
    • DB2 List Databases
    • DB2 LUW
    • DB2 Query
    • DB2 GROUP BY
    • DB2 TO_DATE

DB2 explain

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.

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (85,992 ratings)

Syntax

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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.

DB2 explain output 1

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.

DB2 explain output 2

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
Popular Course in this category
SQL Training Program (7 Courses, 8+ Projects)
  7 Online Courses |  8 Hands-on Projects |  73+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more