Why does a plan generated by the optimizer and completely evaluated remotely, have much worse performance than the original query executed directly at the remote data source?

• The ordering of predicates in the query might have changed.
• Predicates found in the original query might have been removed, replaced by equivalent ones, or augmented by additional predicates.
• Subqueries might have been rewritten as joins.
• Additional functions that do conversion or string truncation might have been added to maintain Db2® semantics

With the exception of the last item listed above, these changes usually have a favorable impact on performance. However, in a few cases, the changes might cause the remote query optimizer to generate a different (and slower) plan than it would have for the original query

A good query optimizer should not be sensitive to the predicate ordering of a query. Unfortunately, not all DBMS optimizers are identical. It is likely that the optimizer at the remote data source will generate a different plan based on the input predicate ordering. If this is true, this is a problem inherent in the remote optimizer. Consider either modifying the predicate ordering or contacting the service organization of the remote data source for assistance.

Also, check for predicate replacements. A good query optimizer should not be sensitive to equivalent predicate replacements. It is possible that the optimizer at the remote data source will generate a different plan based on the input predicate. For example, some optimizers cannot generate transitive closure statements for predicates.