Performance Features in DB2 V9 for z/OS – Part 3

As I talked about the last two weeks, DB2 Version 9 for z/OS has over 50+ great new performance features. Many of these really stand out and in the coming weeks I will continue to write about some that can really help you reduce the CPU demand on your system and application environments.

One of the new features in DB2 9 for z/OS is really a great data warehousing and reporting feature migrated from DB2 LUW: the RANK, DENSE_RANK and ROW_NUMBER SQL scalar functions. These OLAP type functions are great for a wide variety of data warehousing applications, moving sums, moving averages, complex calculations or processing. These OLAP functions improve performance because it drives this complex processing into the DB2 database engine.

SQL RANK Function

The first OLAP function, RANK is used to order and prioritize your data according to your specific criteria when you do not want ties or the same number on duplicate rows. RANK orders your data assigning successive sequential numbers to the rows returned from the SQL query. The ranked rows can be individual data rows or groups of data rows.

SQL RANK Example:

SELECT WORKDEPT,AVG(SALARY+BONUS)AS AVG_TOTAL_SALARY,

RANK() OVER (ORDER BY AVG(SALARY+BONUS) DESC) AS RANK_AVG_SAL

FROM BEULKE.EMPLOYEE

GROUP BY WORKDEPT

ORDER BY RANK_AVG_SAL

 

In the example above, the RANK function provides a way to order the data across two different criteria providing the user the data in an easy to understand report, each Work Department and the Average Salary within that particular department.

SQL DENSE_RANK Function

The next OLAP function DENSE_RANK provides a way to manage ties or duplicate values within your ranking of the result set rows. DENSE_RANK orders your data and assigns successive sequential numbers based on the OVER PARTITION data values found. DENSE_RANK differs from RANK because common values or ties are assigned the same number.

 

SQL DENSE RANK Example:

SELECT WORKDEPT, EMPNO, LASTNAME, FIRSTNME, EDLEVEL

DENSE_RANK() OVER (PARTITION BY WORKDEPT ORDER BY EDLEVEL DESC)

AS RANK_EDLEVEL

FROM BEULKE.EMPLOYEE

ORDER BY WORKDEPT, LASTNAME

In the example above, the DENSE_RANK function provides a way to order the data even though there a number of people with the same level of education within the same work department.

SQL ROW_NUMBER Function

The ROW_NUMBER function provides a way to add row numbers to any result set. These additional ROW_NUMBERs can be a great assistance for evaluation of applications, their processing logic and end-user web applications or service architectures.

SQL ROW_NUMBER Example:

SELECT ROW_NUMBER() OVER (ORDER BY WORKDEPT, LASTNAME) AS NUMBER,

LASTNAME, SALARY

FROM BEULKE.EMPLOYEE

ORDER BY

WORKDEPT, LASTNAME

So review your current applications and see where any histograms, moving sums, moving averages or complex calculations take place within your reports or other situations where these OLAP functions might help your performance. Since the OLAP DB2 engine features are faster than any application program process, these new powerful SQL features can easily improve your application response time while reducing your overall CPU demand.

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>