DI Studio 4.901 - Specify NOIPASSTHRU on Extract Transformation?

[bheinsius]

Hi,

I need to specify NOIPASSTHRU on the PROC SQL code that the DIS 4.901 Extract transformation generates to prevent query from passing thru to SPDS.
Any way to do that?

Bart

[LinusH]

Why would you like to that? Can't really imagine the situation when an extract is better performed by the SAS client.
The only way I know of is to avoid use the IP=YES LIBNAME option.
But that can be quite awkward in a metadata environment if you wish to use IP=YES in other situations.

[bheinsius]

SPDS still uses SQL optimizer from SAS 6.12

Some queries run much faster when processed by SAS 9.4. Mine goes from 2 minutes to 18 seconds when not passing through.

[David_Luttrell]

By default all commands are carried out in the SAS engine so you should be okay. As part of a PROC SQL statement you need to stipulate that the SQL is being passed through to the database server. e.g. below:

proc sql outobs=15;
   connect to oracle as ora2 (user=user-id password=password);
   select * from connection to ora2 (select lname, fname, state from staff);
   disconnect from ora2;
quit;

You could try and lock this down even further by tightening how your Library connects to the DB

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001342247.htm

[bheinsius]

SAS passes the query down to SPDS automatically unless I specify NOIPASSTHRU (implicit pass-thru this is called).

My problem is that i can't find an option in the extract transformation that prevents the passthru.

There is a NODBIDIRECTEXECT option but this doesn't work for SPDS (i tried).

[bheinsius · Accepted solution]

I changed from the SQL Extract to the SQL Join transformation and not joining anything, which lets me specify proc sql options.