Blog Network Home > Informatica Way > Informatica and Oracle hints in SQL overrides

Informatica and Oracle hints in SQL overrides

by Ganesan Thiyagarajan on April 22, 2009 in Informatica Way

HINTS used in a SQL statement helps in sending instructions to the Oracle optimizer which would quicken the query processing time involved. Can we make use of these hints in SQL overrides within our Informatica mappings so as to improve a query performance?

On a general note any Informatica help material would suggest: you can enter any valid SQL statement supported by the source database in a SQL override of a Source qualifier or a Lookup transformation or at the session properties level.

While using them as part of Source Qualifier has no complications, using them in a Lookup SQL override gets a bit tricky. Use of forward slash followed by an asterix (“/*”) in lookup SQL Override [generally used for commenting purpose in SQL and at times as Oracle hints.] would result in session failure with an error like:

TE_7017 : Failed to Initialize Server Transformation lkp_transaction

2009-02-19 12:00:56 : DEBUG : (18785 | MAPPING) : (IS | Integration_Service_xxxx) : node01_UAT-xxxx : DBG_21263 : Invalid lookup override

SELECT SALES. SALESSEQ as SalesId, SALES.OrderID as ORDERID, SALES.OrderDATE as ORDERDATE FROM SALES, AC_SALES WHERE AC_SALES. OrderSeq >= (Select /*+ FULL(AC_Sales) PARALLEL(AC_Sales,12) */ min(OrderSeq) From AC_Sales)

This is because Informatica’s parser fails to recognize this special character when used in a Lookup override. There has been a parameter made available starting with PowerCenter 7.1.3 release, which enables the use of forward slash or hints.

§ Infa 7.x

1. Using a text editor open the PowerCenter server configuration file (pmserver.cfg).

2. Add the following entry at the end of the file:

LookupOverrideParsingSetting=1

3. Re-start the PowerCenter server (pmserver).

§ Infa 8.x

1. Connect to the Administration Console.

2. Stop the Integration Service.

3. Select the Integration Service.

4. Under the Properties tab, click Edit in the Custom Properties section.

5. Under Name enter LookupOverrideParsingSetting

6. Under Value enter 1.

7. Click OK.

8. And start the Integration Service.

§ Starting with PowerCenter 8.5, this change could be done at the session task itself as follows:

1. Edit the session.

2. Select Config Object tab.

3. Under Custom Properties add the attribute LookupOverrideParsingSetting and set the Value to 1.

4. Save the session.

You might want to read these awesome related posts


{ 9 comments }

1 Manish July 16, 2009 at 4:21 pm

Hi Ganesh,

Is it possible to include oracle hints in targets as well? Hints like /*+ APPEND */ for direct load or some clauses?

Thanks,
Manish

2 Ritesh July 29, 2009 at 6:59 am

Ganesh –

Also,Please be aware that you cannot use hints in the Pre and Post SQL in Informatica (even with their latest version, 8.6). You could add them, and the log might show that it’s executing it but if you look on the DB side,it won’t. It kind of misleading but that’s what we found during one of our performance tuning exercises.

3 Ganesan Thiyagarajan July 29, 2009 at 10:17 pm

Hi Rithesh,

Can you be more specific on the performance issue that you faced and the hint you used to solve it.

Just to be clear per my understanding hint passes an instruction to optimizer to decide its execution plan. So eventually the hint should be part of the query which you want to optimize.

If your hint is a part of Pre / Post SQL then it would only optimize the Pre / Post SQL query.

Was it like you entered a hint in the pre sql and you expected it to optimize the target write query. If so it is not going to happen, since the hint is not a part of the write query which is generated on the fly by Informatica. Only if you can find a way to edit the target write query just before it hits the database you can acheive this.

So usage of hints is possible in Informatica pre/post sqls.

Correct me if I’ve misunderstood your point.

Regards,
Ganesh

4 Ganesan Thiyagarajan July 29, 2009 at 10:22 pm

Hi Manish,

My earlier reply to Ritesh might as well answer your doubt. I would see if it is possible to alter a Informatica write query so as to use “Append” as part of it, not sure if there is a way, will let you know.

In the mean time please do share your views in case you’ve already fixed this.

Regards,
Ganesh

5 Poornima September 14, 2009 at 8:36 pm

Hi,
I do have Infa 7.1.1.I am unable to see the file pmserver.cfg. Can you let me know where the file lies?When I search for pmserver, I get 3 files pmserver.prm, pmserver.exe, pmserver.jar.Are you talking about the file pmserver.prm?Also without any modifications, the hints work for me.I was just curious to know.

6 Misha September 23, 2009 at 5:06 am

Hi

I would like to request for help.
I have a requirement. I am asked to design a mapping that connects to oracle and sends email displaying system date from oracle, if it gets connected.
If it does not connect, it should send email to users by telling them the issue and connection failure message.

Can anyone help?

Thanks a lot.

Regards
Misha

7 Manish October 27, 2009 at 8:47 am

1:DBA’s are saying that they are not able to see Parallel Session Running in DB when we submit SQL from Informatica, but Same SQL is showing as Parallel when running from Tode or SQL Developer….

8 hital April 8, 2011 at 5:59 am

Hi Ganesh,

Was going through ur updates on using “to alter a Informatica write query so as to use “Append” as part of it “.Is there a way of doing it informatica 8.1 of using /*+ APPEND */

feature in informatica write query .
Awaiting for repsonse.

Thanks in advance.

Regards,
Hital

9 Salam Khan July 21, 2011 at 8:23 pm

Hi Ganesh,

When we ran our Initial Load workflow, we received the following error message:
“TE_7017 Internal error. Failed to initialize transformation [LKP_REF_BASE_CITY{{BLD}}].

Can you please provide me steps to resolve this issue.

Thank you,
Salam Khan

Comments on this entry are closed.