I have a scenario where i want to insert record if it doesn't exist in DB2. If it already exists update is_active column to 0 of the existing row and insert the new row with is_active as 1.

I cannot use merge into as i cannot run 2 queries in when matched section.

How can i achieve this in batch.

If i were to run queries one by one i could have run them. But since there are millions of such records i want to do this in batch.

I want to do this using java prepared statement.

  • Unfortunately, you didn't provide enough details about your table structure for a proper answer, but one way to approach this is to insert records unconditionally, then set is_active for earlier records if they exist. – mustaccio Mar 7 at 15:34
  • How i would do it in batch and how would i know which one is earlier record – Abhishek Mar 7 at 15:41
  • Exactly. How do you know "If it already exists"? – mustaccio Mar 7 at 16:01

Using temporary table

create table test_tab (id int not null, is_active int not null) in userspace1;

with stream (id) as (values 
--1, 2, 3
--2, 3, 4
3, 4, 5
, upd as (
select count(1) cnt
from new table(
  update test_tab t
  set is_active=0
  where is_active=1 and exists (select 1 from stream s where s.id=t.id)
select count(1) cnt
from new table(
  insert into test_tab(id, is_active)
  select id, 1
  from stream

select * from test_tab;

Assuming you accumulate your batch values into the streams table before each attempt to change the base table test_tab.
You can do all your operations using such a single statement as described above.
If you want to use some real table instead of streams, it's worth to collect statistics on it before every such a statement and test the performance using various number of records in the streams table.

Using batch inserts

create table test_tab2 (id int not null, is_active int not null default 1, id_int bigint not null generated always as identity) in userspace1;
create index test_tab2_idx on test_tab2(id);

create trigger test_tab2_air
after insert on test_tab2
referencing new as n
for each row
update test_tab2 t
set is_active=0
where t.is_active=1 and t.id=n.id and t.id_int<>n.id_int;

insert into test_tab2 (id) values 
--1, 2, 3
--2, 3, 4
3, 4, 5

You can just insert new records in batches in your java program. The trigger deactivates the corresponding old records.

  • Appologies, i wanted to this from java prepared statement. Sorry about missing this info. Have updated the question – Abhishek Mar 8 at 5:42
  • @Abhishek, nothing prevents you from using Marks suggestion in a prepared statement – Lennart Mar 8 at 7:12

I don't think you can do it in one statement with MERGE because you need to both update and insert when there is a match. But you can surely do it with 2 statements, UPDATE and INSERT (and a 3rd to clear the source), in a single transaction.

Lets say that we want to update table_name with the data from table new_values and the columns that identify a "match" are columns uq1 and uq2:

    UPDATE table_name AS t           -- the target
    SET is_active = 0
          ( SELECT 1
            FROM new_values AS s     -- the source
            WHERE s.uq1 = t.uq1
              AND s.uq2 = t.uq2
              -- AND <cond>          -- batching condition

    INSERT INTO table_name
      ( uq1, uq2, c3, c4, c5, is_active )
        uq1, uq2, c3, c4, c5, 1
    -- WHERE
    --     <cond>          -- batching condition

    DELETE FROM new_values
    -- WHERE
    --     <cond>          -- batching condition
  • How would i do it in batch, there millions of rows if it inserts one by one that takes so long. As you mentioned i could do it in 2 queries but how in batches – Abhishek Mar 7 at 16:13
  • I made an edit with a way (assuming you have the data in a (temporary) source table) – ypercubeᵀᴹ Mar 7 at 16:14
  • This is streaming data, scenario is 1 record arrives insert with is_active 1 now 2 record arrive which is update on the same key as 1 so need to update record 1 isactive to 0 and insert new record 2 with is_active 1, now i need to do this in batch of lets say 4000 so that at once 4000 recorda gets inserted and when these 4k records get insert it updates isactive to 0 if there is an update on the same key – Abhishek Mar 7 at 18:14
  • Your previous comment said millions of rows. Now you say 1. Which is which? How many rows per second do you need to insert? – ypercubeᵀᴹ Mar 7 at 18:17
  • Updated previous comment, there are 500 msg per sec – Abhishek Mar 7 at 18:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.