Skip to Content

DB2 9.7 - compression and SAP's cluster and pool tables/tablespaces

I'm just starting to migrate from 9.5 to 9.7 and have started to use the 9.7 features for compression of data and index. I recall having read in the past somewhere that SAP recommended not to compress data/or index for SAP cluster and pool tables, ie these would be the tablespaces called CLU and POOL.

I have heard from an aquaintance that in new (fresh out of the box) SAP on 9.7 implemenations all tables/indexes are automatically (by default) compressed, except of course syscats and MDC's etc. On that note, also that volatile tables are not compressed.

My question:

What are others choosing to do? compress all or "ignore SAP cluster and pool tables"? volatile tables?

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Posted on Dec 09, 2011 at 06:55 PM

    Hi,

    For the Index compression, SAP states that after upgrade to DB2 9.7, compression flag is set to yes for the indexes that is created after upgrade. You can also refer to SAP note 1351160. Also, check below link:

    http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/b0a390a2-6861-2c10-fea3-c67b9fbed2aa?QuickLink=index&overridelayout=true

    Thanks

    Sunny

    Add a comment
    10|10000 characters needed characters exceeded

    • Hello Anke,

      let me add to what Frank already has posted.

      Often you would not see optimal compression rates with cluster tables in comparison to transparent tables. it could be something like 20 - 30% space savings Cluster vs. 80 - 90 % Transparent.

      So, often compressing cluster tables does not add significantly to overall space savings on database level.

      Nevertheless, depending on the individual structure of your database, even 30% space savings on a Cluster table may add significantly to overall space savings, for example if the major part of space is allocated by cluster tables.

      In any case, i think, it makes sense to compress indexes, regardless of a table being transparent or not.

      Hans-Juergen

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.