1 From the existing DB2 9 Deep Compression …“With DB2 9, we’re seeing compression rates up to 83% on the Data Warehouse. The projected cost savings are more than $2 million initially with ongoing savings of $500,000 a year.” - Michael Henson “We achieved a 43 per cent saving in total storage requirements when using DB2 with Deep Compression for its SAP NetWeaver BI application, when compared with the former Oracle database, The total size of the database shrank from 8TB to 4.5TB, and response times were improved by 15 per cent. Some batch applications and change runs were reduced by a factor of ten when using IBM DB2.” - Markus Dellermann 1.5 Times Better 3.3 Times 2.0 Times 8.7 Times Reduce storage costs Improve performance Easy to implement DB2 9 Other

2 Index Compression What is Index Compression?The ability to decrease the storage requirements from indexes through compression. By default, if the table is compressed the indexes created for the table will also be compressed. including the XML indexes Index compression can be explicitly enabled/disabled when creating or altering an index. Why do we need Index Compression? Index compression reduces disk cost and TCO (total cost of ownership) Index compression can improve runtime performance of queries that are I/O bound. When does Index Compression work best? Indexes for tables declared in a large RID DMS tablespaces (default since DB2 9). Indexes that have low key cardinality & high cluster ratio. What is Index Compression? Since table data and indexes are stored differently, DB2 does not implement the same Lempel-Ziv algorithm that row compression uses to compress data. Index compression can be enabled through the CREATE INDEX or ALTER INDEX commands using the COMPRESS YES option. Why do we need Index Compression? Index compression directly reduces the # of I/O requests and uses CPU cycles to compress / decompress data, thereby more efficiently using idling CPU cycles that were previously waiting on I/O.

3 Index Compression Index Page (pre DB2 9.7) Page HeaderFixed Slot Directory (maximum size reserved) AAAB, 1, CCC 1055, 1056 AAAB, 1, CCD 3011, 3025, 3026, 3027, 3029, 3033, 3035, 3036, 3037 BBBZ, 1, ZZZ 3009, 3012, 3013, 3015, 3016, 3017, 3109 BBBZ, 1, ZZCCAAAE 6008, 6009, 6010, 6011 How does Index Compression Work? DB2 will consider multiple compression algorithms to attain maximum index space savings through index compression. Index Key RID List

4 Index Compression Index Page (DB2 9.7) Page Header Variable SlotDirectory Saved Space from Variable Slot Directory AAAB, 1, CCC 1055, 1056 AAAB, 1, CCD 3011, 3025, 3026, 3027, 3029, 3033, 3035, 3036, 3037 BBBZ, 1, ZZZ 3009, 3012, 3013, 3015, 3016, 3017, 3109 Variable Slot Directory Each index page begins with a page header, followed by a slot directory. Prior to 9.7, a slot directory was pre-allocated and fixed based on the minimum index key size. With variable slot directory, fewer pages per index can be attained. BBBZ, 1, ZZCCAAAE 6008, 6009, 6010, 6011 Variable Slot Directory In 9.7, a slot directory is dynamically adjusted in order to fit as many keys into an index page as possible. Index Key RID List

5 Index Compression Index Page (DB2 9.7) Page Header Variable SlotDirectory Saved Space from Variable Slot Directory First RID RID Deltas AAAB, 1, CCC Saved 1055, 1 AAAB, 1, CCD 3011, 14, 1, 1, 2, 4, 2, 1, 1 Saved from RID List BBBZ, 1, ZZZ Saved 3009, 3, 1, 2, 1, 1, 92 RID List Compression An index key contains a key value and a keydata list. A keydata contains a RID and its flags. The RIDs in a RID list are sorted. When there are only 1 or 2 RIDs, they are uncompressed format. When inserting a 3rd RID, its storage switches to a compressed format. BBBZ, 1, ZZCCAAAE Saved 6008, 1, 1, 1 RID List Compression Instead of saving the full version of a RID, we can save some space by storing the delta between two RIDs. RID List compression is enabled when there are 3 or more RIDs in an index page. Index Key Compressed RID

6 Index Compression Index Page (DB2 9.7) Page Header Variable SlotCOMMON PREFIX Index Page (DB2 9.7) Page Header Variable Slot Directory Saved Space from Variable Slot Directory AAAB, 1, CC 0, 2 BBBZ, 1, ZZ SUFFIX RECORDS C 1055, 1 Saved D 3011, 14, 1, 1, 2, 4, 2, 1, 1 Saved from RID List and Prefix Compression Z 3009, 3, 1, 2, 1, 1, 92 Saved Prefix Compression Because the key values are sorted on index pages, very often the key values of two adjacent keys are very similar on an index with many different entries. When the page is full, DB2 will try to find the optimal prefixes to use for each page. CCAAAE 6008, 1, 1, 1 Saved Prefix Compression Instead of saving all key values, we can save some space by storing a common prefix and suffix records. During index creation or insertion, DB2 will compare the new key with adjacent index keys and find the longest common prefixes between them. Compressed Key Compressed RID

7 Index Compression Results in a NutshellIndex compression uses idle CPU cycles and idle cycles spent waiting for I/O to compress & decompress index data. When we are not CPU bound, we are able to achieve better performance in all inserts, deletes and updates. Average 36% * Higher is better Summary From 7 complex query database warehouses, an average of 36% index space savings was attained through index compression. Details Algorithms The Prefix and RIDLIST compression algorithms together help us get very good compression ratios Space Savings Focus on best results: 3 warehouses saving over 50% space on indexes Runtime Performance No runtime regressions due to index compression Up to 35% runtime improvement on selects Up to 18% runtime improvement on updates Up to 19% runtime improvement on inserts Up to 16% runtime improvement on inserts Machine utilization Simple selects negligible effect on CPU and i/o wait Simple updates extra 10% CPU, save 7.5% i/o wait Simple insert extra 5% CPU, save 3% i/o wait Simple delete extra 3.7% CPU, save 3.8% i/o wait Testing Configuration The 7 warehouses used to assess the compression rates include 6 regular PQA regression workloads for DB2 9.7 and a workload following the latest TPC configuration for complex query tests. Warehouse #1 – zLinux s390x SLES10 8-way, ~10GB database Warehouse #2 – 3-tier workload, database machine on 4-way AIX Power 3 box, ~125GB database Warehouse #3 – 4-way AIX Power4 box, ~15GB database Warehouse #4 – Four AIX machines DPF, each was a 4-way Power4 box, ~500GB database Warehouse #5 - Four AIX machines DPF, each was a 8-way Power6 box, ~200GB database Warehouse #6 – AIX 32-way Power4 box, MLN, 160GB database Warehouse #7 – 4-way AIX Power4 box, MLN, ~40GB database The insert/update/delete/select tests were performed on an SAP-BW fact table ~15GB in size and various other dimension tables. Ran on xLinux SLES10 2-way box. Runs 16% Faster Runs 18% Faster Runs 19% Faster Runs As fast Lower is better

8 Temp Table CompressionWhat is Temp Table Compression? The ability to decrease storage requirements by compressing temp table data Temp tables created as a result of the following operations are compressed by default: Temps from Sorts Created Global Temp Tables Declared Global Temp Tables Table queues (TQ) Why do we need Temp Table Compression on relational databases? Temp table spaces can account for up to 1/3 of the overall tablespace storage in some database environments. Temp compression reduces disk cost and TCO (total cost of ownership) Note: Temp tables can also be created from MGJN and NLJOIN, though large enough temp tables are not likely

9 Temp Table CompressionHow does Temp Table Compression Work? It extends the existing row-level compression mechanism that currently applies to permanent tables, into temp tables. String of data across a row Canada|Ontario|Toronto|Matthew Canada|Ontario|Toronto|Mark USA|Illinois|Chicago|Luke USA|Illinois|Chicago|John Lempel-Ziv Algorithm Build a string translation table from the text being compressed. Whenever a previously-encountered string is read from the input, the longest of such is determined, and then the code for this string concatenated with the extension character is stored in the table. Lempel-Ziv Algorithm Create dictionary from sample data 0x12f0 – CanadaOntarioToronto … 0xe57a – Mathew … 0xff0a – Mark … 0x15ab – USAIllinoixChicago … 0xdb0a – Luke … 0x544d – John … Saved data (compressed) 0x12f0,0xe57a 0x12f0,0xff0a 0x15ab,0xdb0a 0x15ab,0x544d

10 Temp Table CompressionResults in a Nutshell For affected temp compression enabled complex queries, an average of 35% temp tablespace space savings was observed. For the 100GB warehouse database setup, this sums up to over 28GB of saved temp space. Effective CPU Usage Basics: CPU Cycles that were previously idle, waiting for I/O, are now effectively used to compress / decompress data Testing Configuration Hardware and OS: Four AIX machines DPF, each was a 8-way Power6 box, Database: In accordance with latest TPC DSS database, size ~200GB database Saves 35% Space 5% Faster Lower is better Lower is better

11 XML Data Compression Why do we need XML Data Compression?What is XML Data Compression? The ability to decrease the storage requirements of XML data through compression. XML Compression extends row compression support to the XML documents. If row compression is enabled for the table, the XML data will be also compressed. If row compression is not enabled, the XML data will not be compressed either. Why do we need XML Data Compression? Compressing XML data can improve storage efficiency and runtime performance of queries that are I/O bound. XML compression reduces disk cost and TCO (total cost of ownership) for databases with XML data

12 XML Data Compression How does XML Data Compression Work?Small XML documents (< 32k) can be inlined with any relational data in the row and the entire row is compressed. Larger XML documents that reside in a data area separate from relational data can also be compressed. By default, DB2 places XML data in the XDA to handle documents up to 2GB in size. XML compression relies on a separate dictionary than the one used for row compression. Data (uncompressed) Relational Data < 32KB XML Data 32KB – 2GB XML Data Data (compressed) Small XML This inlining capability was introduced in 9.5. Large XML Compression of XML data stored in the XDA is included in 9.7. XDA: A location separate from the relational data is called the XML Data Area (XDA) Two Dictionaries Since the relational DAT objects and XDA objects are kept on separate places, DB2 uses two separate compression dictionaries generated automatically, one for relational columns and one for the XDA of the table. Source: Comp. Data Inlined < 32KB XML Data Compressed 32KB – 2GB XML Data Dictionary #1 Dictionary #2

13 XML Data Compression Results in a NutshellSignificantly improved query performance for I/O-bound workloads. Achieved 30% faster maintenance operations such as RUNSTATS, index creation, and import. Average compression savings of ⅔ across 7 different XML customer databases and about ¾ space savings for 3 of those 7 databases. Average 67% Higher is better Compression ratio measured for a variety of sample data. Calculated by comparing base table pages and xda pages in table snapshot Graph shows compression results for two different “ADC threshold” sizes; point at which we stop reading data and build the compression dictionary. Improvement in compression due to reading more data is usually not significant. Selected XML DB Test Details XML DB Test #2 Overall for XML DB workload CPU Time regresses by 4% (due to decompression algorithm) Overall for XML DB workload Elapsed Time improves 32% (28 min XDA Compressed vs. 42 min uncompressed) Overall 8 test case improvements (up to 45%) XML DB Test #5 As far as logical I/O is concerned: 48/75 queries show savings in BP DATA LOGICAL READS (avg. about 40% savings). However there are no noticeable changes in index or XDA logical reads. The REORG on a table with compression enabled vs. REORG on a table with no compression enabled is 37% faster for the XML DB workload . Overall for XML DB workload CPU Time regresses by 5% (due to decompression algorithm) Overall for XML DB workload Elapsed Time improves 33% (32 min XDA Compressed vs. 46 min uncompressed) Overall 33 test case improvements (up to 64%) XML DB Test #6 - Optimal ADCTHRES value for this scenario is 4MB when looking at both XDA Compression Rate as well as import speedup - Interesting to see the number of XDA pages go up between ADCTHRES 5MB and 6MB and then down again at 8MB - Choosing ADCTHRES values larger than 4MB doesn't really have additional significant improvements (however setting the value really big to 50MB (10% of the raw data volume and 25% of the compressed XDA pages gives a higher compression rate of 83% while import speedup is still good) XML DB Test #7 Details The XML workload used consists of 75 queries covering SQL, XQuery, XMLQUERY, XMLTABLE, NSE & XML Index Overall the XML workload CPU Time regresses by 13% (due to decompression algorithm) Overall the XML workload Elapsed Time improves 37% (25min XDA Compressed vs. 37min uncompressed) Overall 37 test case improvements (up to 74%), 4 test case slightly regress (up to 14%) 37% Faster Lower is better

14 Range Partitioning with Local IndexesWhat does Range Partitioning with Local Indexes mean? A partitioned index is an index which is divided up across multiple storage objects, one per data partition, and is partitioned in the same manner as the table data Local Indexes can be created using the PARTITIONED keyword when creating an index on a partitioned table (Note: MDC block indexes are partitioned by default) Why do we need Range Partitioning with local Indexes? Improved ATTACH and DETACH partition operations More efficient access plans More efficient REORGs. When does Range Partitioning with Local Indexes work best? When frequents roll-in and roll-out of data are performed When one tablespace is defined per range.

15 Range Partitioning with Local IndexesResults in a Nutshell Partition maintenance with ATTACH: 20x speedup compared to DB2 9.5 global index because of reduced index maintenance. 3000x less log space used than with DB 9.5 global indexes. Asynchronous index maintenance on DETACH is eliminated. Local indexes occupy fewer disk pages than 9.5 global indexes. 25% space savings is typical. 12% query speedup over global indexes for index queries – fewer page reads. * Lower is better Local Indexes Environment: Star-schema data warehouse with 6 months of data in fact table Multiple versions of main fact table: un-partitioned, RP with global indexes, RP with local indexes, RP with no indexes at all Did ATTACH/DETACH of 1 week partitions on all RP table variants to compare time and log space. Also did 1 month partitions, results similar. Compared leaf page counts on index types Also compared query performance on different RP table variants – separate slide deck available soon Testing Configuration The local index tests, attach/detach tests, index size comparison were ran on a data warehouse database with sample ¼ and ½ year data Hardware and OS: 8-way AIX box with 32GB RAM. 25% Space Savings Lower is better

16 Scan Sharing What is Scan Sharing? Why do we need Scan Sharing?It is the ability of one scan to exploit the work done by another scan This feature targets heavy scans such as table scans or MDC block index scans of large tables. Scan Sharing is enabled by default on DB2 9.7 Why do we need Scan Sharing? Improved concurrency Faster query response times Increased throughput When does Scan Sharing work best? Scan Sharing works best on workloads that involve several clients running similar queries (simple or complex), which involve the same heavy scanning mechanism (table scans or MDC block index scans).

17 Scan Sharing How does Scan Sharing work?Unshared Scan A scan How does Scan Sharing work? When applying scan sharing, scans may start somewhere other than the usual beginning, to take advantage of pages that are already in the buffer pool from scans that are already running. When a sharing scan reaches the end of file, it will start over at the beginning and finish when it reaches the point that it started. Eligibility for scan sharing and for wrapping are determined automatically in the SQL compiler. In DB2 9.7, scan sharing is supported for table scans and block index scans. 1 2 3 4 5 6 7 8 B scan 1 2 3 4 5 6 7 8 Re-read pages causing extra I/O Shared Scan A scan 1 2 3 Shared A & B scan 4 5 6 7 8 More Details Shared scanners are managed in “share groups”, to keep the members together as long as possible, so the sharing benefit is maximized. Faster scans can be throttled to allow slower scans to catch up. At run time, during the scans, actual scan speeds and distances between scanners are monitored, and regrouping is done when needed for better sharing characteristics. Regrouping corrects imbalances in scan speed. Terminology Sharing Group: A collection of agents scanning the same object using the same access mechanism Wrapping Scan: A scan that can start at an arbitrary point of the table and “wrap around” after reaching the last page, then starting over from the beginning until the original start point. Throttled Scan: A scan that can be delayed within limits in order to increase bufferpool sharing by reducing drift between scans Scan sharing for RID index scans, utility scans, temp table scans are not supported in DB2 9.7 B scan 1 2 3

18 Scan Sharing MDC Block Index Scan Sharing shows 47% average query improvement gain. The fastest query shows up to 56% runtime gain with scan sharing. Runs 47% Faster! Testing Configuration The tests were executed using a modified TPC warehouse database, executing 1 or 2 queries concurrently and staggered Hardware and OS: 32-way AIX Power4 box Lower is better Runs 14x Faster! 100 concurrent table scans now run 14 times faster with scan sharing! Lower is better

19 Scan Sharing Results in a Nutshell67% Throughput Improved Higher is better Testing Configuration The tests were executed using a modified TPC warehouse database, running 16 streams in parallel Hardware and OS: 8-way AIX Power4 box Results in a Nutshell When running 16 concurrent streams of complex queries in parallel, a 67% increase in throughput is attained when using scan sharing. Scan sharing works fully on UR and CS isolation and by design, has limited applicability on RR and RS isolation levels.

20 Scan Sharing Results in a Nutshell44% Throughput Improved 67% Throughput Improved Higher is better Effective CPU usage Testing Configuration The tests were executed using a modified TPC warehouse database, running 16 streams in parallel Hardware and OS: 8-way AIX Power4 box OLD SETUP - 8 streams - 8-parallel processes - 32-way regatta1 p4 AIX - modified tpch kit (using UR isolation), running select only - 15% performance improvement with scan sharing NEW SETUP - 8 stream - 16-parallel processes - 8-way squid p4 AIX - 44% performance improvement with scan sharing NEW SETUP - 16 streams - 67% performance improvement with scan sharing Results in a Nutshell When running 8 concurrent streams of complex queries in parallel, a 44% increase in throughput is attained when using scan sharing. When running 16, 67% increase in throughput is observed Scan sharing works best with UR and CS isolation level

21 XML Scalability on Infosphere Warehouse (a.k.a DPF)What does it mean? Tables containing XML column definitions can now be stored and distributed on any partition. XML data processing is optimized based on their partitions. Why do we need XML in database partitioned environments? As customers adopt the XML datatype in their warehouses, XML data needs to scale just as relational data XML data also achieves the same benefit from performance improvements attained from the parallelization in DPF environments.

22 XML Scalability on Infosphere Warehouse (a.k.a DPF)Results in a Nutshell Table results show the elapsed time performance speedup of complex queries from a 4 partition setup to an 8 partition setup. Queries tested have a similar star-schema balance for relational and XML. Each query run in 2 or 3 equivalent variants: Completely relational (“rel”) Completely XML (“xml”) XML extraction/predicates with relational joins (“xmlrel”) (join queries only) Queries/updates/deletes scale as well as relational ones. Average XML query-speedup is 96% of relational Testing Configuration Tests were ran on a data warehouse database with 4 and 8 partition setup Hardware and OS: 8-way AIX box with 32GB RAM.

23 Statement ConcentratorWhat is the statement concentrator? It is a technology that allows dynamic SQL statements that are identical, except for the value of its literals, to share the same access plan. The statement concentrator is disabled by default, and can be enabled either through the database configuration parameter (STMT_CONC) or from the prepare attribute Why do we need the statement concentrator? This feature is aimed at OLTP workloads where simple statements are repeatedly generated with different literal values. In these workloads, the cost of recompiling the statements many times adds a significant overhead. Statement concentrator avoids this compilation overhead by allowing the compiled statement to be reused, regardless of the values of the literals. The literals are replaced in the statement with parameter markers and the values of the literals are bound during run time. If a statement already has parameter markers, it is no longer a candidate for the statement concentrator.

24 Statement ConcentratorResults in a Nutshell The statement concentrator allows prepare time to run up to 25x faster for a single user and 19x faster for 20 users. The statement concentrator improved throughput by 35% in a typical OLTP workload using 25 users 19x Reduction in Prepare time! Lower is better Prepare TEST Performance When preparing 20,000 statements of the form "select from