Topic
  • 7 replies
  • Latest Post - ‏2012-10-15T10:06:33Z by bluey01
bluey01
bluey01
50 Posts

Pinned topic Data Comperssion clarification

‏2012-10-10T01:39:22Z |
We have migrated some tables from another database and unfortunately the tables were not defined with Compression. So we plan to alter the tables accordingly.

Once the table is altered, what is the state of the existing data? Does it get compressed, or is only new data compressed?

If the new data is not compressed, what is the process to compress the data (I hope I don't have to unload and reload).

I understand that indexes need to be re-orded. And once its all done I will then get the space freed up.
Updated on 2012-10-15T10:06:33Z at 2012-10-15T10:06:33Z by bluey01
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: Data Comperssion clarification

    ‏2012-10-10T10:22:47Z  
    After alter table, data will not be compressed until you perform table reorganization (reorg table).
    There is interesting technical article about data compression in the developerWorks library http://www.ibm.com/developerworks/data/library/techarticle/dm-0605ahuja/
  • bluey01
    bluey01
    50 Posts

    Re: Data Comperssion clarification

    ‏2012-10-10T11:05:18Z  
    After alter table, data will not be compressed until you perform table reorganization (reorg table).
    There is interesting technical article about data compression in the developerWorks library http://www.ibm.com/developerworks/data/library/techarticle/dm-0605ahuja/
    Thanks - thats exactly what I need to know :-)
  • bluey01
    bluey01
    50 Posts

    Re: Data Comperssion clarification

    ‏2012-10-10T11:17:12Z  
    • bluey01
    • ‏2012-10-10T11:05:18Z
    Thanks - thats exactly what I need to know :-)
    Is it ok to compress all tables, regardless of amount of data (eg small code tables) or should you only concentrate on larger tables? Is there any rule-of-thumb from people with experience.
  • jerrys01
    jerrys01
    104 Posts

    Re: Data Comperssion clarification

    ‏2012-10-10T13:38:51Z  
    I've seen some discussion in the documentation which talked about needing about 1MB of data to build the internal compression table. Based on this, I'd say that each table with less than 1MB (and maybe even under 10MB) won't see a lot of improvement. There is overhead in doing the compression and uncompression. While this overhead is minimal for large tables (we seen disk and time improvement with both V9.5 & V9.7), it's probably more significant for small tables. Your mileage may vary.
  • bluey01
    bluey01
    50 Posts

    Re: Data Comperssion clarification

    ‏2012-10-10T22:31:44Z  
    • jerrys01
    • ‏2012-10-10T13:38:51Z
    I've seen some discussion in the documentation which talked about needing about 1MB of data to build the internal compression table. Based on this, I'd say that each table with less than 1MB (and maybe even under 10MB) won't see a lot of improvement. There is overhead in doing the compression and uncompression. While this overhead is minimal for large tables (we seen disk and time improvement with both V9.5 & V9.7), it's probably more significant for small tables. Your mileage may vary.
    OK - thanks for that. I'll do everything first to get a baseline, and then do the 80/20 process! Only compress the tables that make up 80% of the database
  • jmueller
    jmueller
    71 Posts

    Re: Data Comperssion clarification

    ‏2012-10-11T09:03:57Z  
    • bluey01
    • ‏2012-10-10T22:31:44Z
    OK - thanks for that. I'll do everything first to get a baseline, and then do the 80/20 process! Only compress the tables that make up 80% of the database
    Hi,

    Try SYSPROC.ADMIN_MOVE_TABLE. It's online. With reorg you have for each table a downtime...

    Best regards
    Joachim
  • bluey01
    bluey01
    50 Posts

    Re: Data Comperssion clarification

    ‏2012-10-15T10:06:33Z  
    • jmueller
    • ‏2012-10-11T09:03:57Z
    Hi,

    Try SYSPROC.ADMIN_MOVE_TABLE. It's online. With reorg you have for each table a downtime...

    Best regards
    Joachim
    Thanks for that tip - I'll give it a go.

    Does row compression work on workgroup server edition or do you still have to buy an additional licence - or is it only on enterprise edition?