RE: Finding offending RPG program causing locks on files

We have confirmed it is the RPG programs in the previous case. We could easily replicate it by opening the F7 screen in the IBMi 5250 session for a record and you could no longer login to the web application.

We fixed that program by making sure that update screen was not keeping the file open for as long as you were in that screen.

We are now trying to find the other RPG programs that are doing the same thing.

I can confirm SQLSTATE 57014 is what is being returned in this case as we can replicate the issue.



-----Original Message-----
From: Charles Wilt <charles.wilt@xxxxxxxxx>
Sent: Friday, June 28, 2019 12:08 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Finding offending RPG program causing locks on files

Yes Db2 monitors for deadlocks...and automatically kills one of the offending jobs.

But what you describe isn't a deadlock.

Are you sure it's a exclusive lock on a file? RPG doesn't offer that functionality.
You'd have to be issuing the ALCOBJ command from CL, or the ALLOCATE TABLE SQL statement.

Also, SQLSTATE 57014 isn't what would be returned to the web app, 57033 would be expected from the server.

You might see 57014 if your web app is timing out and canceling the request itself, rather than waiting for the server timeout to expire.

In any event,
There's the WRKOBJLCK command...if you know what object(s) are being locked.

Or take a look at the QSYS2.OBJECT_LOCK_INFO() Db2 Service https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/QSYS2.OBJECT_LOCK_INFO


and/or QSYS2.RECORD_LOCK_INFO() Db2 Service https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/QSYS2.RECORD_LOCK_INFO


Charles





On Fri, Jun 28, 2019 at 10:42 AM Matt Olson <Matt.Olson@xxxxxxxx> wrote:

Is there a deadlock monitoring system built into DB2 like there is in
SQL Server?

If so how do you enable it or view it?

We are finding some RPG programs that are maintaining exclusive locks
on files for as long as you are in the screen, which then causes our
end users of our website to not be able to login as the web program
can't read their record. This exhibits itself in an not very helpful
message of "[57014] [IBM][AS] Resource error - SQLSTATE 57014, SQLCODE -1044."

We have already fixed one RPG program which has helped the situation,
but there appears to be more RPG screens doing this.

I'm wondering what the best strategy is for finding these RPG programs
as the locks are released when they close out and we might not be able
to do a WRKOBJLCK command in time on the four potential files that are
typically required to login.
--
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@xxxxxxxxxxxx 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@xxxxxxxxxxxx for any subscription related questions.

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