Probleme 2 links in the same table on DB2

[raju_chvr]

plaire poste en anglais

Pardon me If I typed something wrong in above line !! For others I asked the Poster to post in English

[ogmios]

Liberal translation... for as for as my French goes:

Excuse me but that I don't write in English

In my job I have 2 lines going to the same table (plugin DB2).

Insert/Update ---------> T1 => Constraint ==> CODE = 1
Delete------------------> T1 => Constraint ==> CODE <> 1

This error provokes an SQL00911N in DB2.

When I erase a line, the problem does not occur

Does anyone have information on this

Thank you

Muriel Auvray

(so far for the translation)

[ogmios]

First of all this site is English only, translating forth and back between languages for a lot of mails would be difficult.

Now for your problem (in English):

You have a deadlock error when using "2 lines", transactions are pretty tricky in DataStage... but you get used to them. Probably you're trying to update/delete the same rows or DB2 does e.g. a lock escalation to table level.

One possible solution I've used before: split up the job in 2 jobs... one doing the insert/updates, the second doing the deletes, but keep in mind that this possibly changes the order of insert/deletes (can it be e.g. that a row is inserted/delete/inserted again in the same ETL cycle).
You may also try to play with the isolation levels in the DB2 stage but you may get mixed results with this.

Ogmios

[ogmios]

Funny T.J. I have been DB2 administrator long enough to know the SQL codes out of my head, SQL00911N is something as "transaction rolled back because of deadlock or timeout".

I never fully understood why transactions are implemented the way they are in DataStage, or maybe it's just a DB2 thing. The update/insert link will probably lock rows or the complete table while the delete link is considered to be in a seperate transaction for DB2 and will cause the deadlock, or the other way around.

Ogmios

[Teej]

So basically split it in 2 jobs and we're all happy?

Hopefully the OP will pick up on this and do it this way.

-T.J.

[auvray.muriel]

Thank you with all for your answers.

In my case, a line cannot go towards the 2 links. The constraint sends the line towards one or the other. I thought well of making 2 jobs instead of one, but I have a large volume to treat.

After having called the Ascential support, they made the following tests:

DS V6 with DB2 V7.2 ==> Problem found
DS V7 with DB2 V7.2 ==> Problem found
DS V6 with DB2 V8 ==> Problem not found
DS V7 with DB2 V8 ==> Problem not found

[ogmios]

"It's not our error, it's the error of the guy behind the tree" ... funny :D.

At our site we're still on DB2 V7.2 fixpack 9 which still has the "problem", and we didn't find another workaround.

By the way splitting the job in 2 jobs will be faster if you can select the update/insert and delete rows seperately in the database (e.g. when you would be using input from dpropr).

We've seen with DataStage that the transformer can also be slow (depending of course on your contraints) and that when you only input the data that is required to be processed - so doing the selection in the SQL code and making seperate jobs - is faster than doing the "selection" in a transformer.

Give it a try.

Regards,
Ogmios

P.S. good English, way to go

[auvray.muriel]

I use translate google to write, then sorry for grammar

However this type of treatment functions under oracle, universe. DB2 cannot treat this kind of operation... Suspect for a relational data base.

I think that our customer will make us spliter the jobs into 2 to treat the delete separately

Thank you with all

Muriel