DSXchange

Hi,

I am baffled as to why the job is failing because it was working fine before the addition of the new field in theTarget DB and the statement to derive the value for this field.

A Db2 Stage extracts Data from 9 tables based on a particular date.

B.The extracted Data while passing through a transformer(TRX1) performs a lookup (Lkp1) on a table using one field. This transofrmer has two links with one link leading to a shared container to capture rejects.

C.The other link goes through another transformer(TRX2) where another lookup(Lkp2) on a hashed file is performed on one field.

D. The output link of the above goes through a third transformer(TRX3).
This transformer perfoms a lookup(LKP3) on one table (using three fields ) - and another lookup (Lkp4) on a hashfile(using one field), has an output link which updates a table and a fourth link that inserts the records to a DB2 stage.

Settings Lkp 3 ->

SELECT A, B, C, MIN(D), MIN(E), MIN(F) FROM TABLE WHERE A= ? AND B=? AND C= ? GROUP BY A, B,C;

In the transformer
For D
if Not(IsNull(Lkp3.D)) then Lkp3.D else ""
For E
If (Lkp4.PROD_SUB_TYP="RC") Then Lkp3.E Else ""

With the above conditions the job worked fine.

Then we added a new field to the target DB.
The contents of that field was derived by the above lookup for F
and we used
if Not(IsNull(Lkp3.F)) then Lkp3.F else @Null.

After this new addition, for certain extraction dates the job aborts with the
following reason:
: [IBM][CLI Driver][DB2/AIX64] SQL0911N The
current transaction has been rolled back because of a deadlock or timeout. Reason code "68". SQLSTATE=40001

The array size in the target was set to 1 and transaction size to 100 in order to capture the rejects....


Would appreciate suggestions to fix the bug.

Don't select from a table and write back into it within the same continuous jobflow. Land to a file first, as the database is indicating you have a deadlock.