Query transformations

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

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, such as IBM® Data Studio, to format SQL statement text and view access path diagrams.

The recommendation to write queries as simply as possible always remains. Do not try to rewrite a simpler query into a more complex form for the purpose of make it eligible for a particular query transformation.

Tip: You can use tools such as IBM Data Server Manager and related Db2 Tools for z/OS® solutions to simplify this task.