Db2 Version 11.5.4 Federation Performance Enhancements

1. Federation Interpartition Parallelism;
2. Federation FMP Enhancement;
3. Federation Fetch Clause FFNR Pushdown;
4. Federation NoSQL Wrapper Pushdown; and
5. Bulk Insert for Oracle

Learn more about each feature below.

Inter-partition parallelism

Introduction

Inter-partition parallelism refers to the process of dividing a single query into multiple parts that run in parallel on different partitions of a partitioned database. Inter-partition parallelism aims to increase the nickname query performance. Inter-partition parallelism takes full advantage of the power of the cluster, it can request all applicable partitions to serve one SQL query at the same time. 

Federation server acts as a database client of the remote database. It fetches data from a remote database through a database driver. When a federation server receives a query that references a remote table, it directly fetches data through one of the database clients. In other words, the federation server fetches the remote table data through the coordinator partition in serial mode. The federation server uses serial fetching and this is the default behavior. 
 

Performance improvement

If inter-partition parallelism is enabled, then the data will be distributed to other partitions.

• When a federation server receives a query that references a remote table, the query will be split and executed on multiple partitions. Multiple database clients fetch data from the same remote table simultaneously. In other words, the federation server uses parallel fetching

Parallel fetching leverages the Db2 DPF infrastructure and exploits resources across all applicable database partitions. In parallel fetching, a single query is split into multiple parts and then every database partition will execute the partial query. When compared to serial fetching, parallel fetching implements parallel processing of data in multiple partitions at the same time thereby increasing the performance significantly.
SUPPORT_PARALLELISM is a server option and NICKNAME_PARALLEL is a new registry variable option value that have been added for this feature.

 The values of these options must be set to ‘Y’ and ‘ON’ at the same time to turn on inter-partition parallelism with nickname parallel fetching.
Data sources that support parallel fetching are: Db2 family, Oracle, PostgreSQL, Greenplum, Netezza, Informix, and Teradata.

 The following graph illustrates the performance improvement test result with the atomic query:

Federation FMP Enhancement — Buffer Size Enhancement

Introduction

The FMP buffer size determines the data volume that is fetched from the data source in a single processing cycle. To reduce the processing time for fetching data from the data source, the default Federation FMP buffer size has been increased from 32 KB to 2 MB which is the optimal value to improve performance based on internal testing.

 Increasing the buffer size to 2 MB, achieves maximal performance with effective memory allocation. This feature applies to all federation native/ODBC/JDBC/NoSQL wrappers.

Performance improvement

This enhancement improves the federation nickname query performance by optimizing the block fetching buffer size. Buffer size enhancement benefits all nickname queries.

The figure below illustrates the performance improvement of the atomic query:

Federation FETCH Clause (FFNR) Pushdown

Introduction

The FETCH FIRST n ROWS (FFNR) clause sets the maximum number of rows that can be retrieved from the data source. Only n rows are retrieved, regardless of the number of rows that the result set might otherwise contain.

 By using Federation FETCH clause (FFNR) Pushdown feature, you will be able to reduce data transfer and improve performance significantly by pushing down the FFNR clause for more than ten types of data sources.

 Depending on the capability of a data source to handle the FETCH clause, you can now build a FETCH clause syntax with different server options for different data sources.

 The following table shows the default setting for the server option fetch_n_rows in a FETCH clause syntax for different data sources:

The following table shows various server options like nested_tab_expr_w_fetch_n_rows that controls the pushdown behaviour depending on the capability of FETCH clause handling of different data sources:

Performance improvement

If the data source has enough capability to handle the FETCH clause, federation will push down the entire SQL query to the remote data source for retrieving data. If the data source has limited capability to handle the FETCH clause, federation will push down partial SQL to the remote data source for query execution and optimal performance.

This enhancement enables federation to push down the largest portion of SQL with the FETCH clause as shown in the following examples:

• Case 1: Entire SQL query with FETCH can be pushed down (as shown below)
• Case 2: Partial SQL query with FETCH can be pushed down (as shown below)
• Case 3: Partial SQL without FETCH can be pushed down (FETCH is executed locally)
   

Federation NoSQL Wrapper Pushdown Framework

Introduction

NoSQL database systems are distributed, non-relational databases designed for large-scale data storage and NoSQL wrappers are designed to access different kind of NoSQL data sources. MongoDB is one of the most popular NoSQL or non-relational databases.

 To configure the federated server to access NoSQL data sources, you must provide the federated server with information about the data sources and objects that you want to access.

 Pushdown analysis tells the query optimizer if a remote data source can perform an operation. Federation pushdown analysis is only performed on relational data sources.

Performance Improvement

In Db2 Version 11.5.4, you can now reduce data transfer by pushing down predicates to MongoDB data sources through NoSQL wrapper. Comparing to federation pushdown analysis, which was designed for relational data sources, a new framework is created in the NoSQL wrapper for handling pushdown in non-relational data sources.

 The federation NoSQL wrapper pushdown framework uses a request-reply protocol which mainly includes:

• Interacting with the Db2 plan optimizer with predicates that can be pushed down to create an access plan.
• Responding with 'query execution description' in remote native API, for using in execution of queries during runtime.
   

The following graph illustrates the performance improvement test result with the following use case:

• Test Data Source: MongoDB
• Data: 10M records in MongoDB collection
• Query: SELECT col1_int from nickname1 WHERE col_int<200
• Result: 50x performance improvement
   

Introduction

Inserting data is also a common use scenario of Federation technology, and the Bulk Insert functionality is also improved when the user is using Federation to insert data into an Oracle data source in v11.5.4.

The Bulk Insert scenarios includes:

1. Insert operation with multiple rows selected from a source table
2. Insert operation with multiple values in SQL directly

To enable the Bulk Insert functionality, the user can specify the server option of ENABLE_BULK_INSERT to "Y". The function also requires the array bind functionality of the Oracle OCI client to work together with the Federation. The user will get better performance improvement when the buffer is large enough to contain more values during the insert processing.

Performance Improvement

The following graph illustrates the performance improvement test result with the following use case:

• Data source: Oracle
• Data: 
  • Table definition: CREATE TABLE LOCAL_TABLE1 (c0 int. c1 char(10), c3 varchar(40), c4 double precision, c5 (timestamp)
  • Data format: ('1','AAAAA','AAAAASSSSSDDDDDFFFFFGGGGGHHHHHJJJJJ','-0.99','1974-11-23-12.12.12.12.123451')
  • Data volume: 1 million rows
• Query: INSERT INTO ORACLE_TABLE SELECT * from LOCAL_TABLE1;
• Result: 84x performance improvement
   

Federation Transformation

Federation Server has gone through many innovative transformations in the past few years. It provides 'Federation Everywhere' capability across all the IBM on-prem and on-cloud Common SQL Engine offerings including Db2, BigSQL, Db2 Warehouse, Db2 Warehouse on Cloud, and IIAS, etc. Federation Server also becomes a core component of the IBM Data Virtualization technology as it provides its sophisticated SQL optimization and pushdown capability. As discussed in this blog, Federation Server has transformed itself with many new performance enhancements to provide a better user experience. There is still more to come on our roadmap.