problem with deleting records in the table before inserting

[cosec]

I have two Outputs from a tranformer going in to the SAME table.

Output1
If the Constraint for the first output is satisfied then I need to delete some records from that table and then insert another set of records.

I have placed the DELETE in the BEFORE PORTION OF THE SQL in the Target Db2 stage.

Output2
If Constraint is satsfied. Inserts Records to the Target Table.

The Problem I have is whichever output link is successful the job executes the DELETE Statment in Output1.

How can I restrict this ?

I tried using the DELETE STATEMENT BEFORE THE INSERT in the USER DEFINED Section but then my table got locked...


Please suggest an alternative...

[ArndW]

Make two separate links go to two DB2 stages. Make the DELETE command commit immediately and order it so it executes before the normal insert link.

[cosec]

Make two separate links go to two DB2 stages. Make the DELETE command commit immediately and order it so it executes before the normal insert link. ...

Hi Arndw

I have two Separate Links already..(1) Delete (Before), Insert(After)
(2) Insert

Should I split the (1) to a further ?

How do I do the ordering...

Thanks

[cosec]

Arndw>>

My Problem is I want to delete only if it is successful in the first link but otherwise I want it to be inserted. As of now whether it's link 1 or link 2 it's deleting the data.

Make two separate links go to two DB2 stages. Make the DELETE command commit immediately and order it so it executes before the normal insert link. ...

Hi Arndw

I have two Separate Links already..(1) Delete (Before), Insert(After)
(2) Insert

Should I split the (1) to a further ?

How do I do the ordering...

Thanks

[ArndW]

Now I have no idea of what you really want to do. Could you perhaps explain with a small example of what you want done?

[cosec]

Ok sorry if I had confused you.....


My job has two separate output links from the transformer to the table as you suggested.
If Constraint = Y then the Link 1 is supposed to delete existing data and then insert.
The Delete Statement is in SQL "Before" And insert is in "User Defined"

If Constraint = N The Link 2 will only insert data.

If the constraint = N then the data should not be deleting any existing data....but the problem is it is doing so. So I am assuming that the DELETE Statement in the "Before" Part of the Target Db2 stage connected to Link1 is being executed. How Can I prevent this deletion when the constraint is not Y?








[/img]

[ArndW]

I suggest using 2 links and 2 distinct stages for exactly that reason.

[cosec]

You mean I will have three output links then ?

and the order of execution will be
link 1) Constraint = Y Delete
link 2) Constraint = Y INSERT
link 3) Constraint = N INSERT

I suggest using 2 links and 2 distinct stages for exactly that reason. ...

[ray.wurlod]

Can't you just change your rule to "Replace existing rows"?

[cosec]

Hi Ray,

No ray can't do that as the Primary Key is generated for every new row.

Can't you just change your rule to "Replace existing rows"? ...

[ArndW]

Link 1 has your before-sql "delete" in the DB2 stage, Link 2 doesn't. That is why you need to separate your stages as well.

[cosec]

Yes I do have two separate output links goint to two separate Target Db2 Stages and yet I have the problem.....can you suggest an alternative ?

Link 1 has your before-sql "delete" in the DB2 stage, Link 2 doesn't. That is why you need to separate your stages as well. ...

[cosec]

What update Action should I use and how do I make it commit immediately ?

Make two separate links go to two DB2 stages. Make the DELETE command commit immediately and order it so it executes before the normal insert link. ...

[ArndW]

Remove the Delete from the 2nd link. Doesn't that satisfy your logic?

[cosec]

Hi

I have only one delete and thats in the 1st link that leads to a DB2 stage.

The second link has an insert

But my job hangs up when i do this....

Remove the Delete from the 2nd link. Doesn't that satisfy your logic? ...