IBM Support

How to insert data in desired sequence

Technical Blog Post


Abstract

How to insert data in desired sequence

Body

 There are two independent insertions on table T01, one after another. The insertion on table T01 will trigger a stored procedure call which then writes a row into another table T02. Will the sequence of insertions on T01 guarantee the sequence of insertions on T02? Let's take a look into the scenario:

 

Point in time: A ----------> B --------------------> C
          Insert #1 ---------->  calls SP1() ---------> insert #3
 
Point in time: X ----------> Y --------------------> Z
          Insert #2 ----------> calls SP1() ---------> insert #4

 
In terms of the point in time in each timeline, we know A happens prior to X, and B happens prior to Y:

  A < X, B < Y

But this will NOT guarantee C < Z
 
Here is a demo in which I intentionally prolong the execution of the procedure by putting it in sleep for a random seconds.

# setup.db2
CREATE TABLE T01(id int, TS timestamp)@
CREATE TABLE T02(id int, TS timestamp)@
 
CREATE OR REPLACE PROCEDURE SP01 (IN ID INT)
 LANGUAGE SQL
BEGIN
  DECLARE v_msg VARCHAR(80);
  DECLARE v_status INTEGER;
  call DBMS_ALERT.WAITONE('SLEEP_BY_SECONDS_NOT_REAL_ALERT',v_msg,v_status,sysfun.rand()* 10);
    insert into t02 values (id, current timestamp);
END@
 
CREATE OR REPLACE TRIGGER T01_TR
  AFTER INSERT ON T01
  REFERENCING NEW AS N
  FOR EACH ROW
  CALL SP01(N.ID)@
 
# insertA
db2 connect to tj01
db2 "insert into t01 values (1,current timestamp)"
 
# insertB
db2 connect to tj01
db2 "insert into t01 values (2,current timestamp)"

 
Kick off the two insert scripts one by one

./inserta &
sleep 0.5
./insertb &

 
After they are finished, checking the result we may see the different sequences in T01 vs T02 like this one:

 

[inst105@sades1 TS001234567]$ db2 "select * from t01"
 
ID     TS
----------- --------------------------
     1 2018-11-07-18.12.51.800131
     2 2018-11-07-18.12.52.300895

 
 2 record(s) selected.
 
[inst105@sades1 TS001234567]$ db2 "select * from t02"
 
ID     TS
----------- --------------------------
     2 2018-11-07-18.12.52.304467
     1 2018-11-07-18.12.55.812912

 2 record(s) selected.

 

The test indicates in real life, there are number of factors affecting the execution of procedure. Even we fire the same procedure from two separate sessions at the same point in time, there is no guarantee they will finish at the same time; unless there is a dependency to guarantee the sequence of the procedure calls like:
 
db2 "delete from t01"
db2 "delete from t02"

./inserta
./insertb

 

This time script inserb will not be executed until inserta has returned. So the insertion on T02 will be the same as in T01.

 

[inst105@sades1 TS001234567]$ db2 "select * from t01"
 
ID     TS
----------- --------------------------
     1 2018-11-07-18.25.38.005355
     2 2018-11-07-18.25.44.065217

 
 2 record(s) selected.
 
[inst105@sades1 TS001234567]$ db2 "select * from t02"
 
ID     TS
----------- --------------------------
     1 2018-11-07-18.25.43.009524
     2 2018-11-07-18.25.44.067161

 
 2 record(s) selected.

 

 

[{"Business Unit":{"code":"BU029","label":"Data and AI"}, "Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm11140112