Enhanced SQL MERGE - a Db2 12 greatest hit with John Campbell
Jennie Chang 550000A5PG Visits (4610)
This post is part of a series that provides John Campbell's perspective on some of the most popular new features and capabilities in Db2 12 for z/OS.
Back in Version 9, Db2 for z/OS introduced very simple and minimal support for the SQL MERGE statement, only providing an upsert capability with limited functionality. This feature restricted MERGE statements to only accept a single INSERT clause and UPDATE clause. Now, Db2 12 for z/OS enhances MERGE statements to be consistent with the SQL 2016 Core standard, as well as compatible with the Db2 family.
This update introduces new functionality for MERGE statements so you can:
A highlight of these enhancements is that you can specify queries identifying the source rows for MERGE statements. The query can even be a join; however, this could cause the statement to return millions, or even billions, of rows and operations. When using such a powerful MERGE statement, keep in mind the following considerations:
Nevertheless, these enhancements are an advantageous update, providing significant benefits for application developers. With the new functionality, MERGE statements can now be used to provide increased development productivity, improved performance, and easier application porting to Db2 12 for z/OS.
John Campbell is an IBM Distinguished Engineer for Db2 for z/OS development. Claire McFeely is a Senior Software Engineer and lead SQL language architect for Db2 for z/OS. Jennie Chang is a technical writer for Db2 for z/OS.