Merge

citi · #1 (**permalink**) 02-04-11, 08:40

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.

Stealth_DBA · #2 (**permalink**) 02-04-11, 09:28

No. You need V9 z/OS to use Merge.

citi · #3 (**permalink**) 02-04-11, 09:38

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.

Stealth_DBA · #4 (**permalink**) 02-04-11, 09:59

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'

citi · #5 (**permalink**) 02-04-11, 10:18

Thank you!

dr_te_z · #6 (**permalink**) 02-07-11, 02:11

Quote:

Originally Posted by Stealth_DBA

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.

Stealth_DBA · #7 (**permalink**) 02-07-11, 05:57

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.

dr_te_z · #8 (**permalink**) 02-07-11, 06:37

Quote:

Originally Posted by Stealth_DBA

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

tonkuma · #9 (**permalink**) 02-07-11, 13:46

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.

tonkuma · #10 (**permalink**) 02-07-11, 14:11

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'
       )

Internet Services

Web Development

Digital Marketing

Consumer Tech