Re: QZDASOINIT file lock prevents CLRPFM after v5r2 --> v5r4 upgrade

Thanks for the reply Elvis.

The Web Method process is using connection pooling, so I don't believe
the connection itself being left open is usual.

However, my understanding is that the statement/results set should be
being closed. I'm told that is happening, but we're double checking
it. I assume that ends up being a pseudo.

I agree with you about the use of SQL DELETE, but it's not my team
<grin>. In addition, the owning team in particular is concerned about
root cause, so they can understand implications for the future.

Charles

On Mon, Feb 9, 2009 at 2:45 PM, Elvis Budimlic
<ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

Pseudo or not, 'O' in ODP stands for Open, so lock is still held on the
file. In my experience, CLRPFM fails in that case. I have not seen the
behavior you describe (CLRPFM request fully closing ODPs of another job and
releasing the locks).
The question I have is why isn't the Webmethod process Closing the JDBC
connection it used. If it did, the database host server prestart job should
be recycled and go to PSRW state. All of its resources would be released
(including ODPs). That's a good avenue to pursue, as all client connections
should release server resources when they no longer need them.
In short term though, I think you're better off switching to "DELETE FROM
myFile" SQL command. If there are locks, it'll still work, perhaps slower
than CLRPFM. If there are no locks, it is most likely that the 'fast-path'
will be used. I believe fast-path DELETE was introduced in V5R3. In essence,
the OS checks if it can perform a CLRPFM and does so when there are no
restrictions that prevent it from doing so.
I was also going to suggest ALCOBJ CONFLICT(*RQSRLS), but I've had mixed
success with that approach.

Hth, Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: QZDASOINIT file lock prevents CLRPFM after v5r2 --> v5r4 upgrade

All,

I've got a set of files that get written to by a Web Methods job via JDBC.

A couple of hours later a batch job on the system processes the files
and uses CLRPFM to clear them.

Even since the machine was upgraded from v5r2 to v5r4, the process has
stared failing becasue the CLRPFM can't get an exclusive lock to the
file and the QZDASOINIT job still has a lock.

We're going to be double-checking that the Web Method job is closing
everything it should be. And we know that we could use SQL instead of
CLRPFM clear the file without the lock issue.

I know the system will sometime "pseudo-close" open data paths. But
my understanding is that if an ODP is "pseudo-closed", any request for
an incompatible lock by another process, ie. the CLRPFM, would cause
the ODP to be fully closed and locks released. Thus, the CLRPFM
shouldn't fail from a "pseduo-close".

Is my understanding correct?

Irregardless, the question being asked is what changed, as we started
having a problem the day after the upgrade and the Web Method process
has not been changed. So I'm wondering if anybody knows or has any
thoughts about
--When the "pseduo-close" functionality was added.
--System values the might control the use of "pseduo-close"
--changes between v5r2 and v5r4 that might be causing this
--things to check, like MAXUSE() on the pre-start job entry that could
have been reset by the upgrade

Others?

Links to IBM documentation gladly accepted.

Thanks!
Charles Wilt


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.