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
After all that the locks are still there after two sql queries. I've tried
changing OPEN_CURSOR_THRESHOLD to 1 and OPEN_CURSOR_CLOSE_COUNT to 1, but
the locks are always there.
Then I tried to add a JDBC propery cursor hold to my JDBC url, setting it to
false.
Nothing worked, am I missing something?
date: Sun, 21 Mar 2010 21:49:16 -0000
from: "Neill Harper" <neill.harper@xxxxxxxx>
subject: RE: Locks are held... never released...
You are right Chuck, the Doc that Davor sent states.
"Some operating system commands and all SQL statements that require an
exclusive lock (DLTF, DROP TABLE, CLRPFM, and so on) will cause a pseudo
closed cursor to be hard closed so that the lock is released"
It kind of feels like to me, someone couldn't be bothered changing all of
the OS commands when pseudo closing was introduced :-)
-----Original Message-----
From: java400-l-bounces@xxxxxxxxxxxx [mailto:java400-l-bounces@xxxxxxxxxxxx]
On Behalf Of CRPence
Sent: 21 March 2010 19:30
To: java400-l@xxxxxxxxxxxx
Subject: Re: Locks are held... never released...
Neill Harper wrote:
That has helped bring clarity to the situation.
It does raise one question though....
When should you use ALCOBJ CONFLICT(*RQSRLS)?
CRPence wrote:
The same effect as ALCOBJ CONFLICT(*RQSRLS) is part of the
SQL ALTER TABLE request, at the very beginning, so issuing
the ALCOBJ explicitly would be redundant. Most every database
action that requires exclusive use of the TABLE will similarly
effect that request; albeit using a method within the OS database
[SQL] programs rather than via the CL command. There should be no
need to issue that CL request to effect removal of the locks held
to maintain "pseudo closed cursors".
<<SNIP>>
So anyhow... that CL request should not be [inferred to be]
recommended, neither as a corrective nor as a preventive action.
There should be few [if any] operations by the SQL database
against a TABLE that would be impacted by an actual pseudo-closed
cursors; except for example, the impacts noted in the previous
paragraph.
I should have written "Most every database *SQL* action that
requires exclusive use of the TABLE will similarly effect" the
equivalent of CONFLICT(*RQSRLS), similar to my mention of "operation
by the SQL database" in the summary paragraph.
The CL command ALCOBJ would be used mostly for non-SQL operations
that will still conflict in some manner with the pseudo-closed held
locks, but which are operations deemed unworthy for the database to
request the release of those locks to perform that operation, since
the requests are outside of the SQL and thus best not unnecessarily
impact the SQL. Examples might be: RNMOBJ versus SQL RENAME,
ENDJRNPF vs no SQL equivalent, RSTOBJ vs no SQL equivalent, CLRPFM
vs SQL DELETE with no or high cardinality WHERE clause, GRTOBJAUT vs
SQL GRANT etc.. I am unsure if some less critical SQL DDL requests
such as COMMENT ON or LABEL ON might be impacted\prevented by
pseudo-closed held locks, but I suspect that even these do not
require the ALCOBJ. Being an SQL request [versus a non-SQL database
request which is typically CL-invoked], the SQL will attempt to
perform as if there were no such thing as the pseudo-closed held
locks, in order to work just like any other database; i.e. if after
multiple repeated SELECT & close cursor requested in a
thread\process on another database would still allow LABEL ON in any
other thread\process, then so too should the DB2 for i SQL [barring
limitations imposed upon effecting the closes in the other threads
or processes].
Regards, Chuck
--
This is the Java Programming on and around the iSeries / AS400 (JAVA400-L)
mailing list
To post a message email: JAVA400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/java400-l
or email: JAVA400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/java400-l.
midrange.com
