db2 - In what sequence will the Delete SQL execute? -

refer previous posting.

sql cleanup script, delete 1 table that's not in other1

using db2 ibm (as400, db2).

i executing following sql cleanup script 3am.

delete p6prodpf (0 = (select count(*) p6opipf b   b.opiid = a.opiid)) 

i have different process @ same time sql runs inserts 2 records, first record p6opipf record , inserts detail record p6prodpf.

the problem. p6prodpf record missing after sql cleanup ran. remember process stores records ran same time.

how understand sql go's through p6prodpf , checks if record in p6opipf if not in p6opipf delete p6prodpf.

but ran visual explain in systems navigator on sql , got following result.

now confused.

after visual explain looks statement starts checking p6opipf.

so reads: if there's record in instance of time in p6opipf , no record same key in p6prodpf delete p6prodpf.

this explain problem p6prodpf gets deleted when process inserts records , sql script runs @ same time.

so how see in sequence.(my theory)

1. the process inserts 2 records starts.

2. the process inserts 2 records inserts first record in p6opipf.

3. at same time sql cleanup runs. query see's p6opipf record , checks if has p6prodpf record. @ stage there still no p6prodpf inserted sql thinks needs delete record in p6prodpf.
4. in same time process inserts 2 records inserts second record in p6prodpf.
5. and because sql did not see p6prodpf @ stage deletes new inserted record in p6prodpf leaving p6opipf record no p6prodpf record.

am correct?

what want know delete script listed above. how understand sql go's through p6prodpf , checks if record in p6opipf if not in p6opipf delete p6prodpf. after visual explain can see starts checking p6opipf. delete statement first check?

the code of insert generated in ca plex generator. rpgiv code.

my 1 function insert first p6opipf(operationsitem.update.insertrow) , detail in p6prodpf(productdetail.update.insertrow).

insert row function

my scheduled function code execute delete script.

scheduled delete script function

hope makes sense.

do have heard of concepts "transaction" , "isolation"? typically different processes running against same database shielded (isolated) against each other, operating without seeing immediate impact of other transaction running @ same time. logically 2 transactions (a process or sequence of sql statements) executed @ same time executed in serial way.

in case either first process "first" or "second". if repeat tests may see different results depending on "first" (logically).