Error inserting into Netezza table using ODBC Stage
Posted: Tue Jul 29, 2008 12:00 pm
We've got a very simple job (OS is AIX) that had been working fine until this month when it began failing on our test system with the following error:
ERROR: Attribute 'C0' not found
The job reads from a DataSet, goes through a Transform which does Null handling on those fields that are nullable and then inserts into a Netezza table using an ODBC stage (where the failure occurs). The use of an ODBC stage is required as some of the fields contain special characters that cause the nzload process to fail.
DEBUG gave us this:
2008-07-22 16:51:01.281950 CDT [6208] DEBUG: QUERY: insert into MKW_FLAT_LD_MKV_CIF(CUST_NM,CUST_CRT_DT,CUST_CLS_DT,ACCT_ANALY_IND,ACCT_BILL_FREQ_CD,DEP_PROD_TYP_CD,CUST_REC_TYP_CD,GCI_ID,CO_CST_CTR_ID,CUST_CLAS_CD,DEP_APP_SYS_ID,ANALY_APP_SY
S_ID,SRC_ACCT_STAT_CD,ACCT_STAT_CD,CUST_ACCT_SRC_ID,CIF_MKT_SEG_GRP_CD,CIF_MKT_SEG_CD,NXT_BILL_DT,IB_ACCT_IND,PHYS_STATE_CD,PER_CCYYMM,CIF_ID_PFX,CIF_ID,CIF_ID_SFX,B
LT_ID_PFX,BLT_ID,BLT_ID_SFX,SUB_PROD_ID,SUB_ACCT_NUM,ACCT_CHRG_STAT_CD,CLIENT_MGR_ID) values
(c0,c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20,c21,c22,c23,c24,c25,c26,c27,c28,c29,c30)
It appears that c0 through c30 are internal variable names that are incorrectly being passed to Netezza. As Netezza expects character strings to be enclosed in quotes it fails.
Adding a constraint to the Transform that eliminates any records from processing allows the job to complete. That is, if you use constraint CIF_ID > 30000000000 the job completes. If you use CIF_ID <= 30000000000 the job completes. It is only when all records are being processed and only near the very end of processing (just under 10 million records) that the job fails. I've tried adding in a Sort stage both before and after the transform with the same result.
Has anyone seen anything similar to this?
Thanks,
ERROR: Attribute 'C0' not found
The job reads from a DataSet, goes through a Transform which does Null handling on those fields that are nullable and then inserts into a Netezza table using an ODBC stage (where the failure occurs). The use of an ODBC stage is required as some of the fields contain special characters that cause the nzload process to fail.
DEBUG gave us this:
2008-07-22 16:51:01.281950 CDT [6208] DEBUG: QUERY: insert into MKW_FLAT_LD_MKV_CIF(CUST_NM,CUST_CRT_DT,CUST_CLS_DT,ACCT_ANALY_IND,ACCT_BILL_FREQ_CD,DEP_PROD_TYP_CD,CUST_REC_TYP_CD,GCI_ID,CO_CST_CTR_ID,CUST_CLAS_CD,DEP_APP_SYS_ID,ANALY_APP_SY
S_ID,SRC_ACCT_STAT_CD,ACCT_STAT_CD,CUST_ACCT_SRC_ID,CIF_MKT_SEG_GRP_CD,CIF_MKT_SEG_CD,NXT_BILL_DT,IB_ACCT_IND,PHYS_STATE_CD,PER_CCYYMM,CIF_ID_PFX,CIF_ID,CIF_ID_SFX,B
LT_ID_PFX,BLT_ID,BLT_ID_SFX,SUB_PROD_ID,SUB_ACCT_NUM,ACCT_CHRG_STAT_CD,CLIENT_MGR_ID) values
(c0,c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20,c21,c22,c23,c24,c25,c26,c27,c28,c29,c30)
It appears that c0 through c30 are internal variable names that are incorrectly being passed to Netezza. As Netezza expects character strings to be enclosed in quotes it fails.
Adding a constraint to the Transform that eliminates any records from processing allows the job to complete. That is, if you use constraint CIF_ID > 30000000000 the job completes. If you use CIF_ID <= 30000000000 the job completes. It is only when all records are being processed and only near the very end of processing (just under 10 million records) that the job fails. I've tried adding in a Sort stage both before and after the transform with the same result.
Has anyone seen anything similar to this?
Thanks,