Db2expln – explain access paths

db2expln -database ais -statement “<query>” -terminal -graph -opids

db2expln -database ais -statement “select xml from datatable d join log l on d.sid=l.sid where d.sid=4” -terminal -graph -opids

DB2 Universal Database Version 10.5, 5622-044 (c) Copyright IBM Corp. 1

Licensed Material – Program Property of IBM

IBM DB2 Universal Database SQL and XQUERY Explain Tool

 

DB2 Universal Database Version 10.5, 5622-044 (c) Copyright IBM Corp. 1

Licensed Material – Program Property of IBM

IBM DB2 Universal Database SQL and XQUERY Explain Tool

 

******************** DYNAMIC ***************************************

 

==================== 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”, “SYSI”

 

 

Statement:

 

select xml

from datatable d join log l on d.sid=

l.sid

where d.sid=4

 

 

Section Code Page = 1208

 

Estimated Cost = 7.051899

Estimated Cardinality = 1.000000

 

( 3) Access Table Name = LOG ID = 2,281

| Index Scan: Name = PK_LOG_SID ID = 1

| | Regular Index (Not Clustered)

| | Index Columns:

| | | 1: SID (Ascending)

| #Columns = 1

| Single Record

| Fully Qualified Unique Key

| Skip Inserted Rows

| Avoid Locking Committed Data

| Currently Committed for Cursor Stability

| #Key Columns = 1

| | Start Key: Inclusive Value

| | | 1: 4

| | Stop Key: Inclusive Value

| | | 1: 4

| Index-Only Access

| Index Prefetch: None

| Lock Intents

| | Table: Intent Share

| | Row : Next Key Share

( 2) Nested Loop Join

( 4) | Access Table Name = DATATABLE ID = 2,280

| | Index Scan: Name = PK_DATATABLE_SID ID = 1

| | | Regular Index (Not Clustered)

| | | Index Columns:

| | | | 1: SID (Ascending)

| | #Columns = 2

| | Single Record

| | Fully Qualified Unique Key

| | Skip Inserted Rows

| | Avoid Locking Committed Data

| | Currently Committed for Cursor Stability

| | Evaluate Predicates Before Locking for Key

| | #Key Columns = 1

| | | Start Key: Inclusive Value

| | | | 1: 4

| | | Stop Key: Inclusive Value

| | | | 1: 4

| | Data Prefetch: None

| | Index Prefetch: None

| | Lock Intents

| | | Table: Intent Share

| | | Row : Next Key Share

( 1) Return Data to Application

| #Columns = 1

 

End of section

 

 

Optimizer Plan:

 

Operator

(ID)

 

RETURN

( 1)

|

NLJOIN

( 2)

/——/ \———-\

IXSCAN *

( 3) /—/ \

| IXSCAN Table:

Index: ( 5) DATATABLE

PK_LOG_SID |

Index:

PK_DATATABLE_SID

 

https://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0005736.html