View previous topic :: View next topic
|
Author |
Message |
GaganGarg
Active User
Joined: 31 Mar 2010 Posts: 134 Location: India
|
|
|
|
Hi
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. |
|
Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
Nothing is "thrown" on a mainframe. Errors are displayed, encountered, met etc and messages issued, displayed etc. |
|
Back to top |
|
|
RahulG31
Active User
Joined: 20 Dec 2014 Posts: 446 Location: USA
|
|
|
|
How do you know if record is present in table when it is Not yet committed and especially, if the Select sql returns sqlcode=100 ?
Computers don't lie. So, if your select sql is returning a Not Found, that means the record is Not Found.
You need to investigate, why it is not found, instead of saying that the record is present in table.
. |
|
Back to top |
|
|
GaganGarg
Active User
Joined: 31 Mar 2010 Posts: 134 Location: India
|
|
|
|
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). |
|
Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
As I said before - nothing is "thrown" on a mainframe so, please, stop using that word.
A row is not written to a table until a COMMIT is issued atthe end of the unit of work. There are restrictions on when you can update using a cursor - are you using a cursor? If so, please show it. |
|
Back to top |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008 Posts: 8696 Location: Dubuque, Iowa, USA
|
|
|
|
Quote: |
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. |
|
Back to top |
|
|
RahulG31
Active User
Joined: 20 Dec 2014 Posts: 446 Location: USA
|
|
|
|
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.
. |
|
Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
Quote: |
And when you torture it a few times |
Must be 9 times precisely before it gives up. |
|
Back to top |
|
|
|