z/OS DB2 V11 NFM - MERGE from TableA into TableB

Paul Ogborne

z/OS DB2 V11 NFM - MERGE from TableA into TableB
Dear Listers,


(Pulling what's left of my hair out!!)



z/OS DB2 V11 NFM - MERGE from TableA into TableB



It appears possible on other platforms and versions but is it possible in z/OS DB2 V11 NFM
to MERGE all rows from TableA into TableB ? I am trying to get the syntax to work using an embedded SELECT.


So any rows with matching keys would cause an UPDATE (WHEN MATCHED)
and any rows without matching keys would cause an INSERT (WHEN NOT MATCHED)


If yes, do you have an example please?


Thanks and regards,
Paul Ogborne.

Walter Janißen

AW: z/OS DB2 V11 NFM - MERGE from TableA into TableB
(in response to Paul Ogborne)
Hi

I think you need Db2 V12 for that. In V11 you can’t specify a table as source.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Paul Ogborne [mailto:[login to unmask email]
Gesendet: Freitag, 23. März 2018 15:58
An: [login to unmask email]
Betreff: [DB2-L] - z/OS DB2 V11 NFM - MERGE from TableA into TableB

Dear Listers,

(Pulling what's left of my hair out!!)

z/OS DB2 V11 NFM - MERGE from TableA into TableB

It appears possible on other platforms and versions but is it possible in z/OS DB2 V11 NFM
to MERGE all rows from TableA into TableB ? I am trying to get the syntax to work using an embedded SELECT.

So any rows with matching keys would cause an UPDATE (WHEN MATCHED)
and any rows without matching keys would cause an INSERT (WHEN NOT MATCHED)

If yes, do you have an example please?

Thanks and regards,
Paul Ogborne.

-----End Original Message-----
Attachments

  • image001.png (2.6k)

Paul Ogborne

AW: z/OS DB2 V11 NFM - MERGE from TableA into TableB
(in response to Walter Janißen)
Walter,


As I feared :(
Thanks for confirming.


Regards,
Paul



-----Original Message-----
From: Walter Jani&#223;en <[login to unmask email]>
To: db2-l <[login to unmask email]>
Sent: Fri, 23 Mar 2018 15:23
Subject: [DB2-L] - AW: z/OS DB2 V11 NFM - MERGE from TableA into TableB



Hi

I think you need Db2 V12 for that. In V11 you can’t specify a table as source.

Kind regards
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Paul Ogborne [mailto:[login to unmask email]
Gesendet: Freitag, 23. März 2018 15:58
An: [login to unmask email]
Betreff: [DB2-L] - z/OS DB2 V11 NFM - MERGE from TableA into TableB

Dear Listers,



(Pulling what's left of my hair out!!)



z/OS DB2 V11 NFM - MERGE from TableA into TableB



It appears possible on other platforms and versions but is it possible in z/OS DB2 V11 NFM

to MERGEall rows from TableA into TableB ? I am trying to get the syntax to work using an embedded SELECT.



So any rows with matching keys would cause an UPDATE (WHEN MATCHED)

and any rows without matching keys would cause an INSERT (WHEN NOT MATCHED)



If yes, do you have an example please?



Thanks and regards,

Paul Ogborne.



-----End Original Message-----




Attachment Links: image001.png (3 k)
Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription

This email has been sent to: [login to unmask email]
** ** ** IDUG DB2 Data and Analytics Technical Summit in Chennai, India 2018 ** ** **
---> Chennai, India, March 29, 2018 <---
http://ibm.biz/IDUGChennai2018


Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2



Michael Hannan

RE: z/OS DB2 V11 NFM - MERGE from TableA into TableB
(in response to Paul Ogborne)

Paul,

MERGE is a solution which may not fit your problem, and indeed most problems in Db2 V11. There is of course a way to implement your requirements. Everyone lived fine before MERGE started to arrive. Often first version of something may not be mature, e.g. Outer Join (the limited one in DB2 V4 was reworked in V6) and Type 2 Indexes.

It is not clear if you want to update all columns in target table, but if so a DELETE with subquery followed by an INSERT form SELECT would work out O.K. assuming index on the keys. If the requirement is a little different, then adjust the solution. 

Another wild idea. Use Cross loader from a Full Outer Join into a new table, then use Cross Loader to replace the data in original table. That might be most efficient approach, assuming exclusive use of tables, and making them unavailable to other processes for a short period. Full Outer Join uses Merge Scan Join and is a Match Merge process, but retains all unmatched rows. Not sure if Cross Loader can bypass the need for an intermediate table, probably not.

In Reply to Paul Ogborne:

z/OS DB2 V11 NFM - MERGE from TableA into TableB

It appears possible on other platforms and versions but is it possible in z/OS DB2 V11 NFM
to MERGE all rows from TableA into TableB ? I am trying to get the syntax to work using an embedded SELECT.

So any rows with matching keys would cause an UPDATE (WHEN MATCHED)
and any rows without matching keys would cause an INSERT (WHEN NOT MATCHED)

Michael Hannan,

DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Mar 27, 2018 - 06:20 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Mar 27, 2018 - 06:21 AM (Europe/Berlin)

Daniel Luksetich

z/OS DB2 V11 NFM - MERGE from TableA into TableB
(in response to Michael Hannan)
Db2 12 for z/OS offers MERGE of two tables. To add to Michael’s solution a bit you can do a LOAD REPLACE SHRLEVEL CHANGE or you can do what I do and use a during join predicate to switch table access. The load causes a brief outage. The table switch does not.



Load table A making sure the switch is set to B, lead table B making sure the switch is set to A



SELECT COALESCE(A.SUM_DATE, B.SUM_DATA)

FROM SWITCH_TABLE AS S

LEFT JOIN

SUM_TABLE_A AS A

ON S.TABLE_INDICATOR = 'A'

LEFT JOIN

SUM_TABLE_B AS B

ON S.TABLE_INDICATOR = 'B'



Daniel L Luksetich

DanL Database Consulting



IBM GOLD Consultant

IBM Champion for Analytics

IDUG Content Committee Past-Chairman

IDUG DB2-L Administrator

IBM Certified Database Adminstrator – DB2 11 DBA for z/OS

IBM Certified System Administrator – DB2 11 for z/OS

IBM Certified Application Developer – DB2 11 for z/OS

IBM Certified Advanced Database Administrator – DB2 10.1 for Linux UNIX and Windows



From: Michael Hannan <[login to unmask email]>
Sent: Monday, March 26, 2018 11:16 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: z/OS DB2 V11 NFM - MERGE from TableA into TableB



Paul,

MERGE is a solution which may not fit your problem, and indeed most problems in Db2 V11. There is of course a way to implement your requirements. Everyone lived fine before MERGE started to arrive. Often first version of something may not be mature, e.g. Outer Join (the limited one in DB2 V4 was reworked in V6) and Type 2 Indexes.

It is not clear if you want to update all columns in target table, but if so a DELETE with subquery followed by an INSERT form SELECT would work out O.K. assuming index on the keys. If the requirement is a little different, then adjust the solution.

Another wild idea. Insert a Full Outer Join into a new table, then use Cross Loader to replace the data in original table. That might be most efficient approach, assuming exclusive use of tables, and making them unavailable to other processes for a short period. Full Outer Join uses Merge Scan Join and is a Match Merge process, but retains all unmatched rows.

In Reply to Paul Ogborne:

z/OS DB2 V11 NFM - MERGE from TableA into TableB

It appears possible on other platforms and versions but is it possible in z/OS DB2 V11 NFM
to MERGE all rows from TableA into TableB ? I am trying to get the syntax to work using an embedded SELECT.

So any rows with matching keys would cause an UPDATE (WHEN MATCHED)
and any rows without matching keys would cause an INSERT (WHEN NOT MATCHED)

Michael Hannan,

DB2 Application Performance Specialist
CPT Global Ltd



-----End Original Message-----

Tommy Petersen

RE: z/OS DB2 V11 NFM - MERGE from TableA into TableB
(in response to Paul Ogborne)

It is not clear if you want to update all columns or just some of them or if the two tables have the same layout. The following will select the rows from the table with the updates, you will select columns from tab2 for those that you want to update and from tab1 for those you want to keep, and it will work even if the tables have different layouts, if you want to use values from tab1 when the rows is found and values from tab2 if the row is not found, you can use coalesce (it could be create_date, for instance) Create table temp1 like tab1;

Insert into temp1   select tab2.key1, tab2.col1, tab2.col3, tab1.col4, coalesce(tab1.col4, tab2.col5)     from tab2    Outer join tab1 on tab2.key1 = tab1.key1

Delete from tab1 where key1 in (select key1 from tab2);

Insert into tab1 select * from temp1;

Commit;

Drop table temp1

 

If you have temporal tables you will get a delete and insert for each row updated, and if you do not want that, you can create a view that joins the tables and then an instead of trigger on the view to update tab1 for existing rows and insert for non-existing rows.

I believe you can add "for update" on the select to prevent updates of TAB1 by other users during the process.

Paul Ogborne

z/OS DB2 V11 NFM - MERGE from TableA into TableB
(in response to Michael Hannan)
Hi,


Thank you for all your helpful responses.
I got around the problem with indexing, LOAD RESUMEs and a huge DISCARDS :)


Ironically, this was all to build a temporal datastore of some RTS statistics. A similar feature becomes available in DB2 z/OS V12, so would it have been quicker to install V12 I hear you say?


Answer = "Not here!" :(


Regards,
Paul


-----Original Message-----
From: Michael Hannan <[login to unmask email]>
To: DB2-L <[login to unmask email]>
Sent: Tue, 27 Mar 2018 5:15
Subject: [DB2-L] - RE: z/OS DB2 V11 NFM - MERGE from TableA into TableB



Paul,
MERGE is a solution which may not fit your problem, and indeed most problems in Db2 V11. There is of course a way to implement your requirements. Everyone lived fine before MERGE started to arrive. Often first version of something may not be mature, e.g. Outer Join (the limited one in DB2 V4 was reworked in V6) and Type 2 Indexes.
It is not clear if you want to update all columns in target table, but if so a DELETE with subquery followed by an INSERT form SELECT would work out O.K. assuming index on the keys. If the requirement is a little different, then adjust the solution.
Another wild idea. Insert a Full Outer Join into a new table, then use Cross Loader to replace the data in original table. That might be most efficient approach, assuming exclusive use of tables, and making them unavailable to other processes for a short period. Full Outer Join uses Merge Scan Join and is a Match Merge process, but retains all unmatched rows.

In Reply to Paul Ogborne:
z/OS DB2 V11 NFM - MERGE from TableA into TableB

It appears possible on other platforms and versions but is it possible in z/OS DB2 V11 NFM
to MERGE all rows from TableA into TableB ? I am trying to get the syntax to work using an embedded SELECT.

So any rows with matching keys would cause an UPDATE (WHEN MATCHED)
and any rows without matching keys would cause an INSERT (WHEN NOT MATCHED)
Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd



Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription

This email has been sent to: [login to unmask email]
** ** ** IDUG DB2 Data and Analytics Technical Summit in Chennai, India 2018 ** ** **
---> Chennai, India, March 29, 2018 <---
http://ibm.biz/IDUGChennai2018


Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2