What is an EXPLAIN in DB2.
How and what is the steps followed in EXPLAIN command. Can
all the queries be used in EXPLAIN command to check the
performance.

Answers were Sorted based on User's Feedback



What is an EXPLAIN in DB2. How and what is the steps followed in EXPLAIN command. Can all the quer..

Answer / yuvaevergreen

>>Explain can be used to improve the performance of queries.
>>Explain command populates three tables
PLAN_TABLE,DSN_STATEMNT_TABLE,DSN_FUNCTION_TABLE. These
tables contains various info about the queries which can be
used for improving the performance.
>>Step1:PLAN_TABLE must be created.
>>Step2:Use explain sql or explain option in Bind to
populate the tables.
>>For example,explain plan set queryno=01 for select empname
from employee;
This would populate plan and statement table.Assigned
queryno would be 01.
>>Alternately EXPLAIN(YES) in BIND option.
>>Only select,insert,update,select for update,delete, delete
current of cursor,update current of cursor can be used in
explain.

Is This Answer Correct ?    7 Yes 0 No

What is an EXPLAIN in DB2. How and what is the steps followed in EXPLAIN command. Can all the quer..

Answer / s.v.venkata sivaprasad

explain is the db2 tool which used to improve db2
performance and provides best access path to package

Is This Answer Correct ?    1 Yes 0 No

What is an EXPLAIN in DB2. How and what is the steps followed in EXPLAIN command. Can all the quer..

Answer / ryan

Whenever we run a query,DB2 creates an access plan that specifies how it will access the requested data. This is created whenever the sql is compiled at bind time for static sql and before execution for dynamic sql.
DB2 bases the access paths on the SQL statements and also on the statistics and configuration parameters of the system.
Even when an sql is made efficient, it can become inefficient as data grows. So, we need to run DB2 runstats  so as to keep updated statistics . DB2 config and storage can change and plans can be rebound. Db2 explain gives us the info for the plan, package, or SQL statement when it is bound. The output of the EXPLAIN  is stored in user-created table called plan table. Whenever we want to tune a query, we need to go and check the plan table so as to get an idea of the access path DB2 optimizer is using.
http://mframes.blogspot.in/2015/01/db2-explain-and-plan-table-column-names.html

Is This Answer Correct ?    0 Yes 0 No

Post New Answer

More DB2 Interview Questions

where will u check for sqlcode ?

2 Answers   TCS,


what is the steps followed in EXPLAIN Process or EXPLAIN command. (Explain is for identifying the optimized access path but how or what is the steps for doing the EXPLAIN)

4 Answers   IBM,


define clustering index.

0 Answers  


what are the SQL codes u freequently got?

2 Answers   Logica CMG,


What is meant by index cardinality?

2 Answers  


what is the name of the default db2 catalog database?

3 Answers   IBM,


What is the difference between TYPE 1 index & TYPE 2 index

1 Answers   IBM,


What is the result of open cursor statement?

0 Answers  


Request to database but database is not available so what is was abend is raised?

2 Answers   UST,


How does cobol compile db2 program?

0 Answers  


PLAN IS EXECUTABLE AND PACKAGE IS NOT EXECUTABLE . THEN WHAT IS THE USE OF PACKAGE?

2 Answers   Tech Mahindra, Wipro,


What is the use of reorg in db2?

0 Answers  


Categories