1

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.

Improve this question
2
  • 2
    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). Commented Aug 4, 2015 at 6:03
  • @Clockwork-Muse thanks for the tip I will use this in future. Commented Aug 4, 2015 at 7:19

3 Answers 3

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

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.

Improve this answer

4 Comments

I agree with you, I also think that the Delete ran before the insert but it does not seem possible... There is one program that calls 2 inserts the first insert is in P6OPIPF, after it the same program calls another insert into P6PRODPF. In the same time that this program run the Delete SQL ran. After the Delete ran I checked the records and I can see It deleted P6PRODPF. Why did the Delete SQL script delete P6PRODPF even if there was a P6OPIPF record?.
I think there is some confusion over the words 'at the same time'. Do you really mean that the program ran 3 separate SQL statements in this order: 1) Insert a 2) insert b 3) DELETE
Sorry let me clarify : There are two RPGIV functions 1. the one inserts P6OPIPF and after P6OPIPF in the same function inserts its detail into P6PRODPF. 2. Is a scheduled function that will execute That Delete SQL on a scheduled time. Now in my instance function one started and when I look at the time function 2 executed it looks as if its the same time. I posted the code of the 2 functions please could you have a look.
If there are two separate actions (insert and delete) and they run in two different jobs, and it looks like they both ran at the same time, there is no guarantee which one ran first. To fix this, put the delete in the same program as the insert so that the delete will always run after the insert.
1

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).

Improve this answer

7 Comments

Thanks for your reply. It makes sense what you are saying. 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 order in which the tables are accessed can change depending on the presence of indexes and the amount of data in the tables. You should not rely on the order of access.
So in your opinion will the Sql delete script behave differently depending on the presence of indexes and the amount of data in the tables. For example on a live system. Table A gets inserted through program, then Delete Sql script runs starting to check Table A or B depending on Indexes, then Table B inserts through program. Could this be a potential problem?
@Renier - it's worse than that: you have no control over the ordering of which rows are dealt with, or the timing of processes. Even if you start the INSERT first, it won't necessarily actually insert the row first. It's best to consider statements from separate process to always interleave, because they will. You need to start a transaction.
@Renier - neither of those two are all that helpful, because neither contain the actual statements. The "delete" one is being given a statement from somewhere else. I'm not going to bother completely tracing through the insert program (Especially given it looks like that). You have a lot to read - transactions are a somewhat complex topic. Your DBA should be able to help you; for one thing, they're usually the one who has to worry about locking anyways.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.