Some years ago DB2 for z/OS V8 delivered multiple enhancements in the area of partitioned tables. You can define partitioned tables without a partitioned index, or secondary indexes (also known as NPI) as DPSI (Data partitioned secondary index). It is the table data that defines the physical partitioning limits for this new index type, and not, as in the past, the limit keys defined on the partitioned index. DPSIs are a good way to get rid of those huge NPIs.
But be aware ! A change of the physical database structure from NPI to DPSI must be planned under consideration of existing SQL accesses. Depending on the WHERE clauses within your SQL, response times can suffer significantly, if a REBIND of SQL accessing a DPSI produces a worse access path.
Wouldn’t it be nice and comfortable to understand possible access path changes BEFORE actually changing the NPI to a DPSI ?
BMC SQL Performance for DB2 comes with a unique index advisor technology, which makes it possible for you to define so called ‚what if’ indexes, and to check the access path against those cloned indexes. All catalog statistics of the original index (NPI) are automatically migrated to the cloned ‚what if’ index (DPSI). That means, the access path, calculated by the DB2 optimizer reflects the real situation on your production system, and is meaningful, because the cloned index has been created by the 'what if' index function temporarly on the production system. Based on that analysis of that new, simulated access path, you can now plan to adjust your existing index design. This functionality means less risk for such kind of schema changes, because important access paths can be verified and adjusted before the actual production change is applied.