We have recently migrated to Informatica 10 from Informatica 9.6 and were experiencing degradation in performance especially in reading from sql server sources.
RR_4050 First row returned from database to reader takes more than 30 minutes while in 9.6 it used to take 10 sec.
Version 9.6
2017-10-07 11:24:26 : INFO : (4816 | WRITER_1_*_1) : (IS | IS_INFA_INTG) : node_01 : WRT_8158 :
*****START LOAD SESSION*****
Load Start Time: Sat Oct 07 11:24:26 2017
Target tables:
TABLE_CUSTOMER
2017-10-07 11:24:26 : INFO : (4816 | READER_1_1_1) : (IS | IS_INFA_INTG) : node_01 : RR_4049 : RR_4049 SQL Query issued to database : (Sat Oct 07 11:24:26 2017)
2017-10-07 11:24:36 : INFO : (4816 | READER_1_1_1) : (IS | IS_INFA_INTG) : node_01 : RR_4050 : RR_4050 First row returned from database to reader : (Sat Oct 07 11:24:36 2017)
Version 10.1.1
2017-10-07 12:21:34 : INFO : (6672 | WRITER_1_*_1) : (IS | IS_INFA_INTG) : node_01 : WRT_8158 :
*****START LOAD SESSION*****
Load Start Time: Sat Oct 07 12:21:34 2017
Target tables:
TABLE_CUSTOMER
2017-10-07 12:21:34 : INFO : (6672 | READER_1_1_1) : (IS | IS_INFA_INTG) : node_01 : RR_4049 : RR_4049 SQL Query issued to database : (Sat Oct 07 12:21:34 2017)
2017-10-07 12:51:23 : INFO : (6672 | READER_1_1_1) : (IS | IS_INFA_INTG) : node_01 : RR_4050 : RR_4050 First row returned from database to reader : (Sat Oct 07 12:51:23 2017)
Root Cause
The issue is the SQLServer DB side where by default, the static cursor usage is on.
For every query raised to the DB, a cursor is created. That process could take much time if the result of the query is in the order of millions of records.
Solution
We can disable the static cursors in the DB,by add the following property in your service or custome property at session level
For PowerCenter Integration Service, set the CUSTOM PROPERTY DisableStaticCursorsForSQLServerODBCProvider in the properties section within the processes tab of the IS, and set it to Yes.
RR_4050 First row returned from database to reader takes more than 30 minutes while in 9.6 it used to take 10 sec.
Version 9.6
2017-10-07 11:24:26 : INFO : (4816 | WRITER_1_*_1) : (IS | IS_INFA_INTG) : node_01 : WRT_8158 :
*****START LOAD SESSION*****
Load Start Time: Sat Oct 07 11:24:26 2017
Target tables:
TABLE_CUSTOMER
2017-10-07 11:24:26 : INFO : (4816 | READER_1_1_1) : (IS | IS_INFA_INTG) : node_01 : RR_4049 : RR_4049 SQL Query issued to database : (Sat Oct 07 11:24:26 2017)
2017-10-07 11:24:36 : INFO : (4816 | READER_1_1_1) : (IS | IS_INFA_INTG) : node_01 : RR_4050 : RR_4050 First row returned from database to reader : (Sat Oct 07 11:24:36 2017)
Version 10.1.1
2017-10-07 12:21:34 : INFO : (6672 | WRITER_1_*_1) : (IS | IS_INFA_INTG) : node_01 : WRT_8158 :
*****START LOAD SESSION*****
Load Start Time: Sat Oct 07 12:21:34 2017
Target tables:
TABLE_CUSTOMER
2017-10-07 12:21:34 : INFO : (6672 | READER_1_1_1) : (IS | IS_INFA_INTG) : node_01 : RR_4049 : RR_4049 SQL Query issued to database : (Sat Oct 07 12:21:34 2017)
2017-10-07 12:51:23 : INFO : (6672 | READER_1_1_1) : (IS | IS_INFA_INTG) : node_01 : RR_4050 : RR_4050 First row returned from database to reader : (Sat Oct 07 12:51:23 2017)
Root Cause
The issue is the SQLServer DB side where by default, the static cursor usage is on.
For every query raised to the DB, a cursor is created. That process could take much time if the result of the query is in the order of millions of records.
Solution
We can disable the static cursors in the DB,by add the following property in your service or custome property at session level
For PowerCenter Integration Service, set the CUSTOM PROPERTY DisableStaticCursorsForSQLServerODBCProvider in the properties section within the processes tab of the IS, and set it to Yes.
No comments:
Post a Comment