Question about external backups to filesystems

[RoB]

Hi all,

I'm coming from the Informix world and I have a customer using DB2
8.2.3 for Linux on Red Hat Enterprise ES.

The customer is performing filesystem backups of the containers etc
every night but they are not shutting down the database server while
doing this. I only assume that this most likelly would leave an
inconsistant backup image as there is nothing assuring that the
modified pages in the buffer pool get written to disk before the
filesystem backup starts. There is plenty of acticivty on the database
24x7.

Question: Apart from shutting down the instance before performing such
an external backup, is there a way in db2 to block all access and make
sure that all modified pages in memory gets written to disk? I've
tried the not so user-friendly online db2 documentation but without
any luck. With Informix Dynamic Server (IDS) this would be archieved
by issuing the commands "onmode -c BLOCK" and then "onmode -c UNBLOCK"
once the external backup has finished.

Any input is appreciated.

RoB

[Bernard Dhooghe]

On Mar 26, 10:31 am, "RoB" <pluma...@yahoo .comwrote:

On Mar 23, 9:38 pm, Ian <ianb...@mobile audio.comwrote:

RoB wrote:

Thanks AJ! That was exactly the sequence of commands what I was
after.

This customer doesn't use SAN splits but by applying these commands
around their filesystem backup it will be consistent, which is what
I've been trying to achieve.

You need to read the documentation to understand how this is used, and
the ins and outs (and consequences) of it.

Using SET WRITE SUSPEND will block DB2 from doing any writes to disk,
however it does not flush data from bufferpools to disk, or do anything
to cause the OS to flush applicable I/O buffers (at least that's my
understanding). The database is not consistent.

The intent of this (when used with split-mirrors) is to allow you to
mount the mirrors on another server and then use the BACKUP DATABASE
command to actually back up the database. This is a very common way to
handle large data warehouses (like 15Tb), if the entire database must be
backed up.

However, you have to realize that "unfreezing the database" using the
'db2inidb' command can have consequences for recovery.

Using SET WRITE SUSPEND with the customer's desired backup mechanism
might work, but I wouldn't depend on it. There's a reason people are
suggesting you use the supported backup mechanism. And you really
should direct your customer towards a workable solution.

In addition, your customer would need to keep the database in
write-suspend mode for the duration of the backup (i.e. to ensure that
all DB2 data backed up is consistent). If you're talking about 15Tb
of data, this file system level backup will take a LONG time, and
will likely have some effect on database users. i.e., if you can't
write to temporary tablespaces, even select queries can block.

Thanks for that Ian. The procedure made will basically be the same
described here only that it'll be done to a filesystem (the size of
their db is in the range of 60 GB and that will, as you correctly
pointed out, take quite a while to perform on a filesystem):

http://www.db2mag.com/story/showArti...leID=173500277

If using a good SAN solution (like EMC etc) the creation of the
mirrors can by the SAN can normally be performed while the database
server is online so you only need to issue the blocking mechanism at a
time when the SAN has finished with creating the mirrors. Any changes
done against the disks while the mirrors are being performed will be
tracked by the SAN and applied to the mirror image before finishing.

I have stressed the importance (and will continue doing so) of a db2
type backup to the customer but implementing it is their choice to
make.

So, is there actually a way to force a db2 instance to flush the dirty
buffers in the buffer pools to disk?

The flushing of the OS cache is probably handled differently on
different OSes but does anyone have any idea of how to force RedHat to
do this?
Also, while we're at it, does anyone know how to disable the OS
caching of filesystems on RedHat to prevent the double caching of
accessed pages from the containers (by both the OS and the db2
instance)?

Thanks,

RoB

1. As a system can crash any time, DB2 does have a capability to
recover from it. So there is no need to sync as DB2 is taking care.

2. In AIX -only- there is a way to disable mmap read, write usage, so
I would conclude there is no double caching on Linux when using SMS
(Remark: double caching can be benefical on AIX depending on the
environment)

3. if file system backup is prefered, if the file systems support a
software snapshot capability, a read-only snapshot could be taken :
1. suspend I/O DB2 2. start file systems snapshot 3. resume I/O on DB2
4. backup the snapped file systems 5. drop read-only snapped part of
file sytems

Bernard Dhooghe

[Ian]

RoB wrote:

Thanks for that Ian. The procedure made will basically be the same
described here only that it'll be done to a filesystem (the size of
their db is in the range of 60 GB and that will, as you correctly
pointed out, take quite a while to perform on a filesystem):

And they realize that the database may appear to "hang" during this
window?

Seriously, if your customer is really prepared to have a possible
(perhaps even likely) outage because I/O is suspended and 60Gb data
is getting backed up, why not just shut down DB2 to do the backup?

Or, better, explain that an online DB2 backup is far better (and
won't have the side effect of causing the database to appear as
though it has "locked up" because I/O is suspended.) You can do
the backup, dump it out to a file system. Or media manager like
NetBackup, NetWorker or TSM.

I just have a really hard time imagining a customer who would be so
stuck on a particular technical implementation that they would be
willing to sacrifice availability to achieve it. It just seems more
likely that they are not understanding something properly.

i.e., Thinking that 'set write suspend' is equivalent to putting a
database in read-only mode, effectively disabling insert/update/delete
queries. SET WRITE SUSPEND does *not* do this, it blocks ALL I/O,
including I/O to temporary tables that gets gets flushed.

http://www.db2mag.com/story/showArti...leID=173500277

If using a good SAN solution (like EMC etc) the creation of the
mirrors can by the SAN can normally be performed while the database
server is online so you only need to issue the blocking mechanism at a
time when the SAN has finished with creating the mirrors. Any changes
done against the disks while the mirrors are being performed will be
tracked by the SAN and applied to the mirror image before finishing.

I have stressed the importance (and will continue doing so) of a db2
type backup to the customer but implementing it is their choice to
make.

So, is there actually a way to force a db2 instance to flush the dirty
buffers in the buffer pools to disk?

The only way to force this to occur is to deactivate the database (i.e.
shut it down). Otherwise, you're at the mercy of the page cleaners.
Theoretically you could de-tune them such that they are constantly
cleaning pages as soon as any become dirty, but that would be a bad
idea.

The flushing of the OS cache is probably handled differently on
different OSes but does anyone have any idea of how to force RedHat to
do this?
Also, while we're at it, does anyone know how to disable the OS
caching of filesystems on RedHat to prevent the double caching of
accessed pages from the containers (by both the OS and the db2
instance)?

Well, you can tell DB2 to open files and disable any caching
(alter tablespace X no file system caching).

On AIX, you can mount the file system with the 'dio' option
(or the 'cio' option, which is better); I *think* the equivalent
for this is the 'sync' option on Linux. (mount -o sync /db2/data).
However, use care with this. IBM's recommendation is to use the
'alter tablespace ... no file system caching' option instead of
setting file system mount options.