The DB2 DBA: Identifying Indexes with Shared Access Paths
July 13, 2011 Hey, Mike
|
The SysIndexStat catalog view has the column Owning_Index_Owner (plus schema, system names, etc.). However, that catalog view does not include constraint-created indexes, nor does it include keyed logical and physical files that share the access path owned by an SQL index. The DSPFD command does show the SQL index that owns the access path for such logical files, but I have not been able to generate this information in an outfile format from that command. The information must exist somewhere (since it shows on screen) and I would think it would be in one of the file description APIs, but I haven’t found it yet. Do you know where I can access it programmatically? I want to build a function that would supply that information for logical files that are not in the SysIndexStat catalog. With this information sorted by key column names, we could do a better job of cleaning up duplicate indexes and also make better decisions about new indexes. –Nelson This is a great question, Nelson. For those who may not know, I’d like to explain a bit about index ownership. When an index is built that shares one or more initial key columns of another existing index, DB2 for i is smart enough to build the new index by re-using the “shared columns” from the pre-existing index. To illustrate, say an index called INDEX1 on the ORDER_HEADER table consists of columns COMPANY, CUSTOMER, and SHIP_DATE. Then an index called INDEX2 is built on the same table with columns COMPANY, CUSTOMER, and ORDER_NUMBER. Because the first two columns (COMPANY, CUSTOMER) are shared in common with the same sort order (ascending), DB2 recognizes it is a waste to build another completely new index with these same two primary columns again (because it would have to store the same information again and it would have to take the time to do the sorting again). When building INDEX2, DB2 leverages the sort order for the first two columns in INDEX1 so that the only remaining work to do when building the access path for INDEX2 is to sort by ORDER_NUMBER within the pre-existing COMPANY and CUSTOMER access path from INDEX1. Because of this sharing, INDEX1 is known as the “owner” of INDEX2. In DB2 for i, this ownership mechanism encompasses more than just SQL indexes, it also includes constraints such as primary keys and unique keys, keyed physical files and keyed logical files. I asked IBM‘s Dan Cruikshank about how to programmatically retrieve index ownership information and he responded with three surprisingly simple approaches:
All of us who have some kind of management responsibility over our DB2 databases should follow in Nelson’s footsteps to identify the shared indexes on our systems. Indexes help a database system by improving query performance. However, maintaining too many indexes during database modifications imposes a performance tax on the database. It’s the database administrator’s job to determine the optimal balance between improved query performance with indexes and minimal index overhead. Identifying shared indexes helps with decision making as follows:
Maintaining a balanced index strategy is key to an optimally running database. –Michael Sansoterra RELATED STORY Eliminate Unnecessary Access Paths
|
