KR editions 104 to 106

KR-104 (EXPLAINing itself-I)

Hi everyone,

Have you come across EXPLAIN(NO) in programming (in DB2)?

• While binding a COBOL program, in the BIND step (within the control card), you would find a statment: EXPLAIN(NO/YES) 
• NO means the "explain" feature will be turned off while YES would turn it on. If we were to turn on the explain feature, then we can get some handy information from DB2 regarding the use of indexes in SQLs, what sort of access method is being used etc. 
• If we specified EXPLAIN(YES) then the result of the explain feature would be placed in the table named "owner-name.PLAN_TABLE". 
• So if a code say TESTCODE had 5 sql statements then after binding this code (i.e. after precompiling, compiling and linking), if we specified EXPLAIN(YES), we'll have 5 rows inserted into the table "owner-id.PLAN_TABLE" Another table modified by EXPLAIN is the statement table DSN_STATEMNT_TABLE).

Note: The table PLAN_TABLE is a standard DB2 table (but it isn't mandatory that it should be present in all systems). If not present then it can be created in the system by the DB admin. Again though the plan table is present the statement table might not be. EXPLAIN can still be used but it will only insert into the plan table.

The access path details (whether index is used etc) can be found in the plan table while the cost of the SQL is present in the statement table.

We'll take a closer look into the plan table in the next edition...
Tips/facts on EXPLAIN are welcome.

KR-105 (EXPLAIN - II)

Hi everyone,

Continuing with the EXPLAIN feature.....
Some columns in the PLAN_TABLE are:

1)QUERYNO- every SQL is given a query number 
2)PROGNAME- the program name 
3)METHOD- denotes whether a join has been used (anything other than 0 is a join). 
4)TNAME- the table accessed by this SQL. 
5)ACCESSTYPE- how the table is being accessed in this SQL (values of I, I1 and N denote indexed access). 
6)MATCHCOLS- the number of index columns present in the SQL (the where condition) which can be used by DB2; useful only if the access type is an indexed-access. 
7)QBLOCKTYPE - denotes the type of SQL (SELECT or UPDATE etc).


Let's say our program (called testcode) has one select SQL:
SELECT * FROM mytab.dummy_t
WHERE colB='05'
AND colC='20'

Let's say that our table mytab.dummy_t has an index on columns colA, colB, colC (they are the date columns). 

The result of EXPLAIN will be:
1)PROGNAME- testcode 
2)METHOD- 0 
3)TNAME- mytab.dummy_t 
4)ACCESSTYPE- I 
5)MATCHCOLS - 0 
6)QBLOCKTYPE - SELECT

Food for thought: Note something peculiar? MATCHCOLS is 0 even though we use are using the index columns in our query!

That's all for this week; have a great weekend...
any notes or useful points regarding EXPLAIN are welcome.

KR-106(EXPLAIN - III)

KR celebrates one year! 

Continuing with the EXPLAIN command...
Recap:
Our SQL was
SELECT * FROM mytab.dummy_t
WHERE colB='05'
AND colC='20'

and the result in PLAN_TABLE (using EXPLAIN) said that ACCESSTYPE is I and MATCHCOLS is 0.

Explanations:

• 'I' stands for Indexed access - i.e. DB2 will make use of the index pages to retrieve the rows (and in our query colB and colC are 2 columns of the composite index). 
• MATCHCOLS indicates the number of index columns present in the SQL condition (i.e. the predicate) which DB2 can use to select the rows. So even though we've used 2 columns which are part of the index DB2 cannot make use of them. But then how is it indexed access? Contradictory?
  
  Let's change the SQL to: 
  SELECT * FROM mytab.dummy_t
  WHERE colA='2005'
  AND colB='05'
  
  Now the values in PLAN_TABLE for this SQL will be: ACCESSTYPE = I and MATCHCOLS = 2 
  
  The reason for the difference is that in the 2nd SQL we've used the higher order index column- the composite index is on the columns colA, colB and colC. 
  
  When MATCHCOLS has a value it denotes a matching-indexed acess and when MATCHCOLS=0 it means DB2 will use a non-matching indexed access. What's all this matching and non-matching?
  
  
  Did you know that the first edition of KR was released on 2nd June 2004 (and it happened to be about DB indexes)? 
  We'll consolidate the theory (about indexed scan) with practical findings (using EXPLAIN) in the subsequent editions.
  
  Feel free to contribute to KRs (tips, suggestions for topics welcome).
  
  

Go back to the main contents page