How to find out the access path selected by optimizer for a particular query?

DB2 optimizer plays an important role in the overall performance of the database. The optimizer selects the optimal access path for each query through which data can be fetched from the database. It identifies the indexes to follow, query predicates, etc.

The optimizer selects the access path automatically and we can easily find the access path using EXPLAIN DB2 command. We have to SET the query number first and then give our SQL query to find out its access path in three simple steps.

For example,

We have a DB2 ORDERS table and we want to examine the SELECT query which has a predicate on the ORDER_DATE column.

Step 1: Execute below EXPLAIN command and set the query number followed by SELECT query ad below.

Example

EXPLAIN PLAN SET QUERYNO=23445 FOR
SELECT ORDER_ID FROM ORDERS WHERE ORDER_DATE = ‘12-08-2020’

Step 2: The above EXPLAIN command will update the PLAN table with relevant query and explain details. Execute below command to view details in PLAN table.

Example

SELECT * FROM PLAN_TABLE WHERE QUERYNO = 23445

Step 3: The result of the above query will give below details.

• ACCESTYPE
• MATCHCOLS
• ACCESSNAME
• INDEXONLY

The ACCESTYPE column has the details of access methods like Index scan, Index only scan, etc. The MATCHCOLS column stores the number of index keys utilized for index scan. The ACCESSNAME column stores the name of the index used for index scan and INDEXONLY column tells us whether data can be fetched from the index itself.