Book Image

IBM DB2 9.7 Advanced Application Developer Cookbook

Book Image

IBM DB2 9.7 Advanced Application Developer Cookbook

Overview of this book

With lots of new features, DB2 9.7 delivers one the best relational database systems in the market. DB2 pureXML optimizes Web 2.0 and SOA applications. DB2 LUW database software offers industry leading performance, scale, and reliability on your choice of platform on various Linux distributions, leading Unix Systems like AIX, HP-UX and Solaris and MS Windows platforms. This DB2 9.7 Advanced Application Developer Cookbook will provide an in-depth quick reference during any application's design and development. This practical cookbook focuses on advanced application development areas that include performance tips and the most useful DB2 features that help in designing high quality applications. This book dives deep into tips and tricks for optimized application performance. With this book you will learn how to use various DB2 features in database applications in an interactive way.
Table of Contents (15 chapters)
IBM DB2 9.7 Advanced Application Developer Cookbook
Credits
About the Authors
About the Reviewers
www.PacktPub.com
Preface

Working with OLAP functions


Consider a scenario where we have a sales table that holds sales data for different regions, and we want to rank all regions based on their total sales. We might also want to rank them based on their contribution to the total profit. This can certainly be achieved by writing complex SQL statements. Let's see what DB2 offers in such situations. DB2 provides a rich set of Online Analytical Processing (OLAP) functions that provide us with the ability to perform complex ordering and aggregation of result sets, in a very simple manner. OLAP functions do not perform any kind of filtering, and act on every row in the result set. They are always applied on a window. We can also use scalar functions and aggregate functions over OLAP windows. In this recipe, we will discuss how we can use the different OLAP functions provided by DB2.

Getting ready

This recipe uses the T_SALES table as an example. The sample table and data can be created by the following set of SQL statements...