Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2006
    Posts
    6

    Unanswered: UDB Merge issues/problem

    We have 64 partition 50+ terabyte datawarehouse environments. The development team wanted to use Merge statement for populating the data from one layer to another 1 to 1. Did anyone saw any issues or problem with Merge statement for moving data in millions or rows? Wouldn’t ETL (datastage) be a better option?

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    I'm not sure I can give a definitive answer to suit your environment ...

    Generally speaking , there is no specific disadvantage using MERGE Statement that I'm aware of ... I would think, a MERGE stmt will perform much better than a ETL job as there is no data movement outside the database manager ...

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Sep 2006
    Posts
    6
    I agree with the no data movement outside of the DBM. However, since there is no control over frequent commits. The transaction put an exclusive lock on the tables. This will result into table being not available for access for other users. What you’re thought on this.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Locking is probably not a big issue. A much bigger problem will be logging. Each change to the table is logged and a MERGE statement that changes a lot of rows produces a lot of log records. You could use NOT LOGGED INITIALLY for your table to avoid logging, but that has certain implications on the recoverability of the database.

    As for the question regarding committing in between, you could apply the usual techniques to process batches of rows (with a single MERGE statement) and commit after each batch.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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