monitoring temp space

Hi I was wondering about how to monitor temp space. I received an error this morning that the database was out of temp space so I added another chunk. However before I added the chunk I checked the temp space through onmonitor it appeared to be fine and looking directly at the disk it also appeared to be fine. Is there away to monitor what is in this space other than using oncheck -pe. And is there a way to clear out this space - in case processes have left information in here. thanks Bridget

Bridget Reitsma wrote: > > Hi > > I was wondering about how to monitor temp space. I received an error > this morning that the database was out of temp space so I added another > chunk. However before I added the chunk I checked the temp space > through onmonitor it appeared to be fine and looking directly at the > disk it also appeared to be fine. > > Is there away to monitor what is in this space other than using oncheck > -pe. And is there a way to clear out this space - in case processes > have left information in here. > > thanks Bridget Hi Bridget, the problem concerning temp space is when a process allocates but can't get any more temp space, the process will fail and normally terminate. The temp space will be freed immediately when the process terminates. However, if you just react to the information you will be always too late. The best way to monitor the temp space is: Create separate dbspaces for temporary data. Use regular dbspaces and temporary dbspaces and enter the dbspace names into the DBSPACETEMP configuration parameter. Monitor the current temp space needed by "onstat -d" or by using the SM-Interface. Keep in mind that on most systems you have a crontab which can run the monitoring process for you. Best regards, -- Stefan Weideneder Phone: +49 89/3565478-2 --------------- --- Fax: +49 89/3565478-3 ------------- ------ mailto:/stefan@weideneder.de --- -------- http://www.weideneder.de -----

In article <38AB4480.71750E17@auckland.ac.nz>, Bridget Reitsma <b.reitsma@auckland.ac.nz> wrote: > Hi > > I was wondering about how to monitor temp space. I received an error > this morning that the database was out of temp space so I added > another chunk. However before I added the chunk I checked the temp > space through onmonitor it appeared to be fine and looking directly > at the disk it also appeared to be fine. > > Is there away to monitor what is in this space other than using > oncheck -pe. And is there a way to clear out this space - in case> processes have left information in here. > > thanks Bridget Bridget, Sounds like you shoulda not gotten outa bed today. We all have such days. ;-) OK, I know of two packages in the IIUG archives that can monitor temp space, depending on exactly what you want to know. 1. monitor-space: This is available at URL: http://www.iiug.org/members/memb_software/archive/monitor_space The heart of the package is a shell/awk script called dbspace-pages.sh, which produces output like: DB-Space |DBS-Num|NumChunks|TotPages|FreePages|%-Full| ... tmp_dbs1 | 4| 1| 256000| 255267| 0.29| tmp_dbs2 | 5| 1| 256000| 255203| 0.31| ... So while some questionable transaction is running in one window you can repeatedly enter the above command (pipe it through grep if you like) and watch the dwindling space as it is happening. If you get this, you can read the document that comes with it, wherein I describe how to monitor dbspaces with a cron job. You an adapt it to your needs. 2. partitions The shell/SQL/awk script partitions.sh is capable of monitoring tblspaces in a specified dbspace. It is available at URL: http://www.iiug.org/members/memb_software/archive/partitions For your needs, the command would be: partitions.sh -D tmp_dbs1,tmp_dbs2.. it would produce output like: |TableName |FT|Partition |DBspace |NRows|Xtns|NPused|NPtotl|IndexName| .... |imm:t_data1 |t |0x004000A9|tmp_dbs1| 0| 1| 0| 8|(temp) | |imm:t_data1 |t |0x00500067|tmp_dbs2| 0| 1| 0| 8|(temp) | .... |imm:tt_buyer|t |0x004000C1|tmp_dbs1| 1| 1| 2| 8|(temp) | |imm:tt_buyer|t |0x004000C9|tmp_dbs1| 0| 1| 0| 8|(temp) | |imm:tt_buyer|t |0x005000A5|tmp_dbs2| 1| 1| 2| 8|(temp) | |imm:tt_buyer|t |0x005000C4|tmp_dbs2| 0| 1| 0| 8|(temp) | If you then decide that tt_buyer is the temp table to keep an eye on, you can type: $ partitions.sh -t tt_buyer Go get'em! -- Jake (Pleads guilty to crime of tooting own horn a bit.) +----- Jacob Salomon - DBA JSalomon@bn.com - --------------------------+ |------------------- Bulletin Board Announcement ----------------------| | Congregants will please note that the bowl at the back of the church | | bearing the sign "For the Sick" is for monetary contributions only. | +----------------------------------------------------------------------+ Sent via Deja.com http://www.deja.com/ Before you buy.