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

How to pass oracle hint?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

How to pass oracle hint?

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


Accepted Solutions
Solution
‎05-28-2012 09:45 AM
SAS Employee
Posts: 21

Re: How to pass oracle hint?

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


All Replies
Super Contributor
Posts: 349

Re: How to pass oracle hint?

Hi,

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

Thanks,

Shiva

Columns.png

Occasional Contributor
Posts: 7

Re: How to pass oracle hint?

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.

Occasional Contributor
Posts: 15

Re: How to pass oracle 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.

Super User
Posts: 5,437

Re: How to pass oracle hint?

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
SAS Employee
Posts: 21

Re: How to pass oracle hint?

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

Occasional Contributor
Posts: 15

Re: How to pass oracle hint?

Posted in reply to EyalGonen

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.

SAS Employee
Posts: 21

Re: How to pass oracle hint?

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

Occasional Contributor
Posts: 15

Re: How to pass oracle hint?

Posted in reply to EyalGonen

Hi,

Did you test with sql join pass-through?

Thank you


Solution
‎05-28-2012 09:45 AM
SAS Employee
Posts: 21

Re: How to pass oracle hint?

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.

Occasional Contributor
Posts: 15

Re: How to pass oracle hint?

Posted in reply to EyalGonen

Hi,

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

Thank you

🔒 This topic is solved and locked.

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

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