DB2 table design and performance considerations

[jsimone]

This question is about DB2 table design and performance.

We are using DB2 UDB Enterprise 8.2 on Linux.

We have 6 tables in a parent-child (one-to-many) relationship with each other. Each child table having an FK up to its parent. This particular design is motivated by our domain model.

A -> B -> C -> D -> E -> F

There is some debate in our team about how best to accomplish this. Two approaches have been discussed.

One alternative is to have a unique ID (UID) as a DECIMAL(0,19) for each PK/FK combination and then do selects for each table based only on this key. I have paraphrased the pseudo SQL as follows:

create table A ( A_UID PK not null, ...):
create table B ( B_UID PK not null, A_UID FK, ...);
create table C ( C_UID PK not null, B_UID FK, ...);
create table D ( D_UID PK not null, C_UID FK, ...);
create table E ( E_UID PK not null, D_UID FK, ...);
create table F ( F_UID PK not null, E_UID FK, ...);

Another approach is to use a UID only at the top table and then have sequence numbers (0,1,2,3, ...) as INTEGER for each additional table as we go down. Therefore to access the top table all we need is a single key.

To select rows from the second table we would need the composite key (FK, SEQ_NO1). To access the third table, C, we would need the composite key (FK, SEQ_NO1, SEQ_NO2). To access the 6th table we would need the composition key (FK, SEQ_NO1, SEQ_NO2, SEQ_NO3, SEQ_NO4, SEQ_NO5). In this case we need all the sequence numbers to fully specify the records we want from the table. Again here is some pseudo SQL which represents the design:

create table A ( A_UID PK not null, ...);
create table B ( B_SEQ_NO not null,
A_UID FK, ...);
PK is composite of (B_SEQ_NO, A_UID)

create table C ( C_SEQ_NO not null,
B_SEQ_NO not null,
A_UID FK, ...);
PK is composite of (C_SEQ_NO, B_SEQ_NO, A_UID)

create table D ( D_SEQ_NO not null,
C_SEQ_NO not null,
B_SEQ_NO not null,
A_UID FK, ...);
PK is composite of (D_SEQ_NO, C_SEQ_NO, B_SEQ_NO, A_UID)

create table E ( E_SEQ_NO not null,
D_SEQ_NO not null,
C_SEQ_NO not null,
B_SEQ_NO not null,
A_UID FK, ...);
PK is composite of (E_SEQ_NO, D_SEQ_NO, C_SEQ_NO, B_SEQ_NO, A_UID)

The belief is that DB2 will already have these records in the same set of pages for the second approach but will not have them for the first approach. So, page caching comes into play for second approach but not the first. The intent is to do the inserts for the cascade with 1 insert statement. So the first approach seems reasonable. But, I would think if the records are scattered on the disk, inserts come in at random times then the second approach is more reasonable.

Hence it is reasoned the second approach will be more performant. Or is it? Does having composite keys cost more?

Do these arguments have any merit?

Thank-you very much in advance.