topic Re: How to pass oracle hint? in SAS Data Management

How to pass oracle hint?

deh_asma — Tue, 22 May 2012 09:43:25 GMT

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

Re: How to pass oracle hint?

shivas — Tue, 22 May 2012 10:46:47 GMT

Hi,

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

Thanks,

Shiva

Re: How to pass oracle hint?

djfoot — Tue, 22 May 2012 12:00:59 GMT

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.

Re: How to pass oracle hint?

deh_asma — Tue, 22 May 2012 13:27:34 GMT

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.

Re: How to pass oracle hint?

LinusH — Tue, 22 May 2012 15:19:17 GMT

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.

Re: How to pass oracle hint?

EyalGonen — Fri, 25 May 2012 07:27:37 GMT

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

Re: How to pass oracle hint?

deh_asma — Mon, 28 May 2012 11:24:12 GMT

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.

Re: How to pass oracle hint?

EyalGonen — Mon, 28 May 2012 12:04:54 GMT

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

Re: How to pass oracle hint?

deh_asma — Mon, 28 May 2012 12:44:42 GMT

Hi,

Did you test with sql join pass-through?

Thank you

Re: How to pass oracle hint?

EyalGonen — Mon, 28 May 2012 13:45:58 GMT

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.

Re: How to pass oracle hint?

deh_asma — Mon, 28 May 2012 14:00:06 GMT

Hi,

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

Thank you