BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
deh_asma
Calcite | Level 5

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

hint.jpg

1 ACCEPTED SOLUTION

Accepted Solutions
EyalGonen
Lapis Lazuli | Level 10

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.

View solution in original post

10 REPLIES 10
shivas
Pyrite | Level 9

Hi,

I don't have SAS 4.3 DI studio to check ,but check this it might be helpful.

Thanks,

Shiva

Columns.png

djfoot
Fluorite | Level 6

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.

deh_asma
Calcite | Level 5

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.

LinusH
Tourmaline | Level 20

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.

Data never sleeps
EyalGonen
Lapis Lazuli | Level 10

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

deh_asma
Calcite | Level 5

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.

EyalGonen
Lapis Lazuli | Level 10

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

deh_asma
Calcite | Level 5

Hi,

Did you test with sql join pass-through?

Thank you


EyalGonen
Lapis Lazuli | Level 10

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.

deh_asma
Calcite | Level 5

Hi,

I used implicit pass through and the oracle hint is displayed in generated code.

Thank you

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to connect to databases in SAS Viya

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.

Discussion stats
  • 10 replies
  • 8431 views
  • 0 likes
  • 5 in conversation