I am populating a work file (DDS not DDL) that has a unique key (reference
numbers gathered from a master file).

1. I open a dynamic cursor with my selection criteria (SQLCode = 0).
2. I block-fetch 32,464 rows into a multi-occurance data structure (SQLCode
= 0).
3. I loop through the MODS and write the rows to an array.
4. The cursor is closed at the end of cursor fetches (SQLCode = 0).
5. The array is block-inserted to the work file for the specific number of
array entries (based on the number of elements/rows populated to the array)
(SQLCode = 0).
6. The next cursor is opened with different select criteria (but that might
qualify master file rows previously selected in the prior SQL). The second
SQL statement contains an exception join to the work file (to which I just
wrote the array) so that I do not select master file records that are
already in the work file (SQLCode = 0).
7. I block-fetch 32,464 rows into a multi-occurance data structure (SQLCode
= 0).
8. I loop through the MODS and write the rows to an array.
9. The cursor is closed at the end of cursor fetches (SQLCode = 0).
10. The array is block-inserted to the work file for the specific number of
array entries (based on the number of elements/rows populated to the array)
(SQLCode = -803).

The problem I seem to be having is that when the second SQL cursor is
opened it is NOT finding the rows that were added in the prior block-insert
and is throwing SQL error -803 (Duplicate key value specified) when I write
the next array to the work file.

Yes, I am clearing the array between the first cursor close and the next
cursor open.
Yes, I am clearing the array counter as well.

Can anyone tell me why DB2 does not commit those rows between the SQL
INSERT and the next OPEN cursor statement? Other than adding a WRKJOB
delay between operations to guess the time needed byt DB2 to commit the
inserts, is there an opcode that can be used to assure that the rows are
inserted before the next cursor is opened?

FYI - the SQLRPGLE program is compiled with COMMIT (*NONE).

Thank you, in advance, for your advice.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2022 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.