Federation server enhancements

Db2 11.5.8 provides enhanced access to data sources in a Federation Server environment.

Attention: This mod pack release is currently available for the following Db2 products:
Enhancement Description
Federation pushdown capability for running fetch first n row (FFNR) queries against several data sources Several data stores are added to the list of supported federated data sources for FFNR queries. This enhancement enables Db2 to take the fetch-clause portion of an SQL query and push it down to a remote data source. This enhancement results in better performance and is available for several data sources:
  • Db2
  • Db2 for z/OS
  • Db2 iSeries
  • Derby
  • DVM
  • NET8
  • RedShift
  • BigQuery
  • Salesforce
New abbreviated syntax for Federation sever The create server statement syntax is now abbreviated.
When creating a Federation server, it is now optional for the user to enter the driver package and the driver class. If the user does not enter the driver package and the driver class, the Federation server loads to the default driver package and driver class for that server type. The user can still use the full syntax if they need an alternative driver package and driver class because it is now optional. DDL statements that were supported in the previous Db2 release are still supported.
For example, the following code block shows the full syntax for creating a Federation server:
create server JDBC_POSTGRESQL type jdbc version 10 
OPTIONS(DRIVER_PACKAGE '/home/lloucks/sqllib/federation/jdbc/lib/FOpostgresql.jar', 
DRIVER_CLASS 'com.ibm.fluidquery.jdbc.postgresql.PostgreSQLDriver', 
URL 'jdbc:ibm:postgresql://sapase1.fyre.ibm.com:5432;DatabaseName=postgres');
The following example shows the abbreviated version. 
create server jdbc_postgresql type jdbc version 10 
OPTIONS(URL 'jdbc:ibm:postgresql://sapase1.fyre.ibm.com:5432;DatabaseName=postgres');
This abbreviated syntax is applicable to Federation JDBC servers that come prepackaged with a Federation JDBC driver. This feature is available for all platforms except Windows.
Federation support for MySQL 8. Federation now supports MySQL version 8 as it applies to establishing connections, data type mapping, function pushdown, and server attributes optimization. This enhancement improves the query performance.
Federation tolerance support for PostgreSQL 12, PostgreSQL 13, and PostgreSQL 14. Federation now provides tolerance support for PostgreSQL 12, PostgreSQL 13, and PostgreSQL as it applies to establishing connections, data type mapping, and base function pushdown.
Federation JDBC wrapper support for Impala data source. Federation now supports connections to Impala data sources through the JDBC driver. Support includes establishing connections, data type mapping, function pushdown, and server attribute optimization. This enhancement improves the Db2 query Impala data correctness and performance.
Federation DSN-less connection support for native SQL Server wrapper Federation now supports connections to SQL Server data sources through a native wrapper by using the DSN-less (Data Source Name-less) connection method. This enhancement simplifies the Federation-to-SQL Server configuration.
Transport Layer Security (TLS), authentication database, and multiple host support on the NoSQL wrapper for MongoDB. Federation now supports TLS authentication for MongoDB databases. Federation also provides the high availability for the connection to the MongoDB replica set when the authentication database is different from the database to which the user is connected.
TLS support on ODBC wrapper for SAP HANA Federation now supports the TLS options for SAP HANA database with its official ODBC driver.
Pushdown enhancement on SQL with string comparison when remote data sources with different trailing blank sensitivity This enhancement improves the performance of queries with string comparison, especially when federation is set to NO PDA comparison.
Pushdown enhancement on LIKE predicate with half-width, full-width underscore, or percent wildcard This enhancement improves the performance and the query results.
Pushdown enhancement on OLAP functions on DRDA wrapper for Db2 LUW and JDBC wrapper for Netezza Federation supports online analytical processing (OLAP) function pushdown on the Distributed Relational Database Architecture (DRDA) wrapper for Db2 and the JDBC wrapper for Netezza. This enhancement improves the OLAP query performance.