Re: Performance SQL Delete vs RPGLE Delete

If there's an actual row lock...

I'd expect DELETE FROM to fail...

Whereas CLRPFM fails when there's a job with a *READ lock on the TABLE
itself.

Pseudo-closed cursors are often the cause of a *READ lock that causes
CLRPFM to fail.

TRUNCATE TABLE might still work, look at some of the other parameters.

Charles


On Fri, Nov 20, 2020 at 10:41 AM K Crawford <kscx3ksc@xxxxxxxxx> wrote:

All good questions. The design was done a long time ago before me. Do not
want to change the process just looking for a more efficient way to remove
the rows. This appears to be a bottleneck.

I did not know about the truncate table (I learned something new, thank
you). I tested it and it will issue an error if a row has a lock.
I tested the delete from. It worked even with the lock on a row.

We might be able to remove the two LF to help the process.

As for the being concerned about a row not being fully processed by the
person with the lock on it. Very good concern. Almost all programs that
look at it are from our web site and are display only. It would be a
bigger design change and time eater that management would not approve. I
can not get them to get rid of OCL36. Yes that is what I said. As they
say "It is working, don't fix something that is not broken".

When I propose this change I will approach the remove locks thing again.

You guys are the best.
Kerwin.

On Fri, Nov 20, 2020 at 6:22 AM Paul Therrien <
paultherrien@xxxxxxxxxxxxxxxxxx> wrote:

+1


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
smith5646midrange@xxxxxxxxx
Sent: Thursday, November 19, 2020 8:26 PM
To: 'Midrange Systems Technical Discussion' <

midrange-l@xxxxxxxxxxxxxxxxxx

Subject: RE: Performance SQL Delete vs RPGLE Delete

Somewhat beyond the scope of the question but if there is something that
has
the file open, aren't you running the risk of deleting records that have
not
yet been processed? I would be really concerned deleting all of the
records
in the file if I didn't have 100% control of it.

Just my $.02 that on a subject where it wasn't asked for.


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of K
Crawford
Sent: Thursday, November 19, 2020 5:25 PM
To: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
Subject: Performance SQL Delete vs RPGLE Delete

I know the normal answer is, depends. Just looking for thoughts.

I have a program (RPGLE) that has this code (the file has a lock on it
otherwise we would use CLRPFM or some other method).

Read FileA;
Dow not %eof(FileA);
Delete FileAr;
Read FileA;
Enddo;

How would the performance compare to SQL with something like, in that

same

RPGLE program.
Exec SQL Set option Commit=*None
Exec SQL Delete From FileA;

FileA will have 0.5 - 6 million rows.
--
Kerwin Crawford
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-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 Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-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 Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-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

--
KCrawford
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-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