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