DB2 Query Tuning – db2expln

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