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.
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.
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.
SELECT * FROM PLAN_TABLE WHERE QUERYNO = 23445
Step 3: The result of the above query will give below details.
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.