topic Re: DI Studio 4.901 - Specify NOIPASSTHRU on Extract Transformation? in SAS Data Management

DI Studio 4.901 - Specify NOIPASSTHRU on Extract Transformation?

bheinsius — Wed, 30 Nov 2016 16:06:03 GMT

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

Re: DI Studio 4.901 - Specify NOIPASSTHRU on Extract Transformation?

LinusH — Fri, 02 Dec 2016 11:11:01 GMT

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.

Re: DI Studio 4.901 - Specify NOIPASSTHRU on Extract Transformation?

David_Luttrell — Fri, 02 Dec 2016 12:48:55 GMT

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

Re: DI Studio 4.901 - Specify NOIPASSTHRU on Extract Transformation?

bheinsius — Fri, 02 Dec 2016 13:41:43 GMT

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.

Re: DI Studio 4.901 - Specify NOIPASSTHRU on Extract Transformation?

bheinsius — Fri, 02 Dec 2016 13:45:01 GMT

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).

Re: DI Studio 4.901 - Specify NOIPASSTHRU on Extract Transformation?

bheinsius — Mon, 17 Apr 2017 14:19:57 GMT

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