FedSQL Implicit Pass-Through Facility

Overview

Implicit pass-through (IP) is the process of translating SQL query code into equivalent data source-specific SQL code so that it can be passed directly to the data source for processing. IP improves query response time and enhances security.
The performance benefits that are provided by IP can be divided into two primary categories: data transfer volume reduction and leveraging of data source-specific capabilities. The volume of data being transferred is reduced by performing the query on the data source. The number of rows that are transferred from the data source to FedSQL can be significantly reduced, thereby decreasing the overall query processing time. The leveraging of data source-specific capabilities, such as massively parallel processing, are specific to a data source. Other examples of special capabilities are advanced join techniques, data partitioning, table statistics, and column statistics. These capabilities often allow the data source to perform the SQL query more quickly than FedSQL.
The security benefit of IP is that every part of an IP query that can be processed is processed on the data source side. This eliminates the need to have its associated tables, which might contain sensitive information, transmitted over to the FedSQL side for query processing.

How to Use FedSQL Implicit Pass-Through

FedSQL IP is performed automatically. You are not required to specify any options to use IP.

Single Source FedSQL Implicit Pass-Through

When a query is accessing a single data source, either the full query is implicitly passed down to the data source or the predicates (for example, the WHERE clauses) are passed down to subset the rows that must be transported into FedSQL for local processing. FedSQL can pass queries implicitly only when the SQL syntax is ANSI-compliant. The following limitations might prevent IP:
  • functions that are FedSQL-specific, such as PUT.
  • certain aggregate statistics such as SKEWNESS, STUDENTS_T, NMISS, KURTOSIS, CSS, USS, and PROBT.
  • mathematical functions such as SIN, COS, ATAN, and TAN.
  • ANSI-compliant FedSQL syntax might prevent IP if the data source is not ANSI compliant in that area.

Multiple Source FedSQL Implicit Pass-Through

FedSQL can perform IP on queries that include multiple data sources. This is accomplished by breaking the query into multiple queries and passing these individual queries to their respective drivers. In addition to the restrictions listed in Single Source FedSQL Implicit Pass-Through , multiple source IP has the following additional limitations:
  • A maximum of ten tables can be in one comma join.
  • Each side of a set operation (for example, UNION, INTERSECT, and EXCEPT) must have tables from multiple sources for multiple source IP to perform correctly.
  • If the query contains a correlated subquery, no multiple source IP is attempted.
Last updated: February 23, 2017