Tuesday, August 01, 2017

A very common requirement
for application developers is to be able to read through new data – from a
table, a file or as entered by an end user – and either INSERT the data if it
does not already exist or UPDATE data that does exist with new values.

The ANSI SQL standard
defines the MERGE statement for this purpose. The purpose of the MERGE
statement is to take two “tables” and merge the data into one table. DB2 for
z/OS has supported the MERGE statement since Version 9, but it is more
functional now as of Version 12.

Prior to DB2 12, the MERGE
statement could not accept a table reference as a way of supplying source data.
Input to the MERGE can only be a host variable array or a list of values. This
limitation caused MERGE to be somewhat lightly implemented.

Well, Version 12 does away
with this limitation – and adds even more features. So you can now write a
MERGE statement where data from one table is merged with data from another
table. Remember, merge takes the data and compares it and when the comparison
is matched does one thing… and when the comparison is not matched it does
another. So you can UPDATE when matched and INSERT when not matched.

Consider the following SQL:

MERGE INTO EMP Tgt

USING (SELECT EMPNO, FNAME, LNAME, ADDRESS,
SALARY FROM NEW_EMP) Src

ON (Tgt.EMPNO = Src.EMPNO)

WHEN MATCHED THEN

UPDATE
SET (Tgt.FNAME, Tgt.LNAME, Tgt.ADDRESS, Tgt.SALARY) =

(Src.FNAME,
Src.LNAME, Src.ADDRESS, Src.SALARY)

WHEN NOT MATCHED THEN

INSERT
(EMPNO, FNAME, LNAME, ADDRESS, SALARY)

VALUES
(Src.EMPNO, Src.FNAME, Src.LNAME, Src.ADDRESS, Src.SALARY)

ELSE IGNORE;

This
MERGE statement takes a table containing new/revised employee data and inserts
the data when a match is not found and updates the data if it is found. Note
that this is a simple MERGE that assumes that all the columns (in this case)
are provided if the data is to be updated.

More
complex MERGE statements are possible as of DB2 12 because you can now provide
additional matching condition options and additional predicates on the matching
conditions (instead of just matched/not matched). It is also possible to issue
a SIGNAL statement to return an error when a matching condition evaluates to True.

When you
use the new functionality of the MERGE statement in DB2 12+, the operations is atomic;
this means that the source rows are processed as a set of rows by each WHEN
clause. If an error occurs for any source row, processing stops and no target rows
are modified.

But the bottom line here is that the MERGE statement has been significantly improved and is a powerful way of processing data using only SQL as of DB2 12 for z/OS.

4 comments:

Each certification level is for a different skill set and which one is best depends upon what you are trying to achieve/certify. The fundamentals certification is the base for the rest of the certifications and is the best place to start. From there, take the path that best suits you for development, DBA or sysadmin and platform z/OS Linux Unix Windows...