Enabling Explicit Pass-Through Processing for SQL Join Transformations :: SAS(R) Data Integration Studio 4.6: User's Guide

Problem

You want to enable explicit pass-through processing for an SQL Join transformation.

Solution

Perform the following tasks:

Tasks

Determine Whether Explicit Pass-Through Processing Is Possible

Pass-through processing sends DBMS-specific statements to a database management system and retrieves the DBMS data directly. In some situations, explicit pass-through processing can improve the performance of SQL transformations in the context of a SAS Data Integration Studio job. However, explicit pass-through is not always feasible. The query has to be able to work as is on the database. Therefore, if the query contains anything specific to SAS beyond the outermost select columns portion, the database generates errors. For example, using any of the following in a WHERE clause expression or in a subquery on the WHERE or FROM clauses causes the code to fail on the database:

SAS formats
SAS functions
DATE or DATETIME literals or actual numeric values
date arithmetic (usually does not work)
INTO: macro variable
data set options

Even if explicit pass-through is not enabled, the SAS SQL procedure still tries to pass the query or part of the query down to the database with implicit pass-through. This attempt to optimize performance is made without the user having to request it. SQL implicit pass-through is a silent optimization that is done in PROC SQL. Implicit pass-through interprets SAS SQL statements, and, whenever possible, rewrites the SAS SQL into database SQL.

There is no guarantee that the SQL is passed to the database. However, PROC SQL tries to generate SQL that passes to the database. If the optimization succeeds in passing a query (or parts of a query) directly to a database, the SQL query executes on the database. Only the results of the query are returned to SAS. This approach can greatly improve the performance of the PROC SQL code. If the query cannot be passed to the database, records are read and passed back to SAS, one at a time. Implicit pass-through is disabled by the following query constructs:

Queries that incorporate explicit pass-through statements: If explicit pass-through statements are used, the statements are passed directly to the database as they are. Therefore, there is no need to try to prepare or translate the SQL with implicit pass-through to make it compatible to the database. It is already assumed to be compatible.
Queries that use SAS data set options: SAS data set options cannot be honored in a pass-through context.
Queries that use an INTO: clause: The memory that is associated with the host variable is not available to the DBMS that processes the query. The INTO: clause is not supported in the SQL Join transformation.
Queries that contain the SAS OUTER UNION operator: This operator is a non-ANSI SAS SQL extension.
Specification of a SAS Language function that is not mapped to a DBMS equivalent by the engine. These functions vary by database.
Specification of ANSIMISS or NOMISS in the join syntax.
Heterogeneous queries: Implicit pass-through is not attempted for queries that involve different engines or on queries that involve a single engine with multiple librefs that cannot share a single connection because they have different connection properties (such as a different database= value). For heterogeneous queries, try explicit pass-through. With the SQL Join transformation, you can also use the Upload Library Before SQL, Pre-Upload Action, and Use Bulkload for Upload properties in the table properties panes to improve the situation.

Note: The Upload Library Before SQL property in the SQL Jon transformation can be used to create a homogeneous join, which can then enable an explicit pass-through operation. This property enables you to select another library on the same database server as other tables in the SQL query. The best choice for a library is a temporary space on that database server. The operations on that temporary table can also be modified to choose between deleting all rows or deleting the entire table. Bulk-load is also an option for the upload operation with the Use Bulkload for Uploading property. It is generally a good practice to upload the smaller of the tables in the SQL query because this operation can be expensive.

Enable Explicit Pass-Through Processing

To enable explicit pass-through processing by default for new instances of most SQL transformations, select Tools

Options

Job Editor Tab, and then select the pass-through check box in the Automatic Settings area. This setting affects SQL Join transformations and also any SQL transformation whose properties window includes a Database pass-through option on its Options tab. This includes SQL transformations such as Create Table, Insert Rows, Set Operators, Delete, and Update.

To enable pass-through processing for an SQL Join transformation, open the properties window for the transformation and specify Yes in the Pass Through property. The SQL Properties pane also contains the Target Table is Pass Through property, which determines whether explicit pass-through is active for the target table. This property enables the target to have the select rows inserted into the target within the explicit operation. This property is valid only when all the tables in the query, including the target, are on the same database server. The Target Table is Pass Through property has a corresponding property, named Target Table Pass Through Action. The Truncate option in this property is useful for DBMS systems that does not allow the target to be deleted or created. In this case, the only option is removing all of the rows. If Truncate is selected, all of the rows in the table are deleted. If the table does not exist, it is created.