Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2011
    Posts
    9

    Unanswered: Update data - Stored Procedure or Script?

    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

  2. #2
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    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

  3. #3
    Join Date
    Jul 2011
    Posts
    9
    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

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    have a look at merge command :
    SQL merge operators
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •