Hi all,

From the Knowledge Center:

"INSERT_UPDATE Updates rows with matching primary key values with values of input rows. Where there's no matching row, inserts imported row into the table."

So each input record results in either 1 row being inserted or 1 row being updated, right?

I found the following while importing batches rows (representing SAP transports) from different sources (SIDs) for comparison. However the example below is generic and simplified for brevity. Output is shown indented - remove the indents if trying to
recreate...

db2 "create table test(key1 char(8) not null default 'dft_cart', key2 char(8) not null, data varchar(20))"
db2 "alter table test add constraint test_pk primary key (key1, key2)"

vi testfile1.del testfile2.del

cat testfile1.del

1|6 x oranges
2|3 x apples

cat testfile2.del

1|2 x pumpkin
1|3 x pumpkin

db2 "import from testfile1.del of del modified by coldel| insert into test(key2, data)"
db2 "update test set key1 = 'cart1' where key1 = 'dft_cart'"

db2 "select * from test"

KEY1 KEY2 DATA
-------- -------- --------------------
cart1 1 6 x oranges
cart1 2 3 x apples

2 record(s) selected.

db2 "import from testfile2.del of del modified by coldel| insert_update into test(key2, data)"

Number of rows read = 2
Number of rows skipped = 0
Number of rows inserted = 1
Number of rows updated = 1
Number of rows rejected = 0
Number of rows committed = 2

db2 "update test set key1 = 'cart2' where key1 = 'dft_cart'"

db2 "select * from test"

KEY1 KEY2 DATA
-------- -------- --------------------
cart1 1 1 x pumpkin <-- This update is wrong!
cart1 2 3 x apples
cart2 1 1 x pumpkin

3 record(s) selected.

So instead of 4 rows, 2 for each cart, the the row with KEY2 = 1 is updated by the INSERT_UPDATE. So actually the second import has inserted 1 row and updated 2 (contrary to output above).

It seems the utility is doing a partial key match when not all keys are specified. This is undocumented behaviour and very unlikely to be intended.

As before with my rollforward timestamp bug I'd love to report this, but the client has no DB2 support contract, so the bug stays. I am seeing this on DB2 10.5 fix pack 7 and DB2 11.1 fix pack 1 on Linux (Intel). There's nothing in later fix pack APAR
lists to suggest this has been fixed since. I'd be interested to know how far back this goes, my guess is it is probably long-standing.

I'll look at working around this with INGEST but it's sad not being able to report it and get this fixed.

Jeremy

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

As expected, ingest behaves correctly.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)