Enabling SQL Passthru

In this section: Specifying an RDBMS Using SET SQLENGINE Setting the Database Engine Using a Client Setting the Database Engine Using Stored Procedures Setting the Database Engine Using the API

There are two ways to enable SQL Passthru:

• Specifying an RDBMS using the SET SQLENGINE command.
• Setting the database engine using the client.

Both of these methods are discussed below.

Specifying an RDBMS Using SET SQLENGINE

If you configure a Hub or Full-Function Server, the server profile does not contain an engine setting by default. Instead, the Hub or Full-Function Server intercepts the request and then either passes it directly to the RDBMS (Automatic Passthru), or converts it into Data Manipulation Language before passing it to the RDBMS (SQL Translation).

Example: Specifying a Particular RDBMS

SET SQLENGINE=SQLORA

In this example, SQLORA is the name used to specify Oracle. See Valid Database Engine Settings for a complete list of database engine settings.

Setting the Database Engine Using a Client

A client connecting to a Hub or Full-Function Server can set SQL Passthru on demand. This effectively puts the server into SQL Passthru mode per session or per query.

When the client sets the engine in this manner, SQL Passthru behavior is altered for that client's session only; it does not affect any other client/server sessions.

The client application can set a database engine using two methods:

• Executing stored procedures that specify a database engine setting.
• Setting the database engine using the API.

Before using the client to change the database engine setting, you must configure the server environment. For more information, see the Server Administration manual.

Setting the Database Engine Using Stored Procedures

How to: Terminate SQL Passthru Mode Example: Specifying a Particular RDBMS Putting the Client's Session in SQL Passthru Mode for All Requests

The client application can execute stored procedures to enable SQL Passthru on a per session or per request basis.

Example: Putting the Client's Session in SQL Passthru Mode for All Requests

To put the client's session in SQL Passthru mode for all following requests, the client must execute a stored procedure that contains the SET SQLENGINE command, as shown in the following example.

SET SQLENGINE = SQLORA
   SQL SELECT COST FROM ORDERS;
   TABLE
   ON TABLE PCHOLD
   END
   SQL SELECT SALARY FROM EMPLOYEE;
   TABLE
   ON TABLE PCHOLD
   END

In this example, all SQL requests after the SET SQLENGINE command are passed directly to Oracle until the client disconnects. This is equivalent to setting the engine in the server profile, but affects only the client's session.

When the database engine is specified in a stored procedure, the server passes requests directly to the RDBMS for processing. This behavior persists as long as the database engine is set on the server. Therefore, RDBMS rules apply regarding SQL syntax and qualification of table and column names. If the request is not acceptable to the RDBMS, the server returns an error message to the client application.

In addition, when the database engine is set, ODBC stored procedure requests use RDBMS-specific ODBC programs.

Syntax: How to Terminate SQL Passthru Mode

To terminate SQL Passthru mode, another stored procedure must be executed that contains the following command:

SET SQLENGINE = OFF

Example: Putting the Client's Session in SQL Passthru Mode for a Single Request

To put the client's session in SQL Passthru mode for a single request, the client must execute a stored procedure that contains the SQL engine command. In this mode, the server processes any requests that include this command using SQL Passthru, while processing all other requests using SQL Translation. This is shown in the following example.

SQL SQLDS
SELECT COUNTRY FROM OWNER.CAR;
TABLE
ON TABLE PCHOLD
END

SQL SQLORA
SELECT * FROM OWNER.EMPLOYEE;
TABLE
ON TABLE PCHOLD
END

In this example, the server passes the first query directly to DB2, and the second to Oracle. The engine setting applies only for the duration of the SELECT statement. When the engine is specified in a stored procedure, RDBMS rules apply regarding SQL syntax and qualification of table and column names. If the request is not acceptable to the RDBMS, the server returns an error message to the client application.

For a list of database engine settings that can be specified in a stored procedure, see Valid Database Engine Settings.

Setting the Database Engine Using the API

An API application can issue a command to set the database engine to a specific RDBMS. All subsequent EDASQL and EDAPREPARE calls are directed to that RDBMS for the duration of the session. Remote procedure calls (executed by EDARPC), however, are not affected by the engine setting.

The engine setting can be changed as needed. In this way, applications can request data from multiple RDBMSs.

Syntax: How to Set the Database Engine Using the API

You can set the database engine in the API application by issuing the command

EDASET (0,eng)

where:

0

Is the code that indicates you are changing the database engine setting. Must be set to 0 (zero).

eng

Is a database engine setting for the API. When the database engine is set in the application, the server expects SQL in the dialect of the RDBMS. Otherwise, it returns an error message to the application. If no engine is specified, the server defaults to SQL Translation.

See Valid Database Engine Settings for a list of permissible settings for RDBMSs.

You can also specify a database engine using the Connector for ODBC. For instructions, see the Connector for ODBC manual.