Unanswered: Update data - Stored Procedure or Script?
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?
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.
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?
Best Regards, Guy Przytula
Database Software Consultant
Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
Information Server Datastage Certified http://www.infocura.be