HI,
I want to use orale hint in Pass-through SQL Join under SAS DATA INTEGRATION, for that, i add %STR(/)%STR(*)+parallel(CONTRAT ,2)%STR(*)%STR(/) in ORHINTS option as shown in below screenshot. But this option is not displayed in SAS generated code. In SAS documentation, i found that i shoud add preserve_comments option to force SAS to pass the oracle hint.
Where can i add preserve_comments option under SAS DATA INTEGRATION?
thank you
Oops...Sorry. Now I see what you mean...
It seems that invoking SQL pass-through causes Data Integration Studio to ignore the hints you specify in the table properties.
I have no better solution but to NOT use pass through for this query and then the hints seem to be generated OK (using implicit pass through) or as a last resort and if you need full control of the SQL then write the SQL pass through code yourself as a user written code in the DI job.
Hi,
I don't have SAS 4.3 DI studio to check ,but check this it might be helpful.
Thanks,
Shiva
You need to apply the preserve_comments statement in the libname statement. Go into SAS Management Console (looks like you're using SAS 9.2 or SAS 9.3) and select 'Data Library Manager' and then expand your list of libraries. Right-click the library you need to change and select 'Properties. Select 'Options' then 'Advanced Options' then 'Other options. Type 'PRESERVE_COMMENTS' in the field and apply the change.
Regenerate your DI code. The libname statement will now have the 'PRESERVE_COMMENTS' value so that you can pass the hint.
Hi,
Thank you.
I followed these steps, preserve_comments statement is added in the libname statement but it's not applied in sql pass-through statement(connect to) and
the hint option is not displayed in SAS generated code.
Adding options to the libname in SMC does not make any changes how DIS generates the code. In this case, this option will only open the possibility to use oracle comments in your code (which you need anyway).
The issue of the comment/hint not showing up in the generated code could possible be a DIS bug (I don't have an oracle to test same, sorry). I suggest that you open a track to tech support.
Hi,
The ORHINTS is a data set option (see http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001371596.htm) so I *guess* you should see it in the DIS generated code as part of the table name and not in the libname statement. For example, you should see something like DW_F_CONTRATO(orhints='/*+parallel(CONTRAT ,2) */') in your SQL join code.
Did you look there?
Eyal
Hi,
thank you
yes exactely, after that i add oracle hint in ORHINTS option in SQL Join Properties pane, i should see in generated code something like:
select '/*+parallel(CONTRAT ,2) */' sum(MONTANT).......
but the oracle hint is not dispayed in generated code.
Well...I just tested on DIS 4.4 and it does seem to work fine...
Here is the partial SQL code I got (aaaa is the hint I typed - it is just to see if it works or not):
from
oralocal.CLASSFIT
(
ORHINTS = aaaa
),
test.FACT1
;
quit;
Note that the ORHINTS data set option is found in the "from" statement and not in the "select" statement. Did you look there?
If the problem still persists maybe you should contact SAS Technical Support for this.
Eyal
Hi,
Did you test with sql join pass-through?
Thank you
Oops...Sorry. Now I see what you mean...
It seems that invoking SQL pass-through causes Data Integration Studio to ignore the hints you specify in the table properties.
I have no better solution but to NOT use pass through for this query and then the hints seem to be generated OK (using implicit pass through) or as a last resort and if you need full control of the SQL then write the SQL pass through code yourself as a user written code in the DI job.
Hi,
I used implicit pass through and the oracle hint is displayed in generated code.
Thank you
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.