DB2 version 8 has been in use for several years and DB2 9 is beginning to take over. With each new version of DB2 new features and functions are introduced. It is hard to overlook all the new possibilities and especially find those bits and pieces that may be useful.

Recently I worked with a PL/I program where I had to do some date math. PL/I does not have any good features for date math so I ended up with the following solution:

EXEC SQL
  SELECT DAYS(CURRENT DATE) - DAYS(:myDate)
  INTO :dayDiff
  FROM SYSIBM.SYSDUMMY1
  ;

Shortly after I read somewhere that it is no good idea to use SYSDUMMY1 for purposes like the above. SQL statements referencing SYSDUMMY1 is treated just like any other SQL statement using a DB2 table. DB2 must go and fetch the row in SYSIBM.SYSDUMMY1 to perform the above SQL statement. Then I thought there must be other means of acheiving the same result. And of course there is. In DB2 version 7 the functionality of the SET statement was enhanced a lot. It is now possible to perform any DB2 expression and even more expressions in the same statement. I changed the above SQL statement to:

EXEC SQL
  SET :dayDiff = SELECT DAYS(CURRENT DATE) - DAYS(:myDate)
  ;

This is far more readable and efficient than the SELECT statement. With this SET enhancement it becomes easier and quicker to use the vast number of functions that DB2 has been equipped with during the last 20 years. But remember to use SET carefully. Any SQL statement is shipped to the DB2 address spaces for processing and therefore it is much cheaper in performance to use the native functions of your programming language than using the equivalent DB2 expression. In other words use should use the facilities in your programming language before turning to DB2.

The full description of SET is available in SQL reference. Please notice the possibility of executing more than one assignment in just one SQL statement.