Re: SQL Block Reads To MODS and SQL Block Inserts From An Array In SQLRPGLE Issue

This is all in one program?

Then I would say what you think is happening, is not.
"Can anyone tell me why DB2 does not commit those rows between the SQL
INSERT and the next OPEN cursor statement? "
"compiled with COMMIT (*NONE)"

Rows inserted under COMMIT(*NONE) are there once the statement returns.
I suppose you could have found some bug in the OS, but assuming you're
current on PTFs that is unlikely.

Assuming your code actually works as described, any chance something else
is adding rows to the workfile between steps?

I question why you're reading the rows into the RPG program in the first
place? (also why you're reading into a MODS and moving to an array to
insert.)

Why not just generate the work file with SQL? Preferably as a single
insert into workfile (select ....) statement.

Charles





On Wed, Jan 5, 2022 at 8:37 AM Robert Wenzel <robert.wenzel@xxxxxxxxx>
wrote:

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.

--

*Robert Wenzel, Software Engineer*
iSeries Applications Support and Development

[image: Inmar]
<
https://www.inmar.com/?utm_source=email%20signature&utm_medium=email&utm_campaign=Associate%20Email%20Signature&utm_content=Inmar%20Logo

robert.wenzel@xxxxxxxxx

635 Vine Street, Winston Salem, NC 27101
*p: 336-770-3478*
www.inmar.com
<
https://www.inmar.com/?utm_source=email%20signature&utm_medium=email&utm_campaign=Associate%20Email%20Signature&utm_content=URL%20link

| LinkedIn <https://www.linkedin.com/company/inmar> | Facebook
<https://www.facebook.com/inmarinc> | Twitter
<https://twitter.com/inmarinc>

"We have no choice, to be a human being is to be a latent fool. The choice
we have is whether or not we are going to be practicing fools" - Stewart
Emery

--


********************************************





*Inmar Confidentiality
Note*: This e-mail and any attachments are confidential and intended to
be
viewed and used solely by the intended recipient. If you are not the
intended recipient, be aware that any disclosure, dissemination,
distribution, copying or use of this e-mail or any attachment is
prohibited. If you received this e-mail in error, please notify us
immediately by returning it to the sender and delete this copy and all
attachments from your system and destroy any printed copies. Thank you
for
your cooperation.





*Notice of Protected Rights*: The removal of any
copyright, trademark, or proprietary legend contained in this e-mail or
any
attachment is prohibited without the express, written permission of Inmar,
Inc. Furthermore, the intended recipient must maintain all copyright
notices, trademarks, and proprietary legends within this e-mail and any
attachments in their original form and location if the e-mail or any
attachments are reproduced, printed or distributed.



********************************************
--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com