Results 1 to 10 of 10

Thread: Merge

  1. #1
    Join Date
    Aug 2007
    Posts
    56

    Unanswered: Merge

    Is MERGE supported on V8 (Z/os)?

    I'm attempting to execute the following:

    MERGE
    INTO SERDB.I117_ANE_FINAL_T FINALL
    USING (
    SELECT I110_EXPLOITATION, I110_NEGLECT, I110_PHYSICAL, I110_PSYCHOLOGICAL, I110_SEXUAL
    FROM SERDB.I110_ANE_INITIAL_T INITIAL
    WHERE I110_FORM_STATUS = 'A')
    ON INITIAL.I110_ABU_NEG_EXP_ID = FINALL.I117_ABU_NEG_EXP_ID
    AND FINALL.I117_FINAL_TYPE = 'MR'
    WHEN MATCHED THEN
    UPDATE
    SET FINALL.I117_EXPLOITATION = INITIAL.I110_EXPLOITATION,
    FINALL.I117_NEGLECT = INITIAL.I110_NEGLECT,
    FINALL.I117_PHYSICAL = INITIAL.I110_PHYSICAL,
    FINALL.I117_PYSCHOLOGICAL = INITIAL.I110_PSYCHOLOGICAL,
    FINALL.I117_SEXUAL = INITIAL.I110_SEXUAL


    I'm receiving a SQLState: 42601 error.

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    No. You need V9 z/OS to use Merge.

  3. #3
    Join Date
    Aug 2007
    Posts
    56
    Ok.

    Would it be possible to perform an insert on a destination table using values selected from a primary table? The columns are of the same datatype.

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Yes, you can do an Insert (or an Update) from one table to another. Not sure why you are asking about an Insert as your example is an Update. Since I don't see an Insert, the following Insert is a guess. I think the Update will do the same as your Merge.
    Code:
    INSERT INTO SERDB.I117_ANE_FINAL_T
      SELECT I110_EXPLOITATION
           , I110_NEGLECT
           , I110_PHYSICAL
           , I110_PSYCHOLOGICAL
           , I110_SEXUAL
      FROM SERDB.I110_ANE_INITIAL_T INITIAL
      WHERE I110_FORM_STATUS = 'A'
    
    
    UPDATE SERDB.I117_ANE_FINAL_T FINALL
    SET ( FINALL.I117_EXPLOITATION 
        , FINALL.I117_NEGLECT      
        , FINALL.I117_PHYSICAL     
        , FINALL.I117_PYSCHOLOGICAL
        , FINALL.I117_SEXUAL
        )
      =
        (SELECT INITIAL.I110_EXPLOITATION
              , INITIAL.I110_NEGLECT
              , INITIAL.I110_PHYSICAL
              , INITIAL.I110_PSYCHOLOGICAL
              , INITIAL.I110_SEXUAL 
         FROM SERDB.I110_ANE_INITIAL_T INITIAL
         WHERE INITIAL.I110_ABU_NEG_EXP_ID = FINALL.I117_ABU_NEG_EXP_ID
           AND INITIAL.I110_FORM_STATUS    = 'A'
        )
    WHERE FINALL.I117_FINAL_TYPE = 'MR'

  5. #5
    Join Date
    Aug 2007
    Posts
    56
    Thank you!

  6. #6
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by Stealth_DBA View Post
    No. You need V9 z/OS to use Merge.
    No. On my last mainframe assignment the client upgraded to V8 and from that time I could use the new SQL capabilities like CTE's and MERGE. Sometimes the V8 runs in V7-modus (I recall 3 stages) and then you cannot use the new SQL thingies.

  7. #7
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    No. Merge is not available in DB2 V8 z/OS. CTE became available but not Merge. It first became available in V9.1. Here is the entry in Info Center.

    DB2 9 - What's new - MERGE and SELECT FROM MERGE statements

    We currently have DB2 V8 z/OS (in the middle of V9.1 Upgrade) and MERGE does not work.

  8. #8
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by Stealth_DBA View Post
    We currently have DB2 V8 z/OS (in the middle of V9.1 Upgrade) and MERGE does not work.
    Yep, just checked my old docs. A lot of new features but no merge. It seems that my memory serves my wrong.... (is the english?)
    I'll never contradict you again

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    In DB2 for z/OS,
    you can specify only "USING (VALUES ... )".

    See Information Center.
    DB2 10 for z/OS > DB2 reference information > DB2 SQL > Statements MERGE

    I think that you can't use MERGE to update a table from another table on z/OS.

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought that AND EXISTS should be added.
    Because, if there was no matching row in SERDB.I110_ANE_INITIAL_T INITIAL,
    subselect in SET clause would return null values.

    Code:
    UPDATE SERDB.I117_ANE_FINAL_T FINALL
       SET ( FINALL.I117_EXPLOITATION 
           , FINALL.I117_NEGLECT      
           , FINALL.I117_PHYSICAL     
           , FINALL.I117_PYSCHOLOGICAL
           , FINALL.I117_SEXUAL
           )
         =
           (SELECT INITIAL.I110_EXPLOITATION
                 , INITIAL.I110_NEGLECT
                 , INITIAL.I110_PHYSICAL
                 , INITIAL.I110_PSYCHOLOGICAL
                 , INITIAL.I110_SEXUAL 
             FROM  SERDB.I110_ANE_INITIAL_T INITIAL
             WHERE INITIAL.I110_ABU_NEG_EXP_ID = FINALL.I117_ABU_NEG_EXP_ID
               AND INITIAL.I110_FORM_STATUS    = 'A'
           )
     WHERE FINALL.I117_FINAL_TYPE = 'MR'
       AND EXISTS
           (SELECT *
             FROM  SERDB.I110_ANE_INITIAL_T INITIAL
             WHERE INITIAL.I110_ABU_NEG_EXP_ID = FINALL.I117_ABU_NEG_EXP_ID
               AND INITIAL.I110_FORM_STATUS    = 'A'
           )

Posting Permissions

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