proc sql connect using

[DBailey]

Is there a way to add the PRESERVE_COMMENTS hint to a connection defined by the new "connect using" syntax?  I don't think that PRESERVE_COMMENTS is allowed in the libname statement but it is needed when using pass through.

[jakarman]

When you are coding explicit pass through  you are defining a complete new session. By that you are required to specify all options over and over again.

Do you want to optimize that redundant coding you can uses a macro in that position. That macro can read all parameters from a file and you can uses many records key-text.

When you are using implicit pass through you are using all setting of a libname.

You can use pass through coding and referering the libname setting. 

see: SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Fifth Edition (Example:  Retrieve DBMS Data with a Pass-Through Query)

These three approaches all behaving slightly different.

When the implicit pass through is behaving well enough there is no need to make life difficult and trying to do it in an other way.
What is your real issue?  

[DBailey]

I'm trying to take advantage of the new syntax for creating a connection using an already existing library.  Often, oracle queries need tuning to achieve optimal performance which is done through query hints.  SAS thinks these are comments and does not pass them through unless the PRESERVE_COMMENTS option is enabled on the connection.  Using the new syntax doesn't seem to allow that but it does allow end users to make a direct connection much more easily.

For example, if I have an library defined for an oracle database (MDM), I can certainly use implicit pass through.  I can't set the PRESERVE_COMMENTS option but it doesn't matter since SAS is generating the actual oracle query.

If I define a connection directly, I can set the option which allows me to do something like

connect to oracle as cmdm (path=...user=...pw=....PRESERVE_COMMENTS)

create table work.tmp as select * from connection to cmdm (select /*+parallel(6)*/ from.....);

without the PRESERVE_COMMENTS option, the parallelization hint is not sent to oracle.

With the new syntax, all you need to make a separate connection is

connect using mdm as cmdm;

but I can't find a way to enable the preserve_comments option.

[Sven111 · Accepted solution]

I was having the same questions and came across a solution that seems to be working.  Basically I put the compiler hints in a macro variable and BQUOTE the comment delimiters.  Like this:

    %LET ORAC_HINT__PARALLEL_16 = %BQUOTE(/)%BQUOTE(*)+ PARALLEL (16) %BQUOTE(*)%BQUOTE(/);

Then I just place the macro variable reference where I want the hint at.  This also stops the SAS Macro Facility from stripping out the comments on macro compilation (most of my code is managed via macros).  Doing this also allows the hints to be passed to Oracle since it's not seeing them as hints and thus not stripping them out, despite the PRESERVE_COMMENTS option not being available on the Oracle LIBNAME statement and CONNECT USING method.  After making these changes, the hints were also showing up in the SAS Trace log from Oracle, which coupled with improved performance, indicates it is working properly.

[SASKiwi]

PRESERVE_COMMENTS is available on an Oracle LIBNAME:

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

That also means if your CONNECT USING refers to the LIBNAME connection PRESERVE_COMMENTS should be honoured.

[Sven111]

Actually no, as far as I can tell.  If you look at the link you posted it specifically says:

• You specify PRESERVE_COMMENTS as an argument in the CONNECT statement. You then specify the hints in the Oracle SQL query for the CONNECTION TO component. Hints are entered as comments in the SQL query and are passed to and processed by Oracle.
• Restriction: This option is valid only in the CONNECTION statement, not in the LIBNAME statement.

So if you want to use the CONNECT USING syntax, there is no way to pass that parameter that I can see.  The BQUOTE method I outlined above bypasses the need for the parameter however.  Trying to pass that parameter in the LIBNAME statement caused an error when I tried it.

[SASKiwi]

Yes, you are correct - note to self, read documentation more carefully - I missed the restriction bit!

[jakarman]

Going for the Libname options there is a lot

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

The preserve comments is found at the explicit pass through way. http://support.sas.com/documentation/cdl/en/acreldb/67473/HTML/default/viewer.htm#n05b4mygsvt845n1vn...

Remember this is Larry Ellison native language not universal and by that you are having a lock in

The oracle dba is needed in good cooperation. That is often difficult as he is nothing understanding of sas. Mostly hating that as it is not oracle only.