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.

Posted by Ganesan Thiyagarajan
Comments (9)
April 22nd, 2009

Comments (9)

Salam Khan - July 21st, 2011

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

hital - April 8th, 2011

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

Manish - October 27th, 2009

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....

Misha - September 23rd, 2009

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

Poornima - September 14th, 2009

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.

Ganesan Thiyagarajan - July 29th, 2009

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

Ganesan Thiyagarajan - July 29th, 2009

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

Ritesh - July 29th, 2009

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.

Manish - July 16th, 2009

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

Comments are closed.