I've used the MERGE statement before in Oracle, but I'm having trouble getting in work for db2 9.1 on the Z. I know it's different, but I cannot find concrete examples of how it's different. Here is a very simple test:

CREATE TABLE TEMP_TEST1 (ID INTEGER, DATA VARCHAR(20) );
CREATE TABLE TEMP_TEST2 (ID INTEGER, DATA VARCHAR(20) );

INSERT INTO TEMP_TEST1 VALUES (2,'SOMEDATA_B1');
INSERT INTO TEMP_TEST1 VALUES (3,'SOMEDATA_C1');

INSERT INTO TEMP_TEST2 VALUES (1,'SOMEDATA_A2');
INSERT INTO TEMP_TEST2 VALUES (3,'SOMEDATA_C2');
INSERT INTO TEMP_TEST2 VALUES (4,'SOMEDATA_D2');

MERGE INTO TEMP_TEST1 AS T1
USING TEMP_TEST2 AS T2
ON T1.ID = T2.ID
WHEN MATCHED THEN
UPDATE SET T1.DATA = T2.DATA
WHEN NOT MATCHED THEN
INSERT VALUES (T2.ID, T2.DATA);

SQL0104N An unexpected token "TEMP_TEST2" was found following "". Expected tokens may include: "(".

Can someone explain why this is not working or explain the syntax for the Z?

Dave

The zOS syntax is here:
IBM Information Management Software for z/OS Solutions Information Center

I've found out that the MERGE is only half implemented for the Z. You cannot merge a table, only a single value, which makes it fairly useless. Yet another Z limitation....

davebert99, While you are correct that you can't use a Table in a Merge statement, you can do multiple values by using a host-variable-array.

I'm trying to use it as a MERGE statement to replace a cursor Insert/Update loop in a SQL Stored Procedure. Replacing 200,000 or more separate single Insert/Updates with a single statement is much more efficient.

It works just fine in Oracle or DB2 LUW, just not on the Z

I don't think I can use it in the SP. Unless someone has an example...

Dave