Many years ago it became possible to perform OUTER JOINS in DB2, but I have the impression that the possibilities offered through OUTER JOINS are seldom used. For those of you who use OUTER JOINS, I hope this tip will shed some light on the way OUTER JOINS work and it may also improve performance. The idea behind the tip was originally presented to me by Susan Lawson, one of the leading DB2 experts.

As usual I will start by showing you an example:

SELECT TS.DBNAME, TS.NAME AS TSNAME, TS.NTABLES
     , VALUE(TB.CREATOR, '*') AS CREATOR
     , VALUE(TB.NAME, 'NO TABLES') AS TBNAME
FROM SYSIBM.SYSTABLESPACE TS
LEFT OUTER JOIN SYSIBM.SYSTABLES TB
  ON  TS.DBNAME = TB.DBNAME
  AND TS.NAME = TB.TSNAME
  AND TS.NTABLES > 0
WHERE TS.DBNAME LIKE 'DSN%'

This SQL statement should be able to run on any DB2 mainframe installation. The question is: what is it doing and what is the point? The statement finds all tables in all tablespaces located in databases having a name starting with DSN. If the tablespace does not contain any tables (this happens sometimes) then the CREATOR column is assigned the value '*' and the TBNAME column is assigned the value 'NO TABLES'. In a traditional join the tablespaces without tables would not have been part of the result set, but this is of course the main idea of the OUTER JOIN.

The point is the join criteria which is extended with AND TS.NTABLES > 0. This join criteria does not look like a normal join criteria where you match two columns from different tables. DB2 also evaluates this criteria. If it is not true the two tables are not joined and DB2 only returns values from the "left" table (SYSIBM.SYSTABLESPACES). The good thing is that DB2 will not access the "right" table if NTABLES equals 0. The SQL statement will return exactly the same result set without this extra criteria, but it will access SYSTABLES for rows having NTABLES equal to 0.

Now you can start to locate SQL statements in your installation using OUTER JOINS having columns in one table which contains information about whether data is present or not in the joined table. Such OUTER JOINS may be optimized using the method in this tip. You may also introduce columns to support the method if they are not available. This will of course require program and table changes, but in critical applications they might prove to be worth the effort.