Chapter 8. SQL fundamentals 195
Note that the ORDER BY columns do not have to be in the list of selected columns.
8.5 Subquery to join transformation
Depending on several conditions, DB2 can perform a subquery to join transformation for
SELECT, UPDATE, and DELETE statements between the result table of a subquery and the
result table of an outer query.
A complete list of conditions for a subquery to join transformation can be found in DB2 for
z/OS Application Programming and SQL Guide, SC18-7415-02.
A major advantage of subquery to join transformation is the flexibility for DB2 to decide the
most efficient order to process the tables.
It is possible that, in some cases, subquery to join transformation will degrade performance.
This is most likely to happen when the outer query returns a small number of rows and the
subquery returns a large number of rows. But you can also find the same effect the other way
around. In such a case, transformation provides no performance benefit, but there is
additional overhead to prepare the statement with transformation activated.
You can find a candidate for a noncorrelated subquery to join transformation in Example 8-7.
Example 8-7 Candidate query for subquery to join transformation
SELECT * FROM T1
WHERE COLa IN
(SELECT COLa FROM T2
WHERE COLb = ’10’);
Since you do not tell DB2 that the select statement is read-only, DB2 is looking for an access
path where it can maintain the current cursor position to allow for updates on the result set.
If you specify FOR FETCH ONLY, then DB2 does not need to know the current position of
your cursor on the base table, because you are not going to update qualifying rows.
Therefore, a more efficient access path for read-only cursors may be available to the
optimizer. See Example 8-8 to allow the optimizer for another join sequence.
Example 8-8 Specifying FOR FETCH ONLY to improve optimizer’s choices
SELECT * FROM T1
WHERE COLa IN
(SELECT COLa FROM T2
WHERE COLb = ’10’)
FOR FETCH ONLY;
Tip: PTF UQ50928 for APAR PQ45052 provides you with the ability of disabling correlated
subquery to join transformation in DB2 Version 7 at the system-wide level.
Tip: While FOR UPDATE OF forces a certain access path, FOR FETCH ONLY provides
more opportunities to the optimizer to choose the most efficient access path.

Get DB2 UDB for z/OS: Design Guidelines for High Performance and Availability now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.