hi,
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??
SQLCODE -811, why always 2nd row is fetched??
Moderator: Moderator Group
- dbzTHEdinosauer
- Moderator
- Posts: 981
- Joined: Mon Oct 02, 2006 8:31 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.
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.
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.
Duplication (-811) when handled fetches the second row.. ??
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
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
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.
Regards
Nic
Nic
FREE TUTORIALS
Tutorials
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.
Interview
Mainframe Interview questions
- Cobol Interview Questions
50+ Interview Questions - JCL Interview Questions
50+ Interview Questions - DB2 Interview Questions
100+ Interview Questions - CICS Interview Questions
70+ Interview Questions - VSAM Interview Questions
27 Interview Questions
Other References
Mainframe Tools and others
- XPEDITER Reference
Explains how we can debug a program - FILEAID Reference
Explains how to browse , edit and delete datasets - Change Man Reference
Quick Start tutorial on Changeman - Abend Reference
Important Abend codes explained - FaceBook Page
MainframeGurukul FaceBook Page - LinkedIn Page
MainframeGurkul Linkedin Page