DB2 Server for VSE & VM: Performance Tuning Handbook

Organizing Referential Structures

Because referential operations (update of a primary or foreign key, deletion of a parent row, or insertion of a foreign key) involve access to more than one table, when organizing a referential structure you should carefully consider the implications of concurrency. (The issues discussed here are equally applicable to any set of related tables.)

Referential operations require access to multiple tables, and possibly to multiple rows of dependent tables. This characteristic increases the possibility of deadlock situations. When the primary key of a parent table is modified (DELETE, UPDATE), all dependent rows are accessed (and possibly also modified). Conversely, when a foreign key in a dependent table is modified (INSERT, UPDATE), the parent table is accessed. If two such operations run concurrently in different logical units of work (LUWs), a deadlock situation could result, which would trigger the automatic rollback of the later LUW.

Note:

A similar potential deadlock situation would be encountered whenever logically related data is concurrently accessed in opposing ways.

Similarly, when multiple users access referential structures, lock contention increases (because the users are accessing the same tables, and the number of rows accessed can be quite large). This contention may reduce concurrency.

A user who understands the nature of referential operations can minimize their effect on concurrency, by reducing the chances of multiple users performing logically unrelated operations contending for locks. (Contention cannot be avoided if users are performing logically conflicting operations.) Consider the following ways to improve concurrency:

• Do not put tables from different referential structures in the same dbspace. In general always try to place only one table in each dbspace.
• Create an index on a foreign key. (Whenever possible this should be a unique index that exactly matches the columns of the foreign key. If necessary make the index unique by creating a multicolumn index.) This provides the most selective access path possible to dependent rows whose foreign key has a particular value. Operations on a parent row can use this index to scan the dependent table, thus avoiding the need for a dbspace scan or a nonselective index scan.

  You should use discretion in creating such indexes, especially on tables with several foreign keys. For example, you may consider creating them only on the most often referenced foreign keys. These indexes will be of particular use in reducing deadlock situations in environments where parent tables and dependent tables are being modified concurrently, and will also provide faster execution of all referential operations against parent rows.