 | |
Drop table from temp dbspace
comp.databases.informix comp.databases.informix
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Drop table from temp dbspace -
02-18-2010
, 04:08 AM
#2
| |||
| |||
|
|
Hello, Have a situation were one temporary table is not getting dropped automatically from the temp dbspace. Table name is start with _ (underscore). Table name is _temptable. Number of extents for this table is above 500 and our monitoring tool is throwing alerts. Some application is doing this. Killing the session didn’t work. And also restarted the application didn’t solve the issue. I know bouncing the Informix server will solve the issue. Is there any other solution apart from bouncing? Or bouncing is the only solution. Informix version: Informix Dynamic Server 2000 Version 9.21.H OS: HP-UX 11.X Output from oncheck: Chunk Pathname * * * * * * * * * * * * * * * * * * * * Size Used * * *Free * * *4 /tempchunk1 * * * * * * * * * * * * * * * * * 1000000 14545 * *985455 *Description Offset * * Size *------------------------------------------------------------- -------- -------- *RESERVED PAGES 0 * * * *2 *CHUNK FREELIST PAGE 2 * * * *1 *(0x400001) temp1dbs:'informix'.TBLSpace 3 * * * 50 *(0x400042) xxx:'aa'._temptable 53 * * * *8 *(0x400003) xxx:'aa'._temptable 61 * * * *8 ………… Thanks & Regards, Jose |
#3
| |||
| |||
|
|
Hello Jose, are you sure that it is a temp table?? if you gointo dbaccess and choose the database xxx(if possible...) is there a table present that is called _temptable??? if that is the case you can use sql to fix the issue. also if that is the case then temp1dbs is not a real temp dbspace, when it was created the t flag was forgotten. Superboer On 18 feb, 11:08, Jose <joseph... (AT) gmail (DOT) com> wrote: Hello, Have a situation were one temporary table is not getting dropped automatically from the temp dbspace. Table name is start with _ (underscore). Table name is _temptable. Number of extents for this table is above 500 and our monitoring tool is throwing alerts. Some application is doing this. Killing the session didn’t work. And also restarted the application didn’t solve the issue. I know bouncing the Informix server will solve the issue. Is there any other solution apart from bouncing? Or bouncing is the only solution. Informix version: Informix Dynamic Server 2000 Version 9.21.H OS: HP-UX 11.X Output from oncheck: Chunk Pathname * * * * * * * * * * * * * * * * * * * * Size Used * * *Free * * *4 /tempchunk1 * * * * * * * * * * * * * * * * * 1000000 14545 * *985455 *Description Offset * * Size *------------------------------------------------------------- -------- -------- *RESERVED PAGES 0 * * * *2 *CHUNK FREELIST PAGE 2 * * * *1 *(0x400001) temp1dbs:'informix'.TBLSpace 3 * * * 50 *(0x400042) xxx:'aa'._temptable 53 * * * *8 *(0x400003) xxx:'aa'._temptable 61 * * * *8 ………… Thanks & Regards, Jose |
#4
| |||
| |||
|
|
Ooops i was too trigger happy ignore the prev. posting please Superboer On 18 feb, 14:04, Superboer <superbo... (AT) t-online (DOT) de> wrote: Hello Jose, are you sure that it is a temp table?? if you gointo dbaccess and choose the database xxx(if possible...) is there a table present that is called _temptable??? if that is the case you can use sql to fix the issue. also if that is the case then temp1dbs is not a real temp dbspace, when it was created the t flag was forgotten. Superboer On 18 feb, 11:08, Jose <joseph... (AT) gmail (DOT) com> wrote: Hello, Have a situation were one temporary table is not getting dropped automatically from the temp dbspace. Table name is start with _ (underscore). Table name is _temptable. Number of extents for this table is above 500 and our monitoring tool is throwing alerts. Some application is doing this. Killing the session didn’t work. And also restarted the application didn’t solve the issue. I know bouncing the Informix server will solve the issue. Is there any other solution apart from bouncing? Or bouncing is the only solution. Informix version: Informix Dynamic Server 2000 Version 9.21.H OS: HP-UX 11.X Output from oncheck: Chunk Pathname * * * * * * * * * * * * * * * * * * * * Size Used * * *Free * * *4 /tempchunk1 * * * * * * * * * * * * * * * * * 1000000 14545 * *985455 *Description Offset * * Size *------------------------------------------------------------- -------- -------- *RESERVED PAGES 0 * * * *2 *CHUNK FREELIST PAGE 2 * * * *1 *(0x400001) temp1dbs:'informix'.TBLSpace 3 * * * 50 *(0x400042) xxx:'aa'._temptable 53 * * * *8 *(0x400003) xxx:'aa'._temptable 61 * * * *8 ………… Thanks & Regards, Jose- Hide quoted text - - Show quoted text - |
I can't query / choose the table through dbaccess.
Thanks.
Jose.
#5
| |||
| |||
|
|
Hello, Have a situation were one temporary table is not getting dropped automatically from the temp dbspace. Table name is start with _ (underscore). Table name is _temptable. Number of extents for this table is above 500 and our monitoring tool is throwing alerts. Some application is doing this. Killing the session didn’t work. And also restarted the application didn’t solve the issue. I know bouncing the Informix server will solve the issue. Is there any other solution apart from bouncing? Or bouncing is the only solution. Informix version: Informix Dynamic Server 2000 Version 9.21.H OS: HP-UX 11.X Output from oncheck: Chunk Pathname * * * * * * * * * * * * * * * * * * * * Size Used * * *Free * * *4 /tempchunk1 * * * * * * * * * * * * * * * * * 1000000 14545 * *985455 *Description Offset * * Size *------------------------------------------------------------- -------- -------- *RESERVED PAGES 0 * * * *2 *CHUNK FREELIST PAGE 2 * * * *1 *(0x400001) temp1dbs:'informix'.TBLSpace 3 * * * 50 *(0x400042) xxx:'aa'._temptable 53 * * * *8 *(0x400003) xxx:'aa'._temptable 61 * * * *8 ………… Thanks & Regards, Jose |
Well, you could try and run onstat -g opn and see if you can find a
thread that has the partnum that matches reported as open. If you can
find a thread with that partition open, you could try and get it to
exit. However, it seems like I recall some old defects where temp
space may not have been properly cleaned up in all cases when a
session exited. If that is the problem you are running into, then it
is very likely that the only way to resolve the issue is the bounce of
the server, which re-initializes the temp dbspaces when the engine is
brought on-line.
Jacques Renaut
IBM Informix Advanced Support
APD team
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.