Hi Guys,
I have a table containing requests, with about 500,000 rows, and another table called REQUESTS_MERGER which contains the previous 5 days requests, and I need to merge the request table with the merger table (insert new records, and update and changed details in existing requests). My current query uses DB2/SQL MERGE INTO statement:

MERGE INTO REQUESTS A1 USING REQUESTS_MERGER A2

ON (A1.REQUEST_NUMBER = A2.REQUEST_NUMBER)

WHEN MATCHED THEN
UPDATE
SET
[Columns in A1 with their values from A2]

WHEN NOT MATCHED THEN
INSERT ([A1 Columns])
VALUES ([A2 Columns])

(I removed the columns, to save space, but there is about 30 columns in the REQUESTS table)

The query runs fine, it just takes ages! Is there a better process to merge the two tables... Temporary tables are possible, but I can't think of a way to do it.

If you think indexes would help, please could you help how to create and manage indexes?

Looking forward to your suggestions!-Cheers in advance!

Indexes for REQUESTS.REQUEST_NUMBER and REQUESTS_MERGER.REQUEST_NUMBER may improve performance.

Thanks a lot tonkuma,

For those who are interested, I created UNIQUE indexes on both the original and merger tables using the syntax:

CREATE UNIQUE INDEX SR_MAIN
ON REQUESTS (REQUEST_NUMBER);


CREATE UNIQUE INDEX SR_MERGE
ON REQUESTS_MERGER (REQUEST_NUMBER);

The query now runs in 5 seconds as opposed to 8 hours! Thanks again for your help