IDS Forum

Re: TEMP DBSPACE Usage:

Posted By: Art Kagel
Date: Wednesday, 16 September 2009, at 11:46 a.m.

In Response To: TEMP DBSPACE Usage: (VIKAS HIVARKAR)

See my comments inline below:
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.

On Wed, Sep 16, 2009 at 6:07 AM, VIKAS HIVARKAR <vikas.hivarkar@gmail.com>wrote:

> Hi,
>
> IDS 11.10.FC3 on solaris 10.
>
> I have the DBSPACETEMP set to:
>
> DBSPACETEMP tempdbs1,tempdbs2
>
> While running a SQL code which does a big selects with 'joins' and 'group
> by'
> on many columns and inserts the data in 3 temp tables.
>
> 2 out of these 3 temp tables hold where small data around 10M but the 3rd
> table hold data around 1GB.
>
> I find that the tempdbs2 is getting filled entirely when there is lots of
> free
> space in tempdbs1.
>
> I understand that due to round robin fragmentation strategy some how the
> Big
> temp table is getting created tempdbs2 only and causing us the problems.
>
> I dont want to change the code and make the big table reside in to
> tempdbs2.
>
You could fragment the three temp tables across the two temp dbspaces.

>
> Other option would be "export DBSPACETEMP=tempdbs1" but then for all the
> tables created in that session would use just tempdbs1.
>
> And yes i can add more space to the tempdbs2
>
> Is there any other way to do this?
>
> Question#2
>
> Is it correct that for sorting operations the Sort Pool in Virtual segment
> of
> shared memeory will be used? Is there any scenario where the tempdbs would
> be
> used for Decision support queries?
>
That depends. If PDQPRIORITY is zero the DS_NONPDQ_QUERY_MEM controls the
quantity of MGM memory used for sorting in memory and DS_TOTAL_MEMORY and
DS_MAX_QUERIES controls the quantity of MGM memory that is available for
sorting in memory when PDQPRIORITY is > 0 (DS_TOTAL_MEMORY /
DS_MAX_QUERIES). If the amount of data exceeds these values the the sorts
will spool sort-work files to disk keeping only the permitted amount of data
in memory at any one time. This will require merging the sort-work files at
the end of the sort operation. If PSORT_DBTEMP is set the sort-work files
are written to the filesystem(s) listed there, otherwise they are written as
unlogged temp files to the temp dbspaces in DBSPACETEMP. FYI, note that the
default value for DS_NONPDQ_QUERY_MEM is 128(KB) which is REALLY tiny! I
typically reset that to 51200 (50MB) on systems that I manage unless memory
is an issue, then I set it to the old default for DBUPSPACE which was 15MB
(DS_NONPDQ_QUERY_MEM overrides the older DBUPSPACE envirnoment setting).

>
> Regards
> Vikas
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--0022158df8f77f28270473b3cc13

Messages In This Thread

• TEMP DBSPACE Usage:
  VIKAS HIVARKAR -- Wednesday, 16 September 2009, at 6:07 a.m.
  • Re: TEMP DBSPACE Usage:
    Art Kagel -- Wednesday, 16 September 2009, at 11:46 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.