Introduce explicit pass-through option in Cloud Data Exchange

[RichardHorgen]

As far as I understand, there is currently no option to use explicit pass-through with cloud data exchange. This is a major drawback for customers migrating to Viya, that over the years have made extensive use of this functionality on the SAS 9.4 environment. One of the main reasons explicit pass-through has been used, is to make use of non-standard SQL functions available from the database vendor. One such function that will require a lot of rewriting of code is the Qualify-statement available at several vendors. 

Thanks in advance for your attention to this matter.

[Patrick]

@RichardHorgen I've asked recently a very similar question and there is actually a way via a "double hop".

I'll provide feedback to the docu so hopefully this will become official at some point in time. Right now I'd treat this as an undocumented feature.

proc sql;
connect to cde (dataagentname="sas-data-agent-server-colocated" dsn=TSTAUTO_ORACLE);    
select * from connection to cde   /* from SAS client to agent */
(
  select * from connection to TSTAUTO_ORACLE_CDA /* from agent to DB server */
  (
    select * from cars where rownum=1 /* This is native ORACLE syntax */ 
    )
  );    
quit;

[rhmthomson1]

Appologies for missing this. I have now put this into our review process and will report back with updates

[Patrick]

@rhmthomson1 

I just checked and the example for explicit pass-through SQL with the cde engine made it now into the docu 🙂

https://go.documentation.sas.com/doc/en/pgmsascdc/v_048/dataagenteng/n0febhwsuihialn1dvi0dif3m9g8.ht... 

[RichardHorgen]

@rhmthomson1 

Hi

The suggestion to use a "double hop" suggested by @Patrick works quite well for explicit pass-through that lifts data into SAS. However, when I try to perform an execute( -statement it seems this approach has some shortcomings.

The following execute statement works in terms of creating a table on the Teradata-server we have, but when the table is created the session still runs as the cde-engine does not allow me to add "disconnect from teradata" into the inner execute-statement. 

proc sql;
connect to cde (dataAgentName="cde" dsn=teradata bulkload=yes );
execute(
execute(
CREATE MULTISET TABLE v.newTable AS ( 
SELECT

* 

from x.OldTable

)with data;

) by teradata;
/* disconnect from teradata; */
) by cde;
disconnect from cde;
%put &sqlxmsg;
%put &sqlxrc;
quit; 

[Patrick]

@RichardHorgen Given that the double hop approach is now documented here I suggest you raise this issue with SAS Tech Support. 

Make sure you are very clear when describing the behaviour you observe and the impact this has.

[rhmthomson1]

As note below, method to do this is now documented at SAS Help Center: About PROC SQL and Explicit Pass-Through.

If any issues as noted on specific cases that dont work, please raise with support.