Re: Re[2]: Persistent File Locks when using SQLRPGLE for Neverending Programs

Rick,

Again, the system is working as designed....

Even if you're not opening a cursor directly, the system will try to
reuse an open data path (ODP) if possible. By default, the third time
a statement is used by a job the ODP will be left open, if possible,
instead of being closed.

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/sqlp/rbafycncr.htm

"Default and user-specifiable lock-wait time-out values are supported.
DB2 UDB for iSeries creates tables, views, and indexes with the
default record wait time (60 seconds) and the default file wait time
(*IMMED). This lock wait time is used for DML statements. You can
change these values by using the CL commands Change Physical File
(CHGPF), Change Logical File (CHGLF), and Override Database File
(OVRDBF).

The lock wait time used for all DDL statements and the LOCK TABLE
statement, is the job default wait time (DFTWAIT). You can change this
value by using the CL commands Change Job (CHGJOB) or Change Class
(CHGCLS).

In the event that a large record wait time is specified, deadlock
detection is provided. For example, assume one job has an exclusive
lock on row 1 and another job has an exclusive lock on row 2. If the
first job attempts to lock row 2, it will wait because the second job
is holding the lock. If the second job then attempts to lock row 1,
DB2 UDB for iSeries will detect that the two jobs are in a deadlock
and an error will be returned to the second job.

You can explicitly prevent other users from using a table at the same
time by using the SQL LOCK TABLE statement. Using COMMIT(*RR) will
also prevent other users from using a table during a unit of work.

In order to improve performance, DB2 UDB for iSeries will frequently
leave the open data path (ODP) open. This performance feature also
leaves a lock on tables referenced by the ODP, but does not leave any
locks on rows. A lock left on a table may prevent another job from
performing an operation on that table. In most cases, however, DB2 UDB
for iSeries will detect that other jobs are holding locks and events
will be signalled to those jobs. The event causes DB2 UDB for iSeries
to close any ODPs (and release the table locks) that are associated
with that table and are currently only open for performance reasons.
Note that the lock wait time out must be large enough for the events
to be signalled and the other jobs to close the ODPs or an error will
be returned."

Check the WAITFILE parameter of the file in question.
Check the DFTWAIT parameter of the job doing the that needs the file.

As long as WAITFILE(*IMMED) and DFTWAIT(0) are _NOT_ specified, the
system should automatically close the ODP when needed thus allowing
the job that needs the file, ie. the one doing the CHGPF, to
complete.

You can also try using ALCOBJ WAIT(30) CONFILCT(*RQSRLS) before
attempting the CHGPF.

If the locks aren't released, it's an indication that the application
hasn't closed a cursor.

Your other option would be to break up the program so that you could
take advantage of the ACTGRP and/or CLOSQLCSR parameters.


HTH,
Charles Wilt



On Tue, Jul 7, 2009 at 5:19 PM, Rick DuVall<R_C_DUVALL@xxxxxxxxxx> wrote:

Hello Charles,

     Actually, I think the point is that I don't like unneeded locks
     on files. I don't have any 'Problem' with any running process.

     I guess I didn't explain myself very well. It's hard to know
     what to include. The only problem that I have is when I need to
     do some out of the ordinary processing - such as performing a
     CHGPF or whatever, that requires an exclusive lock. This doesn't
     happen often, but when it does I have to end these processes. I
     didn't have to in the past and I was looking for a way to make
     the occasional use of SQL work in such a way that this would
     continue to be the case. Things can certainly go on this way -
     perhaps I am asking too much?

     I have always written such programs to close files when not
     using them. What brought it up today was that I wanted to do a
     CHGPF to add some edit words for output, to the file in
     question. I would have to wait till the end of the day anyway,
     but I will also have to end and restart a number of these nep
     jobs. Didn't have to do that in the past...

     Any way thanks for your reply.

--
Best regards,
 Rick

Systems Manager
Dealer's Auto Auction of Okc
1028 S. Portland
Oklahoma City, OK 73108
(405) 947-2886
mailto:rick@xxxxxxxxxx
http://www.nothingisreal.com/dfki/no-word


Tuesday, July 7, 2009, 3:57:14 PM, you wrote:

CW> You're missing the point that the system is working as designed....

CW> The problem is that you've got one program designed to work in a
CW> high-performance multi-user environment and another that expects
CW> basically to be the only process running and thus wants an exclusive
CW> lock on it's resources.

CW> Assuming the application is closing the cursors when it is done, then
CW> the file locks are a result of the system "pseduo closing" the
CW> cursors.

CW> Let me guess, you've got another process trying to do a CLRPFM which
CW> is failing do to the lock on the file...
CW> Easiest fix, quit using CLRPFM and instead use and SQL DELETE.

CW> Otherwise, http://archive.midrange.com/midrange-l/200902/msg00534.html

CW> HTH,
CW> Charles


CW> On Tue, Jul 7, 2009 at 4:23 PM, Rick
CW> DuVall<R_C_DUVALL@xxxxxxxxxx> wrote:

Hi everybody,

  I have a number of NEPs that used to

  1. Open files
  2. perform all processing
  3. Close files
  4. Sleep for x minutes
  5. repeat.

  Since we have begun using sql, these programs maintain file locks, on those
  files opened by SQL, while sleeping.

  Since the program is not run by a CL wrapper, there appears to be no
  clear way to relinquish the locks during the sleep period.  The only
  SQL option that appears to apply is the CloSqlCsr and it only supports
  *ENDMOD or *ENDACTGRP neither of which ever occur.  Am I missing something?

  Thanks!


--
Best regards,
 Rick

Systems Manager
Dealer's Auto Auction of Okc
1028 S. Portland
Oklahoma City, OK 73108
(405) 947-2886
mailto:rick@xxxxxxxxxx
http://www.nothingisreal.com/dfki/no-word

--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.