Teradata Connector in BULK Mode (Write mode = Update)

[shershahkhan]

I am trying to update a view through teradata Connector stage with the following settings
1) Write Mode = Update
2) Bulk Mode
3) Data Volumn (51million)
4) MissingUpdateRow = Default
5) Error1 table = ET_DWH_ARRG_CALC_VAL_Update
6) Error2 table = UV_DWH_ARRG_CALC_VAL_Update
7) Log table = LOG_DWH_ARRG_CALC_VAL_Update
8 ) Work table = WRK_DWH_ARRG_CALC_VAL_Update
9) Updating the table through a view

When i try to run the job with give the following error after passing through all the rows took 8 hours to return this error

'Update_ARRG_CALC_VAL,0: [IIS-CONN-TERA-005004] RDBMS code 5628: Column ARRG_ID not found in GB014207.UV_DWH_ARRG_CALC_VAL_Update. SQL statement: LOCK ROW FOR ACCESS SELECT DBCErrorCode, TRIM(DBCErrorField), SMTSeq, ImportSeq, DMLSeq, ApplySeq, SourceSeq, Uniqueness, CAST(ARRG_ID AS DECIMAL(18 )), CAST(CALC_PROJ_TYCD AS VARCHAR(8 )), CAST(CALC_VAL_TYCD AS VARCHAR(8 )), CAST(REC_ACDT AS VARCHAR(10)), CAST(REC_EDT AS VARCHAR(10)) FROM UV_DWH_ARRG_CALC_VAL_Update (CC_TeraConnection::executeSelect, file CC_TeraConnection.cpp, line 2,215)'

It is looking for the table column in the one of the error table and it do no contain the column rather Error code for each row

If i do the following it works fine
1) MissingUpdateRows = ignor (which i don't want as i need to abor the job if the rows are not in the view)

2) if instead of view i update on the table

Do you know if this is some Datastage bug? and can we do some workaround for this, the following are my constraint
1) Need to update through view
2) Need to use Teradata Connector Stage
3) Need to keep MissingUpdateRows = Default

[toshea]

It appears to be a combination of a Teradata Connector bug and a limitation in the Parallel Transporter's Update operator. If you update a table, missing rows and unique constraint violations go into error table 2 (the _UV table), and that error table will contain the column values that caused the violation. That way you know which rows failed. The Teradata Connector attempts to query those values and send them down the reject link.

But when you load a view, appearently the Update operator does not put those column values into the _UV table. The Teradata Connector fails when it attempts to query those values, since the columns are missing from the error table. Even if IBM fixed the Teradata Connector such that it would not fail, you would not be able to get the information you're looking for, i.e. which rows failed with a unique constraint violation. The only useful information that the Teradata Connector would be able to retrieve from the error table is the DBCErrorCode such as Teradata error 2794. So you'd be able to determine whether some rows failed because they were missing or they had a unique constraint violation, but you wouldn't know which rows they were.

The good thing is, the job aborted which is what you wanted. The Teradata Connector only queries the _UV table if the Update operator inserted rows into there. If there were no missing rows or unique constraint violations, the Teradata Connector would not have queried the _UV table and the job would not have aborted.

If you want to view the contents of the _UV table, you can change the connector's "Cleanup mode" property from Drop to Keep so that it does not drop the _UV table at the end of the job.