SQL In service Programs is notoriously hard to get the pseudo cursors to close.
A potentially better solution would be to stop deleting the table each time and just remove the entries from it.
On way would be to modify the program to catch the DLTF error and run a CLRPFM instead, then skip the create table.
Changing the DLTF to an SQL Drop Table QTEMP.FILE might solve the pseudo cursor issue, since it also operations in what I like to call "SQL LAND".
Birgitta's suggestion to use GLOBAL TEMPORARY TABLE with replace is a good one too.
I've also had good luck using a "Global Temporary Table" with the "ON COMMIT DELETE ROWS".
But that requires you to activate commitment control.
Which you could always do within your Named Activation group.
Create a CLLE program which runs in the named activation group and then run STRCMTCTL CMTSCOPE(*ACTGRP).
Then, when you are done using the data in the QTEMP table just run COMMIT within the named activation group.
Even though the Service Program is running in its own activation group, that activation group does not get removed from the job after the call.
In you test the original scenario, with the CLLE in *NEW, you might have to rerun the program many times before the iSeries decides to "help you out" with the pseudo close cursors.
Trying to use RCLACTGRP will cause the mapping between the current activations (of the SRVPGM) and the SRVPGM to be destroyed.
Thus the "tried to refer to all or more objects that no longer exist" errors.
I've only ever seen this work when there were no activations of the SRVPGM within the Default Activation group.
Having default activation group programs and legacy programs in the mix just adds more "fun" to that scenario.
I tend to make my SRVPGM SQL programs use "CLOSQLCSR = *ENDACTGRP".
I've never really seen *ENDMOD work for SRVPGM objects.
But that's mostly because the SRVPGM module is normally only "initiated" once.
If CLOSQLCSR(*ENDMOD) is specified, all cursors are in the closed state each time the module is initiated.
A DISCONNECT would be one method to for All cursors to be closed.
DISCONNECT CURRENT
CONNECT RESET
Your best bet is probably to try a DROP TABLE or just DELETE FROM QTEMP.FILE.
Chris Hiebert
Senior Programmer/Analyst
Disclaimer: Any views or opinions presented are solely those of the author and do not necessarily represent those of the company.
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Jay Vaughn
Sent: Wednesday, July 20, 2022 8:18 AM
To: RPG programming on the IBM i (AS/400 and iSeries) <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: ILE and locked file
I have a *SRVPGM that runs in a named activation group.
It contains a procedure that does the following...
- dltf qtemp.file
- create table qtemp.file
- inserts/updates the qtemp.file
From a CLLE pgm that runs in *NEW, I am testing this procedure call in the
*SRVPGM.
The procedure is called 4 different times with no issues. Expected results
for each call.
Now I place those same 4 calls to the procedure into a different CLLE that
runs in *CALLER (this may have nothing to do with it but just throwing it
out there).
On the 2nd call to the procedure when it tries to do the DLTF, it cannot
allocate the file and fails.
I have tried to DLCOBJ the file from lock state *SHRRD prior but that does
not work.
(when i do a wrkobjlck on the qtemp.file I see it is locked with *SHRRD)
What are my other options to overcome this? Why does it not do this in my
test CLLE?
tia
Jay
midrange.com
