DB2/LUW :- db2expln

Background

It is time to start digging deeper into specific SQL Queries targeting our DB/2 LUW Instance.

Query

A simple query

select *
  from DBO.CALIFORNIA_CITIES
  where SCHOOL_CITY like 'SAN F%'

Tool

db2expln

File Location

On my Linux box, the db2expln file is located in /home/db2inst1/sqllib/bin.

That is /home/[db2account]/sqllib/bin.

Syntax

db2expln -database {database} -t -g -q "{query}"

Sample

db2expln -database CSSTBL -t -g -q "select * from DBO.CALIFORNIA_CITIES where SCHOOL_CITY like 'SAN F%' "

Output

Image

db2expln_20180827_0547PM

Text

==================== STATEMENT ==========================================

        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",
                                   "DB2INST1"

Statement:

  select *
  from DBO.CALIFORNIA_CITIES
  where SCHOOL_CITY like 'SAN F%'

Section Code Page = 1208

Estimated Cost = 25.714016
Estimated Cardinality = 0.999993

Access Table Name = DBO.CALIFORNIA_CITIES  ID = 12,5
|  Index Scan:  Name = DBO.CALIFORNIA_CITIES_PK  ID = 1
|  |  Regular Index (Not Clustered)
|  |  Index Columns:
|  |  |  1: SCHOOL_CITY (Ascending)
|  #Columns = 4
|  Skip Inserted Rows
|  Skip Deleted Keys
|  Skip Deleted Rows
|  Avoid Locking Committed Data
|  Evaluate Predicates Before Locking for Key
|  #Key Columns = 1
|  |  Start Key: Inclusive Value
|  |  |  1: 'SAN F...'
|  |  Stop Key: Inclusive Value
|  |  |  1: 'SAN F▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒...'
|  Data Prefetch: Sequential(1), Readahead
|  Index Prefetch: Sequential(1), Readahead
|  Lock Intents
|  |  Table: Intent Share
|  |  Row  : Next Key Share
|  Sargable Predicate(s)
|  |  Return Data to Application
|  |  |  #Columns = 4
Return Data Completion

End of section

Optimizer Plan:

                      Rows
                    Operator
                      (ID)
                      Cost

                    0.999993
                     RETURN
                      ( 1)
                     25.714
                       |
                    0.999993
                     FETCH
                      ( 2)
                     25.714
               /---/        \
       0.999993               796
        IXSCAN         Table:
         ( 3)          DBO
       12.8613         CALIFORNIA_CITIES
          |
         796
 Index:
 DBO
 CALIFORNIA_CITIES_PK

Explanation

  1. Tables
    • Access Table Name :- DBO.CALIFORNIA_CITIES
    • Index Scan :-
      • Index :- DBO.CALIFORNIA_CITIES_PK ID = 1
      • Index Columns :-
        • SCHOOL_CITY (Ascending)
    • Key Columns = 1
      • Start Key: Inclusive Value
        • ‘SAN F…’
      • Stop Key: Inclusive Value
        • ‘SAN F▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒…’

 

 

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s