Articles & Content
Topic: 2010 EMEA
Subtopic: Cross Platform DB2 for z/OS & LUW
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.
This file is being provided by IDUG. We would encourage you to join IDUG to get full access to all of our files and resources. Joining IDUG is FREE and signing up is simple. Click here to join! or login!Click to Download
NOTE: These are only open to members of IDUG. If you are not a member, please CLICK HERE for more information.