{"id":1364,"date":"2012-12-20T05:00:21","date_gmt":"2012-12-19T23:00:21","guid":{"rendered":"http:\/\/techsatwork.com\/blog\/?p=1364"},"modified":"2016-01-07T11:31:04","modified_gmt":"2016-01-07T05:31:04","slug":"db2-adaptive-compression","status":"publish","type":"post","link":"https:\/\/techsatwork.com\/?p=1364","title":{"rendered":"DB2 V10 Adaptive Compression"},"content":{"rendered":"

Last February, I wrote about DB2’s compression<\/a>. \u00c2\u00a0If that was McLaren, with DB2 V9.7’s compression, IBM upgraded to Lamborghini Aventador. Apparently IBM’s thirst didn’t stop there, they went one more step ahead and got the Bugatti Veyron with DB2 V10’s adaptive compression. \u00c2\u00a0If you didn’t like the analogy, then I will put it in simple terms – DB2 V10 adaptive compression is compression on steroids.<\/p>\n

On V9.5, I have noticed around 70% compression, with V9.7, it slightly increased to 75% compression. But then V9.7 was able to compress the indexes as well. \u00c2\u00a0Galileo (a.k.a V10) adds some more juice to this, it now has adaptive compression.<\/p>\n

So what is adaptive compression ? \u00c2\u00a0Prior to V10, IBM used global pattern replacement algorithm. So if the value is found on the global dictionary its compressed. But with adaptive compression, besides compressing based on global pattern,\u00c2\u00a0DB2 will now look within a data page to see if there are further compression opportunities by finding locally repeating patterns within a data page. This increased the chance of compressing the data exponentially. \u00c2\u00a0I have seen compression ratio of 85 % on certain tables.<\/p>\n

I am not going to explain how the wheels move and turns and all that stuff. Most of us don’t need to know how the algorithm works. \u00c2\u00a0So I am just going to show you how to use it and reap the benefit. After all thats what most of us are after. If you need to know how the algorithm works, I am sure there are many sites that explain it.<\/p>\n

First of all \u00c2\u00a0in order to use the compression feature you need to have the license for Storage Optimization or have AESE license. You can do that by doing a db2licm -l<\/span> and see if the storage optimization is licensed or you have a AESE edition.<\/p>\n

Let’s get started and rev up the engine:<\/p>\n

1. If you are migrating from previous version of DB2 and \/or want to see if you are already compressing tables :<\/p>\n

SELECT SUBSTR(CREATOR, 1, 10) AS TABSCHEMA,<\/span>
\n SUBSTR(NAME, 1, 30) AS TABNAME,<\/span>
\n COMPRESSION, ROWCOMPMODE<\/span>
\n FROM SYSIBM.TABLES<\/span>
\n WHERE CREATOR NOT LIKE ‘SYS%’;<\/span><\/address>\n
\u00c2\u00a0<\/address>\n

2. To find out how much you will save by compressing in DB2 V10 use the below SQL. Run against the schema you want to evaluate. The currently_pct_pagessaved will show you how many percent of pages are saved currently by compression (if any), the classic_pct_pagessaved shows you how many percent of pages will be saved with classic compression under V10 and the adaptive_pct_pagessaved show you how many percent of pages will be saved with adaptive compression. The storagesize_MB shows you how large the table is :<\/p>\n

select tc.TABSCHEMA,tc.TABNAME, tc.ROWCOMPMODE ,<\/span>
\n sum(tc.PCTPAGESSAVED_CURRENT) as CURRENTLY_PCTPAGESSAVED ,<\/span>
\n sum(tc.PCTPAGESSAVED_STATIC) as CLASSIC_PCT_PAGESSAVED,<\/span>
\n sum(tc.PCTPAGESSAVED_ADAPTIVE)as ADPATIVE_PCT_PAGESAVED,<\/span>
\n SUM(TI.DATA_OBJECT_P_SIZE)\/1024 AS STORAGESIZE_MB<\/span>
\n from table(sysproc.admin_get_tab_compress_info(‘RAJU’,”)) TC<\/span>
\n join TABLE (sysproc.admin_get_tab_info(‘RAJU’, ”)) TI<\/span>
\n ON TC.TABSCHEMA = TI.TABSCHEMA AND TC.TABNAME = TI.TABNAME<\/span>
\n group by tc.TABSCHEMA,tc.TABNAME, tc.ROWCOMPMODE<\/span>
\n order by 7 desc;<\/span>
\n<\/span><\/address>\n

3. Starting from DB2 V9.7 you can compress index as well. Run the following sql to find out how much compression you will get on indexes :<\/p>\n

SELECT INDSCHEMA, INDNAME,TABNAME, index_compressed,<\/span>
\n \u00c2\u00a0 \u00c2\u00a0 \u00c2\u00a0 pct_pages_saved, num_leaf_pages_saved\u00c2\u00a0<\/span>
\n \u00c2\u00a0 \u00c2\u00a0 FROM TABLE(sysproc.admin_get_index_compress_info(‘T’, ‘RAJU’, ”, NULL, NULL));<\/span><\/address>\n

 <\/p>\n

Now you have the data to take a informed decision. As I mentioned on my previous article on compression<\/a> , use due diligence before selecting the tables you want to compress.\u00c2\u00a0 Generally tables with a read\/write ratio of 70 percent or more reads and 30 percent or less writes are good candidates for compression.\u00c2\u00a0 If the table gets lot of updates, then you lose cpu cycles on db2 uncompressing and compressing data.\u00c2\u00a0\u00c2\u00a0 Row compression is a great feature for DSS based systems, where there is lot of data and has more reads than updates.\u00c2\u00a0 Row compression performs best in I\/O or memory-bound environments where the bottleneck is not on the CPU. Extra CPU cycles are required to perform row compression and expansion of data rows whenever they are accessed or modified. However this overhead can be offset by efficiencies that are gained in doing fewer I\/O operations.<\/p>\n

Similarly, index compression is another awesome feature , but not all indexes will benefit from compression. Best candidates for index compression are indices with multi column, columns with character data, varchar columns, columns with low cardinality.<\/p>\n

4. Once you determine what tables and index to compress , you now need to turn on compression \u00c2\u00a0:<\/p>\n

\u00c2\u00a0 \u00c2\u00a0alter table raju.orders_history compress yes;<\/span><\/span><\/span>
\n \u00c2\u00a0 \u00c2\u00a0alter index raju.orderhist_lkp1 compress yes;<\/span><\/address>\n

5. Now that you have altered the objects to turn on compression you can either wait for the dictionary to get build automatically by ADC or run one of utilities that will build the dictionary, I recommend you running a reorg if you can :<\/p>\n

\u00c2\u00a0reorg table raju.orders_history index raju.orderhist_lkp2 resetdictionary;<\/span>
\n \u00c2\u00a0reorg indexes for all table raju.orders_history;<\/span><\/address>\n

6. Run the query from step 2 and 3 and see how much space you have saved. Prepared to be amazed.<\/p>\n

As always, make sure you get some metrics before and after compressing the tables. Saving storage is great, but whats even more great is saving system resources. \u00c2\u00a0I have noticed I\/O wait time go down by 80% in certain cases and logical reads going high because now DB2 can fit more rows in the buffer pools. \u00c2\u00a0CPU cycle does go a bit high,but overall its lower than before because of less I\/O operations.<\/p>\n

They say a picture says a thousand words. \u00c2\u00a0The below graph shows my % I\/O wait before and after I turned on adaptive compression:<\/p>\n

\"\"<\/a><\/p>\n

You can see how much it has gone down and in some cases it has gone away. \u00c2\u00a0Now do you see why I say its the Bugatti Veyron ! If you haven’t looked into compression, I strongly suggest your try it out.<\/p>\n

As always the above is my opinion and I strongly urge you to test it out in your environment to see what benefits you get. I am sure you won’t be disappointed.<\/p>\n","protected":false},"excerpt":{"rendered":"

Last February, I wrote about DB2’s compression. \u00c2\u00a0If that was McLaren, with DB2 V9.7’s compression, IBM upgraded to Lamborghini Aventador. Apparently IBM’s thirst didn’t stop there, they went one more step ahead and got the Bugatti Veyron with DB2 V10’s adaptive compression. \u00c2\u00a0If you didn’t like the analogy, then I will put it in simple […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-container-style":"default","site-container-layout":"default","site-sidebar-layout":"default","site-transparent-header":"default","disable-article-header":"default","disable-site-header":"default","disable-site-footer":"default","disable-content-area-spacing":"default","footnotes":""},"categories":[3,358],"tags":[778,549,551,844,552,843,550,842,546,545,92,220,570,530,841,529,75,547,548],"class_list":["post-1364","post","type-post","status-publish","format-standard","hentry","category-db2","category-how-to","tag-adaptive-compression","tag-adc","tag-admintabinfo","tag-admin_get_index_compress_info","tag-admin_get_tab_compress_info","tag-admin_get_tab_info","tag-automatic-dictionary-creation","tag-classic-compression","tag-compress-yes","tag-database-compression","tag-db2-compression","tag-db2-performance","tag-db2-tuning","tag-db2-udb","tag-db2-v10-compression","tag-deep-compression","tag-performance","tag-resetdictionary","tag-row-compression"],"_links":{"self":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1364","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1364"}],"version-history":[{"count":14,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1364\/revisions"}],"predecessor-version":[{"id":1372,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1364\/revisions\/1372"}],"wp:attachment":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1364"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1364"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1364"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}