SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

DI Studio 4.901 - Specify NOIPASSTHRU on Extract Transformation?

Accepted Solution Solved
Reply
Regular Contributor
Posts: 182
Accepted Solution

DI Studio 4.901 - Specify NOIPASSTHRU on Extract Transformation?

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


Accepted Solutions
Solution
‎04-17-2017 10:20 AM
Regular Contributor
Posts: 182

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

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

View solution in original post


All Replies
Super User
Posts: 5,256

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

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.
Data never sleeps
Regular Contributor
Posts: 182

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

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.

Contributor
Posts: 22

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

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

Regular Contributor
Posts: 182

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

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

 

Solution
‎04-17-2017 10:20 AM
Regular Contributor
Posts: 182

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

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 295 views
  • 0 likes
  • 3 in conversation