How to drop temp tables ....?

Posted in 2003

Summary

Fatima asked how to get rid of temp tables left behind in rootdbs by applications when the creating session/owner is gone. Replies noted temp tables normally vanish when the session ends, and that leftovers are cleaned up when the engine is restarted (though starting with oninit -p skips that cleanup); past bugs affected only engine-created implicit temp tables. Others suspected they were really permanent "temp" tables (as with PeopleSoft) needing a nightly drop/recreate script, and advised creating a dedicated temp dbspace and setting DBSPACETEMP/DBSTEMP so temp tables avoid rootdbs. No confirmation of what fixed Fatima's case is recorded.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: Storage & Space Management, Jobs, Consulting & Announcements

Fatima Caleya — Wed, 05 Feb 2003 18:01:12 — source: IIUG Forums & Mailing Lists

Hi everybody! I hope you can help me on this. We have several applications which leave temp tables on the rootdbs and need to drop them . Is there a way to do that, even when the owner is gone? Thanks for your help in advance. Fatima. -- Fatima Caleya -- IBM Consultant -- fcaleya@briti.sh <a href="http://www.universalpostoffice.com"><img src="http://www.universalpostoffice.com/mail/imgs/upo-small.gif"; width="76" height="55"></a>

↪ replying to Fatima Caleya

John Carlson — Wed, 05 Feb 2003 19:20:36 — source: IIUG Forums & Mailing Lists

That's strange . . . .the temp tables should go away when the session stops. BTW, it's usually best to create temp tables in temp dbspaces, not rootdbs. > -----Original Message----- > From: Fatima Caleya [mailto:fcaleya@briti.sh] > Sent: Wednesday, February 05, 2003 1:01 PM > To: ids@iiug.org > Subject: How to drop temp tables ....? [250] > > > Hi everybody! > > I hope you can help me on this. > > We have several applications which leave temp tables on the > rootdbs and need to > drop them . > > Is there a way to do that, even when the owner is gone? > > Thanks for your help in advance. > > Fatima. > > -- Fatima Caleya > -- IBM Consultant > -- fcaleya@briti.sh > > > > <a href="http://www.universalpostoffice.com"><img > src="http://www.universalpostoffice.com/mail/imgs/upo-small.gi > f" width="76" height="55"></a> > "CONFIDENTIALITY NOTICE: This message originates from WHSmith USA Travel Retail. This email message and all attachments may contain legally privileged and confidential information intended solely for the use of the addressee. If you are not the intended recipient, you should immediately stop reading this message and delete it from the system. Any unauthorized reading, distribution, copying, or other use of this message or its attachments is strictly prohibited. All personal messages express solely the sender's views and not those of WHSmith USA Travel Retail. This message may not be copied or distributed without this disclaimer."

↪ replying to Fatima Caleya

007 — Wed, 05 Feb 2003 19:32:42 — source: IIUG Forums & Mailing Lists

> You shouldn't have to worry about dropping temp tables. Temp tables dropped automatically when a session ended. If you have to drop the temp tables while > the session still active, you may drop the tables just like you would do to regular tables. I would recommend that temp tables be created in a temp dbspace for performance and good practice. You just create a temp dbspace, and create temp tables with "with no log" at the end of the create table statement. James > > We have several applications which leave temp tables on the rootdbs and need to > drop them . > > Is there a way to do that, even when the owner is gone? > > Thanks for your help in advance. > > Fatima. >

↪ replying to Fatima Caleya

Rajib Sarkar — Wed, 05 Feb 2003 21:25:33 — source: IIUG Forums & Mailing Lists

That's pretty strange ... usually, if the sessions go away the temp tables should go away too ... anyway, we distinguish the temp tables in the bitmap page of the tablespace tablespace for the dbspace and if you bounce the engine, it can detect it and remove it (will provide messages in the online log regarding the dropping of the temp table) but if you startup the instance using oninit -p then it won't drop it while booting. I am thinking possibly the engine was started using the oninit -p flag causing the temp tables to be remain in the rootdbspace ... (but it maybe a longshot ..:-) ) There were a couple of bugs where the session won't free up the tempspace even after the session has gone away ..but they were for "implicit" temp tables created by the engine not for user-created temp tables ... I guess I haven't provided any solution to the problem but just some suggestions to look at .... HTH Thanx much, Rajib Sarkar Advisory Support Engineer (Wells Fargo Bank) IBM Data Management Group Ph : (602)-217-2100 Fax: (602)-217-2100 As long as you derive inner help and comfort from anything, keep it -- Mahatma Gandhi "Fatima Caleya " <fcaleya@briti.sh To: ids@iiug.org > cc: Sent by: Subject: How to drop temp tables ....? [250] forum.subscriber@ iiug.org 02/05/2003 11:01 AM Hi everybody! I hope you can help me on this. We have several applications which leave temp tables on the rootdbs and need to drop them . Is there a way to do that, even when the owner is gone? Thanks for your help in advance. Fatima. -- Fatima Caleya -- IBM Consultant -- fcaleya@briti.sh <a href="http://www.universalpostoffice.com"><img src=" http://www.universalpostoffice.com/mail/imgs/upo-small.gif"; width="76" height="55"></a>

↪ replying to Rajib Sarkar

Phillips, Ken — Wed, 05 Feb 2003 22:51:26 — source: IIUG Forums & Mailing Lists

By the sound of it. I would assume these are some sort of permenant Temp tables, I've seen this with our peoplesoft applications. I had to right a script that would drop and recreate all the temp tables nightly. Note: that if you have tuned the process you will want to run update stats on the temp tables when they are full. and not re-run them everynight when you drop and recreate them. Thats what we did and it kept our batch and onlines running within our limited SLA windows.. good luck.. Ken -----Original Message----- From: Rajib Sarkar [mailto:rsarkar@us.ibm.com] Sent: Wednesday, February 05, 2003 3:26 PM To: ids@iiug.org Subject: Re: How to drop temp tables ....? [254] That's pretty strange ... usually, if the sessions go away the temp tables should go away too ... anyway, we distinguish the temp tables in the bitmap page of the tablespace tablespace for the dbspace and if you bounce the engine, it can detect it and remove it (will provide messages in the online log regarding the dropping of the temp table) but if you startup the instance using oninit -p then it won't drop it while booting. I am thinking possibly the engine was started using the oninit -p flag causing the temp tables to be remain in the rootdbspace ... (but it maybe a longshot ..:-) ) There were a couple of bugs where the session won't free up the tempspace even after the session has gone away ..but they were for "implicit" temp tables created by the engine not for user-created temp tables ... I guess I haven't provided any solution to the problem but just some suggestions to look at .... HTH Thanx much, Rajib Sarkar Advisory Support Engineer (Wells Fargo Bank) IBM Data Management Group Ph : (602)-217-2100 Fax: (602)-217-2100 As long as you derive inner help and comfort from anything, keep it -- Mahatma Gandhi "Fatima Caleya " <fcaleya@briti.sh To: ids@iiug.org > cc: Sent by: Subject: How to drop temp tables ....? [250] forum.subscriber@ iiug.org 02/05/2003 11:01 AM Hi everybody! I hope you can help me on this. We have several applications which leave temp tables on the rootdbs and need to drop them . Is there a way to do that, even when the owner is gone? Thanks for your help in advance. Fatima. -- Fatima Caleya -- IBM Consultant -- fcaleya@briti.sh <a href="http://www.universalpostoffice.com"><img src=" http://www.universalpostoffice.com/mail/imgs/upo-small.gif"; width="76" height="55"></a>

↪ replying to Phillips, Ken

Juan Manuel Naranjo — Thu, 06 Feb 2003 13:49:38 — source: IIUG Forums & Mailing Lists

In order to avoid the creation of TEMP tables in ROOT DBS do the following. - Create an Ad-Hoc dbspace ( ie - dbstemp ) for the temp tables WITHOUT the T flag - In $ONCONFIG in the DBSTEMP add the name of the dbspace - Bounce IDS Cordialmente: Juan Manuel Naranjo Arango Administrador Infraestructura BASIS Productora de Papeles S.A. ( PROPAL) Tel: (572) 6512 411 Fax: (572) 6694 345 -----Mensaje original----- De: Phillips, Ken [mailto:ken.phillips@transamerica.com] Enviado el: miércoles, 05 de febrero de 2003 17:51 Para: ids@iiug.org Asunto: RE: How to drop temp tables ....? [256] By the sound of it. I would assume these are some sort of permenant Temp tables, I've seen this with our peoplesoft applications. I had to right a script that would drop and recreate all the temp tables nightly. Note: that if you have tuned the process you will want to run update stats on the temp tables when they are full. and not re-run them everynight when you drop and recreate them. Thats what we did and it kept our batch and onlines running within our limited SLA windows.. good luck.. Ken -----Original Message----- From: Rajib Sarkar [mailto:rsarkar@us.ibm.com] Sent: Wednesday, February 05, 2003 3:26 PM To: ids@iiug.org Subject: Re: How to drop temp tables ....? [254] That's pretty strange ... usually, if the sessions go away the temp tables should go away too ... anyway, we distinguish the temp tables in the bitmap page of the tablespace tablespace for the dbspace and if you bounce the engine, it can detect it and remove it (will provide messages in the online log regarding the dropping of the temp table) but if you startup the instance using oninit -p then it won't drop it while booting. I am thinking possibly the engine was started using the oninit -p flag causing the temp tables to be remain in the rootdbspace ... (but it maybe a longshot ..:-) ) There were a couple of bugs where the session won't free up the tempspace even after the session has gone away ..but they were for "implicit" temp tables created by the engine not for user-created temp tables ... I guess I haven't provided any solution to the problem but just some suggestions to look at .... HTH Thanx much, Rajib Sarkar Advisory Support Engineer (Wells Fargo Bank) IBM Data Management Group Ph : (602)-217-2100 Fax: (602)-217-2100 As long as you derive inner help and comfort from anything, keep it -- Mahatma Gandhi "Fatima Caleya " <fcaleya@briti.sh To: ids@iiug.org > cc: Sent by: Subject: How to drop temp tables ....? [250] forum.subscriber@ iiug.org 02/05/2003 11:01 AM Hi everybody! I hope you can help me on this. We have several applications which leave temp tables on the rootdbs and need to drop them . Is there a way to do that, even when the owner is gone? Thanks for your help in advance. Fatima. -- Fatima Caleya -- IBM Consultant -- fcaleya@briti.sh <a href="http://www.universalpostoffice.com"><img src=" http://www.universalpostoffice.com/mail/imgs/upo-small.gif"; width="76" height="55"></a>

Related threads

How to get the sequence value in ESQL/C program?