Nickname parallel fetching

In a Db2® DPF environment it consists of multiple partitions whereas every partition is deployed in a physical machine or can be considered as an independent db2 instance, multiple logical partitions can be deployed in same physical machine.

Every partition represents a full package of Db2 instance which can receive and execute request, partition can communicate with each other using networking facility. Because in Db2 partitioned database the data is distributed across partitions, you can exploit the power of multiple processors on multiple physical machines to satisfy requests for information. Data retrieval and update requests are decomposed automatically into sub-requests and executed in parallel among the applicable partitions. And the fact that request is split into sub-request is transparent to users issuing SQL statements. This will improve performance significantly. On the other hand, federation server is designed to running only in one Db2 instance at the very beginning, this is true even in a DPF or BigSQL environment where multiple worker nodes or database partitions are available, in such environment it runs in coordinator node only. This limitation truly loses the power of multiple node clusters where the computing resources can be scaled horizontally. Furthermore, with more and more customer using federation server, there are strong request on performance, and digital world is growing so fast that the data volume has been increasing rapidly.

Given the above background, a new feature is developed to break this restriction and make federation nickname query running in parallel, and therefore exploit all computing resource that are dedicated to one Db2 cluster.

So what is nickname parallel fetching, let’s describe how federation server works without this feature. In DPF system, there are multiple database partitions available and each partition can be deployed in separate physical server, and each partition has the full Db2 image and functionalities. For a local table, the data are stored across all partitions and when a query is received all partitions will act as a worker to serve that query together at the same time. But for federation server, if parallel fetching is not enabled, it only runs on coordinator node even other partitions also bundled federation server, this means for nickname query the data fetching only happens on coordinator node and only one connection to remote table.

Figure 1.

Figure 1 shows an example of a simple query (select * from nickname) executing in a 4 partitions DPF environment without this feature.

Nickname parallel fetching, on the other hand, leverages all the computing resource to serve nickname query, that is in this example, other 3 partitions are also involved in nickname query execution (data fetching and processing). To implement nickname query parallel fetching, Db2 query compiler takes nickname as partitioned table and generates parallel access plan, to help better understand this, this is very similar to query a partitioned table or a Hadoop table. Once we successfully generate parallel access plan, at the runtime component, the access plan can be distributed to other partitions and get executed separately. Picture 2 shows the general flow of nickname interpartition parallelism with parallel fetching feature. In this example, we suppose the data in table1 is evenly distributed (nickname parallel fetching doesn’t require this though).

Figure 2.

From Figure 2 we can see that a nickname query is divided into 4 sub queries, and every sub query runs in each partition. From the partition perspective, they are accessing the same remote table simultaneously and they all work for one query.

One query will normally involve multiple nicknames and different nicknames may reference to different data sources. Figure 3 shows the general flow of federation nickname query that involves multiple nicknames. In this example, one query involves 3 nicknames from three data sources. While compiling this query, three independent remote statements will be generated in form of SHIP operator in each partition, a SHIP operator indicates the boundary of federation server and Db2 engine, all operations under SHIP will inclusively be executed by federation server. And inside runtime component 3 federation FMPs will execute these 3 SHIP operator in parallel by starting federation wrapper and sending remote statement to remote data source, and then get result back. Retrieving data from 3 data source is occur in parallel, every connection to data source will return all required rows. The parallelism is occurring in one node, as well as in the other 3 partitions.

Figure 3.