DB2 UDB's High-Function Business Intelligence in e-business

DB2 UDB's High-Function Business Intelligence in e-business

by Nagraj Alur, Peter Haas, Daniela Momiroska, Paul Read, Nicholas Summers, Virginia Totanes, Calisto Zuzarte
Released September 2002
Publisher(s): IBM Redbooks
ISBN: None

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

This IBM Redbooks publication deals with exploiting DB2 UDB’s materialized views (also known as ASTs/MQTs), statistics, analytic, and OLAP functions in e-business applications to achieve superior performance and scalability. This book is aimed at a target audience of DB2 UDB application developers, database administrators (DBAs), and independent software vendors (ISVs).

We provide an overview of DB2 UDB’s materialized views implementation, as well as guidelines for creating and tuning them for optimal performance.

We introduce key statistics, analytic, and OLAP functions, and describe their corresponding implementation in DB2 UDB with usage examples.

Finally, we describe typical business level queries that can be answered using DB2 UDB’s statistics, analytic, and OLAP functions. These business queries are categorized by industry, and describe the steps involved in resolving the query, with sample SQL and visualization of results.

Table of contents

  1. Figures
  2. Tables
  3. Examples
  4. Notices
    1. Trademarks
  5. Preface
    1. The team that wrote this redbook
    2. Notice
    3. Comments welcome
  6. Chapter 1: Business Intelligence overview
    1. e-business drivers
      1. Impact of e-business
      2. Importance of BI
    2. IBM’s BI strategy and offerings
      1. BI and analytic enhancements in DB2 UDB
      2. Advantages of BI functionality in the database engine
    3. Redbook focus
      1. Materialized views
      2. Statistics, analytic and OLAP functions
  7. Chapter 2: DB2 UDB’s materialized views
    1. Materialized view overview
      1. Materialized view motivation
      2. Materialized view concept overview
      3. Materialized view usage considerations
      4. Materialized view terminology
    2. Materialized view CREATE considerations
      1. Step 1: Create the materialized view
      2. Step 2: Populate the materialized view
      3. Step 3: Tune the materialized view
    3. Materialized view maintenance considerations
      1. Deferred refresh (1/2)
      2. Deferred refresh (2/2)
      3. Immediate refresh
    4. Loading base tables (LOAD utility)
    5. Materialized view ALTER considerations
    6. Materialized view DROP considerations
    7. Materialized view matching considerations
      1. State considerations
      2. Matching criteria considerations
      3. Matching permitted (1/3)
      4. Matching permitted (2/3)
      5. Matching permitted (3/3)
      6. Matching inhibited
    8. Materialized view design considerations
      1. Step 1: Collect queries & prioritize
      2. Step 2: Generalize local predicates to GROUP BY
      3. Step 3: Create the materialized view
      4. Step 4: Estimate materialized view size
      5. Step 5: Verify query routes to “empty” the materialized view
      6. Step 6: Consolidate materialized views
      7. Step 7: Introduce cost issues into materialized view routing
      8. Step 8: Estimate performance gains
      9. Step 9: Load the materialized views with production data
      10. Generalizing local predicates application example
    9. Materialized view tuning considerations
    10. Refresh optimization
    11. Materialized view limitations
      1. REFRESH DEFERRED and REFRESH IMMEDIATE
      2. REFRESH IMMEDIATE and queries with staging table
    12. Replicated tables in nodegroups
  8. Chapter 3: DB2 UDB’s statistics, analytic, and OLAP functions
    1. DB2 UDB’s statistics, analytic, and OLAP functions
    2. Statistics and analytic functions
      1. AVG
      2. CORRELATION
      3. COUNT
      4. COUNT_BIG
      5. COVARIANCE
      6. MAX
      7. MIN
      8. RAND
      9. STDDEV
      10. SUM
      11. VARIANCE
      12. Regression functions
      13. COVAR, CORR, VAR, STDDEV, and regression examples (1/2)
      14. COVAR, CORR, VAR, STDDEV, and regression examples (2/2)
    3. OLAP functions
      1. Ranking, numbering and aggregation functions (1/2)
      2. Ranking, numbering and aggregation functions (2/2)
      3. GROUPING capabilities ROLLUP & CUBE
      4. Ranking, numbering, aggregation examples (1/3)
      5. Ranking, numbering, aggregation examples (2/3)
      6. Ranking, numbering, aggregation examples (3/3)
      7. GROUPING, GROUP BY, ROLLUP and CUBE examples (1/3)
      8. GROUPING, GROUP BY, ROLLUP and CUBE examples (2/3)
      9. GROUPING, GROUP BY, ROLLUP and CUBE examples (3/3)
  9. Chapter 4: Statistics, analytic, OLAP functions in business scenarios
    1. Introduction
      1. Using sample data
      2. Sampling and aggregation example
    2. Retail
      1. Present annual sales by region and city
      2. Provide total quarterly and cumulative sales revenues by year
      3. List the top 5 sales persons by region this year
      4. Compare and rank the sales results by state and country
      5. Determine relationships between product purchases
      6. Determine the most profitable items and where they are sold
      7. Identify store sales revenues noticeably different from average
    3. Finance
      1. Identify the most profitable customers
      2. Identify the profile of transactions concluded recently
      3. Identify target groups for a campaign
      4. Evaluate effectiveness of a marketing campaign (1/2)
      5. Evaluate effectiveness of a marketing campaign (2/2)
      6. Identify potential fraud situations for investigation
      7. Plot monthly stock prices movement with percentage change
      8. Plot the average weekly stock price in September
      9. Project growth rates of Web hits for capacity planning purposes
      10. Relate sales revenues to advertising budget expenditures
    4. Sports
      1. For a given sporting event
      2. Seed the players at Wimbledon
  10. Appendix A: Introduction to statistics and analytic concepts
    1. Statistics and analytic concepts
      1. Variance
      2. Standard deviation
      3. Covariance
      4. Correlation
      5. Regression
      6. Hypothesis testing
      7. HAT diagonal
      8. Wilcoxon rank sum test
      9. Chi-Squared test
      10. Interpolation
      11. Extrapolation
      12. Probability
      13. Sampling
      14. Transposition
      15. Histograms
  11. Appendix B: Tables used in the examples
    1. DDL of tables
  12. Appendix C: Materialized view syntax elements
    1. Materialized view main syntax elements
  13. Related publications
    1. IBM Redbooks
      1. Other resources
    2. Referenced Web sites
    3. How to get IBM Redbooks
      1. IBM Redbooks collections
  14. Index (1/2)
  15. Index (2/2)
  16. Back cover

Product information

  • Title: DB2 UDB's High-Function Business Intelligence in e-business
  • Author(s): Nagraj Alur, Peter Haas, Daniela Momiroska, Paul Read, Nicholas Summers, Virginia Totanes, Calisto Zuzarte
  • Release date: September 2002
  • Publisher(s): IBM Redbooks
  • ISBN: None