App unable to use deleted rows
9.30.UC5 on Solaris 8 A C++ application has it's own idx and dbs spaces and is unable to reclaim/reuse space from deleted rows. This causes the spaces to fill over time. The programmer says that this occurs because 'update statistics' must be run periodically on the database. He is building 'update statistics' into his application so space will be reclaimed in the dbspaces. I've never seen a database behave this way. I looked at my docs and 'Informix IDS Unlocking the Mysteries Behind Update Statistics' John F. Miller III, and found no mention of 'update statistics' in this context. I also find no info about reclaiming space being a problem in any context. Has anyone seen this problem? What kinds of things should I look for in the code? Thanks, John
John, It's been a while, but I think that my experience is still valid. In Informix (and most other RDBMS platforms), space is allocated to tables in contiguous pages called extents. The trick is that once an extent is allocated to a table, that extent remains allocated to the table, even if through a delete operation, there is no more data present in the extent. This doesn't mean that the space is unusable; it means that the space is pre-allocated for that particular table (or index, or other structure). Typically, you run into problems with this kind of thing if your developers are creating real (not temporary) tables, populating them with lots of transient data (for example, a data load from an ETL operation), and then deleting that data. This is all well and good if the same table is used over and over again, but if each operation creates a separate table, all of the extents allocated to the previous table remain allocated, and thus consume the space in your dbspace. If this kind of scenario is NOT what's happening with your application, let me know what's going on with the app, and I'll see if I can take another guess. Thanks. Dan Michaelis Database Administrator/Developer eOriginal 351 West Camden Street Suite 800 Baltimore, MD 21201 410.625.5187 (phone) 410.659.9799 (fax) -----Original Message----- From: JOHN WHITTE.... [mailto:john.whittenberger@metnet.navy.mil] Sent: Tuesday, August 09, 2005 11:28 AM To: ids@iiug.org Subject: App unable to use deleted rows [5587] 9.30.UC5 on Solaris 8 A C++ application has it's own idx and dbs spaces and is unable to reclaim/reuse space from deleted rows. This causes the spaces to fill over time. The programmer says that this occurs because 'update statistics' must be run periodically on the database. He is building 'update statistics' into his application so space will be reclaimed in the dbspaces. I've never seen a database behave this way. I looked at my docs and 'Informix IDS Unlocking the Mysteries Behind Update Statistics' John F. Miller III, and found no mention of 'update statistics' in this context. I also find no info about reclaiming space being a problem in any context. Has anyone seen this problem? What kinds of things should I look for in the code? Thanks, John