SQL Translation Operations

In this section: Setting Automatic Passthru How to: Set Automatic Passthru

SQL Translation performs two distinct types of operations:

• Dialect Translation transforms the query into native SQL dialects.
• The query is reconstructed into Data Manipulation Language (DML), allowing for retrieval from the native RDBMS.

The mode of operation is determined by the Automatic Passthru (APT) variable setting. See Setting Automatic Passthru for a description of this variable.

When an SQL request is processed, the following takes place:

1. The request is parsed to ensure that the syntax is valid. If it is not, an error is returned to the application. Parsing produces an abstract syntax tree data structure to be used in later analysis.

2. The Server Dynamic Catalog is used to determine the data source, information about the tables and columns, and the data types of the columns.

3. Semantic analysis is performed on the abstract syntax tree to ensure that the query conforms to the semantic rules. If not, an error is returned to the application.

4. If the Server Dynamic Catalog contains DBA or the query refers to a defined column in the catalog, DML Generation is required (see Data Manipulation Language (DML) Generation for more information). Processing continues with Step 8.

5. If APT=ON or APT=ONLY and the data source is a single RDBMS engine, the feature flags for that RDBMS are obtained.

6. The statement is checked against the feature flags. If the construct has an analogous RDBMS construct as indicated by the feature flags, Dialect Translation is invoked to regenerate the request using the same or similar syntax of the RDBMS. (See Dialect Translation for more information.) Processing continues with Step 10.

7. If APT=ONLY and the request is not translatable, the request is rejected and an error is returned to the application.

8. If APT=OFF, or if APT=ON and the data source is not from a single RDBMS or there is no feature flag for a given construct, DML Generation is invoked.

9. The appropriate data retrieval logic is triggered to process the translated dialect or the DML.

10. The results for SELECT operations from Dialect Translation or DML Generation are returned to the application as an answer set as shown below.

Setting Automatic Passthru

Automatic Passthru (APT) refers to the server profile option that enables SQL Translation Services to translate eligible incoming requests into native SQL dialects. This process, called Dialect Translation, passes the generated SQL to SQL Passthru. This processing is transparent to the application when APT is turned on.

You can use the API call EDASET to set the database engine. This engine, in turn, sets the Passthru mode. For more information on the EDASET API call, see the API Reference manual.

Syntax: How to Set Automatic Passthru

To change the setting for APT, issue the following command on the server:

SQL
SET APT[=] set_apt ;
END

where:

set_apt

Allows you to set the Automatic Passthru mode. Possible values are:

ON turns APT on. This is the default setting in the server profile of a Hub or Full-Function Server. When this is the value of the APT setting, SQL Translation analyzes each inbound request to determine whether Dialect Translation can be invoked to pass the request to SQL Passthru.

The server determines whether the SQL request is valid for the targeted RDBMS:

• If the request passes all the rules of Dialect Translation, it is translated and passed to SQL Passthru for processing.
• If the server receives an SQL request that does not conform to the rules of Dialect Translation, it invokes DML Generation to translate the request into Data Manipulation Language to be handled by one or more data adapters. The data adapters then generate engine-specific DML and pass it to the DBMSs for processing.

See Rules for Dialect Translation for the list of rules.

OFF turns APT off. All SQL requests are processed using DML Generation, which translates them into Data Manipulation Language to be processed by the appropriate data adapters.

ONLY states that all requests must be appropriate for Dialect Translation, that is, they must meet the requirements defined in Rules for Dialect Translation. No SQL requests go through DML Generation. If a request does not meet the requirements, the query is rejected, and an error is returned to the application.

This enables Data Administrators to configure access to servers to avoid any overhead that would be incurred when generating DML.