During the execution a sql query, DB2 generates a temporary file in
the temp table space which is over 6Gig. Does anyone know how to
reduce the size of this temporary file? It is possible to reduce its
size by tuning the database configurations?

T.C. Sheng
TCSHENG@SYSVIEW.COM.TW

On Tue, 09 Dec 2003 03:09:17 -0800, T.C. Sheng wrote:

> During the execution a sql query, DB2 generates a temporary file in
> the temp table space which is over 6Gig. Does anyone know how to
> reduce the size of this temporary file? It is possible to reduce its
> size by tuning the database configurations?
>
> T.C. Sheng
> TCSHENG@SYSVIEW.COM.TW

More likely by tuning the database or rewriting the query.

But we'd need more info - suggest the query SQL and table and index
definitions from describe (the index ones with column details) and the
number of rows in the tables.

Phil

T.C. Sheng,

if you post DDL, the query and the plan, you might get some useful advice.
This could be a cross join, for example.

Philip Nelson <gulfvuk@yahoo.co.uk> wrote in message news:<pan.2003.12.09.12.27.43.600321@yahoo.co.uk>. ..
> On Tue, 09 Dec 2003 03:09:17 -0800, T.C. Sheng wrote:
>
> > During the execution a sql query, DB2 generates a temporary file in
> > the temp table space which is over 6Gig. Does anyone know how to
> > reduce the size of this temporary file? It is possible to reduce its
> > size by tuning the database configurations?
> >
> > T.C. Sheng
> > TCSHENG@SYSVIEW.COM.TW
>
> More likely by tuning the database or rewriting the query.
>
> But we'd need more info - suggest the query SQL and table and index
> definitions from describe (the index ones with column details) and the
> number of rows in the tables.
>
> Phil

This query joins four tables in which three of these tables contain
2.5 million rows and the fourth has 12 million rows. Each table has
only one index base on a single primary key column. Actually, this
query is not complex in any sense, just the result is very large. I
was told that by increase the sort heap might help reduce the size of
the temp file. But, I am not sure how to determine a the sort heap
size "scientifically"