Guidelines for determining where a federated query is evaluated

The Db2® explain utility, which you can start by invoking the db2expln command, shows where queries are evaluated. The execution location for each operator is included in the command output.

  • If a query is pushed down, you should see a RETURN operator, which is a standard Db2 operator. If a SELECT statement retrieves data from a nickname, you also see a SHIP operator, which is unique to federated database operations: it changes the server property of the data flow and separates local operators from remote operators. The SELECT statement is generated using the SQL dialect that is supported by the data source.
  • If an INSERT, UPDATE, or DELETE statement can be entirely pushed down to the remote data source, you might not see a SHIP operator in the access plan. All remotely executed INSERT, UPDATE, or DELETE statements are shown for the RETURN operator. However, if a query cannot be pushed down in its entirety, the SHIP operator shows which operations were performed remotely.

Understanding why a query is evaluated at a data source instead of by the Db2 server

Consider the following key questions when you investigate ways to increase pushdown opportunities:
  • Why isn't this predicate being evaluated remotely?

    This question arises when a very selective predicate could be used to filter rows and reduce network traffic. Remote predicate evaluation also affects whether a join between two tables of the same data source can be evaluated remotely.

    Areas to examine include:
    • Subquery predicates. Does this predicate contain a subquery that pertains to another data source? Does this predicate contain a subquery that involves an SQL operator that is not supported by this data source? Not all data sources support set operators in a subquery predicate.
    • Predicate functions. Does this predicate contain a function that cannot be evaluated by this remote data source? Relational operators are classified as functions.
    • Predicate bind requirements. If it is remotely evaluated, does this predicate require bind-in of some value? Would that violate SQL restrictions at this data source?
    • Global optimization. The optimizer might have decided that local processing is more cost effective.
  • Why isn't the GROUP BY operator evaluated remotely?
    Areas to examine include:
    • Is the input to the GROUP BY operator evaluated remotely? If the answer is no, examine the input.
    • Does the data source have any restrictions on this operator? Examples include:
      • A limited number of GROUP BY items
      • Limited byte counts for combined GROUP BY items
      • Column specifications only on the GROUP BY list
    • Does the data source support this SQL operator?
    • Global optimization. The optimizer might have decided that local processing is more cost effective.
    • Does the GROUP BY clause contain a character expression? If it does, verify that the remote data source and the Db2 server have the same case sensitivity.
  • Why isn't the set operator evaluated remotely?
    Areas to examine include:
    • Are both of its operands evaluated in their entirety at the same remote data source? If the answer is no, and it should be yes, examine each operand.
    • Does the data source have any restrictions on this set operator? For example, are large objects (LOBs) or LONG field data valid input for this specific set operator?
  • Why isn't the ORDER BY operation evaluated remotely?
    Areas to examine include:
    • Is the input to the ORDER BY operation evaluated remotely? If the answer is no, examine the input.
    • Does the ORDER BY clause contain a character expression? If yes, do the remote data source and the Db2 server have different collating sequences or case sensitivities?
    • Does the remote data source have any restrictions on this operator? For example, is there a limit to the number of ORDER BY items? Does the remote data source restrict column specification to the ORDER BY list?