Lock waits during online backup

[michael.spoden]

Hi,

how can I fix lock-waits during an online backup? Is an online backup
in DB2 V8.2 not realy online? I'm using DB2 V8.2 Fixpak 15 on Linux.

The command to perform the backup is: db2 backup db BPEDB online to /
var/tmp
During the backup the application hangs on just one table
(variable_instance_b_t) which is a central table of the application
and which will be used a lot. But the table is not realy large, there
are about 50.000 rows in the table.
Here's an output of the locks hold by the backup session:

List Of Locks
Lock Name = 0x02006C00000000000000000074
Lock Attributes = 0x00000000
Release Flags = 0x40000000
Lock Count = 1
Hold Count = 0
Lock Object Name = 108
Object Type = Internal Table Alter Lock
Tablespace Name = USERSPACE1
Table Schema = DB2INST1
Table Name = VARIABLE_INSTANCE_B_T
Mode = S

Lock Name = 0x02006C0000000000000000006F
Lock Attributes = 0x00000000
Release Flags = 0x40000000
Lock Count = 1
Hold Count = 0
Lock Object Name = 2
Object Type = Internal Online Backup Lock
Tablespace Name = USERSPACE1
Table Schema = DB2INST1
Table Name = VARIABLE_INSTANCE_B_T
Mode = X

Lock Name = 0x02006C00000000000000000054
Lock Attributes = 0x00000000
Release Flags = 0x40000000
Lock Count = 1
Hold Count = 0
Lock Object Name = 108
Object Type = Table
Tablespace Name = USERSPACE1
Table Schema = DB2INST1
Table Name = VARIABLE_INSTANCE_B_T
Mode = S

+ about 8 internal locks
Do you have any tipps to fix this issue?

Thanks

Michael

[Mark A]

<mi************@web.dewrote in message
news:cd**********************************@p25g2000 hsf.googlegroups.com...

Hi,

how can I fix lock-waits during an online backup? Is an online backup
in DB2 V8.2 not realy online? I'm using DB2 V8.2 Fixpak 15 on Linux.

An online backup should allow access to the database by applicaitons for
select, insert, update, delete.

The command to perform the backup is: db2 backup db BPEDB online to /
var/tmp
During the backup the application hangs on just one table
(variable_instance_b_t) which is a central table of the application
and which will be used a lot. But the table is not realy large, there
are about 50.000 rows in the table.
Here's an output of the locks hold by the backup session:

List Of Locks
Lock Name = 0x02006C00000000000000000074
Lock Attributes = 0x00000000
Release Flags = 0x40000000
Lock Count = 1
Hold Count = 0
Lock Object Name = 108
Object Type = Internal Table Alter Lock
Tablespace Name = USERSPACE1
Table Schema = DB2INST1
Table Name = VARIABLE_INSTANCE_B_T
Mode = S

Lock Name = 0x02006C0000000000000000006F
Lock Attributes = 0x00000000
Release Flags = 0x40000000
Lock Count = 1
Hold Count = 0
Lock Object Name = 2
Object Type = Internal Online Backup Lock
Tablespace Name = USERSPACE1
Table Schema = DB2INST1
Table Name = VARIABLE_INSTANCE_B_T
Mode = X

Lock Name = 0x02006C00000000000000000054
Lock Attributes = 0x00000000
Release Flags = 0x40000000
Lock Count = 1
Hold Count = 0
Lock Object Name = 108
Object Type = Table
Tablespace Name = USERSPACE1
Table Schema = DB2INST1
Table Name = VARIABLE_INSTANCE_B_T
Mode = S

+ about 8 internal locks
Do you have any tipps to fix this issue?

Thanks

Michael

I would restart the instance and try again. If that does not work, check the
db2diag.log for more information. Call IBM support if that does not help
you.

[Jayesh Thakrar]

On Jun 11, 11:50*am, "Mark A" <[e-mail]:

<[e-mail] in message

news:cd**********************************@p25g2000 hsf.googlegroups.com...

Hi,

how can I fix lock-waits during an online backup? Is an online backup
in DB2 V8.2 not realy online? I'm using DB2 V8.2 Fixpak 15 on Linux.

An online backup should allow access to the database by applicaitons for
select, insert, update, delete.

The command to perform the backup is: db2 backup db BPEDB online to /
var/tmp

During the backup the application hangs on just one table
(variable_instance_b_t) which is a central table of the application
and which will be used a lot. But the table is not realy large, there
are about 50.000 rows in the table.

Here's an output of the locks hold by the backup session:

List Of Locks
Lock Name * * * * * * * * * = 0x02006C00000000000000000074
Lock Attributes * * * * * * = 0x00000000
Release Flags * * * * * * * = 0x40000000
Lock Count * * * * * * * * *= 1
Hold Count * * * * * * * * *= 0
Lock Object Name * * * * * *= 108
Object Type * * * * * * * * = Internal Table Alter Lock
Tablespace Name * * * * * * = USERSPACE1
Table Schema * * * * * * * *= DB2INST1
Table Name * * * * * * * * *= VARIABLE_INSTANCE_B_T
Mode * * * * * * * * * * * *= S

Lock Name * * * * * * * * * = 0x02006C0000000000000000006F
Lock Attributes * * * * * * = 0x00000000
Release Flags * * * * * * * = 0x40000000
Lock Count * * * * * * * * *= 1
Hold Count * * * * * * * * *= 0
Lock Object Name * * * * * *= 2
Object Type * * * * * * * * = Internal Online Backup Lock
Tablespace Name * * * * * * = USERSPACE1
Table Schema * * * * * * * *= DB2INST1
Table Name * * * * * * * * *= VARIABLE_INSTANCE_B_T
Mode * * * * * * * * * * * *= X

Lock Name * * * * * * * * * = 0x02006C00000000000000000054
Lock Attributes * * * * * * = 0x00000000
Release Flags * * * * * * * = 0x40000000
Lock Count * * * * * * * * *= 1
Hold Count * * * * * * * * *= 0
Lock Object Name * * * * * *= 108
Object Type * * * * * * * * = Table
Tablespace Name * * * * * * = USERSPACE1
Table Schema * * * * * * * *= DB2INST1
Table Name * * * * * * * * *= VARIABLE_INSTANCE_B_T
Mode * * * * * * * * * * * *= S

+ about 8 internal locks

Do you have any tipps to fix this issue?

Thanks

Michael

I would restart the instance and try again. If that does not work, check the
db2diag.log for more information. Call IBM support if that does not help
you.- Hide quoted text -

- Show quoted text -

Ideally there would/should not be any locks - however DB2 does hold
internal locks for a few things - like some of the config files and
other internal objects.
I am not sure of the actual objects, their durations, etc. I would
suggest reaching out to DB2 Support - and they can provide the
details.

All the same, just curious to know if these locks are causing you any
problems?

-- Jayesh

[michael.spoden]

Thanks Mark, thanks Jayesh.

I've found the following in the manuals about the online option in the
backup command:
'During an online backup, DB2 obtains IN (Intent None) locks on all
tables existing in SMS table spaces as they are processed and S
(Share) locks on LOB data in SMS table spaces'

We're using SMS tablespaces and the table which is lockes has a lob
column. Do you think moving this table to a DMS tablespace can fix the
problem.

By the way, there no special entries in the db2diag.log file, just the
starting and finishing of the backup.

Regards

Michael

[Ian]

mi************@web.de wrote:

Thanks Mark, thanks Jayesh.

I've found the following in the manuals about the online option in the
backup command:
'During an online backup, DB2 obtains IN (Intent None) locks on all
tables existing in SMS table spaces as they are processed and S
(Share) locks on LOB data in SMS table spaces'

We're using SMS tablespaces and the table which is lockes has a lob
column. Do you think moving this table to a DMS tablespace can fix the
problem.

By the way, there no special entries in the db2diag.log file, just the
starting and finishing of the backup.

What are you trying to do in your application that is running in to the
locks?

The point of putting locks on objects in the tablespace is to prevent
changes (DDL) while the backup is active. DML is fine, of course.

[Mark A]

<mi************@web.dewrote in message
news:2c**********************************@a70g2000 hsh.googlegroups.com...

Thanks Mark, thanks Jayesh.

I've found the following in the manuals about the online option in the
backup command:
'During an online backup, DB2 obtains IN (Intent None) locks on all
tables existing in SMS table spaces as they are processed and S
(Share) locks on LOB data in SMS table spaces'

We're using SMS tablespaces and the table which is lockes has a lob
column. Do you think moving this table to a DMS tablespace can fix the
problem.

It is worth a try.

[michael.spoden]

Hi,

first tests on our test environment are successful. I've moved the
specific table from a SMS to a DMS tablespace. There are no more
shared locks on this table and no more lock-waits on the database.

Regards

Michael