DB2 Query Tuning – db2expln

14 June,2012 by Jack Vamvas

Db2expln  analyses the accesspath for a specific DB2 query.

Use db2expln for DB2 query tuning.  Once you've identified a slow running query causing a bottleneck - use this process to analyse the access path.

This might be part of of an overall Performace Tuning process 

A syntax example:

db2expln -d MYDB -f myquery.sql -z \; -g -o explain.txt

The db2explain output  for my query is

 

 

        Isolation Level          = Cursor Stability
        Blocking                 = Block Unambiguous Cursors
        Query Optimization Class = 5

        Partition Parallel       = No
        Intra-Partition Parallel = No

        SQL Path                 = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM",
                                   "DB2MY"


Statement:

  select SUBSTR(TBNAME, 1, 40), SUBSTR(TBCREATOR, 1, 10), substr(name,
          1, 30), SUBSTR(CREATOR, 1, 8), substr(colnames, 1, 60),
          firstkeycard, fullkeycard, sequential_pages, density, iid,
          uniquerule, stats_time, colnames
  from sysibm.sysindexes a
  ORDER BY tbcreator, TBNAME, NAME


Section Code Page = 1208

Estimated Cost = 140.881699
Estimated Cardinality = 1245.000000

Access Table Name = SYSIBM.SYSINDEXES  ID = 0,7
|  #Columns = 12
|  Relation Scan
|  |  Prefetch: Eligible
|  Lock Intents
|  |  Table: Intent Share
|  |  Row  : Next Key Share
|  Sargable Predicate(s)
|  |  Insert Into Sorted Temp Table  ID = t1
|  |  |  #Columns = 12
|  |  |  #Sort Key Columns = 3
|  |  |  |  Key 1: TBCREATOR (Ascending)
|  |  |  |  Key 2: TBNAME (Ascending)
|  |  |  |  Key 3: NAME (Ascending)
|  |  |  Sortheap Allocation Parameters:
|  |  |  |  #Rows     = 1245.000000
|  |  |  |  Row Width = 172
|  |  |  Piped
Sorted Temp Table Completion  ID = t1
Access Temp Table  ID = t1
|  #Columns = 12
|  Relation Scan
|  |  Prefetch: Eligible
Return Data to Application
|  #Columns = 13

End of section


Optimizer Plan:

      Rows
    Operator
      (ID)
      Cost

     1245
    RETURN

Read More 

DB2 Tuning Toolkit – DB2 Design advisor - Ddb2advis

DB2 Tuning Toolkit – db2exfmt

Author: Jack Vamvas(http://www.dba-db2.com)

Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been saved. Comments are moderated and will not appear until approved by the author. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on DB2 Query Tuning – db2expln

Comments are moderated, and will not appear until the author has approved them.


dba-db2.com | DB2 Performance Tuning | DBA DB2:Everything | FAQ | Contact | Copyright