Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to find out the PLANS which will be impacted if a index is dropped?
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.
Example
SELECT BNAME, DNAME FROM SYSIBM.SYSPLANDEP WHERE BTYPE = ‘I’ AND BNAME = ‘’
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.
99 Views
