Compensation
The ability by Federation component to process SQL that is not supported by a data source is called compensation.
The federated database does not push down a query fragment if the data source cannot process it, or if the federated server can process it faster than the data source can process it. For example, suppose that the SQL dialect of a data source does not support a CUBE grouping in the GROUP BY clause. A query that contains a CUBE grouping and references a table in that data source is submitted to the federated server. The federated database does not pushdown the CUBE grouping to the data source, but processes the CUBE itself.
- It can request that the data source use one or more operations that are equivalent to the database function stated in the query. For example, a data source does not support the cotangent (COT(x)) function, but supports the tangent (TAN(x)) function. The federated database can request that the data source perform the calculation (1/TAN(x)), which is equivalent to the cotangent (COT(x)) function.
- It can return the set of data to the federated server, and perform the function locally.
For relational data sources, each type of RDBMS supports a subset of the international SQL standard. In addition, some types of RDBMSs support SQL constructs that exceed this standard. An SQL dialect, is the totality of SQL that a type of RDBMS supports. If an SQL construct is found in the SQL dialect, but not in the relational data source dialect, the federated server can implement this construct on behalf of the data source.
The federated database can compensate for differences in SQL dialects. An example of this ability is the common-table-expression clause. SQL includes the clause common-table-expression. In this clause, a name can be specified by which all FROM clauses in a fullselect can reference a result set. The federated server will process a common-table-expression for a data source, even though the SQL dialect used by the data source does not include common-table-expression.
With compensation, the federated database can support the full SQL dialect for queries of data sources. Even data sources with weak SQL support or no SQL support will benefit from compensation. You must use the SQL dialect with a federated system, except in a pass-through session.