I expect that this is doing what you asked, and although it does seem a sensible query, it is not being very practical. I think that your request is going to build some huge temporary tables to work out the various clauses before applying any inserts/updates. You may even fill your temporary table tablespace before your MERGE completes this step.
You might have some success with indices though. What indices do you have on table
schemaname.Customer_Staging and
schemaname.Customer_Staging?
- If you don't have a single index for the three columns you mention in the SELECT DISTINCT, then you might do a full table scan of schemaname.Customer_Staging.
- If you don't have a single index for all the columns in the whole query (e.g A.Name, B.Name, A.Load_dt etc. ) then you might do a full table scan of the appropriate table.
Any reason to do a full table scan that these scales will be bad. An index might take a while to build and needs to have space, but then your MERGE should run better. I've had something that took me 20 minutes to build an index then the process I wanted ran in about an hour, after which I dropped the index again.because it was a one-off report. The run without the index was abandoned after over 22 hours (someone set if off and went home)
Is there some sort of query profiler you can use to consider this? MSSQLServer has one, Oracle has one so I'm sure that DB2 will have one, but I've not ever used it. You need to avoid a full table scan when dealing with this volume of data.
I hope that this helps,
Robin