TempDB help needed

Hi, About a week ago, I created a new chunk on a separate disk from my main database and created a TempDB dbpaces (with Temp flag set) via onmonitor. It was my understanding that this space would automatically be used for all Informix generated Temp tables (ie - hash joins, indexes, etc...). However, the following 'onstat -D' shows no usage of that chunk. Is this normal? By the way, I didn't bounce the engine. Is that necessary for the TempDB to take effect? Thanks, Mike Hoffman P.s. -- Don't worry about the rootdbs space being hammered. All Logging is being done there.... for the time being. This will be fixed during the next DB bounce after we add more disk space. onstat -D INFORMIX-OnLine Version 7.23.UC7 -- On-Line -- Up 5 days 13:14:46 -- 43488 Kbytes Dbspaces address number flags fchunk nchunks flags owner name bade100 1 1 1 1 N informix rootdbs bade430 2 1 2 1 N informix qvdata bed2e20 3 2001 3 1 N T informix tempdb 3 active, 2047 maximum Chunks address chk/dbs offset page Rd page Wr pathname bade170 1 1 0 436 1534578 /qvdb/qvmaster bade280 2 2 0 532695 448966 /qvdb/qvdata bade358 3 3 0 0 0 /qvdb/qvdata0 3 active, 2047 maximum

You need to add the temp dbspace that you created in the DBSPACETEMP of the onconfig file and you need to restart after this. In article <8u9b08$bl1$1@news.panix.com>, Michael Hoffman <mrh@panix.com> wrote: > > Hi, > About a week ago, I created a new chunk on a separate disk from my > main database and created a TempDB dbpaces (with Temp flag set) via onmonitor. > It was my understanding that this space would automatically be used for all > Informix generated Temp tables (ie - hash joins, indexes, etc...). However, > the following 'onstat -D' shows no usage of that chunk. Is this normal? > By the way, I didn't bounce the engine. Is that necessary for the TempDB to > take effect? > > Thanks, > Mike Hoffman > P.s. -- Don't worry about the rootdbs space being hammered. All Logging is > being done there.... for the time being. This will be fixed during the next > DB bounce after we add more disk space. > > onstat -D> > INFORMIX-OnLine Version 7.23.UC7 -- On-Line -- Up 5 days 13:14:46 -- 43488 Kbytes > > Dbspaces > address number flags fchunk nchunks flags owner name > bade100 1 1 1 1 N informix rootdbs > bade430 2 1 2 1 N informix qvdata > bed2e20 3 2001 3 1 N T informix tempdb > 3 active, 2047 maximum > > Chunks > address chk/dbs offset page Rd page Wr pathname > bade170 1 1 0 436 1534578 /qvdb/qvmaster > bade280 2 2 0 532695 448966 /qvdb/qvdata > bade358 3 3 0 0 0 /qvdb/qvdata0 > 3 active, 2047 maximum > > Sent via Deja.com http://www.deja.com/ Before you buy.

Thanks. Did the onconfig modification, and checked it through onmonitor. Didn't realize that a restart was necessary. Funny how some things happen on the fly, but others need a bounce. In <8u9c8s$m2q$1@nnrp1.deja.com> article, Norman Erickson Lugtu mentioned that: : You need to add the temp dbspace that you created in the DBSPACETEMP of : the onconfig file and you need to restart after this. : In article <8u9b08$bl1$1@news.panix.com>, : Michael Hoffman <mrh@panix.com> wrote: :> :> Hi, :> About a week ago, I created a new chunk on a separate disk from : my :> main database and created a TempDB dbpaces (with Temp flag set) via : onmonitor. :> It was my understanding that this space would automatically be used : for all :> Informix generated Temp tables (ie - hash joins, indexes, etc...). : However, :> the following 'onstat -D' shows no usage of that chunk. Is this : normal? :> By the way, I didn't bounce the engine. Is that necessary for the : TempDB to :> take effect? :> :> Thanks, :> Mike Hoffman :> P.s. -- Don't worry about the rootdbs space being hammered. All : Logging is :> being done there.... for the time being. This will be fixed during : the next :> DB bounce after we add more disk space. :> :> onstat -D :> :> INFORMIX-OnLine Version 7.23.UC7 -- On-Line -- Up 5 days 13:14:46 -- : 43488 Kbytes :> :> Dbspaces :> address number flags fchunk nchunks flags owner name :> bade100 1 1 1 1 N informix rootdbs :> bade430 2 1 2 1 N informix qvdata :> bed2e20 3 2001 3 1 N T informix tempdb :> 3 active, 2047 maximum :> :> Chunks :> address chk/dbs offset page Rd page Wr pathname :> bade170 1 1 0 436 1534578 /qvdb/qvmaster :> bade280 2 2 0 532695 448966 /qvdb/qvdata :> bade358 3 3 0 0 0 /qvdb/qvdata0 :> 3 active, 2047 maximum :> :> : Sent via Deja.com http://www.deja.com/ : Before you buy.

sql statements will use the DBSPACETEMP area under certain scenerios: if the database has logging, you must use the "with no log" clause i.e select * from customer with no log if the database does not have logging, you don't have to use this clause. Michael Hoffman wrote in message <8u9b08$bl1$1@news.panix.com>... > >Hi, > About a week ago, I created a new chunk on a separate disk from my >main database and created a TempDB dbpaces (with Temp flag set) via onmonitor. >It was my understanding that this space would automatically be used for all >Informix generated Temp tables (ie - hash joins, indexes, etc...). However, >the following 'onstat -D' shows no usage of that chunk. Is this normal? >By the way, I didn't bounce the engine. Is that necessary for the TempDB to >take effect? > >Thanks, >Mike Hoffman >P.s. -- Don't worry about the rootdbs space being hammered. All Logging is >being done there.... for the time being. This will be fixed during the next >DB bounce after we add more disk space. > >onstat -D> >INFORMIX-OnLine Version 7.23.UC7 -- On-Line -- Up 5 days 13:14:46 -- 43488 Kbytes > >Dbspaces >address number flags fchunk nchunks flags owner name >bade100 1 1 1 1 N informix rootdbs >bade430 2 1 2 1 N informix qvdata >bed2e20 3 2001 3 1 N T informix tempdb > 3 active, 2047 maximum > >Chunks >address chk/dbs offset page Rd page Wr pathname >bade170 1 1 0 436 1534578 /qvdb/qvmaster >bade280 2 2 0 532695 448966 /qvdb/qvdata >bade358 3 3 0 0 0 /qvdb/qvdata0 > 3 active, 2047 maximum >

You can do it on the fly with the DBSPACETEMP environment variable in the users' environments. BTW T type dbspaces are ONLY used for non-logged temp tables, you may want to include a specific normal dbspace in DBSPACETEMP so the engine will place logged temp tables somewhere other than rootdbs. Art S. Kagel Michael Hoffman wrote: > > Thanks. Did the onconfig modification, and checked it through onmonitor. > Didn't realize that a restart was necessary. Funny how some things happen on > the fly, but others need a bounce. > > In <8u9c8s$m2q$1@nnrp1.deja.com> article, Norman Erickson Lugtu mentioned that: > : You need to add the temp dbspace that you created in the DBSPACETEMP of > : the onconfig file and you need to restart after this. > > : In article <8u9b08$bl1$1@news.panix.com>, > : Michael Hoffman <mrh@panix.com> wrote: > :> > :> Hi, > :> About a week ago, I created a new chunk on a separate disk from > : my > :> main database and created a TempDB dbpaces (with Temp flag set) via > : onmonitor. > :> It was my understanding that this space would automatically be used > : for all > :> Informix generated Temp tables (ie - hash joins, indexes, etc...). > : However, > :> the following 'onstat -D' shows no usage of that chunk. Is this > : normal? > :> By the way, I didn't bounce the engine. Is that necessary for the > : TempDB to > :> take effect? > :> > :> Thanks, > :> Mike Hoffman > :> P.s. -- Don't worry about the rootdbs space being hammered. All > : Logging is > :> being done there.... for the time being. This will be fixed during > : the next > :> DB bounce after we add more disk space. > :> > :> onstat -D > :> > :> INFORMIX-OnLine Version 7.23.UC7 -- On-Line -- Up 5 days 13:14:46 -- > : 43488 Kbytes > :> > :> Dbspaces > :> address number flags fchunk nchunks flags owner name > :> bade100 1 1 1 1 N informix rootdbs > :> bade430 2 1 2 1 N informix qvdata > :> bed2e20 3 2001 3 1 N T informix tempdb > :> 3 active, 2047 maximum > :> > :> Chunks > :> address chk/dbs offset page Rd page Wr pathname > :> bade170 1 1 0 436 1534578 /qvdb/qvmaster > :> bade280 2 2 0 532695 448966 /qvdb/qvdata > :> bade358 3 3 0 0 0 /qvdb/qvdata0 > :> 3 active, 2047 maximum > :> > :> > > : Sent via Deja.com http://www.deja.com/ > : Before you buy.