Query transformations

Db2 sometimes modifies the text of SQL statements to improve access path efficiency.

Remember: The recommendation to write queries as simply as possible always remains. Do not try rewriting a simpler query into a more complex form to make it eligible for a particular query transformation.

Query transformations become most important for complex queries, especially complex queries that are created by query generators. Db2 might apply the following types of transformations to SQL statements, among others:

Removal of unneeded or pre-evaluated predicates
Addition of generated predicates
Removal of table references for certain joins
Correlation or de-correlation of subqueries
Conversion of subqueries to joins
Simplification and removal of subselects in statements that contain UNION ALL operators

Because of the complexity of such queries, exhaustive description of every case for transformation and every restriction is not practical. Therefore, the transformations are described at only a very a high level with basic examples. None of the transformations are guaranteed to occur for any particular statement.

In most cases, you do not have to do anything to take advantage of such transformations. However, you might notice some of these changes in PLAN_TABLE output. You might also see such changes when you use query tuning tools to format SQL statement text and view access path diagrams.

Tip: Query tuning capabilities that can help you with this task, such as visual explain and statistics advisor, are available in IBM® Db2 Administration Foundation for z/OS® and IBM Db2 for z/OS Developer Extension.