In modern data modeling it is common practise to include versioning or history. There are different ways of doing this giving different results in implementation. One thing is certain: the SQL statements against these data models are pretty complicated and involves the use of correlated subselects.

I have discovered that such correlated subselectes may be optimized without regard of DB2 version as long as we are dealing with version 2 or later. Let me show you an example that you can use on your own installation:

SELECT DBNAME, TSNAME, ICDATE, ICTIME
FROM SYSIBM.SYSCOPY Y
WHERE Y.DBNAME = 'DSNDB06'
  AND Y.TIMESTAMP =
( SELECT MAX(S.TIMESTAMP)
  FROM SYSIBM.SYSCOPY S
  WHERE C.DBNAME = S.DBNAME
    AND C.TSNAME = S.TSNAME
    AND C.DSNUM = S.DSNUM
    AND ICTYPE = 'F'
)

This SQL statement will list the date and time when the last full image copy was taken of every single tablespace belonging to the DB2 catalogue. If the result is too boring in your installation then try another database. This SQL statement is a typical example of how you find the latest version of something in many data model implementations. In the example I am only interested in listing full image copies. In the SYSCOPY table there are a lot of other rows with another value than F in ICTYPE. Therefore it will be a very good idea to include this predicate in the outer select as well. This is what this tip is about.

The general application of the tip is that predicates in the subselect should be included in the outer select if they are not part of the join predicates between the outer select and the subselect. Please note that if the predicate ICTYPE = 'F' is used only in the outer select then the SQL statement will not return the expected result (unless ICTYPE is F for all involved rows). Experiences like this is what makes many people move predicates from the outer select into the subselect instead of copying it. A copy gives the fastest result.

And why is that so? Because the subselect will only be executed for the rows that fulfill the predicates in the outer select. For each row that you can exclude in the outer select, DB2 has one less subselect to perform. It may mean a big difference. Imagine in the example that only 20% of the rows has ICTYPE = 'F' then adding ICTYPE = 'F' in the outer select will reduce the number of subselects with 80%.