Can't drop dbspace

Posted in 2017

Summary

On IDS 11.70 (Linux), dropping a database that had DELIMIDENT upper-case table names appeared to succeed, but oncheck -pe showed leftover tables (IWTEMP* objects created by the old Informix Warehouse Feature) still occupying the dbspace, so the dbspace couldn't be dropped. Art Kagel suggested using oncheck -pe to identify the owning database and noted that logged temp tables would be cleaned up by restarting the instance; the poster confirmed they were not temp tables. Andreas Legner said there is no built-in way to empty a dbspace of such orphaned objects or drop a non-empty one, and recommended opening a PMR with a reproducible case. The poster agreed to try to reproduce it; no resolution is recorded in the thread.

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

Topics: Storage & Space Management

IVAN ZAVIS — Tue, 14 Mar 2017 12:21:29 — source: IIUG Forums & Mailing Lists

Informix: 11.70.FC4IE (test server) OS: Linux Dropping database which had some table name created in UPPER CASE (table is created using variable DELIMIDENT=Y) has executed without any errors. However, several tables of dropped database remained in dbspace, and now I can't drop this dbspace !?! How can I delete the table from dbspace that does not belong to any of databases ? oncheck -pe shows me that the dbspace is not empty, but really there are nomore database in which those tables belonged to ...

↪ replying to IVAN ZAVIS

Art Kagel — Tue, 14 Mar 2017 12:26:18 — source: IIUG Forums & Mailing Lists

The onstat -pe should tell you what the database is. If they are logged temp tables bouncing the instance should clean them up. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Mar 14, 2017 at 8:21 AM, IVAN ZAVIS <ivan.zavis@mi-system.co.rs> wrote: > Informix: 11.70.FC4IE (test server) > OS: Linux > > Dropping database which had some table name created in UPPER CASE (table is > created using variable DELIMIDENT=Y) has executed without any errors. > > However, several tables of dropped database remained in dbspace, and now I > can't drop this dbspace !?! > > How can I delete the table from dbspace that does not belong to any of > databases ? > > oncheck -pe shows me that the dbspace is not empty, but really there are no> more database in which those tables belonged to ... > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1146fd1c07fbde054aafefd4

↪ replying to Art Kagel

Lloyd S — Tue, 14 Mar 2017 12:33:15 — source: IIUG Forums & Mailing Lists

Art meant oncheck -peFrom: "Art Kagel" <art.kagel@gmail.com>Sent: Tue, 14 Mar 2017 17:57:22To: ids@iiug.orgSubject: Re: Can't drop dbspace [38749]The onstat -pe should tell you what the database is. If they are loggedtemp tables bouncing the instance should clean them up.ArtArt S. Kagel, President and Principal ConsultantASK Database Managementwww.askdbmgt.comBlog: http://informix-myview.blogspot.com/Disclaimer: Please keep in mind that my own opinions are my own opinionsand do not reflect on the IIUG, nor any other organization with which I amassociated either explicitly, implicitly, or by inference. Neither dothose opinions reflect those of other individuals affiliated with anyentity with which I am affiliated nor those of the entities themselves.On Tue, Mar 14, 2017 at 8:21 AM, IVAN ZAVIS <ivan.zavis@mi-system.co.rs>wrote:> Informix: 11.70.FC4IE (test server)> OS: Linux>> Dropping database which had some table name created in UPPER CAS E (table is> created using variable DELIMIDENT=Y) has executed without any errors.>> However, several tables of dropped database remained in dbspace, and now I> can't drop this dbspace !?!>> How can I delete the table from dbspace that does not belong to any of> databases ?>> oncheck -pe shows me that the dbspace is not empty, but really there are no> more database in which those tables belonged to ...>>> ************************************************************> *******************> Forum Note: Use "Reply" to post a response in the discussion forum.>>--001a1146fd1c07fbde054aafefd4************************************ ******************************************* Forum Note: Use "Reply" to post a response in the discussion forum. 

↪ replying to Lloyd S

Ivan Zavis — Tue, 14 Mar 2017 12:38:18 — source: IIUG Forums & Mailing Lists

OK, I also mean: oncheck -pe On 14.03.2017 13:33, Lloyd S wrote: > Art meant oncheck -peFrom: "Art Kagel" > <art.kagel@gmail.com>Sent: Tue, 14 Mar 2017 17:57:22To: > ids@iiug.orgSubject: Re: Can't drop dbspace [38749]The onstat -pe should > tell you what the database is. If they are loggedtemp tables bouncing the > instance should clean them up.ArtArt S. Kagel, President and Principal > ConsultantASK Database Managementwww.askdbmgt.comBlog: > http://informix-myview.blogspot.com/Disclaimer: Please keep in mind that my > own opinions are my own opinionsand do not reflect on the IIUG, nor any other > organization with which I amassociated either explicitly, implicitly, or by > inference. Neither dothose opinions reflect those of other individuals > affiliated with anyentity with which I am affiliated nor those of the entities > themselves.On Tue, Mar 14, 2017 at 8:21 AM, IVAN ZAVIS > <ivan.zavis@mi-system.co.rs>wrote:> Informix: 11.70.FC4IE (test > server)> OS: Linux>> Dropping database which had some table name > created in UPPER CAS > E (table is> created using variable DELIMIDENT=Y) has executed without any > errors.>> However, several tables of dropped database remained in > dbspace, and now I> can't drop this dbspace !?!>> How can I > delete the table from dbspace that does not belong to any of> databases > ?>> oncheck -pe shows me that the dbspace is not empty, but really there > are no> more database in which those tables belonged to ...>>> > ************************************************************> > *******************> Forum Note: Use "Reply" to post a response > in the discussion > forum.>>--001a1146fd1c07fbde054aafefd4************************************ ******************************************* Forum > Note: Use "Reply" to post a response in the discussion forum.  > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > -- *Ivan Zavi* System & database administrator *T* +381 21 68 98 608 | *M* +381 69 846 99 08 *@*ivan.zavis@mi-system.co.rs <mailto:ivan.zavis@mi-system.co.rs> *M&I Systems, Co. Group* Bulevar vojvode Stepe 16, 21000 Novi Sad *T:* +381 21 68 98 602 *F:* +381 21 68 98 604 *@:* info@mi-system.co.rs *w:* www.mi-system.co.rs <http://www.facebook.com/pages/MI-Systems-Co/263409380366499>; <http://www.linkedin.com/company/m&i-systems-co.>; <http://www.youtube.com/misystemsco>; Odricanje od odgovornosti: Ovaj dokument namenjen je samo licima kojima je upucen i za pozivanje na isti od stane bilo kog lica, neophodna je naknadna pismena potvrda njegovog sadraja. Shodno tome, M&I Systems, Co. Novi Sad odrice svaku odgovornost i ne prihvata bilo kakvu obavezu (ukljucujuci slucaj nepanje) za posledice koje moe pretrpeti bilo koje lice zbog cinjenja ili necinjenja na bazi takve informacije pre nego to takva lica prime dodatnu pismenu potvrdu. Ukoliko ste grekom primili ovu elektronsku poruku, unitite ili izbriite istu sa vaeg racunara. Svako umnoavanje, irenje, kopiranje, obelodanjivanje, izmene, distribucija i/ili objavljivanje ove elektronske poruke je strogo zabranjeno. Sadraj ove elektronske poruke ne predstavlja nuno stavove M&I Systems, Co. Novi Sad

↪ replying to Ivan Zavis

Art Kagel — Tue, 14 Mar 2017 12:41:53 — source: IIUG Forums & Mailing Lists

B^) Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Mar 14, 2017 at 8:38 AM, Ivan Zavis <ivan.zavis@mi-system.co.rs> wrote: > OK, I also mean: > > oncheck -pe> > On 14.03.2017 13:33, Lloyd S wrote: > > Art meant oncheck -peFrom: "Art Kagel" > > <art.kagel@gmail.com>Sent: Tue, 14 Mar 2017 17:57:22To: > > ids@iiug.orgSubject: Re: Can't drop dbspace [38749]The onstat -pe > should > > tell you what the database is. If they are loggedtemp tables bouncing the > > instance should clean them up.ArtArt S. Kagel, President and Principal > > ConsultantASK Database Managementwww.askdbmgt.comBlog: > > http://informix-myview.blogspot.com/Disclaimer: Please keep in mind > that my > > own opinions are my own opinionsand do not reflect on the IIUG, nor any > other > > organization with which I amassociated either explicitly, implicitly, or > by > > inference. Neither dothose opinions reflect those of other individuals > > affiliated with anyentity with which I am affiliated nor those of the > entities > > themselves.On Tue, Mar 14, 2017 at 8:21 AM, IVAN ZAVIS > > <ivan.zavis@mi-system.co.rs>wrote:> Informix: 11.70.FC4IE (test > > server)> OS: Linux>> Dropping database which had some table name > > created in UPPER CAS > > E (table is> created using variable DELIMIDENT=Y) has executed without > any > > errors.>> However, several tables of dropped database remained in > > dbspace, and now I> can't drop this dbspace !?!>> How can I > > delete the table from dbspace that does not belong to any of> > databases > > ?>> oncheck -pe shows me that the dbspace is not empty, but really > there > > are no> more database in which those tables belonged to > ...>>> > > ************************************************************> > > *******************> Forum Note: Use "Reply" to post a > response > > in the discussion > > > forum.>>--001a1146fd1c07fbde054aafefd4** > ************************************************************ > ***************** Forum > > Note: Use "Reply" to post a response in the discussion > forum.  > > > > > > > ************************************************************ > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > -- > *Ivan Zavi* > System & database administrator > *T* +381 21 68 98 608 | *M* +381 69 846 99 08 > *@*ivan.zavis@mi-system.co.rs <mailto:ivan.zavis@mi-system.co.rs> > > *M&I Systems, Co. Group* > Bulevar vojvode Stepe 16, 21000 Novi Sad > *T:* +381 21 68 98 602 > *F:* +381 21 68 98 604 > *@:* info@mi-system.co.rs > *w:* www.mi-system.co.rs > > <http://www.facebook.com/pages/MI-Systems-Co/263409380366499>; > <http://www.linkedin.com/company/m&i-systems-co.>; > <http://www.youtube.com/misystemsco>; > Odricanje od odgovornosti: > Ovaj dokument namenjen je samo licima kojima je upucen i za pozivanje na > isti od stane bilo kog lica, neophodna je naknadna pismena potvrda > njegovog sadraja. Shodno tome, M&I Systems, Co. Novi Sad odrice svaku > odgovornost i ne prihvata bilo kakvu obavezu (ukljucujuci slucaj > nepanje) za posledice koje moe pretrpeti bilo koje lice zbog cinjenja > ili necinjenja na bazi takve informacije pre nego to takva lica prime > dodatnu pismenu potvrdu. Ukoliko ste grekom primili ovu elektronsku > poruku, unitite ili izbriite istu sa vaeg racunara. Svako > umnoavanje, irenje, kopiranje, obelodanjivanje, izmene, distribucija > i/ili objavljivanje ove elektronske poruke je strogo zabranjeno. Sadraj > ove elektronske poruke ne predstavlja nuno stavove M&I Systems, Co. > Novi Sad > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11442284fb09a7054ab02236

↪ replying to IVAN ZAVIS

Andreas Legner — Tue, 14 Mar 2017 12:55:49 — source: IIUG Forums & Mailing Lists

I'm afraid there's no built-in way for getting rid of a non-empty=20 dbspace/chunk, and none for emptying a dbspace from such orphaned objects. How about a PMR for this in case this warrants the endeavor? How about a reproduction in exchange for a patch? ;-) Can you post / send the 'oncheck -pe' output? From: "IVAN ZAVIS" <ivan.zavis@mi-system.co.rs> To: ids@iiug.org Date: 14.03.2017 13:22 Subject: Can't drop dbspace [38748] Sent by: ids-bounces@iiug.org Informix: 11.70.FC4IE (test server)=20 OS: Linux=20 Dropping database which had some table name created in UPPER CASE (table=20 is=20 created using variable DELIMIDENT=3DY) has executed without any errors.=20 However, several tables of dropped database remained in dbspace, and now I = can't drop this dbspace !?!=20 How can I delete the table from dbspace that does not belong to any of=20 databases ?=20 oncheck -pe shows me that the dbspace is not empty, but really there are=20 no=20more database in which those tables belonged to ...=20 ***************************************************************************= ****=20 Forum Note: Use "Reply" to post a response in the discussion forum.=20

↪ replying to Andreas Legner

IVAN ZAVIS — Tue, 14 Mar 2017 13:16:11 — source: IIUG Forums & Mailing Lists

I will try reproduce the situation from beginning, and if I success, i will open a PMR ... Here is the output from oncheck -pe BTW: Database mi_wms are dropped successfully, and these table are NOT TEMP table ! Table was created by (now unsupported) tool from IBM, called: "Informix Warehouse Feature" https://www.ibm.com/developerworks/data/tutorials/dm-0904warehouse1/ DBspace Usage Report: bazadbs Owner: informix Created: 05/11/2015 Chunk Pathname Pagesize(k) Size(p) Used(p) Free(p) 3 /opt/informix/dbs/bazadbs1 8 2500000 17859 2482141 Description Offset(p) Size(p) ------------------------------ -------- -------- RESERVED PAGES 0 2 CHUNK FREELIST PAGE 2 1 bazadbs:'informix'.TBLSpace 3 16000 FREE 16003 342316 mi_wms:'iwfadmin'.IWTEMP739502 358319 4 mi_wms:'iwfadmin'.IWTEMP699502 358323 4 mi_wms:'iwfadmin'.IWTEMP819502 358327 4 FREE 358331 256 mi_wms:'iwfadmin'.IWTEMP859502 358587 4 FREE 358591 5031 mi_wms:'iwfadmin'.IWTEMP1684802 363622 4 FREE 363626 2032 mi_wms:'iwfadmin'.IWTEMP18413876 365658 1576 FREE 367234 821 mi_wms:'iwfadmin'.IWTEMP0894 368055 4 FREE 368059 1370440 mi_wms:'iwfadmin'.IWTEMP18413876 1738499 256 FREE 1738755 761245

Related threads

Glossary

dbspace