Ian <ia*****@mobile audio.com> wrote in message news:<40******* ***@corp.newsgr oups.com>...
Unfortunately you can't disable lock escalation entirely, without
resorting to locking the entire table for each transaction (this
would negatively affect concurrency :-)
If you have increased locklist (from 64000 to 128000) and maxlocks
(from 20 to 60), and are still having a problem, are you sure that
the changes have gone into effect? Keep in mind that the database
may need to be deactivated/reactivated for any changes to take effect
(I don't think you mentioned if you're on V7.x or V8.1).
Are you _sure_ your app is actually committing after each insert
or update? What is the scope of the update statements -- if each
update is updating a HUGE (with a 500 Mb locklist, and 60% available
per application you could lock well over 8,000,000 rows), maybe
something in your app is not right.
You should check the db2diag.log for more information. It will contain
information about lock escalations (like how many row locks were held
when escalation was triggered) as well as deadlocks. This may give you
a clue about an error in the app doing the migration. Trapping the SQL
will also help. As will running the app single threaded and monitoring
closely to see what's going on.
Good questions. Wish I was certain of the answers. I'm using
LotusScript LSC:LC classes and connectors. Of course since DB2 and
Lotus come from the same shop, you'd think they'd play well
together...
I am processing single rows. LotusScript agent code gets a Lotus Notes
document, and processes it to insert/update one row into one table.
The code, however, uses the LSX:LC fieldlist and connection classes so
I don't know what they are REALLY doing under the hood. I do have a
primary key and the agent explicitly identifies those two columns as
keys. As for actually commiting, again, I'm not issuing a direct SQL
statement. I use the connection class's "Action" method and specify
commit and assume it does so. I guess that I should be able to monitor
this someway in DB2 but I've only figured out (so far) how to get
snapshots and I've yet to catch anyting "in the act." Is there a way
to watch what's happening in real-time? I do have to say however, that
I took a look at snapshot for database and found 0 lock escalation
events. What I can't figure out is whether that info is cumulative or
if it's also instantaneous, i.e. 0 because I didn't catch one then or
cumulative and I really haven't had any. If the latter then I'm really
at a loss. Within any one agent, tables are populated serially so that
I don't get foreign key conflicts. Inserting one row at a time into my
contact table, shouldn't create deadlocks. However, I only get the
problem when I run multiple agents. I dont' see how this can create
deadlocks however, because each agent works wholly within its own
database and the primary key includes the Notes database replica ID.
So, no agent should be waiting for a commitment from another agent
because no agent is processing the same Notes database as any other
agent. I'm beginning to think that I just wrote bad code :-<
Thanks.
Randy
