4.5 Oracle Database SQL Construct Processing

One of the most important features of the Oracle Database Gateway products is providing SQL transparency to the user and to the application programmer.

Foreign SQL constructs can be categorized into four areas:

• Compatible SQL Functions
  Oracle database automatically forwards compatible SQL functions to the DRDA database, where SQL constructs with the same syntax and meaning are on both Oracle database and the DRDA database.
• Translated SQL Functions
  Translated functions have the same meaning but different names between the Oracle database and the DRDA database. But all applications must use the Oracle function name.
• Compensated SQL Functions
  Some advanced SQL constructs that are supported by Oracle database may not be supported in the same manner, by the DRDA database.
• Native Semantic SQL Functions
  Some SQL functions that are normally compensated may also be overridden, through the Native Semantics facility.

4.5.1 Compatible SQL Functions

Oracle database automatically forwards compatible SQL functions to the DRDA database, where SQL constructs with the same syntax and meaning are on both Oracle database and the DRDA database.

These SQL constructs are forwarded unmodified. All of the compatible functions are column functions. Functions that are not compatible are either translated to an equivalent DRDA SQL function or are compensated (post-processed) by Oracle database after the data is returned from the DRDA database.

4.5.2 Translated SQL Functions

Translated functions have the same meaning but different names between the Oracle database and the DRDA database. But all applications must use the Oracle function name.

These SQL constructs that are supported with different syntax (different function names) by the DRDA database, are automatically translated by the Oracle database and then forwarded to the DRDA database. Oracle database changes the function name before sending it to the DRDA database, in a manner that is transparent to your application.

4.5.3 Compensated SQL Functions

Some advanced SQL constructs that are supported by Oracle database may not be supported in the same manner, by the DRDA database.

Compensated functions are those SQL functions that are either not recognized by the DRDA server or are recognized by the DRDA server but the semantics of the function are interpreted differently when comparing the DRDA server with the Oracle database. If a SELECT statement containing one of these functions is passed from the Oracle database to the gateway, then the gateway removes the function before passing the SQL statement to the DRDA server. The gateway passes the selected DRDA database rows to Oracle database. Oracle database applies the function.

• Post-Processing
  Oracle database can compensate for a missing or incompatible function by automatically excluding the incompatible SQL construct from the SQL request that is forwarded to the DRDA database.

4.5.3.1 Post-Processing

Oracle database can compensate for a missing or incompatible function by automatically excluding the incompatible SQL construct from the SQL request that is forwarded to the DRDA database.

Oracle database then retrieves the necessary data from the DRDA database and applies the function. This process is known as post-processing.

The gateway attempts to pass all SQL functions to DRDA databases. However when a DRDA database does not support a function that is represented in the computation, the gateway changes that function. For example, if a program runs the following query against a DB2 UDB for z/OS database:

SELECT COS(X_COOR) FROM TABLE_X;

Because the database does not support many of the COS functions, the gateway changes the query to the following:

SELECT X_COOR FROM TABLE_X;

All data in the X_COOR column of TABLE_X is passed from the DB2 UDB for z/OS database to the Oracle database. After the data is moved to the Oracle database, the COS function is performed.

If you are performing operations on large amounts of data that are stored in a DRDA database, then keep in mind that some functions require post-processing.

4.5.4 Native Semantic SQL Functions

Some SQL functions that are normally compensated may also be overridden, through the Native Semantics facility.

If a SQL function has been enabled for Native Semantics, then the function may be passed on to the DRDA database for processing, instead of being compensated. The SQL function is then processed natively in the DRDA database. Refer to Native Semantics for more information.