Db2 Dummy Table Replacement Simplified

I’ve written in the past about the performance advantages of using tableless queries instead of the Db2 dummy table – SYSDUMMY1. As our IBM Expert Labs team has helped clients convert their queries from using SYSDUMMY1 to tableless syntax, we’ve found that some conversions are easier than others.

Simple SYSDUMMY1 queries like this one are easy to change to use a VALUES statement and there’s very little risk of breaking the query. 

  SELECT CURRENT DATE FROM sysibm.sysdummy1

 

The conversions get more interesting when SYSDUMMY1 is referenced in a query along with “real” tables that store your business data. Here’s an example of that type of query:

SELECT 1 INTO :hv1 FROM sysibm.sysdummy1
     WHERE EXISTS (SELECT 1 FROM table1 

                    WHERE col1=:hv2 AND col2=:hv3)

 

While the following converted version of this query is not super complex, the number of required changes to eliminate the usage of SYSDUMMY1 does increase the risk of the query modifications accidentally causing the query to return different results.

WITH return1 (rtnval) AS (VALUES 1)
SELECT 1 INTO :hv1 FROM return1
  WHERE EXISTS (SELECT 1 FROM table1 

                  WHERE col1=:hv2 AND col2=:hv3)

 

Even though the risk of breaking the query is still low, I’ve recently started to recommend a simpler conversion method. This method involves creating an SQL view and replacing the SYSDUMMY1 reference with the name of your view.

 

First, let’s look at the definition of this view. Effectively this view uses the VALUES statement to return the same column value and column name as Db2’s SYSDUMMY1 table. The earlier conversion examples don’t reference the column value or column name, but your application may have SYSDUMMY1 queries that are dependent on the column value or name.

CREATE VIEW mylib.mydummy1 AS
  WITH returny (ibmreqd) AS (VALUES 'Y')
  SELECT ibmreqd FROM returny

 

Now that a view is in place providing a logical equivalent of SYSDUMMY1 – converting a query from SYSDUMMY1 to use tableless syntax is as simple as changing the FROM clause. Essentially, all that’s needed is the Find & Replace option in your code editor is all that’s needed as demonstrated in thew new

SELECT 1 INTO :hv1 FROM mylib.mydummy1 
  WHERE EXISTS (SELECT 1 FROM table1 

                  WHERE col1=:hv2 AND col2=:hv3)

 

I think the only downside of using this view-based approach is that one may overlook whether the SYSDUMMY1 reference is even needed. I’ve recently worked with clients that used SYSDUMMY1 in the following manner. 

SELECT col2 FROM table1 
  WHERE col1 = (SELECT someUDF(:hv1) FROM sysibm.sysdummy1)

 

Not sure why the programmers thought the Db2 dummy table needed to be used – maybe the user-defined function reference. However, a tableless query is also not needed in this case. The better correction of the query would be to change the query to the following:

SELECT col2 FROM table1 WHERE col1 = someUDF(:hv1)

Hopefully, you are able to see how this view-based approach simplifies the conversion to tableless query syntax and eliminates the risk of accidentally breaking your query.