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

Have a look at MERGE statement

cheers
Sathyaram

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.

Thanks for reply...

Is MERGE can handle mass update or insert?

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.

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

Yes, MERGE is a single SQL Statement and is no different to a vanilla INSERT or UPDATE.
That's correct.