Our source is oracle ERP system where we have installed Informatica CDC, our target is Greenplum tables to which we load the data as 1-1 logic. We execute the session in real time mode which means the session will be keep on running, when any changes happened in source the session will process and reflect in target table.
We receive the below error with a data rejection.
2015-09-15 12:31:42 : ERROR : (22994 | WRITER_1__1) : (IS | INF_EN1_ASCII) : NODE_ENDEV_TNLD07879 : WRT_8229 : Database errors occurred: FnName: Execute -- [Informatica][ODBC Greenplum Wire Protocol driver]Socket closed. 2015-09-15 12:31:42 : ERROR : (22994 | WRITER_1__1) : (IS | INF_EN1_ASCII) : NODE_ENDEV_TNLD07879 : WRT_8425 : ERROR: Writer execution failed. 2015-09-15 12:31:42 : ERROR : (22994 | WRITER_1__1) : (IS | INF_EN1_ASCII) : NODE_ENDEV_TNLD07879 : WRT_8164 : Error loading into target [ods_oe_sales_credits] : 2015-09-15 12:31:42 : ERROR : (22994 | WRITER_1__1) : (IS | INF_EN1_ASCII) : NODE_ENDEV_TNLD07879 : WRT_8114 : Row # [1] in bad file
2015-09-15 12:31:42 : INFO : (22994 | WRITER_1_*_1) : (IS | INF_EN1_ASCII) : NODE_ENDEV_TNLD07879 : CMN_1053 : : Rowdata: ( RowType=1(update) Src Rowid=1 Targ Rowid=1 sales_credit_id (sales_credit_id:BigInt:): "19094" creation_date (creation_date:Date:): "09/15/2015 03:01:27.000000000" created_by (created_by:Double:): "1193.000000000000" last_update_date (last_update_date:Date:): "09/15/2015 03:01:27.000000000"
last_updated_by (last_updated_by:Double:): "1193.000000000000"
last_update_login (last_update_login:Double:): "240638.0000000000"
header_id (header_id:BigInt:): "27535" salesrep_id (salesrep_id:BigInt:): "-3" percent (percent:Double:): "100.0000000000000" line_id (line_id:BigInt:): "(NULL)" context (context:Char.60:): "(NULL)" attribute1 (attribute1:Char.480:): "(NULL)" attribute2 (attribute2:Char.480:): "(NULL)" attribute3 (attribute3:Char.480:): "(NULL)" attribute4 (attribute4:Char.480:): "(NULL)" attribute5 (attribute5:Char.480:): "(NULL)" attribute6 (attribute6:Char.480:): "(NULL)" attribute7 (attribute7:Char.480:): "(NULL)" attribute8 (attribute8:Char.480:): "(NULL)" attribute9 (attribute9:Char.480:): "(NULL)" attribute10 (attribute10:Char.480:): "(NULL)" attribute11 (attribute11:Char.480:): "(NULL)" attribute12 (attribute12:Char.480:): "(NULL)" attribute13 (attribute13:Char.480:): "(NULL)" attribute14 (attribute14:Char.480:): "(NULL)" attribute15 (attribute15:Char.480:): "(NULL)" dw_update_advice_flag (dw_update_advice_flag:Char.2:): "(NULL)" wh_update_date (wh_update_date:Date:): "(NULL)" orig_sys_credit_ref (orig_sys_credit_ref:Char.100:): "OE_SALES_CREDITS19094"
sales_credit_type_id (sales_credit_type_id:BigInt:): "1"
lock_control (lock_control:Double:): "1.000000000000000"
sales_group_id (sales_group_id:BigInt:): "-1"
sales_group_updated_flag (sales_group_updated_flag:Char.2:): "(NULL)" inst_id (inst_id:BigInt:): "(NULL)" md_source_system (md_source_system:Double:): "1.000000000000000" flag (flag:Char.2:): "I" md_deleted_flag (md_deleted_flag:Char.2:): "(NULL)" change_ts (change_ts:Date:): "09/15/2015 03:01:42.000000000" )2015-09-15 12:31:42 : ERROR : (22994 | WRITER_1__1) : (IS | INF_EN1_ASCII) : NODE_ENDEV_TNLD07879 : WRT_8164 : Error loading into target [ods_oe_sales_credits] : Bad rows exceeded Session Threshold [1] 2015-09-15 12:31:42 : INFO : (22994 | WRITER_1__1) : (IS | INF_EN1_ASCII) : NODE_ENDEV_TNLD07879 : WRT_8333 : Rolling back all the targets due to fatal session error. 2015-09-15 12:31:42 : ERROR : (22994 | WRITER_1_*_1) : (IS | INF_EN1_ASCII) : NODE_ENDEV_TNLD07879 : CMN_1022 : Database driver error... CMN_1022 []
Not sure where the actual problem is(either informatica/DB side) when checked the bad file it says that the records are rejected for NULL issues
Bad file content:
1,D,19094,D,09/15/2015 03:01:27.000000000,D,1193,D,09/15/2015 03:01:27.000000000,D,1193,D,240638,D,27535,D,-3,D,100,D,,N,,N,,N,,N,,N,,N,,N,,N,,N,,N,,N,,N,,N,,N,,N,,N,,N,,N,,N,OE_SALES_CREDITS19094,D,1,D,1,D,-1,D,,N,,N,1,D,I,D,,N,09/15/2015 03:01:42.000000000,D
But the same session process other records which have null values as well. Couldn't understand where exactly the issue is.
Pls help me in this ...
we got a solution for this. GP firewall setting was the culprit which made the long running queries to fail. GP DBA overridden this setting in DB level and now all our CDC sessions were running for days together without failing.