Blog
DB2utor

Categories

April 08, 2008

DB2 Enhancements Let SQL Do More of the Work

The SQL enhancements that have been delivered in recent DB2 releases is amazing. To answer some business questions, programs must be written to process multiple SQL statements. However, with each release of DB2, more and more processing can be pushed into SQL, removing the need to write programs. I see the new FETCH FIRST n ROWS ONLY and the support of ORDER BY within subselect or fullselect helping in this area.

Using the sample tables that are delivered with DB2, let's say you want the names of departments in your organization that have at least one of your company's five highest-paid employees. To answer this question prior to DB2 9, you'd write a program and process multiple SQL statements using the following steps.

Step 1: Get a list of departments that have the five highest-paid employees:

SELECT E.WORKDEPT
FROM DSN8810.EMP E
ORDER BY E.SALARY
FETCH FIRST 5 ROWS ONLY

That SQL statement produces these results:

E11
E11
E11
D21
E11

Since I asked for the department names, I need to use this list to retrieve them. But because I only want the name listed once, I must make sure my program doesn't return names multiple times. So here's Step 2:

SELECT DEPTNAME
FROM DSN8810.DEPT
WHERE DEPTNO = :host-deptno
WITH UR #

With DB2 9, however, not only can I list the unique department names in one SQL statement, I can order the names for reporting purposes. So this SQL statement saves me from having to write a program to process this and allows me to use reporting tools like QMF to quickly get answers to my business questions:

SELECT D. DEPTNAME
FROM DSN8910.DEPT D
WHERE DEPTNO IN ( SELECT E.WORKDEPT
FROM DSN8910.EMP E
ORDER BY E.SALARY
FETCH FIRST 5 ROWS ONLY)
ORDER BY D.DEPTNAME

Notice the use of ORDER BY in the subselect and the presence of ORDER BY in the final result set.

I'm sorting the employee list in order of salary in the subselect and then telling DB2 to stop processing once it returns the first five rows. Then I'm sorting the results from the department table by department name.

From that, here's the list of departments with at least one of the company's top five paid employees.

ADMINISTRATION SYSTEMS
OPERATIONS

Hopefully this simple example shows you SQL's power and how DB2 9 improvements can reduce the need to write costly programs to answer simple business questions.