Pushing Job Code down to a Database

Problem

You want to submit some of the code in a SAS Data Integration Studio job to a relational database server. Then, you can run transformations on the data in relational database tables directly in the relational database.

Solution

You can use the pushdown feature to specify that the relational database code in the job is processed in the relational database server. This feature enables you to verify that your job contains tables and transformations that support pushdown, validate your job for pushdown, and confirm that pushdown processing will occur when you submit the job.

When both the inputs and outputs of the Extract, SQL Join, Teradata Table Loader, and Table Loader transformations are stored in the same relational database, the code for these transformations can be pushed down to a database server for execution. This option increases performance by shifting data transformation to the most appropriate processing resource.

Note: The use of the Table Loader transformation in a pushdown job requires the following settings:

Load style: select either Append to Existing or Replace
New Rows: select Insert (SQL)

Database processing is validated whenever a job is run. If a job can be run on the database server, it will be by default. You can also perform a check to determine whether it is possible to use database processing for a job. This check is strictly diagnostic. It validates only the possibility of database processing without running the actual job.

Database processing can fail for a variety of reasons. The following causes are common:

using SAS data set options
requesting views instead of tables
disabling the Use the optimized pass-through facility for SQL statements option on a transformation

Perform the following tasks:

Tasks

Populate a Job That Uses the Pushdown Feature

Perform the following steps to populate a job that uses the pushdown feature:

Open an empty job.
Drop the source table on the Diagram tab of the Job Editor window. For example, you can drop an Oracle table that can be pushed down to the relational database for processing.
Drop a transformation from the Transformations tree on the Diagram tab. For the sample job, you can drop the Extract transformation, which supports the pushdown feature.
Right-click the temporary work table that is attached to the transformation and click Replace in the pop-up menu. Then, use the Table Selector window to select the target table for the job.

The target table for the job is an Oracle table, which matches the source table and supports pushdown processing. Note that any source tables must use the same schema and reside in the same data server as the target table. The following display shows the process flow for the sample job.

Populated Process Flow for a Pushdown Job

Note: If you select a library with a different data type as the alternate location for the temporary output tables in your job, the temporary table icons will display the overlay for the proper database. For information about redirecting temporary output, see Redirecting Temporary Output Tables.

The source table for the sample job is named DB_Class Oracle, and the target table is named Class Target. Note that the icons for both tables display the Oracle overlay () in the upper-right corner.

Check Database Processing for the Job

Perform the following steps to determine whether the job can be processed on the database.

Click Check Database Processing in the menu bar of the Job Editor window.
Verify that database processing is possible in the selected job. When a job is successfully validated for database processing, the following indicators are displayed:
- a database overlay on the transformation
- a message in the Details column on the Status tab
The following display shows the sample job after database processing was checked:

Sample Validated Job

Note that an Oracle overlay has been added to the Extract transformation. A message in the Details column confirms that the job will be processed on the Oracle database server.