I have tried to cancel this notification service without any luck. I will write my service provider and ask them to block all of your messages. If they cannot do this then I will end my service.

-----Original Message-----
From: Serge Rielau <db2udbdba-ezmlmshield-x68276394.[Email address protected]
To: LazyDBA Discussion <[Email address protected]
Sent: Tue, 26 Jul 2005 13:20:15 -0400
Subject: Re: System Temporary TableSpace excess utilization


If you check the optimizer plan you will find that there is at least one
TEMP between the SELECT and the INSERT.
This temp is needed as a DAM to ensure the INSERT doesn't bite the SELECT's
tail.
So if the rows to insert are many this can dim the lights.
Not knowing the plan I have two suggestions:
1. Try MERGE INTO table1 a USING table2 b ON a.firm_id = b.firm_id WHEN
NOT MATCHED THEN INSERT VALUES (b.firm, .....);
The plan may be better.
2. use two phases: First collect all the firm_ids you want to insert into a
temp table. Then do the INSERT joining table2 to the temp. Now you control
the temp placement. It may be narrower (only firm_id) compared to what the
optimizer may have chosen (table2.*).

Cheers
Serge





"divakar"
<db2udbdba-ezmlms
hield-x67141530.x To
[Email Address Removed] "LazyDBA Discussion"
a.com> <[Email address protected]
cc
07/26/2005 09:19
AM 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





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

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