Hi,

Here is scenario I have on hand.. We have few transaction data tables with millions of rows.. with two columns are empty. We have to lookup other table to load these missing data in transaction data tables.

These tables are residing in DB2 database and reference data table from which I have to get missing data contains approx 6 million records.

This is daily update process as source is not sending these information in their source feed files.

My question is how can I do this on database side? Should I write stored procedure or update statement to update data in transaction tables?

What should be optimal process to update data?

Thanks for your expert comments.

rwe

I would do this way :

1 ) Write a Stored Procedure to do the update with commits each bunch. This will continue till all rows are up-to-date.
2) Write an update job that will be tied to daily update job. This update will be in comfortable bunches.

If the situation is such that existing update job cannot be modifiled to include everything, then this data can be staged somewhere and the missing columns can be filled in staging. This make sense only if final data has to be complete before real update and that you do not involve this production database in staging operation.
In other situation, you are better off with stored procedure, same as in step 1) above. Also the stored procedure can be customized to include both 1) and 2).

It does not seem practical ( as much I understood from your explanation of need ) to run update job from CLP or OS because the daily update is doing this daily; you need another job to supplement these columns which seems safer with stored procedure.

In any case you are costing the update double.


Hope this helps

DBFinder

Thanks for your reply... I just learned that this is staging database and we will be moving data into live table. Moving of data into live tables will be done using db2 MERGE statements. Can I use join/update withing this Merge to update missing columns?

Greatly appreciate your feedbook/ inputs.

Thx

have a look at merge command :
SQL merge operators