Prev: TRANSLATE function
Next: Yellowfin Business Intelligence Release 4.0 Announced to Industry
From: Arun Srinivasan on 1 Jul 2008 16:32
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
From: Serge Rielau on 1 Jul 2008 23:13
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
From: stefan.albert on 2 Jul 2008 09:10
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

From: Arun Srinivasan on 2 Jul 2008 10:20
On Jul 2, 8:10 am, "stefan.albert" <stefan.alb...(a)spb.de> wrote:
> 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..
 | 
Pages: 1
Prev: TRANSLATE function
Next: Yellowfin Business Intelligence Release 4.0 Announced to Industry