Re: ILE and locked file

On Jul 21, 2022, at 2:13 PM, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx> wrote:

For CLRPFM it needs EXCL authority, so it will first allocate and after
deallocate the table. If it deletes the rows, it will do an RGZPFM or it
will recreate the table.
With DELETE FROM TABLE only the rows to be deleted get are flagged but not
really deleted nor is the file reorganized.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser
Modernization – Education – Consulting on IBM i


"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
„Train people well enough so they can leave, treat them well enough so they
don't want to.“ (Richard Branson)


-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Gad Miron
Sent: Donnerstag, 21. Juli 2022 17:00
To: rpg400-l@xxxxxxxxxxxxxxxxxx
Subject: Fwd: ILE and locked file

My 2 cnts

In a certain job that repeatedly clears and re-populate a (small) work file
in QTEMP we found that delete from QTEMP/WORKF is much faster then CLRPFM
QTEMP/WORKF

Gad

date: Wed, 20 Jul 2022 19:57:41 +0000
from: "Hiebert, Chris" <chris.hiebert@xxxxxxxxxxxxxx>
subject: RE: ILE and locked file

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.

--
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

--
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