Using DB2 UDB OLAP functions

Some practical tips for using OLAP functions

The online analytical Processing,olap function is very flexible and powerful. By using these functions, you can find a simple solution for some problems that are either to iterate over one or more cursors, or to be recursive. In some other cases, it is much easier to use OLAP functions or auxiliary tables than to not use them when writing queries.

Instead of describing OLAP functions, this article describes some of the real-life situations in which a simple solution is often achieved by using OLAP functions.

To generate a secondary table using consecutive numbers or dates

The secondary table does not contain user data. Instead, these tables are used to simplify the writing of queries. We will use two secondary tables:

A sequential table, a list of consecutive integers, starting at 1 and ending with a maximum number.

A calendar table that is a list of consecutive dates between the start and end dates.

In this chapter, we'll learn how to populate a secondary table with OLAP functions.

Note: the "SQL for Smarties" book written by Joe Celko discusses the secondary table in detail.

Let's create and populate a sequential table. Use an OLAP function row_number () to provide consecutive numbers (consecutive number) for each row in the result set, like this:

SELECT ROW_NUMBER() OVER(), TABNAME FROM SYSCAT.TABLES WHERE TABSCHEMA="DB2ADMIN"
1TABNAME
-------------------------------------------
1 CONSECUTIVE_NUMS
2 DATES
3 EXPLAIN_ARGUMENT
4 EXPLAIN_INSTANCE
5 EXPLAIN_OBJECT
6 EXPLAIN_OPERATOR
7 EXPLAIN_PREDICATE
8 EXPLAIN_STATEMENT
9 EXPLAIN_STREAM
10 SALES_DETAIL
11 SALES_DETAIL_CLS
11 record(s) selected.

Note that consecutive numbers are provided even if no columns are selected. For example, let's populate a table with successive numbers starting at 1. The method is as follows:

CREATE TABLE NUMBER_SEQ(NUM INT NOT NULL);
INSERT INTO NUMBER_SEQ SELECT ROW_NUMBER() OVER() FROM
SYSCAT.COLUMNS;