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?

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

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.

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.