Query Regarding Temp Dbspace

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.

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.

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

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.