Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Row not found Sql error

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Row not found Sql error
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: 1800
Location: UK

PostPosted: Tue Mar 07, 2017 3:29 pm    Post subject:
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: 375
Location: USA

PostPosted: Tue Mar 07, 2017 9:01 pm    Post subject: Reply to: Row not found Sql error
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    Post subject: Reply to: Row not found Sql error
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: 1800
Location: UK

PostPosted: Tue Mar 07, 2017 11:14 pm    Post subject:
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: 8086
Location: East Dubuque, Illinois, USA

PostPosted: Tue Mar 07, 2017 11:28 pm    Post subject:
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: 375
Location: USA

PostPosted: Tue Mar 07, 2017 11:29 pm    Post subject: Reply to: Row not found Sql error
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: 2278
Location: @my desk

PostPosted: Wed Mar 08, 2017 12:22 am    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts DB2 Program abending without giving a... rahulgarg14 DB2 7 Mon May 29, 2017 8:10 pm
No new posts Getting error while opening a variabl... apandey1 COBOL Programming 5 Fri May 05, 2017 12:22 pm
No new posts IDC2902I Error tzeche ABENDS & Debugging 2 Tue Mar 28, 2017 11:39 am
No new posts Record Not found in AlX but record re... mukun264 COBOL Programming 7 Fri Mar 24, 2017 9:28 am
No new posts Need help with below DB2 error ashek15 DB2 3 Sat Mar 18, 2017 5:56 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us