May 11, 2010

DB2 RI and the Need for Supporting Indexes

Last week I noted the need for DB2 defined referential integrity (RI) and how performance problems occur if you don't create the index that would support the child foreign key relationship. (The relationship information can be found in the DB2 catalog.)

Here's an example to explain what I meant. Table SYSIBM.SYSRELS contains one row for each defined relationship within the database. A couple of things to note about this table: The relationship can be DB2 enforced or informational. If column ENFORCED is set to "Y," then you know DB2 enforces the RI. Also, when the IXCREATOR and IXNAME columns are blank, the primary index is used for the parent key; otherwise, the index creator and name are present.

This SQL lists the information for creator “coltr05”: click the following to Download DB2utor 5.11.10 table 1
Sample output:click the following to Download DB2utor 5.11.10 table 2

Now I wanted to write some SQL that reports the index and columns supporting the parent table and provides the index and columns for the child foreign key relationship. In this SQL, it's assumed the column names are the same in both the parent and child tables. If you choose not to use the same column names, you could enhance this SQL to include the column data type and size along with position. Click the following to Download Db2utor 5.11.10 table 3
Notice that the child relationship from COLTR05.PARENT doesn't have a supporting index on related table, COLTR05.CHILD. In this situation you won't notice a problem until the child table begins to grow in size and processing times for CASCADE DELETE start to grow.