In what sequence will the Delete SQL execute?

1
votes

Refer to my previous posting.

Sql Cleanup script, delete from one table that's not in the other1

Using DB2 for IBM i (As400, Db2).

I am executing the following sql as a cleanup script 3am. 

DELETE FROM p6prodpf A WHERE (0 = (SELECT COUNT(*) FROM P6OPIPF B WHERE   B.OPIID = A.OPIID))

I have a different process that at the same time that this sql runs inserts two records, the first record is the P6OPIPF record and then inserts the detail record into P6PRODPF.

The problem. The P6PRODPF record is missing after the SQL cleanup ran. But remember that the process that stores the records ran the same time.

How I understand the SQL is that it go's through P6PRODPF and checks if the record is in P6OPIPF if its not in P6OPIPF then delete P6PRODPF.

But then I ran Visual Explain in I systems navigator on this SQL and got the following result.

enter image description here

enter image description here

enter image description here

enter image description here

Now I am confused.

After Visual explain It looks like the statement starts with checking P6OPIPF.

So then it reads: If there's a record in that instance of time in P6OPIPF and no record with the same key in P6PRODPF then delete the P6PRODPF.

This could explain my problem that P6PRODPF gets deleted when my process that inserts the records and the sql script runs at the same time.

So how I see it in Sequence.(My theory)

  1. The process that inserts the two records starts.

  2. The process that inserts the two records inserts the first record in P6OPIPF.

  3. At the same time the SQL cleanup runs. the query see's the P6OPIPF record and checks if it has a P6PRODPF record. At this stage there is still no P6PRODPF inserted so Sql thinks it needs to delete the record in P6PRODPF.
  4. In the same time The process that inserts the two records inserts the second record in P6PRODPF.
  5. And because the Sql did not see the P6PRODPF at that stage it deletes the new inserted record in P6PRODPF leaving a P6OPIPF record with no P6PRODPF record.

Am I correct?

What I actually want to know is just the Delete script listed above. How I understand the SQL is that it go's through P6PRODPF and checks if the record is in P6OPIPF if its not in P6OPIPF then delete P6PRODPF. But after the visual explain I can see its starts with checking P6OPIPF. So What will the delete statement first check?

The code of the insert is generated in CA PLEX generator. RPGIV code.

My one function that will insert first P6OPIPF(OperationsItem.Update.InsertRow) and then its detail in P6PRODPF(ProductDetail.Update.InsertRow).

Insert Row function

My Scheduled function code that will execute the delete Script.

Scheduled delete script function

Hope it makes sense.

3
sqldb2ibm-midrangedb2-400
Side note: the more idiomatic way to do the no-row delete is like this: DELETE FROM P6ProdPF A WHERE NOT EXISTS (SELECT 1 FROM P6OpiPF B WHERE A.opId = B.opId). In many-to-one situations this is likely to be faster (instead of needing to count the rows, it just checks for any). - Clockwork-Muse
@Clockwork-Muse thanks for the tip I will use this in future. - Renier

3 Answers

2
votes

Visual Explain is a useful tool for understanding what the DB is doing, particularly when trying to enhance performance.

But SQL is not a procedural language. You should not nor can you really be trying to say when I run this statement, the DB is doing this, then it's doing this.

While it might be true for that particular run, it's highly dependent on the data and resources available. You can not code a processes around the steps you see.

You really shouldn't be trying to run both processes at the same time, there's simply no way to ensure what you'll end up with; at least when using the default isolation level (probably "no commit" or "read uncommited" depending on the interface.)

If you must run both processes at the same time, you probably want to run the delete under "repeatable read" or "serializable"; which should have the effect of locking the tables being referenced so that no other process can change them.

Optionally, you could run both the delete and insert under the read stability or higher isolation levels.

2
votes

To explain the Visual Explain, DB2 will check the inner expression before executing the DELETE clause - it has to, or else it won't know what rows are affected.

The reason your archived rows aren't archived is because the delete script ran before the insert script.

1
votes

Do you have heard of the concepts "transaction" and "isolation"? Typically different processes running against the same database are shielded (isolated) against each other, so they are operating without seeing the immediate impact of any other transaction running at the same time. Logically two transactions (a process or sequence of SQL statements) executed at the same time are executed in a serial way.

In your case either the first process is the "first" or "second". If you repeat your tests you may see different results depending on who is "first" (logically).