strange behaviour with temporary tables

[Sumanth]

the pseudo code for my update is

for i = 1 to n;
insert k records into temp_table;

update a set c1 = (select c1 from temp_table where
temp_table.pk = a.pk), c2 = ( select c2 from
temp_table where temp_table.pk = a.pk)
where a.pk in (select pk from temp_table)

delete from temp_table
commit;
end for;

When the temp_table is created as a DB2 temporary table, the performance
degrades after each loop, but if temp_table is created as a regular DB2
table we see a consistent performance.

Also the number of row_reads (activity monitor) was found to be extremely
high.

The size of the table that is updated is around 70,000 and the temp_table
has 8192 rows.

Can anyone shed some light on what I am doing wrong? or if there are any
limitations on temporary table that I need to be aware of.

Thanks,
Sumanth

[Serge Rielau]

Sumanth wrote:

the pseudo code for my update is

for i = 1 to n;
insert k records into temp_table;

update a set c1 = (select c1 from temp_table where
temp_table.pk = a.pk), c2 = ( select c2 from
temp_table where temp_table.pk = a.pk)
where a.pk in (select pk from temp_table) update a set (c1, c2) = (select c1, c2 ...)

delete from temp_table
commit;
end for;

When the temp_table is created as a DB2 temporary table, the performance
degrades after each loop, but if temp_table is created as a regular DB2
table we see a consistent performance.

This is APAR# IY77333 fixed in FP11 (never mind the description. It's
too narrow).
DB2 keeps adding rows to the end despite the delete because temp tables
are implicitly defined as APPEND ONLY. So you keep scanning more and
more empty space.
In FP11 DB2 detects a DELETE without WHERE clause on such a temp and
truncates.

However you would be better of declaring the table as ON COMMIT DELETE
ROWS. Simply skip the DELETE. Let COMMIT truncate for you.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab