V8_BI.PPT [只读]

Size: px

Start display at page:

Download "V8_BI.PPT [只读]"

炬鉴坐樊
5 years ago
Views:

1 IBM Software Group DB2 V8 IBM

4 OLTP OLAP External Extract Integrate Transform Maintain Data Warehouse Reporting Legacy Data Mining

6 DB2 UDB: DB2 DB2 DB2 DB2 DB2 DB2

7 DB2 UDB EEE on PSeries 500GB 1TB > 95% 256 concurrent users ibm.com/software/data/pubs/papers/#eeescale 24 way 24 way 500GB 500GB Database Build Query Performance Time (mins) GB 1 TB Time (s) GB 1 TB Load Create Index Runstats Build ASTs X-Axis 0 Query

8 DB2 Universal Database

DB2 UDB Everyplace PalmOS Win CE EPOC-32 Linux

z/os DB2 for VM and VSE DB2 UDB for OS/400 DB2

Solaris Linux, Linux/390 NUMA-Q Personal Win 95,

Linux AIX Solaris HP-UX OS/2 Universal Access

9 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

10 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

11 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

12 DB2 V8

13 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

14 Table Keys Facts Table Many Records... Table Table

15 Data pages - without MDC SKU Store Date Qty Amt / / / / SKU Store Date Qty Amt / / / rows keys Data pages - with MDC SKU Store Date Qty Amt / / / SKU Store Date Qty Amt / / SKU Store Date Qty Amt / /

16 = Row

17 Block index on Date Block index on category Block index on store = Row

19 Customer Daily_Sales Store CustKey Period PerKey * Prodkey * Promokey * Custkey * Perkey * Storekey storekey StoreKey Promotion Product PromoKey Perkey ProdKey = Row

20 seconds Store Key Perkey 0 MDC nonmdc

21 seconds Store Key Perkey 0 MDC nonmdc

22 seconds Store Key 5 Perkey 0 MDC nonmdc

23 seconds Store Key 2 1 Perkey 0 MDC nonmdc

24 seconds Key from dimension block index + Rids from rid index = Resulting rids to fetch MDC nonmdc

25 seconds Key from dimension block index Rids from rid index Resulting blocks and rids to fetch = 0 MDC nonmdc

26 seconds Store *Storekey Daily_Sales * Prodkey * Promokey * Custkey * Perkey * Storekey *storekey * Perkey 0 MDC nonmdc

27 seconds product period store daily_sales MDC nonmdc

29 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

31 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);

32 DB2 V8

33 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)

34 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

35 DB2 V8 VACATE PAGE RANGE: MOVE & CLEAN to make space FILL PAGE RANGE: MOVE & CLEAN to fill space free space TIME

36 IBM DB2 Information Integration

39 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

40 IBM Software Group

學科 100% ( 為單複選題, 每題 2.5 分, 共 100 分 ) 1. 請參閱附圖作答 : (A) 選項 A (B) 選項 B (C) 選項 C (D) 選項 D Ans:D 2. 下列對於資料庫正規化 (Normalization) 的敘

學科 100% ( 為單複選題, 每題 2.5 分, 共 100 分 ) 1. 請參閱附圖作答 : (A) 選項 A (B) 選項 B (C) 選項 C (D) 選項 D Ans:D 2. 下列對於資料庫正規化 (Normalization) 的敘 ITE 資訊專業人員鑑定資料庫系統開發與設計實務試卷編號 :IDS101 注意事項一本測驗為單面印刷試題, 共計十三頁第二至十三頁為四十道學科試題, 測驗時間 90 分鐘 : 每題 2.5 分, 總測驗時間為 90 分鐘二執行 CSF 測驗系統 -Client