Powcenter Lookup Transformation parse error

昨天同事跟我说他做的lookup 出现很奇怪的错误，override sql 明明是好好的，但到workflow里跑就出了问题，这个override 里有查询优化的语句，还有分析函数，跑是就是莫名其妙少了语句。

 

后来在网上找了一些资料，问题终于解决。

 

How to do:

For other SQL overrides, configure the following custom property in the Administration Console for the Integration Service:

lookupOverrideParsingSetting=1

 

Restart the integration server, the problem has been settled.

 

 

更详细的参考：

Informatica and Oracle hints in SQL overrides

By Ganesan Thiyagarajan on April 22nd, 2009 under 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.