Informix 7.24 Temp Tables Not Dropping
Jeff ran IDS 7.24 on HP-UX 10.20: temp tables made with SELECT...INTO TEMP in a logged database landed in rootdbs rather than DBSPACETEMP unless created WITH NO LOG, and once there they were never dropped until the engine was restarted. Replies suggested checking (via onstat -g sql/-u) that sessions were really exiting and explicitly dropping temp tables in the application; one poster reported the same in 7.22, fixed in 7.30. Others clarified that WITH NO LOG is only needed when DBSPACETEMP holds true temporary dbspaces (the two types can be mixed), and that non-logged temp tables go to the database's home dbspace, so landing in rootdbs implies the database itself was created there. No definitive resolution is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi everyone. I have a system running IDS 7.24 on HP-UX 10.20. Some software is creating temporary tables using 'SELECT...INTO TEMP' and these are going into the rootdbs. The database is logged and tables are NOT created 'WITH NOLOG'. This in itself isn't the problem because these tables must be created WITH NOLOG to go to the temporary dbspaces (despite what manuals say!)rather than rootdbs when running in a logged database. The problem we have is that the tables are never dropped, except during initialisation when the engine is bought down. Does anyone else have experience of this or know why it may be happenning. Informix seem unaware of it and reckon the tables should get dropped once the application exits. Jeff McFee -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Are you sure the app that is creating the temp tables is really exiting?
Monitor that with onstat -g sql and onstat -u. I'll guess that those
sessions aren't really going away??? Another alternative is to have the
application programmers add code to explicitly drop the temp table when the
app no longer needs it...that's cleaner anyway.
--
Robert
jeffm@lysander.co.uk wrote in article <7cj90d$trc$1@nnrp1.dejanews.com>...
> Hi everyone.
>
> I have a system running IDS 7.24 on HP-UX 10.20. Some software is
creating
> temporary tables using 'SELECT...INTO TEMP' and these are going into the
> rootdbs. The database is logged and tables are NOT created 'WITH NOLOG'.
>
> This in itself isn't the problem because these tables must be created
WITH
> NOLOG to go to the temporary dbspaces (despite what manuals say!)rather
than
> rootdbs when running in a logged database. The problem we have is that
the
> tables are never dropped, except during initialisation when the engine is
> bought down. Does anyone else have experience of this or know why it may
be
> happenning. Informix seem unaware of it and reckon the tables should get
> dropped once the application exits.
>
> Jeff McFee
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>
In article <01be6f5a$d9f05a60$090b01cf@rgriffin>, "RG" <rgriffin@farmerstel.com> wrote: > > The problem we have is that > > the > > tables are never dropped, except during initialisation when the engine is > > bought down. Does anyone else have experience of this or know why it may > >be > > happenning. Informix seem unaware of it and reckon the tables should get > > dropped once the application exits. > > > > Jeff McFee > > We have the same problem in 7.22. Temp tables not dropped if your applications not correctly exited. Also , in case of HDR, temp tables on the secondary server not dropped even during Infx reboot. This corrected in 7.30. With best regards, Juri Dovgart, Bank's "Ukraine" System Administrator. -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
jeffm@lysander.co.uk wrote: > I have a system running IDS 7.24 on HP-UX 10.20. Some software is creating > temporary tables using 'SELECT...INTO TEMP' and these are going into the > rootdbs. The database is logged and tables are NOT created 'WITH NOLOG'. > > This in itself isn't the problem because these tables must be created WITH > NOLOG to go to the temporary dbspaces (despite what manuals say!)rather than > rootdbs when running in a logged database. I don't know what the manuals say (don't actually have any, anymore, and can't be bothered trying to open up the PDF files), but it's important to distinguish between temporary dbspaces and DBSPACETEMP. DBSPACETEMP does not have to be a temporary dbspace, and tables do not have to be created WITH NO LOG in order to go into DBSPACETEMP. If your DBSPACETEMP is a temporary dbspace, however, then you DO need to create the tables WITH NO LOG in order for them to be created in your DBSPACETEMP. This is a function of whether it is a temporary dbspace, not generic to DBSPACETEMP. June -- june_t@hotmail.com Still alive (barely), recently escaped from Harrisburg, PA Please do not send Informix questions to this account. I would add 'Please do not send spam to this account' but I suppose I would be wasting my bits.
June Tong wrote in message <7d74rd$hrt$2@news-1.news.gte.net>... [snip] >I don't know what the manuals say (don't actually have any, anymore, and can't >be bothered trying to open up the PDF files), but it's important to distinguish >between temporary dbspaces and DBSPACETEMP. DBSPACETEMP does not have to be a >temporary dbspace, and tables do not have to be created WITH NO LOG in order to >go into DBSPACETEMP. If your DBSPACETEMP is a temporary dbspace, however, then >you DO need to create the tables WITH NO LOG in order for them to be created in >your DBSPACETEMP. This is a function of whether it is a temporary dbspace, not >generic to DBSPACETEMP. > This touches on a question I asked a few days ago, can you mix temporary and normal dbspaces in DBSPACETEMP and will the engine then use them appropriately? --------------------------------------- Tony Flaherty aef@mfs.misys.co.uk Analyst Programmer Misys Financial Systems All statements and opinions are my own, Misys don't pay me enough to have opinions on their behalf .
Tony Flaherty wrote: > > June Tong wrote in message <7d74rd$hrt$2@news-1.news.gte.net>... > [snip] > >I don't know what the manuals say (don't actually have any, anymore, and > can't > >be bothered trying to open up the PDF files), but it's important to > distinguish > >between temporary dbspaces and DBSPACETEMP. DBSPACETEMP does not have to > be a > >temporary dbspace, and tables do not have to be created WITH NO LOG in > order to > >go into DBSPACETEMP. If your DBSPACETEMP is a temporary dbspace, however, > then > >you DO need to create the tables WITH NO LOG in order for them to be > created in > >your DBSPACETEMP. This is a function of whether it is a temporary dbspace, > not > >generic to DBSPACETEMP. > > > > This touches on a question I asked a few days ago, can you mix temporary > and normal dbspaces in DBSPACETEMP and will the engine then use them > appropriately? Yes. Art S. Kagel
In article <7d74rd$hrt$2@news-1.news.gte.net>, June Tong <june_t@hotmail.com> wrote: > jeffm@lysander.co.uk wrote: > > > I have a system running IDS 7.24 on HP-UX 10.20. Some software is creating > > temporary tables using 'SELECT...INTO TEMP' and these are going into the > > rootdbs. The database is logged and tables are NOT created 'WITH NOLOG'. > > > > This in itself isn't the problem because these tables must be created WITH > > NOLOG to go to the temporary dbspaces (despite what manuals say!)rather than > > rootdbs when running in a logged database. > > I don't know what the manuals say (don't actually have any, anymore, and can't > be bothered trying to open up the PDF files), but it's important to distinguish > between temporary dbspaces and DBSPACETEMP. DBSPACETEMP does not have to be a > temporary dbspace, and tables do not have to be created WITH NO LOG in order to > go into DBSPACETEMP. If your DBSPACETEMP is a temporary dbspace, however, then > you DO need to create the tables WITH NO LOG in order for them to be created in > your DBSPACETEMP. This is a function of whether it is a temporary dbspace, not > generic to DBSPACETEMP. > > June > -- > june_t@hotmail.com Unfortunately I do know what the manuals say and it doesn't add up to what happens. Unless you specify 'WITH NO LOG' temporary tables created whilst connected to a logged database go to 'rootdbs' and NOT the temporary dbspaces (whether listed in DBSPACETEMP or NOT). Only by specifying 'WITH NO LOG' can we get them into the temporary dbspaces. However, the real problem is that when created in 'rootdbs' they NEVER get dropped (whether application is existed or crashed) until the recovery stage if the engine is bounced. Thanks, Jeff -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Not true. If you list LOGGED dbspaces in DBSPACETEMP, temp tables WILL go there. Also, if you don't have any logged dbspaces listed, and you don't specify WITH NO LOG, then the temp tables go to the database's home dbspace (the dbspace in which the database was created, systables exists, etc.), not to the root dbspace. If a temp table goes to the rootdbs, you've created databases in rootdbs, which is a Very Bad Idea. jeffm@lysander.co.uk wrote in message <7dar3l$30i$1@nnrp1.dejanews.com>... >Unfortunately I do know what the manuals say and it doesn't add up to what >happens. Unless you specify 'WITH NO LOG' temporary tables created whilst >connected to a logged database go to 'rootdbs' and NOT the temporary dbspaces >(whether listed in DBSPACETEMP or NOT). Only by specifying 'WITH NO LOG' can >we get them into the temporary dbspaces. However, the real problem is that >when created in 'rootdbs' they NEVER get dropped (whether application is >existed or crashed) until the recovery stage if the engine is bounced. > >Thanks, > >Jeff > >-----------== Posted via Deja News, The Discussion Network ==---------- >http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Related threads
- Posting from the Informix-list
- Migrating from IDS 9.40.UC6 to 11.50.UC3
- Ip for a network session
- questions onstat -g