DB2 Performance Tips - EXPLAIN

DB2 EXPLAIN Explained

What is DB2 Explain?

It is an DB2 aid for optimization.

What does DB2 Explain does?

All the SQL statements are passed through DB2 optimizer and the access path are externalized. This means any DBA doing performance test can get correct access path by using EXPLAIN. All the access path are recorded in PLAN_TABLE.

What is PLAN_TABLE?

Important coloumns for PLAN_TABLE are as following:

QUERYNO : This is the query number user should assigned to identify query.
QBLOCKNO:  Position of query in the statement being EXPLAINED
APPLNAME : This is PLAN Name
PROGNAME : Program/Package Name
METHOD : There are 4 options and each number signifies type of join

0 Table Access

1 Nested Loop

2 Merge Scan

3 Sort needed by ORDER BY, GROUP BY, SELECT, DISTINCT or UNION

4 Hybrid
CREATOR : User id
TNAME : Tablename
TABNO : position and reserved for IBM only
ACCESSTYPE : 

I - by index

N - indexscan when predicate is IN

R - Table Space Scan

M - Multiple Index Scan

MX - Index Scan

MI - Intersection of multiple index

MU - Union of multiplex index
MATCHCOLS : number of keys used in index scan

ACCESSCREATOR : Creator of Index

ACCESSNAME : Name of Index

INDEXONLY : Y/N ( Yes is index is sufficient to get required data)

SORTN_UNIQ : Y/D (Yes if Sort performed to remove duplicates )

SORTN_JOIN : Sort is performed on table when method is 2 or 4

SORTN_ORDERBY : if query results in order by 

SORTN_GROUPBY : if query results in group by

TSLOCKMODE : Lock Mode of table or tablespace

IS : Intent Share Lock

IX : Intent Exclusive Lock

S : Share Lock

U : Update Lock

X : Exclusive Lock

SIX:  Share with intent exclusive lock

N : UR isolation; no lock

TIMESTAMP : Timestamp at which explain statement is bound

PREFETCH : List Prefetch or Sequential Prefetch

MIXOPSEQ : Sequence

DB2 performance tips - PART 1

DB2 Queries that can optimize performance 

1. Select columns which are required
2. Use where clause for queries where ever possible
3. Avoid using NOT in Queries as NOT is non-indexable
4. Use DATE Functions wherever possible to get number of days.
       Eg: Select DATE('25-04-1986') - DATE('04-09-1995')
5. Details on Index and EXPLAIN:

Before and After creating index always use EXPLAIN functionality of DB2 to measure how   and index is working.If the index you created is being used then you will get that in your query once explain runs.

If you want to see when your index is last used you can do that by this query:

SELECT * FROM SYSIBM.SYSINDEXES where NAME = 'your index name';

I will give details on EXPLAIN in my next DB2 blog.