We are facing an issue with SELECT query in our CICS-DB2 program.
In the code there is a logic to select a row based upon certain conditions in WHERE clause. Issue is here that though the record is present in the table (though not committed yet in the same UOW), SELECT query throws SQLCODE=100 error code. We are using WITH UR clause as well in the SQL query.
It results in hefty payouts to the customer where a transaction is failing due to this issue. To tackle the issue, if the row is not found first time, we call the paragraph executing SQL query in program again. If row is found in 2nd attempt, we continue our processing. Otherwise we will retry the SELECT query again. This is done 9 times. If row is not found till 9th retry, our transaction fails and results in heavy loss to the client.
There are the cases, when record is found in 2nd, 3rd, 4th or nth time (less than 9) and there has been a case when SELECT query keeps throwing record not found error.
Just wanted to know the views from all DB2 professionals here that what can be the possible reason due to which it is happening? Any inputs towards this issue will be very useful. Kindly let me know your valuable thought on this.
We know that record is present in the table. There is another process in same UOW which inserts the record in our table and we read that record in our program (committed and/or uncommitted data). We know record is there in table. This is because we can see the record in table once the process is terminated and row not found sql code is thrown by program. We could check the data in table but our program continues throwing SQLCODE. It will be tried 9 times in our program.
As i said, there are instances when the row was found after nth retry (n <= 9).
Joined: 06 Jun 2008 Posts: 8391 Location: Dubuque, Iowa, USA
Issue is here that though the record is present in the table
No, the issue is that you have NO idea how DB2 works. Until the commit is complete, the record is NOT in the table at all -- it is only potentially there. Even using WITH UR, the record may not exist since a ROLLBACK could be done at any point.
The bottom line is that you either need to fix the design so the read occurs after the records are committed, or stop complaining on this forum about the normal and usual behavior of DB2. Since the process updating the record could issue a ROLLBACK at any time, relying upon data read WITH UR is incorrect -- period. The SQLCODE 100 could be due to a ROLLBACK, and that would be normal behavior.
First of all let me applaud you (or whoever the creator is), for having such an amazing process. Try, try and try.. you never know when you are gonna be lucky. Sadly, there is a reason behind everything a computer software does and they don't run on luck.
Now coming back to your question, You are Not telling what the Insert process is, you are Not even telling what you are trying to Select. You assume that everything you are doing is perfect and it is the Select sql who is telling a lie. And when you torture it a few times, it starts speaking the truth.
Unless you acknowledge that Select sql is telling you the truth with sqlcode=100, you are going nowhere.