Insert,delete,update based on Indicator

[krisp321]

Hi

I have a question, let me take an example

I have five columns, first two columns are key columns and last column is an indicator in which 'I' stands for Insert, 'C' stands for Update and 'D stands for Delete. For first run, I will have all I's which will be all inserts

Col1 Col2 Col3 Col4 Col5
A 1 abc ab I
A 2 abc aa I
B 1 aa bb I
B 2 bb aa I
C 1 aa aa I
C 4 bb bb I

For second run
Col1 Col2 Col3 Col4 Col5
A 1 aa ab C
A 2 bb aa C
B 1 aa bb I
B 2 bb aa D
C 6 aa bb I

How can I do it in datastage, my target is DB2, and source comes as files

Thanks in advance

[chulett]

Three targets with the appropriate action in each and constraints based on the last column's value.

[krisp321]

Three targets with the appropriate action in each and constraints based on the last column's value. ...

Thankyou for your reply

I tried the same, but when some records are passing to delete stream, all records are getting deleted in the database.
Database is DB2, and using DB2 Api stage.

Delete without clearing option I picked
based on first two key columns. but all existing records are getting deleted

[vinothkumar]

Check how your delete query looks in that stage. It should have WHERE clause having your key columns in it.

[krisp321]

At the datastage level I have mentioned where clause with those key columns, but at table level I dont mentioned any key columns...may be that makes to delete all records?

[chulett]

Make sure the key columns are right at the 'DataStage level' so you have a proper where clause, any 'keys' in the actual target table only help it to go faster.