473,320 Members | 1,974 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,320 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 3652
"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: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.