473,394 Members | 1,481 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,394 software developers and data experts.

Lock Escalation, LogRetain & Backup Question.

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
Nov 12 '05 #1
2 3658
"Trent" <pi****@yahoo.com> wrote in message
news:7a*************************@posting.google.co m...
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.

Nov 12 '05 #2
Thank you for your input, Mark.

TJ
Nov 12 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

12
by: xixi | last post by:
hi, we are using db2 udb v8.1 on 64 bit windows, this message is in the db2diag.log file ADM5502W The escalation of "38200" locks on table "xxx.xxx" to lock intent "X" was successful. ...
9
by: Jane | last post by:
Our db2diag.log is full of messages like this: 2004-05-31-17.15.10.383766 Instance:tminst1 Node:000 PID:394948(db2agent (TMDB1) 0) TID:1 Appid:GA140956.EF26.03A4B1202647 data management ...
0
by: Bruce Pullen | last post by:
DB2 v7.2 (FP7 - DB2 v7.1.0.68) on AIX 5.2.0.0. We're seeing unexpected single row (then commit) insert locking behaviour. We're seeing Applications that already hold row-level W locks in...
3
by: db2group88 | last post by:
we are using db2 udb v8.1 on windows, the configure parameter for locks is locklist 1000, maxlocks 60, but somehow i still have the error message ADM5502W The escalation of "1" locks on table...
7
by: situ | last post by:
Hi, we i get a snapshot for lock on db i'm getting LOCK_LIST_IN_USE =5560 and my LOCKLIST parameter = 50, is it ok for OLTP database or do i have to do any tuning here. thanks sridhar
9
by: kavallin | last post by:
I receives the following in the db2diag.log file many times / day : 2007-03-05-14.55.24.836553+060 E12415C457 LEVEL: Warning PID : 2785 TID : 1 PROC :...
3
by: stefan.albert | last post by:
Hi folks, we have a little discussion about lock escalation... What is better for performance: To have an escalation "early" (smaller locklist) or aviod the escalation with a big lock list? ...
0
by: cburnett | last post by:
Have a peculiar problem regarding lock escalation. Originally lock escalation was occurring because of an application changing most of the rows in a large table. After setting locksize to table to...
1
by: clilush | last post by:
I'm getting entries in the windows event log saying "DB2 is performing lock escalation" which of course is followed by another entrie saying "The escalation of 'xxxx' locks on table 'SYSIBM ...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.