How to find out the PLANS which will be impacted if a index is dropped?

DB2DatabaseBig Data Analytics

A DB2 PLAN contains all the SQL statements in executable form. To execute any COBOL-DB2 program we need to provide its corresponding PLAN. The DB2 optimizer will evaluate the access path for the SQLs present in the PLAN to fetch the result for the COBOL-DB2 program. The SQLs in PLAN may use a number of indexes. Indexes facilitate the table search based on a key value and speed up the query processing time.

If any of the index is dropped, it may negatively impact the PLANS which contain the SQLs using that dropped index. Therefore, as part of impact analysis, it is very necessary to find out the PLANS which will be impacted if any index is dropped.

There is a DB2 system table SYSPLANDEP. This table stores the dependencies of a plan on other DB2 objects. We can use the below SQL query on this table to find out all the impacted plans if any index is dropped.



The column BNAME indicates the name of Index and DNAME indicates name of the plan. BTYPE is the DB2 object type. It is ‘I’ for index, ‘V’ for views, ‘A’ for alias, etc.

Published on 30-Nov-2020 13:47:14