Redirecting Temporary Output Tables :: SAS(R) Data Integration Studio 4.5: User's Guide

Problem

You want to redirect the output of your temporary tables to an alternative location.

Solution

Transformations in a job typically create temporary work tables as they execute. The default location for these temporary tables is the SAS WORK library. You can now easily redirect these temporary tables to an alternative location, including a DBMS. Redirecting this output provides the following benefits:

improved performance. For example, processing data in a DBMS requires no data transfer. For more information, see Reviewing Temporary Output Tables.
support for the restarting jobs from checkpoints feature. For more information, see Specify Libraries for a Checkpoint.
support for the pushdown of work to a third-party database. For more information, see Pushing ELT Job Code Down to a Database.

You can redirect the output of your temporary tables within the following scopes: all new jobs, a single job, and a single transformation. Perform the following tasks:

Tasks

Redirect Temporary Output Tables in All New Jobs

Perform the following steps to redirect the output of your temporary tables to an alternative location for all new jobs.

Open the Code Generation tab in the Options window. You can access the Options window at ToolsOptions in the menu bar.
Click Browse for a library, which is adjacent to the Alternative library for temporary tables field, to select an existing library.
Click OK to close the Options window.

Redirect Temporary Output Tables in a Single Job

Perform the following steps to redirect the output of your temporary tables to an alternative location for a single job.

Open the Options tab in the properties window for the job.
Click Browse adjacent to the Alternate library for temporary tables fields to select a library from the Folders tab.

Note: You can set the Clean up alternate temporary library after successful run option to Yes to delete temporary tables after the deployed job runs successfully. If you set this option to No, you should periodically delete the temporary tables manually to conserve disk space.
Click OK to close the properties window.

Redirect Temporary Output Tables Attached to a Single Transformation

Perform the following steps to redirect the output of your temporary tables to an alternative location for a temporary output table attached to a transformation.

Click the Physical Storage tab in the properties window for the temporary output table.
Click Redirect to a registered library in the drop-down menu in the Location field.
Click Select a library in the Library field and select the appropriate library. Click OK to close the Select a library window. You can also click New to access the New Library wizard and register a new library.
Click OK to close the properties window.