IDS Forum

Re: Query Regarding Temp Dbspace

Posted By: Art Kagel
Date: Friday, 8 August 2008, at 10:28 a.m.

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

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

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

> Hello all,
>
> I Dont have a single table with No log, all my databases and table are
> buffered logged.
>
> I was always under the impression that, create temporary dbspace with
> option
> "-t" and make its entry against the onconfig parameter DBSPACETEMP.
>
> But then i was reading some very old threads and found
>
> --There are also two type of dbspaces. Those created with the '-t' option
> which can only contain unlogged tables and those created without this
> option
> which can only contain logged tables. The '-t' option doesn't really mean
> temporary, more it means unlogged.--
>
> also
>
> http://www-1.ibm.com/support/docview.wss?rs=630&context=SSGU8G&dc=DB520&dc=DB560&uid=swg21292093&loc=en_US&cs=UTF-8&lang=en&rss=ct630db2
>
> I have my temp dbspaces created with -t option and marked with T in onstat
> -d.
>
> Does it means that dbspace created with -t option are used only for no log
> tables
> Do I need to create the temp dbspace with out -t option and make its entry
> agains DBSPACETEMP in order to have a temporary dbspace for log tables.
>
> Pls guide or let me know a proper friendly manual to read from.
>
> Thanking you all in advance.
> Vikas.
>
>
>
> *******************************************************************************
> 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.