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

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
    ACCEPTED ANSWER

    Re: Data Comperssion clarification

    ‏2012-10-10T10:22:47Z  in response to bluey01
    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
      ACCEPTED ANSWER

      Re: Data Comperssion clarification

      ‏2012-10-10T11:05:18Z  in response to SystemAdmin
      Thanks - thats exactly what I need to know :-)
      • bluey01
        bluey01
        50 Posts
        ACCEPTED ANSWER

        Re: Data Comperssion clarification

        ‏2012-10-10T11:17:12Z  in response to bluey01
        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
    ACCEPTED ANSWER

    Re: Data Comperssion clarification

    ‏2012-10-10T13:38:51Z  in response to bluey01
    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
      ACCEPTED ANSWER

      Re: Data Comperssion clarification

      ‏2012-10-10T22:31:44Z  in response to jerrys01
      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
        ACCEPTED ANSWER

        Re: Data Comperssion clarification

        ‏2012-10-11T09:03:57Z  in response to bluey01
        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
          ACCEPTED ANSWER

          Re: Data Comperssion clarification

          ‏2012-10-15T10:06:33Z  in response to jmueller
          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?