Dialect Translation

In this section: Rules for Dialect Translation Dialect Translation Processing

Dialect Translation is a feature of SQL Translation Services that enables a Hub or Full-Function Server to route inbound SQL requests to SQL-capable subservers and data adapters wherever possible. Dialect Translation avoids translation to Data Manipulation Language, while maintaining data location transparency.

Dialect Translation transforms an incompatible statement into one that can be processed by the destination SQL engine, while preserving the semantic meaning of the statement.

Dialect Translation is the default behavior of SQL Translation in a Hub or Full-Function Server. This behavior can be modified by changing the server profile or executing a stored procedure to modify the value of the APT variable.

To qualify for Dialect Translation, the request must meet the requirements outlined in Rules for Dialect Translation. If the syntax of a request does not conform to the syntax of the DBMS or is not translatable to the syntax of the DBMS, the request will be translated to DML.

Dialect Translation is shown in the shaded area of the following figure. Valid requests are represented by the broken-line circle as shown in the figure below.

Rules for Dialect Translation

If the incoming request meets the following criteria, Dialect Translation is invoked by SQL Translation Services:

• APT is set to ON or ONLY in the server profile.
• All data referenced by the request resides in an RDBMS.
• No defined columns in the table are referenced.
• The referenced tables contain no security restrictions as listed in the Server Dynamic Catalog.
• The syntax of the incoming SQL statement must correspond precisely to the syntax of the RDBMS, or must be translatable into the RDBMS's dialect of SQL based on the feature flags for that RDBMS. The server translates the following into the dialect of the target RDBMS:
• The "not equal" symbol.
• The substring scalar function, length.
• The concatenation binary operator.
• The PREPARE parameter marker.
• Date literals, durations.
• Cast-type scalar functions (those functions that apply to an expression or column name and change only the data type, not the value).
• Outer joins (with limitations).
• For fetch only.
• <> = NULL.

If a request does not meet these requirements but is valid, the server translates the request into Data Manipulation Language using DML Generation. One or more data adapters then generate driver-specific DML and pass it to the DBMS for processing.

Dialect Translation Processing

The following steps illustrate how a request for relational data is processed using Dialect Translation on a Hub or Full-Function Server:

1. The abstract syntax tree data structure is traversed:
   • For each construct in the abstract syntax tree that corresponds to a feature flag, Dialect Translation substitutes a dialect-correct RDBMS construct.
   • Each table name is changed to its actual name in the targeted DBMS, as stored in the Server Dynamic Catalog.
   • Each column name is changed to its actual name, as stored in the Server Dynamic Catalog.

2. The SQL statement is rebuilt token by token from the abstract syntax tree.

3. The resulting generated SQL is passed to SQL Passthru for processing.

4. SQL Passthru sends the request to the RDBMS, which executes it and, for SELECT operations, returns an answer set.

5. The server returns any resulting answer set to the client.

Incoming SQL syntax is often compatible with the dialect-specific SQL of the target RDBMS. In that case, Dialect Translation substitutes the correct table and column names rather than translates the dialect.