We are executing the following query:

Insert into Table1 ( select * from Table2 a where not exists (select
firm_id from Table1 b where a.firm_id = b.firm_id))

In this query, we are trying to insert into Table1, the records that are
present in Table2 but NOT in Table1.

The issue here is that when we run this query, the temporary table space
defined by us (SMS, Contents = System Temporary data, 32K pagesize)
occupies all the available free space the in file system. The free space
available for this TS to expand before we start this query is around
150Gb. When the process failed after 5 hrs by giving SQL error -968(File
System Full), we checked and found out that the all the free space was
occupied by this TableSpace only. Infact, db2diag.log also reported
errors for container full of this TS.

Each table size should be around 100 GB. Both the table have around 100
million of records each. Both have indexes defined on firm_id. Both
tables have identical structures.

My question here is, is it normal for a Temporary TS to occupy this much
space.
What can we do minimise this space usage. Do we need to work on some DB2
parameters or we should simplify the query itself.

We are using DB2 V8 on AIX.

Thanks in Advance,
Divakar




Confidentiality Notice

The information contained in this electronic message and any attachments to this message are intended
for the exclusive use of the addressee(s) and may contain confidential or privileged information. If
you are not the intended recipient, please notify the sender at Wipro or [Email address protected] immediately
and destroy all copies of this message and any attachments.

DB2 & UDB email list listserv db2-l LazyDBA home page