DB2 8.2 on AIX
Online database backup fails.(SQLCODE -911, Reason Code 68) if an INPLACE REORG is in progress on a table in the database.
Curious to know what the conflicting lock is here, that causes a timeout? Any idea?

If it makes a difference, the table is in SMS tablespace.

Thanks

I have seen the same thing on V8.2 for linux. Maybe if you examined which locks the reorg is holding, you might be able to figure it out.

Let us know.

Andy

Yep, I did try lock snapshots before posting here. I could not see anything useful.
I'll post some snapshots here tomorrow

Thanks

Here's the diaglog. I have also attached the lock snapshot, taken about 5-6 seconds before the timeout.

The locktimeout value on this database is 15 secs.

I'm not sure which ones are actually causing the timeout. any clues? thanks.


2007-11-30-14.28.51.120707+060 I267738C429 LEVEL: Error
PID : 1257700 TID : 1 PROC : db2bm.1417402.3 0
INSTANCE: pqrs_t NODE : 000
APPHDL : 0-221
FUNCTION: DB2 UDB, database utilities, SqlubTableLock::request, probe:81
DATA #1 : String, 45 bytes
Error getting lock.
PoolId: 2, ObjectId: 16
DATA #2 : Hexdump, 4 bytes
0x2FF1EEE0 : 8010 0044 ...D


2007-11-30-14.28.51.121746+060 I268168C370 LEVEL: Severe
PID : 1257700 TID : 1 PROC : db2bm.1417402.3 0
INSTANCE: pqrs_t NODE : 000
APPHDL : 0-221
FUNCTION: DB2 UDB, database utilities, sqlubreadsms, probe:30
MESSAGE : DiagData
DATA #1 : Hexdump, 8 bytes
0x2005A400 : 8010 0044 0000 0044 ...D...D


2007-11-30-14.28.51.121889+060 I268539C350 LEVEL: Error
PID : 1257700 TID : 1 PROC : db2bm.1417402.3 0
INSTANCE: pqrs_t NODE : 000
APPHDL : 0-221
FUNCTION: DB2 UDB, database utilities, sqlubreadsms, probe:30
DATA #1 : Hexdump, 8 bytes
0x2005A400 : 8010 0044 0000 0044 ...D...D


2007-11-30-14.28.52.541576+060 I268890C358 LEVEL: Severe
PID : 1417402 TID : 1 PROC : db2agent (pqrs_T) 0
INSTANCE: pqrs_t NODE : 000 DB : pqrs_T
APPHDL : 0-221 APPID: *LOCAL.pqrs_t.071130132818
FUNCTION: DB2 UDB, database utilities, sqlubcka, probe:140
MESSAGE : Backup Terminated.

I am not sure what is causing the problem either. It looks like the reorg has a bunch of Intent-None (IN) row locks. The only thing that this would prevent is a super-exclusive (Z) lock, and I do not see how the online backup would try that.

The only thing I can suggest is to monitor the lock chain that occurs during the lock wait. This should give you a better insight as to what is causing the problem. Other than that, you can open a PMR with IBM to get an answer.

Andy

Thanks Andy. The issue here is that I do not see the backup in lock-wait state at all :-)
I guess I'll ask the client staff to raise a PMR
Thanks

The problem of addressing a lockwait timeout issue is catching the lockwait. You have to constantly keep snapshot monitoring the lock situation with an interval between snapshots less than the lockwait timeout value. So, if as in your condition, the lockwait timeout value is 15 seconds, you have to get the snapshots every 10 seconds or so. This can be very tedious and rather boring if done manually. If you automate it, you will have tons of data to sift through.

Andy