how to update or insert

[thiru]

i am using db2 table as source and populating to target. my requirement is if the records are allready exists in the target they should be updated or inserted. target table has 20 columns but i need to update only 5 columns, but i am not populating the primary key into the tartet table in this job. its urgent pls give answer asap.[/b]

[ArndW]

Thiru,

please be aware that normally post with "urgent" and "asap" are handled with lower priority in this forum. If you have a service provider you can go to them and expect quick turnaround, but DSXchange is a community of volunteers.

The question you have asked has been posed before and is also one of the base functionalities of working with DataStage; so fortunately while you wait for an ASAP answer in this forum you could use the search facility or look at your training material or product documentation.

[raj_konig]

Thiru,

Basically this is simple one. Have you tried with all the loading options available.

I think "Replace exisitng rows completely" will work for you.

rajesh

[tardifma]

Hi Thiru,
In you DB2 stage, set the Write method to UPSERT.
But be aware... even if the Datastage's documentation says that it does an Update first, and if the row is not found, an Insert... it does not really works this way...

It first does an Insert, and if it fails (due to a unique constraint or a primary key), then it will update the row....

The problem with that is, if you do not have a unique constraint or a primary key... for any reasons... it will always insert records in your table...

In that case, you will not have the choice... use a lookup stage (or a Change Capture stage... depends of your case), and use a switch stage to redirect the data to an Update or an Insert DB2 stage.

Hope this will help.
Thanks

[ray.wurlod]

Of course, any of these "dual-mode upserts" have a performance overhead, as each involves up to two operations (replace is delete then insert). It's much better to perform existence checking within the job design then have a stream for "insert only" and a stream for "update only".