Pushing ELT 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. You need to extract the data, load it in a native database, and transform it in that database. 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. It also enables you to validate your job for pushdown and confirm that pushdown processing occurs 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. To run this check, click Check Database Processing in the job toolbar.
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
The following paper explains how to stage data inside the database and direct SAS to do its data integration work inside the database: “SAS® Data Integration Studio: Tips and Techniques for Implementing ELT.” You can access this paper at http://support.sas.com/resources/papers/proceedings10/116-2010.pdf. If you need user-defined functions, see User-Defined Functions.