"Trent" <pi****@yahoo.c om> wrote in message
news:7a******** *************** **@posting.goog le.com...
Hello, all.
I have the following production DB2 environment.
DB2 8.1.4 (fp4) WG edition with 2 production databases on Windows 2003
standard edition.
My first question is regard with locking. I found some escalations for
X lock on some tables. How do I work out an appropriate MAXLOCK &
LOCKTIMEOUT settings to optimize the lock escalation issue.
The other question has to do with LogRetain and Backup method. On this
environment, I only do offline backup (no user connection at backup
time).
Is it safe to turn the logretain option off for offline backup?
Thanks a lot in advance.
JJ
The term "escalation " can be misleading in DB2. For example, an escalation
can occur from an IX lock (intent exclusive) to X lock (exclusive). This is
not the same issue as escalation from row lock to table lock. You should
only be concerned with the later type of escalation.
DB2 will escalate from row to table locking under one of three conditions:
1. DB2 knows in advance it will have to lock all the rows in the table, such
as when it does a tablespace scan with RR isolation level.
2. The memory allocated for storing locks is used up. This is controlled by
the LOCKLIST database configuration parm, and the default is usually way too
low unless you have a read-only database. Most databases should have it set
it to a minimum of 4096 pages.
3. A single application uses more than x percent of the LOCKLIST memory for
storing locks. This is controlled by the MAXLOCKS database configuration
parameter. To discourage lock escalation, set it higher.
Note that if you have a read-only decision support system, you would want to
encourage lock escalation to table level because multiple share locks can
coexist, and it is more expensive for DB2 to lock at the row level rather
than the table level.
LOCKTIMEOUT does not usually affect escalations, just how long an
application will wait for a resource that is locked by another application
before returning a SQLCODE -911 with reason code 68. However, sometimes if
an application is holding one lock (or many locks) and is waiting on another
application to release an additional lock on another resource, this can
cause lock escalation or even deadlocks. Best to set LOCKTIMEOUT to between
30-60 seconds. The default is -1, which will wait indefinitely.
If you always do a point-in-time recovery to the last backup (if there are
no updates to the database since the last backup), then you don't need
logretain set on. If you want to restore a backup and then roll the logs
forward to the last committed transaction when you do a recovery (such as if
you had a disk failure on the tablespace), then you will need logretain on.
In either event, it is safer to do an offline backup (with or without
logretain) if you have a maintenance window that allows it.
