Treasury Of New DB2 6.1 Features, Part 1: Query Enhancements

Published: February 4, 2009

by Michael Sansoterra

Once again I am happy to report that IBM has provided us with a wealth of enhancements to DB2 in this monumental V6R1 release. Incidentally, in case you haven't heard, IBM is now calling our favorite database by the name DB2 for i (formerly known as DB2/400 and DB2 UDB for iSeries) and our favorite OS is called IBM i version 6.1. Now without further ado, I'll start to rattle off some query enhancements offered in this release.

VALUES in SELECT

The VALUES clause can now be placed within the FROM clause of a SELECT statement or as a derived table result in a FULLSELECT. This means that you can hard-code one or more rows of values to be returned as a table within an SQL statement. Here is a sample SELECT that returns one column with five rows:

Select *
  From (Values(1),(2),(3),(4),(5)) As Counter_Table (RowNo)

The statement returns the following results:

The VALUES clause can now be used in a SELECT similar to how it is used with an INSERT statement. Each row is enclosed in parenthesis and can contain one or more column values. For instance, for a set of values with m rows and n columns the VALUES clause looks like this:

Values(r1c1,r1c2…r1cn),(r2c1,r2c2…r2cn)…(rmc1,rmc2…rmcn)

Of course the values given in each of the rows should be compatible in number and data type.

In the sample above, the portion, As Counter_Table (RowNo), is known as the correlation clause and is used to assign a correlation name and column name(s) to the values specified in the VALUES clause. This is great because it will alleviate the need to create common table expressions (CTEs), work tables, or user-defined table functions to do some relatively trivial tasks such as the counter table example shown above.

Did you ever have the need to artificially duplicate a query row, say, three times? I've had to do it for printing labels. Here is an easy way to make SQL duplicate the rows in the vendor address table:

Select * 
  From VendorAddress,
      (Values(1),(2),(3)) As Repeat (RowNumber)

Using the VALUES clause should also effectively eliminate the need to write queries against single row tables such as QSYS2/QSQPTABL and SYSIBM/SYSDUMMY1. This, in turn, should speed up dynamic queries because the optimizer will no longer have to go through the process of validating, opening, and closing an actual table.

For another example of how to use this, think of the GUI world where it is common to populate a drop-down list that allows a user to select a particular customer. Often developers need to add an extra value to the customer list to allow the selection of all customers. A quick way to add this extra row is to use the values clause:

Select CustomerId,CompanyName 
  From Customers 
Union All 
Select * 
  From (Values('All','All Customers')) X(CustomerId,CompanyName)
Order By CustomerId

This example isn't much better than using constants. But, we could even enhance the above query to include two additional hard-coded options: one for all customers; and one for Web-only customers. We can accomplish this by adding a second row:

Select CustomerId,CompanyName 
 From Customers 
Union All 
Select * 
 From (Values('All','All Customers'),
       ('Web','Web Customers')) X(CustomerId,CompanyName)
Order By CustomerId

Finally, while each sample shown here simply contained constants, the VALUES clause can contain expressions including built-in and user-defined functions. Here is a sample CREATE VIEW that illustrates this concept:

Create View xxxxx/SystemValues As
(Select RowId,Rand() As RandomNo,
      Current_Server As Server,Current_Timestamp As Now
   From (Values(1)) As Row(RowId))

Data Change Table Reference

A new "table reference" option called a "data change table reference" has been added to the FROM clause. This clause will allow a SELECT to immediately query the results of an INSERT operation. To illustrate, consider a scenario where we need to populate a reorder table with a list of products that have fallen below the desired safety stock level.

Create Table ReorderProducts
(RowId    Int As Identity Not Null,
  ProductId  Int       Not Null,
  ProductName VarChar(50)   Not Null,
  QtyToOrder Int       Not Null)

Populating the order table requires a simple query:

Insert Into ReorderProducts
(ProductId,ProductName,QtyToOrder)
Select ProductId,ProductName,
      ReorderLevel-UnitsInStock As OrderQty
  From Northwind/Products
  Where UnitsInstock<ReorderLevel

However, say the application running this insert needs to be aware of the identity values for the inserted rows. Retrieving these values is now simple using the "data change table reference," which is identified for the FROM clause with the keywords "Final Table" or "New Table":

Select * 
 From Final Table ( 
Insert Into ReorderProducts
(ProductId,ProductName,QtyToOrder)
Select ProductId,ProductName, 
    ReorderLevel-UnitsInStock As OrderQty 
 From Northwind/Products 
 Where UnitsInstock<ReorderLevel
 ) As Reorders

All that is required is to nest the INSERT statement within a SELECT. It's that simple. Now this single statement performs a double duty: it inserts the rows and it returns the inserted rows to the caller. Of course, depending on the host application (such as embedded SQL), you may need to also use DECLARE CURSOR for the SELECT in order to read the results.

I think this will be particularly useful when developing and debugging an INSERT statement because it will allow the developer to review exactly what rows are being inserted without having to requery the insertion table after the fact. Right now, I use at least two statements (or more) to accomplish this same task. In particular this will ease the pain of retrieving identity values when inserting multiple rows into a table with an IDENTITY column or a table or view with a data modifying insert trigger.

Hopefully IBM will allow a similar syntax for UPDATEs and DELETEs in a future release. For example, if would be useful to place all rows from a DELETE statement in a deletion log table (or to show to a user in a grid) without having to code multiple statements to do it.

FETCH FIRST and ORDER BY in a Subselect

The ORDER BY and the FETCH FIRST n ROWS ONLY clauses can be placed in any subselect. For instance, if we want to get the customer master record for the customer with the greatest sales, the following subquery will return one customer with the most sales:

Select *
 From Customers 
 Where CustomerId= 
  (Select CustomerId 
    From Orders OH 
    Join OrderDetails OD On OD.OrderId=OH.OrderId 
  Group By CustomerId
  Order By Sum(UnitPrice * Quantity * (1 - Discount)) Desc
  Fetch First 1 Row Only)

Before having the ability to limit a subquery to a single row as in the above example, finding the customer with the greatest sales would've required a second statement, a complex common table expression, or a nested SELECT.

FULL OUTER JOIN

Back in '07, Ted Holt "prophesied" that IBM would add the FULL OUTER JOIN capability to DB2 for i and he gives a good overview of this join type here. In a nutshell, a FULL OUTER JOIN allows a developer to easily write a query that combines a LEFT OUTER JOIN with the union of a RIGHT EXCEPTION JOIN in a single statement.

For example, conceptually say we want to join table 1 (T1) and table 2 (T2) on a common key such as ProductId. Further, if T1 has rows with no matching rows in T2, then we want to return values from T1 and allow T2's columns to appear as NULLs (LEFT OUTER JOIN). Moreover, if T2 has rows with no matching rows in T1 then we also want to return these values from T2 and let the columns from T1 appear as NULLs (RIGHT EXCEPTION JOIN). Now let's look at a concrete example of how we can use this.

In this example, a FULL OUTER JOIN (abbreviated FULL JOIN) is used to assist in finding all customers without a purchase for a specific time frame. It also identifies all products without a single sale for the same time frame. In other words this report would be considered the "stagnant customers and products" report.

When reviewing this SQL, remember that T1 is actually a query that joins the customer master with the order header and order detail tables. T1 includes all customers and additionally identifies customers who have ordered products for the requested time period. T2 is represented by the Products table because we want to know which products have not been purchased for the same period (May through July of 1996). The FULL JOIN will only join rows from the PRODUCTS table that have not been placed on order for the given time period (a RIGHT EXCEPTION JOIN).

  Select C.CustomerId,C.CompanyName, 
     P.ProductId, P.ProductName 
   From Northwind/Customers C 
Left Join Northwind/Orders O On O.CustomerId=C.CustomerId 
              And O.OrderDate Between '1996-05-01'
                        And '1996-07-31'
Left Join Northwind/OrderDetails D On D.OrderId=O.OrderId 
Full Join
    Northwind/Products P On P.ProductId=D.ProductId 
 Where P.ProductId Is Null Or C.CustomerId Is Null
Order By CompanyName,ProductName

The abridged query results look like the following chart that displays customers without orders on the left and products without sales on the right:

Normally a FULL OUTER JOIN returns unmatched rows from the primary table (T1), joined rows with matching keys (T1 and T2), and unmatched rows from the secondary table (T2). However, the WHERE clause in the above example actually eliminates the matching rows because we're only looking for customers without orders and products without orders. So technically, this query is using the FULL OUTER JOIN to achieve the results of a LEFT EXCEPTION JOIN and a RIGHT EXCEPTION JOIN combined into a single result set.

Before the FULL OUTER JOIN was available, I'd have written the above query like this:

With CustomerOrders As (
  Select C.CustomerId,C.CompanyName,
     D.ProductId
   From Northwind.Customers C 
Left Join Northwind.Orders O On O.CustomerId=C.CustomerId 
              And O.OrderDate Between '1996-05-01'
                        And '1996-07-31'
Left Join Northwind.OrderDetails D On D.OrderId=O.OrderId
)
/* Show all customers without orders */
Select CustomerId,CompanyName,
    ProductId,Cast(Null As Char(1)) ProductName
 From CustomerOrders
 Where ProductId Is Null
Union All
/* Show all products without orders */
Select Cast(Null As Char(1)) CustomerId,Cast(Null As Char(1)) CompanyName,
    P.ProductID,P.ProductName
 From Northwind.Products P
 Left Exception Join CustomerOrders 
    On P.ProductId=CustomerOrders.ProductId
Order By CompanyName,ProductName

Wrap Up

Conceptually, there's really been "nothing new under the sun" added to DB2 in terms of brand new functionality. However, the good news is IBM is tweaking SQL to pack more power into single, and sometimes simpler, statements. Anything that can make the developer's job easier is certainly welcome. Keep coming back in the coming weeks as Four Hundred Guru continues to enumerate the improvements IBM has given its DB2 for i community.

Michael Sansoterra is a programmer/analyst for i3 Business Solutions, an IT services firm based in Grand Rapids, Michigan. Send your questions or comments for Mike to Ted Holt via the IT Jungle Contact page.

RELATED STORY

Missing In Action: The Full Outer Join

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot