Datastage Job terminating due to the following error

Question

I'm running a data stage job, Input through DB2 and output to DB2. Input side has a query containing joins and functions.

I'm getting the following warning message;

TRN_HEALTH_INSURANCE_DETAIL,
2: STATEMENT 
INSERT
INTO
HEALTH_INSURANCE_DETAIL
(
    RISK_DETAIL_ID,
    RISK_COVER_ID,
    RD_POLICY_SYSTEM_NO,
    RD_POLICY_END_NO_IDX,
    RD_POLICY_ID,
    RD_LEVEL1_ID,
    RD_SUM_INSURED_AMT_LC,
    RD_PREMIUM_AMT_LC,
    PREMIUM_AMOUNT_FC,
    SUM_INSURED_AMT_FC,
    RD_REC_TYPE,
    RD_EFFECT_FROM_DT,
    RD_EFFECT_TO_DT,
    RD_END_EFFECT_FROM_DT,
    SEX_MAS_CD,
    MARITAL_STATUS_CD,
    EMP_CATG,
    NO_OF_DEPENDENTS,
    EMP_AL_NO,
    DOB,
    EFF_DATE,
    EFF_DATE2,
    NAME,
    RELATIONSHIP_CD_S,
    RELATIONSHIP_CD,
    DESIGNATION,
    BRANCH,
    BANK_ACCOUNT,
    BANK_BRANCH_NAME,
    PRE_EXISTING_AILMENT,
    AUTHORITY_LETTER_NO,
    AGE,
    REGION,
    CNIC,
    CO_CODE,
    EMP_LOCATION,
    SUB_LOCATION,
    CLH_SYSTEM_NO,
    CTH_SYS_ID,
    CTH_POL_SYS_ID,
    CTH_END_NO_IDX,
    CTH_END_SR_NO,
    CTH_CATEGORY,
    CLD_SYS_ID,
    CLDH_SYS_ID,
    CLD_COVER_CD,
    CLD_END_IDX,
    CLD_COVER_DESC,
    CLD_CLM_TYPE_LIMIT,
    CLD_CLM_REL,
    CLD_CLM_AGE_FROM,
    CLD_CLM_AGE_TO,
    CLD_CLM_RB_LIMIT,
    CLD_CATEGORY_LIMIT_FC,
    CLD_CATEGORY_PREM_FC
        )
            VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) failed to run.

I cant see such records in my data. The data quality is good. Then what are these ????, I search a bit and found a suggestion to keep the array size and row count to 1, instead of default 2000. But still I'm getting the same warning.

There are a lot of errors followed by this warning; The next error is also interesting.

TRN_HEALTH_INSURANCE_DETAIL,2: SQLExecute reported: SQLSTATE = 23505: Native Error Code = -803: Msg = [IBM][CLI Driver][DB2/NT64] SQL0803N  One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "DB2ADMIN.HEALTH_INSURANCE_DETAIL" from having duplicate values for the index key.  SQLSTATE=23505 (CC_DB2DBStatement::executeInsert, file CC_DB2DBStatement.cpp, line 1,095)

I believe the errors are due to the first warning. Kindly help me out. Regards, Nuh

Check the table structure. specifically, what fields are defined as being the primary key and also unique indexes. the error is telling you that you are violating a unique constraint on your table. — Adrian Cornwell, Nov 22 '13 at 08:33
Hi Adrian, The PK defined is of first three Columns that are; (RISK_DETAIL_ID, RISK_COVER_ID,D_POLICY_SYSTEM_NO). I already check the source and there is no duplication in records. As for the duplication Indexes. How can I check that? Indexes on that table is only for the PK. — Nuh, Nov 22 '13 at 21:28

score 1 · Accepted Answer · answered Nov 22 '13 at 18:00

1

Make a copy stage before the DB2 connector and put one link to the DB2 and the other to a dataset file to see the data in a data set. But the problem seems to be in the primary key you have a duplicate primary index or a duplicate unique index. It can be either in your data that you want to insert or maybe the table already have a record that you want to insert again

answered Nov 22 '13 at 18:00

Random

467
1
4
9

Hi Random, The tables action is set to 'Replace' so all the prior records are removed before insertions starts. As for the uniqueness, the first 3 Columns (RISK_DETAIL_ID, RISK_COVER_ID, RD_POLICY_SYSTEM_NO) are defined as PK. I checked the source with a query, there is no multiple record for any of the primary key set. I'm not sure about the 'duplicate unique index' thing. How can I check that? I checked the indexes on the table. Its the default index that is made for the PK. – Nuh Nov 22 '13 at 21:26
Hi,Can you post the DDL used to create this table as the error could also be caused by an invalid Foreign key constraint (ie: Parent Record not found) – Adrian Cornwell Nov 25 '13 at 07:30
Do you have a unique constraint other than the PK? And also what do you mean by "replace" do you mean "delete than insert"? – Random Nov 25 '13 at 21:38

Datastage Job terminating due to the following error

1 Answers1