Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2009
    Location
    Pennsylvania
    Posts
    22

    Red face Unanswered: need syntax for MERGE statement on the Z

    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

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Jun 2009
    Location
    Pennsylvania
    Posts
    22
    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....

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    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.

  5. #5
    Join Date
    Jun 2009
    Location
    Pennsylvania
    Posts
    22
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •