Repack/Shrink With In-Table BYTE Columns

All: I have a couple of tables that have millions of records and each of which has one BYTE column stored in-table. I've deleted over a million records from each of them (probably accounting for about 35% of the total records in the table) and then did 'table repack shrink,' which claims to have succeeded. However, no space was actually reclaimed. The records were inserted years ago and we back up logical logs on an ongoing basis, so I can't imagine the must-also-back-up-the-log thing is an issue. I've done the same thing with several other tables that do not contain BYTE or TEXT types and the space reduction was immediately apparent for those. EXTENT and NEXT sizes are sufficiently small that we should see plenty of space freed up. I ran the repack shrink multiple times and still nothing. Has anyone else encountered anything like this? Am I looking at a bug? Missing something obvious? IDS 14.10.FC7W1 on Linux x86_64 TIA, - TJG ------------------------------ TOM GIRSCH ------------------------------ #Informix

Tom: What does the oncheck -pT report show? Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Preliminary output (waiting for the detailed stuff to complete): Number of pages allocated 12281265 Number of pages used 12281265 Number of data pages 812482 Number of rows 27955341 Looks like it isn't freeing up the blobpages. Tom Girsch Lead System Architect Auto Europe Group tgirsch@autoeurope.com Office #207-842-2139 " If you think there is something more important than a Client ... think again "

Tom: I think I know why the blob pages are not being freed. Unlike BLOBSPACE blobs, for TABLESPACE blobs, if the blob entry is smaller than a page multiple blobs are stored on a page. If when you deleted the old rows their blobs were on pages that still have other blobs on them, those pages couldn't be freed. It may be that the REPACK code doesn't move the blobs to consolidate them onto fewer pages. If so, when the details from the oncheck come in, they will show lots of free space on blob pages. Anyone at HCL want to comment? Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

I'm guessing you're looking for this from the oncheck -pT. Note over 5.5 million "Free" pages, but they ain't freeing. Type Pages Empty Semi-Full Full Very-Full ---------------- ---------- ---------- ---------- ---------- ---------- Free 5522396 Bit-Map 3046 Index 0 Data (Home) 812483 Data (Remainder) 1 0 1 0 0 TBLspace BLOBs 5943339 0 1247033 3847314 848992 ---------- Total Pages 12281265 Unused Space Summary Unused data bytes in Home pages 172743481 Unused data bytes in Remainder pages 1631 Unused bytes in TBLspace Blob pages -167521571 Home Data Page Version Summary Version Count 0 (current) 812482 I'm in the process of building a clean copy of the table to see how much space that takes up (and what its oncheck -pT looks like) Tom Girsch Lead System Architect Auto Europe Group tgirsch@autoeurope.com Office #207-842-2139 " If you think there is something more important than a Client ... think again "

Additional tidbit of relevant info: extent size 57344 next size 14336 compressed lock mode row Just tried the repack/shrink again: EXECUTE FUNCTION task('table repack shrink','vouch_tag','euroreserve');(expression) Succeeded: table repack shrink euroreserve:informix.vouch_tag 1 row(s) retrieved. Table size didn't budge.

Tom: Yup, this many free blobpage bytes: 11,137,123,527. Most of that is on partially full blobpages. What's concerning are the ~84,000 free data pages that weren't compacted out. Yea, JC or Vlad has to figure this one out. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

I'm more concerned with the 5.5 million pages that are simply listed as "free." Tom Girsch Lead System Architect Auto Europe Group tgirsch@autoeurope.com Office #207-842-2139 " If you think there is something more important than a Client ... think again "

I'll have to open a ticket here I guess. I created an uncompressed full copy of the table with all the same data, and that version is 70% of the size of the existing, compressed one.

TS008671041 opened Tom Girsch Lead System Architect Auto Europe Group tgirsch@autoeurope.com Office #207-842-2139 " If you think there is something more important than a Client ... think again "

I did a little bit of additional testing and it seems the problem is limited to the BYTE data type. I tested with a table that has the TEXT type and the repack/shrink worked as expected. ------------------------------ TOM GIRSCH ------------------------------

Support has identified this as a bug: idsdb00110607 ( IT40623 ) : REPACK & SHRINK IS NOT SUCCESSFUL IF TABLE CONTAINS PARTITION BLOBS WITH LARGE ADDRESSES No ETA yet on a fix. ------------------------------ TOM GIRSCH ------------------------------

Hi all, I have got exactly the same problem in very heavy core system on big partitioned table (700GB) in version 12.10.FC15. Repack, shrink doesn't work properly on table with blobs. Could you tell me if is there a fix on this or number of Case? Regards Piotr Wolski ------------------------------ Piotr Wolski ------------------------------

They gave us a patch for 14.10 so they could probably provide one for 12.10 pretty easily. But it's not perfect. Works for most but not all of our tables with BYTE. Get Outlook for Android

Thanks a lot I will open CASE. ------------------------------ Piotr Wolski ------------------------------

You might reference our case, TS008671041 Get Outlook for Android