Why is the statement not being completely evaluated remotely?

The federated server seeks to ensure that query semantics and results obtained for federated queries are exactly the same as if they had been completely evaluated by Db2®. The pushdown analysis phase of the query compiler decides whether pushing down processing to remote sources will maintain Db2 semantics. Thus, federated query operations can be safely pushed down only if corresponding operations at the remote source have the same meaning and result. The most common reason for failure to completely push down processing of a query to a single remote source is the existence of subtle differences in functionality between the federated server and the remote source for one or more operations in the query.

The optimizer performs cost-based optimization. Even if pushdown analysis indicates that every operator can be evaluated at the remote data source, the optimizer still relies on its cost estimate to generate a globally optimal plan. There are many factors that contribute to the decision to choose that plan. Suppose that the remote data source can process every operation in the original query. However, its CPU speed is much slower than the CPU speed of the federated server. It might turn out to be more beneficial to perform the operations at the federated server instead. If the desired performance is not achieved, verify the server statistics in the SYSSTAT.SERVEROPTIONS catalog table.