inesrt/update prblm with separate ODBC stage for same tabl

[Rubu]

Hi

I have to design a job in such a way that it does different transactions depends on the value of an input code on the same table.



Tx-----Tx--<Cond1>------Update
|___<Cond2>----{Update+Insert} **same transaction
|____<Cond3>---{Update+Insert} **same transaction
|____<Cond4>---Insert


While running the job only inserting values through<Cond4> and unable to update the table for the rows which satishfies the other conditions.

Director is diplaying two error codes for the unsuccessful updations... Those are

1.SQLSTATE=S1000, DBMS.CODE=-346
[DataStage][SQL Client][ODBC][DataDirect][ODBC Informix Wire Protocol driver][Informix]Could not update a row in the table.

2.SQLSTATE=S1000, DBMS.CODE=-244
[DataStage][SQL Client][ODBC][DataDirect][ODBC Informix Wire Protocol driver][Informix]Could not do a physical/* -order read to fetch next row.

Any idea why its happening? any solution for that?

Regards
Rubu

[dhiraj]

The problem is that you are trying to update the table before commiting the inserted rows.

To overcome this, set the rows per transaction property in the ODBC stage(where you are inserting the records) to 1. i.e. commit after each row is inserted.

But be aware that doing this will give you a poor performance. Why not consider using hash files instead?

And those error messages indicate that there are no records available in the target table with the specified update condition.

IHTH

Dhiraj

[Rubu]

Thanks Dhiraj

I was using 0 row per transaction in <cond4> ODBC and rest were set as 1. But I can not avoid 1 row transaction in cond2 and cond3 as update and insert has to be clubbed.I separated other two links from the 1 row transaction links.... Its running fine...

Regards
Rubu