POP/FED: Progressive Query Optimization for Federated Queries in DB2

Abstract

Federated queries are regular relational queries accessing data on one or more remote relational or non-relational data sources, possibly combining them with tables stored in the federated DBMS server. Their execution is typically divided between the federated server and the remote data sources. Outdated and incomplete statistics have a bigger impact on federated DBMS than on regular DBMS, as maintenance of federated statistics is unequally more complicated and expensive than the maintenance of the local statistics; consequently bad performance commonly occurs for federated queries due to the selection of a suboptimal query plan. To solve this problem we propose a progressive optimization technique for federated queries called POP/FED by extending the state of the art for progressive reoptimization for local source queries, POP [4]. POP/FED uses (a) an opportunistic, but risk controlled reoptimization technique for federated DBMS, (b) a technique for multiple reoptimizations during federated query processing with a strategy to discover redundant and eliminate partial results, and (c) a mechanism to eagerly procure statistics in a federated environment. In this demonstration we showcase POP/FED implemented in a prototype version of WebSphere Information Integrator for DB2 using the TPC-H benchmark database and its workload. For selected queries of the workload we show unique features including multi-round reoptimizations using both a new graphical reoptimization progress monitor POPMonitor and the DB2 graphical plan explain tool.