IBM Software Group DB2 V8 IBM
OLTP OLAP External Extract Integrate Transform Maintain Data Warehouse Reporting Legacy Data Mining
DB2 UDB: DB2 DB2 DB2 DB2 DB2 DB2
DB2 UDB EEE on PSeries 500GB 1TB > 95% 256 concurrent users - http://www- 3.ibm.com/software/data/pubs/papers/#eeescale 24 way 24 way 500GB 500GB Database Build Query Performance Time (mins) 600 500 400 300 200 173 177 404 438 508 507 500 GB 1 TB Time (s) 1200 1000 800 600 400 500GB 1 TB 100 83 84 200 0 Load Create Index Runstats Build ASTs X-Axis 0 Query
DB2 Universal Database
DB2 UDB Everyplace PalmOS Win CE EPOC-32 Linux Win32 Enterprise Servers DB2 UDB for OS/390 and z/os DB2 for VM and VSE DB2 UDB for OS/400 DB2 Connect Enterprise Data Web Java, JDBC SQLJ EJBs UDDI SOAP Net.Data XML Enterprise Win NT, 2000, OS/2 AIX, HP -UX, Solaris Linux, Linux/390 NUMA-Q Personal Win 95, 98, Me NT, 2000 Linux OS/2 Workgroup Win NT,2000 Linux AIX Solaris HP-UX OS/2 Universal Access Universal Application Universal Extensibility Universal Scalability Universal Reliability Universal Management
DB2 UDB Visual Explain Responsive Performance Monitor Large Block Reads Asynchronous Page Cleaners Extended Memory Management Economical Clients Operating System Threads Governor Cost-Based Optimizer & Query Rewrite & Lock Mode CPU CPU CPU Symmetric Multiprocessing (SMP) CPU Sequential and List Prefetch Parallel I/O Raw I/O Physical Logical
DB2 SQL SQL Query Query Optimizer Best Query Plan Threaded Code node 0 node 1 Optimized SQL SQL node 2 Agent Agent Agent Agent Agent Agent Agent Agent Agent Prefetchers Prefetchers Prefetchers DB2 UDB SQL DB2 UD B
DB2 V8
blue blue 2001, 2001, year dimension colour dimension country dimension blue blue blue blue 2001, 2001, 2001, 2001, year dimension colour dimension country dimension Canada slice Yellow slice blue 2001, 20012, year dimension colour dimension blue country dimension blue blue 1998, 1998, year dimension colour dimension country dimension 2002 slice 2001, 2001, Cell for ( ) Each cell contains one or more blocks blue blue blue blue 1998, 1998, year dimension colour dimension 2001, 2001, country dimension
Table Keys Facts Table Many Records... Table Table
Data pages - without MDC SKU Store Date Qty Amt 101 21 04/02 1 1.50 101 21 04/02 1 1.50 101 7 04/02 2 3.00 101 7 04/01 6 8.11 SKU Store Date Qty Amt 101 7 04/02 1 1.50 101 21 04/02 3 4.10 101 7 04/01 2 3.00 rows keys Data pages - with MDC SKU Store Date Qty Amt 101 21 04/02 1 1.50 101 21 04/02 1 1.50 101 21 04/02 3 4.10 SKU Store Date Qty Amt 101 7 04/01 6 8.11 101 7 04/01 2 3.00 SKU Store Date Qty Amt 101 7 04/02 2 3.00 101 7 04/02 1 1.50
= Row
Block index on Date Block index on category Block index on store = Row
01000101011000101100100100 01101100101000110110110100 00001011001011001101001101
Customer Daily_Sales Store CustKey Period PerKey * Prodkey * Promokey * Custkey * Perkey * Storekey storekey StoreKey Promotion Product PromoKey Perkey ProdKey = Row
seconds 70 65 60 50 40 30 32.9 Store Key 20 10 Perkey 0 MDC nonmdc
seconds 50 40 40.2 30 29 Store Key 20 10 Perkey 0 MDC nonmdc
seconds 20 18.8 15 10 10.4 Store Key 5 Perkey 0 MDC nonmdc
seconds 5 5 6 4 4.5 3 Store Key 2 1 Perkey 0 MDC nonmdc
seconds 12 10.9 10 8 6 6.1 Key from dimension block index + Rids from rid index = Resulting rids to fetch 4 2 0 MDC nonmdc
seconds 25 21.2 20 15 10 Key from dimension block index Rids from rid index Resulting blocks and rids to fetch 5 6.2 + = 0 MDC nonmdc
seconds 400 370 Store *Storekey Daily_Sales * Prodkey * Promokey * Custkey * Perkey * Storekey *storekey 300 200 100 108.1 * Perkey 0 MDC nonmdc
seconds 35 30 29.5 25 product 20 15 period store daily_sales 10 5 0 7.4 MDC nonmdc
Join Query 2 Query 10 Query 14 Aggregation Aggregation Aggregation Join Join Table Table Table AST Query 2 Query 10 Query 14 Query 15 Query 20 Aggregation Join Table Table Table
MERGE INTO account AS a MERGE INTO account AS a USING (SELECT id, sum(balance) sum_balance FROM transaction USING (SELECT id, sum(balance) sum_balance FROM transactio GROUP BY id) AS t GROUP BY id) AS t ON a.id = t.id ON a.id = t.id WHEN MATCHED THEN WHEN MATCHED THEN UPDATE SET UPDATE SET balance = a.balance + t.sum_balance balance = a.balance + t.sum_balance WHEN NOT MATCHED THEN WHEN NOT MATCHED THEN INSERT (id, balance) = INSERT (id, balance) = (t.id, t.sum_balance); (t.id, t.sum_balance);
DB2 V8
Rollup Cube OLAP country state city store Month Product Hierarchical dimension Store GROUP-BY Clause GROUP BY super-groups grouping-expression super-groups ROLLUP ( grouping-expression-list ) CUBE ( grouping-expression-list ) grouping-expression-list WITH ROLLUP CUBE SELECT loc.country, loc.state, SUM(ti.amount) AS amount, COUNT(*) AS count FROM stars.transitem AS ti, stars.trans AS t, stars.loc AS loc, stars.pgroup AS pg, stars.prodline AS l WHERE ti.transid = t.transid AND ti.pgid = pg.pgid AND pg.lineid = l.lineid AND t.locid = loc.locid AND year(pdate) between 1990 and 1999 GROUP BY ROLLUP(loc.country, loc.state)
Intelligent Miner Scoring Data Analyst Intelligent Miner for Data Data Warehouse Selected Data Transformed Data Extracted Information Assimilated Information Business Analysts business intelligence tool or application Select Transform Mine Assimilate Historical Data model XML format scoring application SQL DB2 UDF DB2 Warehouse classification model
DB2 V8 VACATE PAGE RANGE: MOVE & CLEAN to make space FILL PAGE RANGE: MOVE & CLEAN to fill space free space TIME
IBM DB2 Information Integration
Significant BI Performance Improvements "We re excited about all the new features in DB2 V8.1. For example, the null and default data compression could cut our disk space requirements by 10 to 30 percent. With our warehouse currently at more than 6 TBs and growing fast, that would mean big savings in storage space. And the incremental AST update feature, which automatically updates a summary table without reloading and re-summarizing, will save our DBAs substantial work. They won t have to keep updating the summary tables every time a record in the underlying base table changes.. Gail A. Mueller, Senior Database Administrator, Aetna Significant Reductions In Disk Storage
IBM Software Group