cancel
Showing results for 
Search instead for 
Did you mean: 

How to enable adaptive compression in 10.5?

former_member196032
Participant
0 Kudos

I'm in the process of upgrading multiple system OLAP and OLTP from 9.7 fp9 to 10.5 fp3 or 3a (when available).  The pdf for the upgrade instructions does not make mention of how to enable adaptive compression in 10.5 for tables that were already set to "compress" in 9.7  Except through the offline/online DB6conv program.

At this point in time all I want is to enable adaptive compression.  My question is there a SAP tool or do I simply run a query against syscat.tables, get the list of row compressed tablenames and run an "alter table ... compress adaptive"?

thanks

Anke

Accepted Solutions (1)

Accepted Solutions (1)

MarcinOzdzinski
Participant
0 Kudos

http://www.dbisoftware.com/db2nightshow/DB2BLUCompressionEstimator.pdf

From version 10.5 ADAPTIVE compression is default for all tables created or updated with option COMPRESS YES

So you don't need to alter them but of course until you reorg them or use ADMIN_MOVE_TABLE data inside will remain compressed using CLASSIC compression.

I'm not sure if new rows inserted or updated in earlier compressed  table are compressed with adaptive or classic compression ?

Frank-Martin
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi Anke,

all tables that have been created in V9.7 with attribute COMPRESS YES will be compressed statically .

db2 " select count(*) , rowcompmode from syscat.tables group by rowcompmode "

After the upgrade to 10.5 all tables created with attribute COMPRESS YES will get rowcompmode='A' but old tables created with V9.7 will stay with rowcompmode='S' .

You can change tables from  rowcompmode='S' to rowcompmode='A' via  ALTER TABLE . After this all new pages or old pages that are touched will be adaptively compressed. But old pages that are not touched will only be static compressed. To get all pages of an existinbg table adaptive compressed you need to move data. For example with DB6CONV.

Regards

               Frank

Answers (3)

Answers (3)

martin_mikala
Participant
0 Kudos

Hi,
You can test how much save this adaptive compression by this statement. For one or all tables. But for all this take some time.

db2 "SELECT substr(TABNAME,1,20) as TABNAME, ROWCOMPMODE, PCTPAGESSAVED_CURRENT, AVGROWSIZE_CURRENT, PCTPAGESSAVED_STATIC, AVGROWSIZE_STATIC, PCTPAGESSAVED_ADAPTIVE, AVGROWSIZE_ADAPTIVE FROM TABLE(SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO('SAPSR3', 'BALDAT'))"

B.R.

Martin

Former Member
0 Kudos

Hello,

Could you please help me how we get the list of tables which have enabled Adaptive compression.

The below one gives count , but I need entire list.



db2 " select count(*) , rowcompmode from syscat.tables group by rowcompmode "



Thanks,

Mahesh.

martin_mikala
Participant
0 Kudos

Hi,

db2 " select substr(tabname,1,20) , rowcompmode from syscat.tables where rowcompmode='A'"

B.R.
Martin

former_member196032
Participant
0 Kudos

Frank, thank you.  I have a lot of work ahead of me to get all tables in all systems.

Frank-Martin
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi Anke,

Yes, unfortunatelly in case of an upgrade from an older DB2 version you need to touch data to take full advantage of adaptive compression. In most cases it is sufficient to do this for lets say the 50 biggest tables in the system.

Regards

                Frank