Re: Locks are held... never released...

Davor wrote:

DB2 has some query options in so called QAQQINI table.

I've found out that I can make my custom QAQQINI... here is the
post.
http://www.sqlthing.com/resources/UsingQAQQINI.htm
or here
http://www.planetjavainc.com/wow_docs/iSeries_SQL_Performance_Notes.pdf

And I made it in my library. And made the modifications:
UPDATE MyLibrary/QAQQINI
SET QQVAL='-1'
WHERE QQPARM='OPEN_CURSOR_THRESHOLD';

-1 means: A value of -1 will force SQL to not reuse ODPs (forces full close and full open), but this must be set at the start of
the session before the first open.


QAQQINI query options are here:
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/rzajq/qryoptf.htm

So after that I modified my jdbc connection url (added qaqqinilib
property):
jdbc:as400://machine;libraries=*libl;naming=system;qaqqinilib=MyLibrary


AS400 JDBC url properties are here:
http://javadoc.midrange.com/jtopen/com/ibm/as400/access/doc-files/JDBCProperties.html#system

What was done to ensure\verify that the property was even in effect, such that the query options library was established and the INI options were both read & established? Note that the CHGQRYA has restrictions; i.e. the special authority *JOBCTL is required to effect changing the query options library, and that would be true irrespective of interface [unless the properties interface adopts the authority to perform the CHGQRYA; hmmm, that sure would be a conundrum for design point].

The database monitor can confirm the job was established with that file [see later for specific values] using QQC102:
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/rzajq/t3014.htm

After all that, the locks are still there after two sql queries.

So was the ALCOBJ for *EXCL with CONFLICT(*RQSRLS) requested from some job to verify that the locks were for pseudo-closed cursors? Other locks, not those from the ODP to implement the pseudo-closed cursor, will *not* be affected by the INI nor the ALCOBJ CONFLICT(*RQSRLS). At least verify that the locks are from opens, by review of WRKJOB OPTION(*OPNF) for the job holding the locks.

I've tried changing OPEN_CURSOR_THRESHOLD to 1 and
OPEN_CURSOR_CLOSE_COUNT to 1, but the locks are always there.

I did not refresh my memory on the threshold setting, but I think the value 1 would be questionable. I believe that value would ask to be extremely aggressively in establishing pseudo-closed cursors, such that for every open versus only after one additional\repeated open of the same cursor, a pseudo-closed cursor should be effected.?

<<SNIP>>

Nothing worked, am I missing something?

Was the DB monitor run to review the values associated with those settings, QVP153 & QVP154, for either variation of the run?
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/rzajq/t1000.htm

Of possible additional interest are the following monitor information for ODP & LOCK.

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/rzajq/t3010.htm
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/rzajq/t3005.htm

Regards, Chuck