Splitting a Sequential File

[holymac]

Dear All,

I have created a server job with one Source (Sequential) One transformer and one Target (DB2). The mappings are quite direct. The Target's Update Action='Update existing or insert new rows'.

I would like to do the following:

Split the Source into 2 links one for update and one for Insert and put each of them into a sequential file. How do i be able to diff between the records to be inserted and updated.

Appreciate all the assistance.

Thank you in advance.

Regards,
holymac

[loveojha2]

Split the Source into 2 links one for update and one for Insert and put each of them into a sequential file. How do i be able to diff between the records to be inserted and updated.

You can do this by including a target table lookup, if lookup passes it should go to the update link else the insert link.

[kris007]

Load all the key column values from the DB2 table into the Hashed file.Now look up against the rows from the hashed files with the source file keys. From the Transformer give two outputs. One for inserts and one for updates.In the Transformer give the constraint as

Code: Select all

InputLink.NOTFOUND = @TRUE(for inserts) and InputLink.NOTFOUND<>@TRUE(for updates)

and you are all set.

In BTW it is also a good design to load your updates and inserts seperately into your Database rather than using the Update action you are using currently, Unless you are dealing with very few rows i.e. some where around 100 rows. IMO.

HTH

[ray.wurlod]

Enrol on IBM's DataStage Essentials class, the entry level class. This is one of the techniques taught.