Did you ever know how powerful MERGE is in DB2? Let me demonstrate that to you through this simple example.
Create a table
$ db2 connect to sample
$ db2 "create table fmtmerge
(c1 int not null generated always as identity primary key,
c2 decimal(13,4) not null,
c3 varchar(30),
c4 char(6),
c5 int,
c6 timestamp)"
The column c1 is a primary key which is also generated by default using IDENTITY feature of DB2 similar to Sybase or SQL Server. You could also do same thing using DB2 SEQUENCES same way you do in Oracle.
Let us say that I get data from other systems on a daily basis and I need to merge the incoming data with FMTMERGE table. The merge is going to be either simple UPDATE or on failure INSERT.
DB2 has a feature called multi-row INSERT and this can be used in this context too. Using VALUES clause, you can actually generate a table on the fly in DB2.
Let us do that first before we jump to MERGE.
Multi-rows in DB2
$ db2 "values (cast(1 as int),cast(2 as int),cast(3 as int)),(3,4,5),(4,5,6)"
1 2 3
----------- ----------- -----------
1 2 3
3 4 5
4 5 6
$ db2 "select * from (values (cast(1 as int),cast(2 as int),cast(3 as int)),
(3,4,5),(4,5,6))as t(c1,c2,c3)"
C1 C2 C3
----------- ----------- -----------
1 2 3
3 4 5
4 5 6
In the first example, we just created a tables using VALUES on the fly. In the second example, we used VALUES in SELECT and gave it a name and assigned each column a name also. Isn’t that nice?
Now, we will use same approach of VALUES to merge table that we created above.
MERGE statement
db2 "merge into fmtmerge t using (values
(1, 12345.34, 'Raleigh', 'NCSU', 34234, NULL),
(2, 1543234.34322, 'Durham', 'DUKE', NULL, CURRENT TIMESTAMP),
(3, 12.34, 'Chapel Hill', 'UNC', 243234, '2007-07-15-12.34.34.000000'))
as s (c1,c2,c3,c4,c5,c6)
on t.c1 = s.c1
when matched then
update set t.c2 = s.c2,
t.c3 = s.c3,
t.c4 = s.c4,
t.c5 = s.c5,
t.c6 = s.c6
when not matched then
insert (c2,c3,c4,c5,c6)
values (s.c2,s.c3,s.c4,s.c5,s.c6)"
To begin with, our table was empty and above merge inserted 3 rows in a single pass.
Just by changing few values in above merge, you either update the table or insert rows. You can use same MERGE in your application (Java or CLI or Embedded SQL) and prepare the statement with parameter markers and execute the statement to merge many rows in a single pass.
This feature is UNIQUE to db2 and with this, achieve the performance that you are looking for.
Use it in the application
Prepare following statement with parameter markers and keep on executing it again and again by binding the parameters. You are merging 3 rows at a time through a single SQL statement. You can use as many rows as possible until you run out of parameter markers. The max limit of parameter markers in db2 is 32K. Of course, if you use multi-row approach, make sure that your SQL statement size is still within the 2MB limit.
merge into fmtmerge t using (values
(cast(? as INT), cast(? as decimal(13,2),
cast(? as varchar(30)), cast(? as char(6)),
cast(? as int), cast(? as timestamp)),
(?, ?, ?, ?, ?, ?),
(?, ?, ?, ?, ?, ?),
as s (c1,c2,c3,c4,c5,c6)
on t.c1 = s.c1
when matched then
update set t.c2 = s.c2,
t.c3 = s.c3,
t.c4 = s.c4,
t.c5 = s.c5,
t.c6 = s.c6
when not matched then
insert (c2,c3,c4,c5,c6)
values (s.c2,s.c3,s.c4,s.c5,s.c6)