Many modern applications uses DB2 on z/OS as database server through DRDA. Typically these applications also use dynamic SQL which makes it impossible to use the SET SQL statement. The SET statement is only available with static SQL. In this context the SYSDUMMY1 table comes into play as it may be used in a SELECT instead of SET. An example is to obtain the value of CURRENT TIMESTAMP:

SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1

In static SQL you will use SET :hostvar = CURRENT TIMESTAMP. The big difference is the elapsed time and CPU usage connected to the two statements. The SET statement is fast and inexpensive. The SELECT version is slow and expensive, because DB2 insists on reading the single row in SYSIBM.SYSDUMMY1 and that operation costs a lot of CPU and takes a lot of time compared to SET.

Fortunately DB2 version 8 introduced the possibility of avoiding reads to the table in a SELECT statement. A feature called pruning. Pruning occurs if the WHERE clause with certainty does not return any rows as in a WHERE 0=1. This trick may be used to optimize the above SELECT:

SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1 WHERE 0=1

However this does not make any sense because the above SELECT will not return any value. We can now cheat by using a column function like MAX or MIN which will force DB2 to return a NULL value when no rows are returned. And then we can use VALUE to convert NULL into a usable value:

SELECT VALUE(MIN(CURRENT TIMESTAMP), CURRENT TIMESTAMP)
FROM SYSIBM.SYSDUMMY1 WHERE 0=1

This SELECT also returns the value of CURRENT TIMESTAMP but it does not read the SYSDUMMY1 table. My measurements shows that this statement has a CPU usage of 1/3 to 1/4 of the original one. In other words the optimized statement can be executed three or four times before it has consumed the same amount of CPU as the original one. This is worth considering especially if the original statement is executed very frequently.

Please note that the trick is available for all combinations of SELECT expression FROM SYSIBM.SYSDUMMY1. You just put expression into a MIN or MAX and repeat expression in the VALUE function. By the way I have written DB2 on Z/OS on purpose because DB2 for LUW works differently with respect to the SYSDUMMY1 table.