© Copyright IBM Corp. 2002 vii
Figures
1-1 Changing business environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
1-2 Business critical processes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
1-3 e-business impact . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
1-4 Intelligent e-business DataBase Associates International copyright . . . 10
1-5 Business Intelligence functionality . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
2-1 Materialized view overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
2-2 CREATE materialized view overview. . . . . . . . . . . . . . . . . . . . . . . . . . . 21
2-3 Deferred refresh. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
2-4 Incremental refresh with staging table . . . . . . . . . . . . . . . . . . . . . . . . . . 30
2-5 Immediate refresh using incremental update. . . . . . . . . . . . . . . . . . . . . 35
2-6 LOAD application sample . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
2-7 Materialized view optimization flow . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
2-8 Matching columns, predicates, and expressions . . . . . . . . . . . . . . . . . . 46
2-9 Matching GROUP BY and aggregate functions. . . . . . . . . . . . . . . . . . . 52
2-10 Overview of the design of REFRESH DEFERRED materialized views . 62
2-11 Get snapshot for dynamic SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63
2-12 Sapient star schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70
2-13 Sapient graphical user interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
2-14 Multi-query optimization in REFRESH TABLE with materialized views. 91
2-15 Materialized view limitation categories. . . . . . . . . . . . . . . . . . . . . . . . . . 92
2-16 Collocation in partitioned database environment . . . . . . . . . . . . . . . . . . 96
3-1 D11 Employee salary & bonus. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
3-2 Linear regression . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116
3-3 Ranking, numbering and aggregate functions . . . . . . . . . . . . . . . . . . . 119
3-4 Window partition and window order clauses . . . . . . . . . . . . . . . . . . . . 120
3-5 Window aggregation group clause. . . . . . . . . . . . . . . . . . . . . . . . . . . . 121
3-6 Windowing relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124
3-7 GROUP BY clause. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125
3-8 Super Groups ROLLUP & CUBE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126
3-9 Employee rank by total salary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130
3-10 Employee DENSE_RANK by total salary . . . . . . . . . . . . . . . . . . . . . . 131
3-11 RANK, DENSE_RANK and ROW_NUMBER comparison. . . . . . . . . . 132
3-12 PARTITION BY window results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133
3-13 Salary as a percentage of department total salary . . . . . . . . . . . . . . . 134
3-14 Five day smoothing of IBM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135
3-15 IBM five day moving average. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136
3-16 Seven calendar day moving average. . . . . . . . . . . . . . . . . . . . . . . . . . 137
3-17 Grouping result . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139

Get DB2 UDB's High-Function Business Intelligence in e-business now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.