Temp tablespace - system or user?

[Arun Srinivasan]

I have a sql that goes like

select ............... from table (select ....... from table t where
<>) as x

Now this temp table x is supposed to keep 10 K rows at a point of
time. Where would they be stored? I know the answer is temp
tablespace, but is it in system temp or user temp?
All I am trying to do is to create a temp (sys or user) with good
amount of pages so that these kinds of queries would speed up. I dont
know the size of which I'd have to create (16, 32k) ? How can we
estimate this so that I can create a system temp space wth 10 gigs.

Please help in defining them.

Ever questioning ,
Arun

[Serge Rielau]

Arun Srinivasan wrote:

I have a sql that goes like

select ............... from table (select ....... from table t where
<>) as x

Now this temp table x is supposed to keep 10 K rows at a point of
time. Where would they be stored? I know the answer is temp
tablespace, but is it in system temp or user temp?
All I am trying to do is to create a temp (sys or user) with good
amount of pages so that these kinds of queries would speed up. I dont
know the size of which I'd have to create (16, 32k) ? How can we
estimate this so that I can create a system temp space wth 10 gigs.

In your example my hope would be that there is no TEMP involved
whatsoever since the access can be pipe-lined.
Anyway any temps "thrown in" bu DB2, including temps to spill sort
memory are SYSTEM TEMPs You should define a system temp for each page
size (4k-32K on DB2 for LUW)

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[stefan.albert]

Use SMS for this - space in the filesystem will only be used when temp
is used.
So put all TEMP into the same filesystem for shared usage.
When TEMP is not needed any more (SQL/transaction ended) the space
will be freed.

Anyway any temps "thrown in" bu DB2, including temps to spill sort
memory are SYSTEM TEMPs You should define a system temp for each page
size (4k-32K on DB2 for LUW)

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Arun Srinivasan]

On Jul 2, 8:10*am, "stefan.albert" <[e-mail]:

Use SMS for this - space in the filesystem will only be used when temp
is used.
So put all TEMP into the same filesystem for shared usage.
When TEMP is not needed any more (SQL/transaction ended) the space
will be freed.

Anyway any temps "thrown in" bu DB2, including temps to spill sort
memory are SYSTEM TEMPs You should define a system temp for each page
size (4k-32K on DB2 for LUW)

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab- Hide quoted text -

- Show quoted text -

Thanks both..