How to Insert and Delete the rows in the same Table

prmuttireddy · Post by **prmuttireddy** » Wed Sep 27, 2006 3:27 am

Hi,

How to Insert and Delete the rows in the same Table within a single job.
Please clarify the issue.

For example let assume SRC as source table and TRG as Target Table. I want to Insert and Delete in the TRG table according the SRC table changes.

Advanced Thanks,

Reddy.

ArndW · Post by **ArndW** » Wed Sep 27, 2006 3:33 am

This is a typical DataStage task and often done. You can use the same stage canvas object for the insert and the delete by having two links into it, or you can use two separate stages. The logic of which row gets sent down which link is done with constraints in a transform stage.
What logic have you tried so far that hasn't worked, if you explain that we might be able to give targeted assistance.

ray.wurlod · Post by **ray.wurlod** » Wed Sep 27, 2006 3:28 pm

The writing rule "replace existing rows completely" effects DELETE followed by INSERT for each row sent to the target.

prmuttireddy · Post by **prmuttireddy** » Wed Sep 27, 2006 10:11 pm

Hi,

Thanks for immediate reply.
The scenario is

In the constraints part of the Transform stage I mentined as
If A_ACT='A' or 'U' then it should be Insert in the Target
and If A_ACT='D' or 'B' it should be Delete in the Target

Means if source side record Inserts or update it should be insert and if source side records deletes it should be delete in the target.

Regards,
Reddy.

ray.wurlod · Post by **ray.wurlod** » Wed Sep 27, 2006 10:59 pm

Supply only the key column(s) on the link that looks after DELETE operations. Use user-defined SQL of the form

Code: Select all

DELETE FROM tablename WHERE keycol = '?';

prmuttireddy · Post by **prmuttireddy** » Thu Sep 28, 2006 2:07 am

Hi Ray,

when i am trying to use two DB2 stages as Target, one for delete and for Insert it has been giving the Deadlock error.

Reddy.

ascen · Post by **ascen** » Thu Sep 28, 2006 2:57 am

prmuttireddy wrote:
when i am trying to use two DB2 stages as Target, one for delete and for Insert it has been giving the Deadlock error.

Use only one target db2 stage with to input links. And do not use any options like: "Truncate table before insert" on two iput links.

Best regards,
Piotrek

prmuttireddy · Post by **prmuttireddy** » Thu Sep 28, 2006 3:16 am

Could you please tell what option i have to select in the target DB2 stage.

Regards,
Reddy.

chulett · Post by **chulett** » Thu Sep 28, 2006 3:47 am

No specific options. The 'fix' is to simply use one target stage instead of two so they are aware of each other and run in the same transaction - this will resolve your deadlock issue.

As long as your constraints and update actions are fine, having both links run into a single stage should be the only change you need to make.