SQLCODE -811, why always 2nd row is fetched??

db2 sql interview questions

Moderator: Moderator Group

Post Reply
Posts: 1
Joined: Thu Aug 05, 2010 4:30 pm

SQLCODE -811, why always 2nd row is fetched??

Post by sukhmeet » Thu Aug 05, 2010 5:06 pm


I want to know if their are multiple records(duplicate rows) in a Db2 table,
on fetching it I am getting the 2nd record everytime in my output file..(after handling sqlcode -811)

I want to know why?? why not 1st or 3rd record or any other??

User avatar
Posts: 981
Joined: Mon Oct 02, 2006 8:31 pm

Post by dbzTHEdinosauer » Thu Aug 05, 2010 6:14 pm

what is the version of your db2.

prior to vsn7,
db2 would always populate the host-variables with 'one of' the resultant rows,
but which was always unpredictable.

vsn7 and >>>
population of the host-variable by db2 on returning a -811 sqlcode, is not done.

and since it is a singleton select,
you do not have an order by,
your comment that it is the 2nd row is not based on definite predictability.
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.

Posts: 1
Joined: Fri Apr 26, 2013 11:34 pm

Duplication (-811) when handled fetches the second row.. ??

Post by vinusha » Fri Apr 26, 2013 11:45 pm

It might not be a unpredictable..i'm not sure but the following might be a reason:
when the select query is executed, internally it would search for a match in criteria. After finding a first match it continue to search to ensure that there are no duplicates. Once it finds a second match it throws a duplication error(-811). If this error is handled, the latest fetched/accessed record would be the second row. Hence the second row is always fetched.

I hope this makes sense :)

Active Member
Posts: 650
Joined: Sun Jul 24, 2011 5:27 pm
Location: Down on the pig farm

Post by NicC » Sat Apr 27, 2013 1:04 pm

It makes sense but...there is no guarantee that the second row fetched was the same as the previous time this happened as DB2 does not guarantee the order unless you use ORDER which, I believe, can only be used with a cursor. Anyway, in the years that have passed since the original post things could have changed from what the OP and DBZ wrote.


Topic deleted by Admin

Post by academyindia4 » Sat Jan 30, 2016 11:25 pm

<< Content deleted By Admin >>


Topic deleted by Admin

Post by academyindia4 » Sat Jan 30, 2016 11:25 pm

<< Content deleted By Admin >>

Post Reply


Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Mainframe Interview questions

Other References
Mainframe Tools and others