Compression Question/Understanding

O/S Linux IDS 11.70.FC8 Good Morning, I have used compression before with inpressive results however, I just finished compressing a table with tblspace blobs in it and really got nothing back in terms of space. My steps were: enable compression on the instance compress the table repack the table shrink the table All of those ended successfully however none of them took very long to complete (def under 5 minutes). The sql statement to estimate compression rate (select sysadmin:task("table estimate_compression", tabname) from systables where tabname = 'gapi_usage_collection') reported: (expression) est curr change partnum table ----- ----- ------ ---------- ----------------------------------- 78.6% 0.0% +78.6 0x00d00002 gapi_dev_a:informix.gapi_usage_coll ection Succeeded: table estimate_compression gapi_dev_a:informix.gapi_u sage_collection From that, I expected approx. 78% back when all was done. What I got back (from looking at oncheck -pe | grep <tabname>) was only the last extent shrunk and I now suspect there were never any rows in that last little bit. I am suprised that the estimate gasve me such a high number and the actual result was basically nothing. Did I miss something? thanx, dan

A bit of additional info. This table has well over 1 million rows and 3 test columns..

Do you have a license for comnpression? You would only have compression activated if you have the Advanced Enterprise Edition (with IWA) or have purchased the compression package add on. Otherwise compression is no-op. 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 Thu, Jan 22, 2015 at 8:58 AM, DAN MUELLER <ddmueller@intercall.com> wrote: > O/S Linux > IDS 11.70.FC8 > > Good Morning, > > I have used compression before with inpressive results however, I just > finished compressing a table with tblspace blobs in it and really got > nothing > back in terms of space. My steps were: > > enable compression on the instance > compress the table > repack the table > shrink the table > > All of those ended successfully however none of them took very long to > complete (def under 5 minutes). The sql statement to estimate compression > rate > (select sysadmin:task("table estimate_compression", tabname) > from systables where tabname = 'gapi_usage_collection') reported: > > (expression) est curr change partnum table > > ----- ----- ------ ---------- ----------------------------------- > > 78.6% 0.0% +78.6 0x00d00002 gapi_dev_a:informix.gapi_usage_coll > > ection > > Succeeded: table estimate_compression gapi_dev_a:informix.gapi_u > > sage_collection > > >From that, I expected approx. 78% back when all was done. What I got back > (from looking at oncheck -pe | grep <tabname>) was only the last extent > shrunk > and I now suspect there were never any rows in that last little bit. > > I am suprised that the estimate gasve me such a high number and the actual > result was basically nothing. Did I miss something? > > thanx, > dan > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e013d167445f7d5050d402687

Art, The way it is acting makes me wonder however, we just signed a new support contract and yes, it was supposed to be for Advanced Enterprise Edition. The actual product I downloaded was "IUE_11.70.FC8_LINUX_X86_64_ML.tar" Can you tell by that? I have never seen the "IUE" beginning before so I assumed that was the Advanced edition. If this gives no clue, how can I tell? Thanx, Dan

The IUE is for Informix Ultimate Edition which is what Advanced Edition was previously called, so yes, you should have compression enabled. So, the next step is to open a PMR and see what IBM has to say about this. 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 Thu, Jan 22, 2015 at 12:46 PM, DAN MUELLER <ddmueller@intercall.com> wrote: > Art, > > The way it is acting makes me wonder however, we just signed a new support > contract and yes, it was supposed to be for Advanced Enterprise Edition. > The > actual product I downloaded was "IUE_11.70.FC8_LINUX_X86_64_ML.tar" Can you > tell by that? I have never seen the "IUE" beginning before so I assumed > that > was the Advanced edition. If this gives no clue, how can I tell? > > Thanx, > Dan > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1134d228b770cd050d414b6d

I suspect the ability to reclaim the free space does not come along until version 12 for partition blobs. I found a white paper out on IIUG on Compression: http://www.iiug.org/library/ids_12/IFMX-Compression-WhitePaper-2013-03-22.pdf Coalescing (repacking) the rows After a partition is compressed, typically there is a significant amount of unused space (or ‘holes’) between the rows. The coalesce operation, also known as a repack operation, moves all of the rows to the front of the partition. It uses small transactions and locks only those rows actively being moved. In Version 12, Informix has added the capability to repack tables that contain partition blobs and indexes. Reclaiming free space After the rows are repacked, the reclaim operation truncates the unused portion of the partition, and returns the space back to the dbspace where the partition is located. For an index, this returns free space at the end of the index to the dbspace, thus reducing the total size of the index. Mark

The Informix Ultimate Edition was what is called today the Enterprise Edition. The Advanced Enterprise Edition was called Ultimate *Warehouse* Edition. As far as compression is concerned, it should be usable in any Enterprise or Ultimate Edition technically. To use it officially and legally, you need to have either the *compression option* if you have the Enterprise or the Ultimate Edition or have the Avanced Enterprise or the Ultimate Warehouse Edition. The only restriction is the minimum number of rows in the table to be compressed. Check with your IBM support contact and open a PMR. Cordialement, Regards, Khaled Bentebal Directeur Général - ConsultiX Président UGIF - User Group Informix France IIUG - Board of Directors Tél: 33 (0) 1 39 12 18 00 Fax: 33 (0) 1 39 12 18 18 Mobile: 33 (0) 6 07 78 41 97 Email: khaled.bentebal@consult-ix.fr Site Web: www.consult-ix.fr Le 22/01/15 18:49, Art Kagel a écrit : > The IUE is for Informix Ultimate Edition which is what Advanced Edition was > previously called, so yes, you should have compression enabled. So, the > next step is to open a PMR and see what IBM has to say about this. > > 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 Thu, Jan 22, 2015 at 12:46 PM, DAN MUELLER<ddmueller@intercall.com> > wrote: > >> Art, >> >> The way it is acting makes me wonder however, we just signed a new support >> contract and yes, it was supposed to be for Advanced Enterprise Edition. >> The >> actual product I downloaded was "IUE_11.70.FC8_LINUX_X86_64_ML.tar" Can you >> tell by that? I have never seen the "IUE" beginning before so I assumed >> that >> was the Advanced edition. If this gives no clue, how can I tell? >> >> Thanx, >> Dan >> >> >> >> > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > --001a1134d228b770cd050d414b6d > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

I've been doing quite a bit of testing on compression, repack and shrink. I've seen incredibly good results for all ops, but there are some items to consider, including available free space for repack/compress - this could be existing free pages in the partition, or in some cases, adding new chunks to provide free pages for the operation and then using shrink to return free space to the dbspace. ** One important finding (and related to Dan Mueller's questions too): one client has 11.5 Enterprise Edition and I starting doing some compression tests. Before getting too far along, Mike Walker suggested I check to see for sure IF we had a license for compression. We did not, and the cost to add it to the environment was $273K. So a good catch from Mike - compression worked fine without the license for it on 11.5. I've seen great results in "repack" in a recent test. The table had grown to about 10GB due to a regularly scheduled purge routine having failed for awhile. Did a repack which took about 90 minutes. The resulting table was about 1.8GB, and the "shrink" returned about 8GB to the dbspace. The free pages were already part of the partition so they were used for the repack. The repack is the finished version of the "oncheck -me" from back in the day. I'll be using repack/shrink for a number of partitions at a current client. Would love to use compress but again, don't have the license. Mark Scranton The Mark Scranton Group www.marscranton.com "All Informix ... all the time.