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

    Unanswered: DB2 - Moving Data from Staging to Live Table

    Hi,

    We have small data warehouse with 26 tables, after ETL process we would like to load data into Staging table with I, U & D flag for insert, update and delet flag with each record in Staging tables.

    Now my question is what is best/optimal method to load data from staging table to live tables?

    I been told to use SQL statements to load data as utilities are not allow at this organization.

    Is thee way to do Mass update and Mass Insert using SQL to load data? How?

    Thanks in advance for your help,

    Ree

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Have a look at MERGE statement

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

  3. #3
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    I hope you will be allowed to use LOAD utility (db2 load). As otherwise with SQL commands like INSERT , UPDATE its going to take long time as they are logged. You can also opt for not logged initially option, but with restriction.

    Jayanta Datta
    DB2 UDB DBA
    IBM India, Global Delivery
    New Delhi

  4. #4
    Join Date
    Jul 2011
    Posts
    9
    Thanks for reply...

    Is MERGE can handle mass update or insert?

  5. #5
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    MERGE operations are also logged by default. So for mass INSERT / UPDATE using MERGE if you want to do, you need to turn off logging in the database table otherwise you can be very soon run out of transaction logs.

    Jayanta Datta
    DB2 UDB DBA
    IBM India, Global Delivery
    New Delhi

  6. #6
    Join Date
    Jul 2011
    Posts
    9
    How Commit will be handled in MERGE? Is by number of records or ALL-In or ALL-Out is case of failure?

    Just side question, I know Load utility is out of question but just want to make sure I got it right. DB2 Load utility will handle only bulk INSERTs but for Update I have to use UPDATE command. Is my understanding correct?

    Thanks all for your time and reply.

    Rwe

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Quote Originally Posted by rreword View Post
    How Commit will be handled in MERGE? Is by number of records or ALL-In or ALL-Out is case of failure?
    Yes, MERGE is a single SQL Statement and is no different to a vanilla INSERT or UPDATE.
    Just side question, I know Load utility is out of question but just want to make sure I got it right. DB2 Load utility will handle only bulk INSERTs but for Update I have to use UPDATE command. Is my understanding correct?

    Rwe
    That's correct.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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