You'll probably get other responses on tempspace, but it sounds like you're
creating very large intermediate result set(s)for what is quite a simple
query. Does the access plan for the query show a correlated subselect,
possibly in the form of a NLJOIN?
One method that would simplify both the query and the amount of work it has
to do is:
SELECT * FROM T2 EXCEPT SELECT * FROM T1
Regards
Alex Levy
-----Original Message-----
From: divakar
[mailto:db2udbdba-ezmlmshield-x67141530.[Email address protected]
Sent: 26 July 2005 14:20
To: LazyDBA Discussion
Subject: System Temporary TableSpace excess utilization



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.


---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



******************************************************
The information in this E-mail and in any attachment is
confidential and is intended solely for the addressee.
Access, copying, disclosure or use of such information
by anyone else is unauthorised. If you are not the
intended recipient please contact [Email address protected]
While reasonable efforts are made to ensure these files are free
of virus infection and offensive materials, if something of this
nature is inadvertently sent to you, please destroy it, accept
our apologies and contact [Email address protected] with
details of the sender. We will ensure that action is taken
immediately to prevent any recurrence. Debenhams accept no
responsibility for any views expressed by the originator of this email.


Debenhams Retail plc (reg. no. 83395) Registered in England and Wales.
Registered office: 1 Welbeck Street, London W1G 0AA.

http://www.debenhams.com

******************************************************


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