Bookmark and Share

Recent Posts

MERGE Statement Enhancements in DB2 12

June 13, 2017

The MERGE statement has been around awhile. It was actually among the first topics I covered upon launching DB2utor in 2007.

I mention this now because DB2 12 brings some new capabilities to the MERGE statement. While the DB2 9 version provided some great benefits?namely, it simplified code and allowed three statements (INSERT, UPDATE and SELECT) to be combined into a single statement?it also had some shortcomings. DB2 12, however, introduces changes to the SQL MERGE statement that provide greater functionality and improved compatibility with the DB2 family.  

Enhancements include support for:
  • table-reference as an alternative way of specifying source data for the MERGE statement,
  • multiple MATCHED clauses?,
  • additional predicates with MATCHED or NOT MATCHED,
  • DELETE operations?,
  • IGNORE and the SIGNAL statement as actions.

Information about MERGE statement syntax can be found in the SQL Reference guide, but if you're like me, you may find the syntax diagram hard to follow without some examples. The IBM Redbook, “IBM DB2 12 for z/OS Technical Overview,” has some good examples. You should also check out this IBM Knowledge Center document.

These slides are from the DB2 12 Technical Workshop. They offer a high-level comparison of the MERGE syntax prior to DB2 12 with the current DB2 12 syntax. (Click on image to view in new window.)

Posted June 13, 2017| Permalink

comments powered by Disqus