Db2 12 for z/OS – Two Highly Requested SQL Enhancements – Part 2

by: Meg Bernal, Chris Crone, IBM SVL Lab

In Part 1 of this article, we introduced the first of two highly requested SQL enhancements by many customers in Db2 12 - Piece-wise DELETE.  In Part 2, we will look at enhancements to MERGE.

Db2 12 Enhanced MERGE statement

Prior to Db2 9 for z/OS, if an application developer needed to update an existing row or insert a new row based on the source values, they would have to write two SQL statements, an UPDATE and an INSERT, plus some amount of application logic.  Db2 9 introduced support for the MERGE statement. 

Using source values supplied via host variables or host variable arrays, the MERGE statement allowed you to either insert one or more new rows into a target table or update one or more existing rows in the target table. This capability was targeted at a specific use case – One where an application inserts a row, and if the INSERT fails (perhaps because the row fails a unique constraint), the application turns around and updates the row –or– the application updates the row, and if the row doesn’t exist (SQLCODE 100), the application turns around and inserts the row.  In essence, the MERGE was an UPSERT statement.[1]

Db2 9 had not even GAed before requests started coming from application developers to have an even more capable MERGE statement.   Developers wanted flexibility on how to specify source values (e.g. MERGE two tables), the ability to specify multiple conditions when matching or not matching with source values, and the ability to delete rows from the target table. 

Db2 12 enhanced the MERGE statement to allow a fullselect to be specified as the source values, an additional condition may be added to the matching condition, rows may be deleted from the target table, and the target table may go untouched with the introduction of the ELSE IGNORE clause.

With the new capabilities introduced in Db2 12, an application developer now has the ability to literally fold large amounts of application logic into one SQL statement.

To demonstrate the power of the enhanced MERGE delivered in Db2 12, well use an example.  Let’s say we want our application developer to build an application that keeps track of customers loyalty points.  We want the application to do the following:

  • Merge the day’s purchases into a customer table and based on the customer’s purchases update the customer’s loyalty points.
  • If the customer purchases more than $100 then their loyalty points are increased by 10% of their day’s purchases.
  • If the customer is a new customer, they receive 100 loyalty points in addition to 10% of their day’s purchases if the day’s purchases are greater than $100.

The enhanced MERGE can be used to perform all of this work by performing the following:

  1. Select data from the purchases table
  2. Compare the account number from the purchases table with the account number from the customer table
    1. If the customer’s purchases are greater than $100 then update the customer’s balance and award the customer with loyalty points at 10% of the day’s purchases
    2. If the customer’s purchases are not greater than $100 then just update the customer’s balance
    3. If the customer is a new customer and their purchases are greater than $100 then insert the new customer’s information and reward them with 100 loyalty points plus 10% of their day’s purchases
    4. If the customer is a new customer and their purchases are not greater than $100 then insert the new customer’s information and reward them with 100 loyalty points

Assume the CUSTOMERS_TABLE table contains the following data.  Customer’s having account numbers from 1-10 exist in the table.

2017-08-31_7-27-10.jpg

Assume the PURCHASES_TABLE table contains the following data.  Customer’s having account numbers 1-5 made purchases plus two new customers having account numbers 12 and 14 made purchases.

2017-08-31_7-27-55.jpg

Issuing the following enhanced MERGE statement will update the CUSTOMERS_TABLE with the day’s purchases.  Notice, the source values from the purchases table are determined by using a GROUP BY.  The use of the GROUP BY ensures that for each customer in the customers table it is modified, at most, once (This is an ANSI SQL Standard requirement).

MERGE INTO CUSTOMERS_TABLE AS Tgt
USING (SELECT ACCT_NO, SUM(PURCHASE_PRICE) AS TOTAL_PURCHASES
       FROM PURCHASES_TABLE GROUP BY ACCT_NO) AS Src
ON (Tgt.ACCT_NO = Src.ACCT_NO)
WHEN MATCHED AND TOTAL_PURCHASES > 100 THEN
  UPDATE SET Tgt.BALANCE = Tgt.BALANCE + Src.TOTAL_PURCHASES,
             Tgt.LOYALTY_POINTS = Tgt.LOYALTY_POINTS +
                                  (Src.TOTAL_PURCHASES * 0.10)
WHEN MATCHED THEN
  UPDATE SET Tgt.BALANCE = Tgt.BALANCE + Src.TOTAL_PURCHASES
WHEN NOT MATCHED AND TOTAL_PURCHASES > 100 THEN
  INSERT (ACCT_NO,BALANCE,LOYALTY_POINTS)
  VALUES(Src.ACCT_NO,Src.TOTAL_PURCHASES,
         100+(Src.TOTAL_PURCHASES*0.10))
WHEN NOT MATCHED THEN
  INSERT (ACCT_NO,BALANCE,LOYALTY_POINTS)
  VALUES(Src.ACCT_NO, Src.TOTAL_PURCHASES,100)
ELSE IGNORE!

 After the MERGE statement, the CUSTOMERS_TABLE table contains the following:

2017-08-31_8-22-34.jpg 

The MERGE statement was able to accomplish all of the updates by coding two MATCHED conditions and two NOT MATCHED conditions.  Let’s take a look at each section of the MERGE statement above.

  • Existing customers having account numbers 1 and 2 had their balances updated but no changes were made to their loyalty points. The part of the MERGE statement that accomplished this update was the second MATCHED condition:
WHEN MATCHED THEN
  UPDATE SET Tgt.BALANCE = Tgt.BALANCE + Src.TOTAL_PURCHASES
  • Existing customers having account numbers 3,4, and 5 received 10% of their day's purchases in loyalty points. The first MATCHED condition of the MERGE statement accomplished this update:
WHEN MATCHED AND TOTAL_PURCHASES > 100 THEN
  UPDATE SET Tgt.BALANCE = Tgt.BALANCE + Src.TOTAL_PURCHASES,
             Tgt.LOYALTY_POINTS = Tgt.LOYALTY_POINTS +
                                  (Src.TOTAL_PURCHASES * 0.10)
  • Existing customers having account numbers 6-10 were unchanged as no conditions were met. Since no conditions were met, the ELSE IGNORE was executed:
ELSE IGNORE
  • The new customer having account number 12 received 100 loyalty points. The last section of the MERGE statement performed the insert:
WHEN NOT MATCHED THEN
 INSERT (ACCT_NO,BALANCE,LOYALTY_POINTS) 
  VALUES(Src.ACCT_NO, Src.TOTAL_PURCHASES,100)! 
  • The new customer having account number 14 received 100 loyalty points plus an additional 10% of their day's purchases in loyalty points. The first NOT MATCHED condition of the MERGE statement provided this update:
WHEN NOT MATCHED AND TOTAL_PURCHASES > 100 THEN
  INSERT (ACCT_NO,BALANCE,LOYALTY_POINTS)
  VALUES(Src.ACCT_NO,Src.TOTAL_PURCHASES,
         100+(Src.TOTAL_PURCHASES*0.10))

As you can see from this discussion of the Db2 12 Enhanced MERGE, as well as the in-depth discussion last month from Frank Petersen, MERGE is now a very capable SQL construct that enables an application programmer to encapsulate large amounts of application logic in a single SQL statement. 

The delivery of enhanced MERGE, and piece-wise DELETE that we previously discussed, were two of the most asked for SQL enhancements in the Db2 backlog.  While we in Db2 development move on to the next set of enhancements, we hope all of you will take a further look at these two SQL enhancements and see if you can take advantage of them in your applications.

 

 

 

1 Like
Recent Stories
How to get started with Db2 for z/OS and Apache Spark on a distributed platform

Db2 12 for z/OS – Two Highly Requested SQL Enhancements – Part 2

What's New In Recovery and Availability : Db2 V11.1.2.2