Transaction table not shrinking with deletions

Hi all, We have a table that logs application transactions. We also cull those transaction records that are older than 30days, with the standard "delete from the-table where it's 30days or older". However our dbspace keeps growing and a 'repack shrink' does not appear to recover the space deleted earlier. It's still our biggest table, when it shouldn't be. Any ideas on how to trouble shoot this? Matthew

oncheck -pe to check space utilization within the dbspace. oncheck -pT to check page usage within the table/index (will take a lock). https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.adref.doc/i ds_adr_0387.htm Is the table compressed? Regards, David. > On 01 August 2017 at 18:31 MATTHEW KAISER <mkaise@midwestern.edu> wrote: > > > Hi all, > > We have a table that logs application transactions. We also cull those > transaction records that are older than 30days, with the standard "delete from > the-table where it's 30days or older". > > However our dbspace keeps growing and a 'repack shrink' does not appear to > recover the space deleted earlier. > > It's still our biggest table, when it shouldn't be. > > Any ideas on how to trouble shoot this? > > Matthew > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

DELETE removes entries which can be reused, but doesn't free space. It may delay growth as some space can be re-used. REPACK SHRINK will move rows to the "beginning" of the table and may free space in all table extents. For the "next" extents it will reduce them "as much as possible" (documentation quote). The first won't shrink to less than the "EXTENT SIZE" of the CREATE TABLE. If you used a very large "EXTENT SIZE" then shrink won't be able to make it smaller then that. You can use ALTER TABLE MODIFY EXTENT SIZE to reduce the table definition (it won't do any physical change) and then try the REPACK SHRINK. A list of the table extent usage before and after should provide more clues... Regards On Tue, Aug 1, 2017 at 7:31 PM, MATTHEW KAISER <mkaise@midwestern.edu> wrote: > Hi all, > > We have a table that logs application transactions. We also cull those > transaction records that are older than 30days, with the standard "delete > from > the-table where it's 30days or older". > > However our dbspace keeps growing and a 'repack shrink' does not appear to > recover the space deleted earlier. > > It's still our biggest table, when it shouldn't be. > > Any ideas on how to trouble shoot this? > > Matthew > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

Thanks we'll try that. We're deleting a days worth of data, each day. I'm wondering if the deleted space just isn't being returned to the system immediately and the following day just fills the deleted space back in, in the same chunks and pages, so nothing seems to be changing and things are as compact as they can be. Would that be true?

This is an aside: Have you considered using interval/range partitioning with rolling windows? That would eliminate the need to delete large numbers of rows in the first place and automatically return the space allocated to dropped partitions to the free space pool. FWIW. 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 Wed, Aug 2, 2017 at 7:36 AM, MATTHEW KAISER <mkaise@midwestern.edu> wrote: > Thanks we'll try that. > > We're deleting a days worth of data, each day. > > I'm wondering if the deleted space just isn't being returned to the system > immediately and the following day just fills the deleted space back in, in > the > same chunks and pages, so nothing seems to be changing and things are as > compact as they can be. > > Would that be true? > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >