|
Q and A
Asked and Answered
I have a very volatile table (high insert and delete activity) that applications were
frequently getting contention (unavailable resource errors) on.
To help solve this problem, I:
1) Changed the
tablespace to be
LOCKSIZE ROW, LOCKMAX 0
(to avoid escalation)
2) Changed or verified that plans and packages accessing the table were bound with
CS
and
RELEASE(DEALLOCATE)
3) Made sure batch programs had commit logic.
This helped quite a bit except for one packaged application that issues dynamic (via JDBC) SQL
selects and deletes against the table.
The package is up 24X7, and when it executes at the same time as a batch program
accessing this same
table a timeout
(-904)
often occurs on the packaged application.
I plan on double-checking the commit frequency,
ISOLATION (CS)
and
CURRENTDATA (NO)
settings of the
batch program and the commit frequency of the packaged app. However, a SQL trace of the packaged
application indicates that it's still trying to acquire a tablespace lock. This surprises me given
the changes I made
(LOCKSIZE ROW, LOCKMAX 0)
.
Does this seem likely or even possible?
Robert Catterall responds:
Do you know how the JDBC statements are getting to DB2? Is it via DB2 Connect or some other gateway?
(I'm assuming that the Java programs themselves are running on a server thatıs remote to the server
on which DB2 is running)? I'm thinking that there's a package associated with that gateway product.
If so, try rebinding that package, making sure that
ISOLATION(CS)
is specified. You
can go from
LOCKSIZE(PAGE)
to
LOCKSIZE(ROW)
; however, if you do, a program might not respect that change until
it has been rebound.
Also, note that any program accessing a table (except, perhaps, a read-only program bound with
ISOLATION(UR)
) is going to request a tablespace lock. Most of the time, that tablespace lock is
going to be non-exclusive (IS or IX). Is the package youıre referring to trying to get an exclusive
tablespace lock?
Keep in mind
that when
RELEASE(DEALLOCATE)
is specified, a tablespace lock acquired by a program
will be held until the thread associated with the program is deallocated. In the case of a CICS
protected thread that gets a lot of reuse, thread deallocation may not occur for hours, or even
days, after thread creation. A long-duration tablespace lock will normally not be a problem, as long
as it's a non-exclusive tablespace lock.
Are you sure that the tablespace targeted by the JDBC statements from the
package is the unavailable
resource causing the problem? What's the return code associated with the
-904
?
You might want to try
RELEASE(COMMIT)
for the batch program. That might result in slightly higher
overhead versus
RELEASE(DEALLOCATE)
, but it might help your concurrency.
See a
complete archive of reader/author Q&As
.
|
