IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Row not found Sql error


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
GaganGarg

Active User


Joined: 31 Mar 2010
Posts: 134
Location: India

PostPosted: Tue Mar 07, 2017 6:29 am
Reply with quote

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
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Tue Mar 07, 2017 3:29 pm
Reply with quote

Nothing is "thrown" on a mainframe. Errors are displayed, encountered, met etc and messages issued, displayed etc.
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Tue Mar 07, 2017 9:01 pm
Reply with quote

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
View user's profile Send private message
GaganGarg

Active User


Joined: 31 Mar 2010
Posts: 134
Location: India

PostPosted: Tue Mar 07, 2017 11:00 pm
Reply with quote

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
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Tue Mar 07, 2017 11:14 pm
Reply with quote

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
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8696
Location: Dubuque, Iowa, USA

PostPosted: Tue Mar 07, 2017 11:28 pm
Reply with quote

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
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Tue Mar 07, 2017 11:29 pm
Reply with quote

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
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Wed Mar 08, 2017 12:22 am
Reply with quote

Quote:
And when you torture it a few times
Must be 9 times precisely before it gives up. icon_smile.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Error to read log with rexx CLIST & REXX 11
No new posts How I Found a Bug in a FORTRAN Compiler All Other Mainframe Topics 4
No new posts Error when install DB2 DB2 2
No new posts CLIST - Virtual storage allocation error CLIST & REXX 5
No new posts Error while running web tool kit REXX... CLIST & REXX 5
Search our Forums:

Back to Top