Re: Fwd: ILE and locked file

[Peter Dow <petercdow@xxxxxxxxx>]

Hi Gad,

Did you count the time required to re-create the file?

DLTF + CRTPF < CLRPFM?

--
*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
petercdow@xxxxxxxxx
pdow@xxxxxxxxxxxxxx /

On 7/21/2022 7:59 AM, Gad Miron wrote:
> 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.