Articles & Content


Six puzzles in SQL with Hierarchies and OLAP functions (E12)

Topic: Cross Platform DB2 for z/OS & LUW

Subtopic: 2010

DATE: 2010-11-10 (14:15 - 15:15)
SPEAKERS: Michael Kamfonas (InfoKarta Inc.)

SQL is a powerful and often underutilized tool that can be used to solve some nagging problems easily and efficiently. We will spend 10 minutes on each of six common problems and their creative solution using SQL. We will demonstrate the underlying set-oriented ways of thinking and illustrate relevant SQL patterns and syntactical elements. We will also discuss applicability, performance and limitations. The six themes are:1. Hierarchies beyond recursion a. Topological ordering of a hierarchies (creating the boundary numbers) b. Variations on ancestor/descendent queries c. Data security from nested allow/disallow rules2. Creative use of OLAP functions a. Gap detection and contiguous counts (RANK, DENSERANK and ROWNUMBER) b. Versioning (LAG/LEAD) c. Inventory aging and cumulative SUMs

EXP. LEVEL: Intermediate,Advanced


Understand the technique of Topological Ordering (Nested Sets) for hierarchy traversal.

Understand how to use OLAP functions

Cultivate set-oriented thinking

Practical ideas where the techniques can be applied

Understand technique limitations, performance plus-minus and tips for tuning.

Click Here to Download

NOTE: These are only open to members of IDUG. If you are not a member, please CLICK HERE for more information.