IDS Forum

Re: Query Regarding Temp Dbspace

Posted By: Art Kagel
Date: Friday, 8 August 2008, at 1:46 p.m.

In Response To: Re: Query Regarding Temp Dbspace (VIKAS HIVARKAR)

It is always a good idea to include one or more 'normal' dbspaces in
DBSPACETEMP for logged temp tables to keep them out of the ROOTDB. Choose
dbspaces with very low IO activity (see onstat -D) and lots of free space.
Root gets significant read activity on most systems so it best to not
interfere with that and so to move logged temp tables away to other dbspaces
if you can. You need the temp dbspaces in DBSPACETEMP , obviously, but
should have normal ones also.

Yes, TEMPTAB_NOLOG set to 1 will force all temp tables to be non-logged by
default (unless the user specifies otherwise).

Art

On Fri, Aug 8, 2008 at 11:46 AM, VIKAS HIVARKAR <vikas.hivarkar@gmail.com>wrote:

> Dear Mr Kagel !
>
> IDS 11.10 FC2W4 on solaris 10
>
> you were correct, too much of reading into what i read confused me.
>
> I have two temp dbspace entries againts DBSPACETEMP.
>
> Pls let me know, would it be a better idea (for some scenario)to have one
> non
> temp dbspace entry againsts DBSPACETEMP ( i know it depends on ones
> requirments and the code written ) but your word of advice will be helpful.
>
> I guess you were mentioning about the onconfig parameter TEMPTAB_NOLOG
> setting this to 1 will disable logging on temp tables and then all the temp
> tables would be created only in temp dbspace with -t option.
>
> Thanks & Regards
> vikas.
>
> >Vikas,
>
> >You are reading too much into what you have read. The post is simply
> >telling you that you cannot create a logged table in a temp dbspace and
> that
> >the engine will ONLY create unlogged temp tables in temp dbspaces and
> logged
> >temp tables ONLY in logged//non-temp dbspaces. So, if you do:
>
> >select *
> >from sometable
> >into temp freddy;
>
> >Since the temp table freddy is a logged table BY DEFAULT unless you are
> >running IDS 10.00 or later and have set the ONCONFIG parameter that
> changes
> >that default. It will NOT be created in your temp dbspaces no matter how
> >many of them you list in DBSPACETEMP. If you also have non-temp dbspaces
> >listed in DBSPACETEMP then freddy will be created in one or more of those
> >non-temp dbspaces or if you have ONLY listed temp dbspaces in DBSPACETEMP
> >then freddy will be created in ROOTDB.
>
> >On the other hand, if you do:
>
> >select *
> >from sometable
> >into temp wilma with no log;
>
> >Temp table wilma should be created in a temp dbspace, so it will by
> default
> >be created in one or more of the temp dbspaces listed in DBSPACETEMP but
> not
> >in any non-temp dbspaces listed there. If DBSPACETEMP is empty or all of
> >the listed dbspaces are non-temp it will be created in a normal dbspace or
> >in ROOTDB.
>
> >That's all. There's a full discussion of the priority of temp table
> >creation in the Performance Guide of the FM set.
>
> >In answer to your direct question, if you want the engine to use your temp
> >dbspaces for temp tables in a logged database, you must include WITH NO
> LOG
> >in the CREATE TEMP TABLE statement or the INTO TEMP clause (or if you are
> >using a recent release - always a good idea to post your version and
> >platform info - set the ONCONFIG parameter that changes the default
> logging
> >status of temp tables in the instance).
>
> >Art
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--
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.

Messages In This Thread

• Query Regarding Temp Dbspace
  VIKAS HIVARKAR -- Friday, 8 August 2008, at 8:57 a.m.
  • Re: Query Regarding Temp Dbspace
    Art Kagel -- Friday, 8 August 2008, at 10:28 a.m.
    • Re: Query Regarding Temp Dbspace
      VIKAS HIVARKAR -- Friday, 8 August 2008, at 11:46 a.m.
      • Re: Query Regarding Temp Dbspace
        Art Kagel -- Friday, 8 August 2008, at 1:46 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.