Unable to insert a record into the table

[dspradeep]

Hi , we are getting the below error for a job in QA, but not in DEV and PROD.

The Job design is same in all the environment.

Code: Select all

Copy_of_Ora_JobPackage,0: Unable to insert a record into the table.
main_program: APT_PMsectionLeader(2, node2), player 5 - Unexpected exit status 1.
APT_PMsectionLeader(2, node2), player 6 - Unexpected exit status 1.
APT_PMsectionLeader(2, node2), player 7 - Unexpected exit status 1.
APT_PMsectionLeader(2, node2), player 8 - Unexpected exit status 1.
APT_PMsectionLeader(2, node2), player 9 - Unexpected exit status 1.
APT_PMsectionLeader(1, node1), player 9 - Unexpected exit status 1.

I have added apt_dump_score in my job and made true that value. My understaning is, I will get single line log info for dump score. I belive this is the line it written

Code: Select all

main_program: This step has 13 datasets:
ds0: {op0[1p] (sequential Copy_of_Ora_JobDefinitionSettingJPK)
      eOther(APT_HashPartitioner { key={ value=TRANSIDCOUNTSTART }
})<>eCollectAny
      op5[2p] (parallel inserted tsort operator {key={value=TRANSIDCOUNTSTART, subArgs={asc, nulls={value=first}}}}(0) in Copy_of_Join_23)}
ds1: {op1[1p] (sequential Copy_of_Ora_Refids)
      eAny<>eCollectAny
      op4[2p] (parallel buffer(0))}
ds2: {op2[1p] (sequential APT_CombinedOperatorController:Copy_of_XML_Input_3)
      eAny<>eCollectAny
      op3[2p] (parallel APT_TransformOperatorImplV9S1_PB_MS1_JOBPKG_Job3_Feb16old_Copy_of_Transformer_35 in Copy_of_Transformer_35)}
ds3: {op3[2p] (parallel APT_TransformOperatorImplV9S1_PB_MS1_JOBPKG_Job3_Feb16old_Copy_of_Transformer_35 in Copy_of_Transformer_35)
      eEntire>>eCollectAny
      op6[1p] (parallel APT_LUTCreateOp in Copy_of_Lookup_34)}
ds4: {op4[2p] (parallel buffer(0))
      eSame=>eCollectAny
      op7[2p] (parallel APT_LUTProcessOp in Copy_of_Lookup_34)}
ds5: {op5[2p] (parallel inserted tsort operator {key={value=TRANSIDCOUNTSTART, subArgs={asc, nulls={value=first}}}}(0) in Copy_of_Join_23)
      [pp] eSame=>eCollectAny
      op9[2p] (parallel APT_JoinSubOperatorNC in Copy_of_Join_23)}
ds6: {op6[1p] (parallel APT_LUTCreateOp in Copy_of_Lookup_34)
      eEntire<>eCollectAny
      op7[2p] (parallel APT_LUTProcessOp in Copy_of_Lookup_34)}
ds7: {op6[1p] (parallel APT_LUTCreateOp in Copy_of_Lookup_34)
      eAny<>eCollectAny
      op7[2p] (parallel APT_LUTProcessOp in Copy_of_Lookup_34)}
ds8: {op7[2p] (parallel APT_LUTProcessOp in Copy_of_Lookup_34)
      eOther(APT_HashPartitioner { key={ value=TRANSIDCOUNTSTART }
})#>eCollectAny
      op8[2p] (parallel inserted tsort operator {key={value=TRANSIDCOUNTSTART, subArgs={asc, nulls={value=first}}}}(1) in Copy_of_Join_23)}
ds9: {op8[2p] (parallel inserted tsort operator {key={value=TRANSIDCOUNTSTART, subArgs={asc, nulls={value=first}}}}(1) in Copy_of_Join_23)
      [pp] eSame=>eCollectAny
      op9[2p] (parallel APT_JoinSubOperatorNC in Copy_of_Join_23)}
ds10: {op9[2p] (parallel APT_JoinSubOperatorNC in Copy_of_Join_23)
      eAny=>eCollectAny
      op10[2p] (parallel APT_TransformOperatorImplV9S0_PB_MS1_JOBPKG_Job3_Feb16old_Copy_of_Transformer_36 in Copy_of_Transformer_36)}
ds11: {op10[2p] (parallel APT_TransformOperatorImplV9S0_PB_MS1_JOBPKG_Job3_Feb16old_Copy_of_Transformer_36 in Copy_of_Transformer_36)
      eAny=>eCollectAny
      op11[2p] (parallel Copy_of_Ora_JobDetail)}
ds12: {op10[2p] (parallel APT_TransformOperatorImplV9S0_PB_MS1_JOBPKG_Job3_Feb16old_Copy_of_Transformer_36 in Copy_of_Transformer_36)
      eAny=>eCollectAny
      op12[2p] (parallel Copy_of_Ora_JobPackage)}
It has 13 operators:
op0[1p] {(sequential Copy_of_Ora_JobDefinitionSettingJPK)
    on nodes (
      node1[op0,p0]
    )}
op1[1p] {(sequential Copy_of_Ora_Refids)
    on nodes (
      node2[op1,p0]
    )}
op2[1p] {(sequential APT_CombinedOperatorController:
      (Copy_of_External_Source_61)
      (Copy_of_XML_Input_3)
    ) on nodes (
      node1[op2,p0]
    )}
op3[2p] {(parallel APT_TransformOperatorImplV9S1_PB_MS1_JOBPKG_Job3_Feb16old_Copy_of_Transformer_35 in Copy_of_Transformer_35)
    on nodes (
      node1[op3,p0]
      node2[op3,p1]
    )}
op4[2p] {(parallel buffer(0))
    on nodes (
      node1[op4,p0]
      node2[op4,p1]
    )}
op5[2p] {(parallel inserted tsort operator {key={value=TRANSIDCOUNTSTART, subArgs={asc, nulls={value=first}}}}(0) in Copy_of_Join_23)
    on nodes (
      node1[op5,p0]
      node2[op5,p1]
    )}
op6[1p] {(parallel APT_LUTCreateOp in Copy_of_Lookup_34)
    on nodes (
      node1[op6,p0]
    )}
op7[2p] {(parallel APT_LUTProcessOp in Copy_of_Lookup_34)
    on nodes (
      node1[op7,p0]
      node2[op7,p1]
    )}
op8[2p] {(parallel inserted tsort operator {key={value=TRANSIDCOUNTSTART, subArgs={asc, nulls={value=first}}}}(1) in Copy_of_Join_23)
    on nodes (
      node1[op8,p0]
      node2[op8,p1]
    )}
op9[2p] {(parallel APT_JoinSubOperatorNC in Copy_of_Join_23)
    on nodes (
      node1[op9,p0]
      node2[op9,p1]
    )}
op10[2p] {(parallel APT_TransformOperatorImplV9S0_PB_MS1_JOBPKG_Job3_Feb16old_Copy_of_Transformer_36 in Copy_of_Transformer_36)
    on nodes (
      node1[op10,p0]
      node2[op10,p1]
    )}
op11[2p] {(parallel Copy_of_Ora_JobDetail)
    on nodes (
      node1[op11,p0]
      node2[op11,p1]
    )}
op12[2p] {(parallel Copy_of_Ora_JobPackage)
    on nodes (
      node1[op12,p0]
      node2[op12,p1]
    )}
It runs 22 processes on 2 nodes.

With the help of this info I am not able to guess what is the issue

please any one help me

[dspradeep]

I like to give some more error info

Code: Select all

Copy_of_Ora_JobPackage,0: The runLocally() of the operator failed.
Copy_of_Ora_JobPackage,0: The runLocally() of the operator failed.
Copy_of_Ora_JobPackage,0: Input 0 consumed 500 records.
Copy_of_Ora_JobPackage,0: Operator terminated abnormally: runLocally() did not return APT_StatusOk
main_program: APT_PMsectionLeader(2, node2), player 6 - Unexpected exit status 1.
APT_PMsectionLeader(2, node2), player 8 - Unexpected exit status 1.
APT_PMsectionLeader(2, node2), player 9 - Unexpected exit status 1.
main_program: Step execution finished with status = FAILED.

please guide me how to rectify the same

[chulett]

What's different about the target table in QA? Regardless, see if an exact search for "Unable to insert a record into the table" helps.

[ArndW]

Do you have custom SQL DML in the Oracle stage? It is highly likely that the problem lies in the data or table definitions (keys, constraints, triggers, etc.) and not in DataStage directly.

Addendum - this time Craig was faster on the trigger