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

[former_member196032]

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?

[sunny_pahuja2]

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-c67b9fbed...

Thanks

Sunny

[former_member196032]

Sunny, your statement is correct, however, what SAP is saying is that newly created indexes will be created compressed.

That leaves the indexes that already existed prior to the upgrade. The upgrade itself doesn't do compression. We all know that.

My question is thus still unanswered.

Anke

[Frank-Martin]

Hi Anke,

if you choose the compress otion in SAPINST , R3load will create all tables with COMPRESS YES but SAPINST will afterwards remove the COMPRESS flag for the tables that are know to be VOLATILE. The reason is that those are mostly queue tables and spending the CPU overhead for volatile data does not make much sense.

Some cluster tables have a bad compression rate since the cluster data is already compressed on SAP end. Now it depends on the length of the cluster data column if those data is created as a LOB column or if most of the LOB data is inlined in the data pages. Even if the compression rate is not good I believe it will not cause much overhead if some of those tables are compressed but most likely it will not give you much benefit. Would like to hear other opinions.

Regards

Frank

[hans-juergen_zeltwanger]

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