Level: Introductory Pat Moffatt (pmoffatt@ca.ibm.com), Information Management Program Manager, IBM Academic Initiative, IBM
Bruce Creighton (bcreight@ca.ibm.com), Skills Segment Planner, IBM
Jessica Cao , Training Tools Developer, IBM
17 Aug 2006 This tutorial describes how to write queries that require basic data analysis. Many of the queries contain sequential calculations, or calculations that operate on an ordered set of rows--queries frequently encountered during business analysis. On-Line Analytical Processing (OLAP) functions provide the ability to return ranking, row numbering and existing column function information as a scalar value in a query result. This tutorial is Part 4 of the SQL & XQuery tutorial for IBM® DB2®
series.
Prerequisites
This tutorial was written for DB2 Express-C 9 for UNIX®, Linux® and Windows® (formerly known as Viper). You should be familiar with DB2 and databases. It is preferable that you view tutorials one, two, and three in this series before attempting this tutorial, part four.
System requirements
To use this tutorial to the fullest, you should have IBM DB2 9 installed. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edtion and provides a solid base to build and deploy applications. You will also need to install the Aroma database, which can be downloaded in the tutorial.
Duration
Under 2 hours
Formats html, pdf
About this tutorial
This tutorial describes how to write queries that require some kind of data analysis. Many of the queries contain sequential calculations, or calculations that operate on an ordered set of rows, queries frequently encountered during business analysis. For example:
- What is the cumulative total (or running sum) by month?
- What is the moving average by week?
- How do monthly sales figures rank with one another?
- What is the ratio of current month sales to annual sales?
IBM DB2 provides an efficient way to answer these kinds of questions using standard SQL OLAP functions that are included as part of DB2 9. On-Line Analytical Processing (OLAP) functions provide the ability to return ranking, row numbering, and existing column function information as a scalar value in a query result. An OLAP function can be included in expressions in a select-list or the ORDER BY clause of a select-statement.
This tutorial contains a series of examples with the business query and associated syntax presented in each case.
| 
