on 08-21-2014 9:33 PM
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
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 ?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Frank, thank you. I have a lot of work ahead of me to get all tables in all systems.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
83 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.