Backup

[Stanley Sinclair]

I just brought a new small server online. It has two disks in RAID1
(mirrored) for the operating system and logs and DB2 DBMS. DB2 data
on a separate RAID 5EE array on four disks. (IBM ServeRAID 6i
controller.)

I don't expect more than 200 GB total data ever in the DB2 database
(starting with 120 MB), and 150 GB in other stuff.

Want to backup the database separately, and then everything else. Now
that DB2 has a backup utility that uses compression, should I use it?
Also have ArcServe on the server.

Have a good tape system on its own SCSI card. Win 2003 server.

Suggestions?

[John]

"Stanley Sinclair" <st*************@bellsouth.net> wrote in message
news:6f**************************@posting.google.c om...

I just brought a new small server online. It has two disks in RAID1
(mirrored) for the operating system and logs and DB2 DBMS. DB2 data
on a separate RAID 5EE array on four disks. (IBM ServeRAID 6i
controller.)

I don't expect more than 200 GB total data ever in the DB2 database
(starting with 120 MB), and 150 GB in other stuff.

Want to backup the database separately, and then everything else. Now
that DB2 has a backup utility that uses compression, should I use it?
Also have ArcServe on the server.

Have a good tape system on its own SCSI card. Win 2003 server.

Suggestions?

If you have version 8, fixpack 4 or above, there is compression option built
into the DB2 backup. I can't think of any reason not to use it, unless you
might need to restore to a database server running at a lower release level
without compression.

Backup and recovery can be a bit complex depending on whether you use
circular logging (default) or archive logging (which allows roll forward
recovery and/or on-line backups). With on-line backups or roll forward
recovery, you must have the logs available (along with the backup image) to
recover. If you are using circular logging and off-line backups, it should
be relatively easy.

Getting the backup to go directly to tape is a little tricky (especially
initializing the tapes), but you should be able to figure it out. If you
could backup to disk and then copy the file to tape, that might work better
and be a lot faster if you have the disk space.

Some high end backup solutions like Tivoli Storage Manager have integrated
DB2 backup support, but I doubt that ArcServe does. However, you can backup
to disk with DB2, and then backup the DB2 backup file to tape with ArcServe.

[Jean-David Beyer]

Stanley Sinclair wrote:

I just brought a new small server online. It has two disks in RAID1
(mirrored) for the operating system and logs and DB2 DBMS. DB2 data
on a separate RAID 5EE array on four disks. (IBM ServeRAID 6i
controller.)

I don't expect more than 200 GB total data ever in the DB2 database
(starting with 120 MB), and 150 GB in other stuff.

Want to backup the database separately, and then everything else. Now
that DB2 has a backup utility that uses compression, should I use it?
Also have ArcServe on the server.

Have a good tape system on its own SCSI card. Win 2003 server.

Suggestions?

Some tape drives already have hardware compression. E.g., my Exabyte VXA-1
and VXA-2 drives. It seems to me that if you have a drive with good
hardware compression, there would be little point in using software
compression as well. It seems to me that it would just gobble up processor
power for something the dedicated compression processing in the tape drive
might do better.

I never looked at DB2 data on disk to see how well they compress. I assume
someone must have done this by now. Exabyte seem to think that most data
compress to 1/2 original size. This is sure variable. Some manufacturers
seem to think most data compress to 1/3 original size. But stuff like JPEG
do not compress all that much and other stuff compresses more.

An Exabyte VXA-2 tape can hold 80 GByte uncompress and 160 GByte if you
get 2:1 compression.

--
.~. Jean-David Beyer Registered Linux User 85642.
/V\ Registered Machine 241939.
/( )\ Shrewsbury, New Jersey http://counter.li.org
^^-^^ 23:30:00 up 5 days, 8:29, 5 users, load average: 11.97, 11.88, 10.82

[David Mooney]

Jean-David Beyer <jd*****@exit109.com> wrote:

Stanley Sinclair wrote:

Want to backup the database separately, and then everything else. Now
that DB2 has a backup utility that uses compression, should I use it?
Also have ArcServe on the server.

Some tape drives already have hardware compression. E.g., my Exabyte VXA-1
and VXA-2 drives. It seems to me that if you have a drive with good
hardware compression, there would be little point in using software
compression as well. It seems to me that it would just gobble up processor
power for something the dedicated compression processing in the tape drive
might do better.
I'd endorse that point of view. Compression is very CPU-intensive, so
if you can offload it to the hardware in the tape drive, that would be
beneficial.
I never looked at DB2 data on disk to see how well they compress. I assume
someone must have done this by now. Exabyte seem to think that most data
compress to 1/2 original size. This is sure variable. Some manufacturers
seem to think most data compress to 1/3 original size. But stuff like JPEG
do not compress all that much and other stuff compresses more.

Exactly. The expected level of compression will depend heavily on
what's in the database. If you've got a lot of LOBs that hold JPEGs,
you'll burn a lot of CPU trying to compress the data, only to end up
storing the original uncompressed data. If you've got a lot of
char(250) columns holding plain text, you might get compression of 10:1.

The default compression algorithm shipped with DB2 is idential to good
old boring "compress", which has shipped with versions of Unix since the
dawn of time. If you run "compress" over the containers in your
database you'll get a ballpark idea of how compressible your data is.
(Of course, you won't want to actually compress the containers,
especially if the database is in use. Send the compressed data to
stdout and pipe it through wc, or somesuch.)

dave

[Philip Nelson]

Jean-David Beyer wrote:

Stanley Sinclair wrote:

I just brought a new small server online. It has two disks in RAID1
(mirrored) for the operating system and logs and DB2 DBMS. DB2 data
on a separate RAID 5EE array on four disks. (IBM ServeRAID 6i
controller.)

I don't expect more than 200 GB total data ever in the DB2 database
(starting with 120 MB), and 150 GB in other stuff.

Want to backup the database separately, and then everything else. Now
that DB2 has a backup utility that uses compression, should I use it?
Also have ArcServe on the server.

Have a good tape system on its own SCSI card. Win 2003 server.

Suggestions?

Some tape drives already have hardware compression. E.g., my Exabyte VXA-1
and VXA-2 drives. It seems to me that if you have a drive with good
hardware compression, there would be little point in using software
compression as well. It seems to me that it would just gobble up processor
power for something the dedicated compression processing in the tape drive
might do better.

I never looked at DB2 data on disk to see how well they compress. I assume
someone must have done this by now. Exabyte seem to think that most data
compress to 1/2 original size. This is sure variable. Some manufacturers
seem to think most data compress to 1/3 original size. But stuff like JPEG
do not compress all that much and other stuff compresses more.

An Exabyte VXA-2 tape can hold 80 GByte uncompress and 160 GByte if you
get 2:1 compression.

Not only does DB2 compression use more CPU cycles, we have found that it
needs significantly more memory. We had to increase the utility heap size
to approximately 4x its previous value to prevent "not enough memory"
errors. If you can afford the memory and the cycles I'd still recommend
using it however.

Phil

[Blair Adamache]

Are you on a 32-bit or 64-bit DB2 instance?

Philip Nelson wrote:

Jean-David Beyer wrote:

Stanley Sinclair wrote:

I just brought a new small server online. It has two disks in RAID1
(mirrored) for the operating system and logs and DB2 DBMS. DB2 data
on a separate RAID 5EE array on four disks. (IBM ServeRAID 6i
controller.)

I don't expect more than 200 GB total data ever in the DB2 database
(starting with 120 MB), and 150 GB in other stuff.

Want to backup the database separately, and then everything else. Now
that DB2 has a backup utility that uses compression, should I use it?
Also have ArcServe on the server.

Have a good tape system on its own SCSI card. Win 2003 server.

Suggestions?

Some tape drives already have hardware compression. E.g., my Exabyte VXA-1
and VXA-2 drives. It seems to me that if you have a drive with good
hardware compression, there would be little point in using software
compression as well. It seems to me that it would just gobble up processor
power for something the dedicated compression processing in the tape drive
might do better.

I never looked at DB2 data on disk to see how well they compress. I assume
someone must have done this by now. Exabyte seem to think that most data
compress to 1/2 original size. This is sure variable. Some manufacturers
seem to think most data compress to 1/3 original size. But stuff like JPEG
do not compress all that much and other stuff compresses more.

An Exabyte VXA-2 tape can hold 80 GByte uncompress and 160 GByte if you
get 2:1 compression.

Not only does DB2 compression use more CPU cycles, we have found that it
needs significantly more memory. We had to increase the utility heap size
to approximately 4x its previous value to prevent "not enough memory"
errors. If you can afford the memory and the cycles I'd still recommend
using it however.

Phil

[Jean-David Beyer]

Philip Nelson wrote:

Jean-David Beyer wrote:

[snip]

Some tape drives already have hardware compression. E.g., my Exabyte VXA-1
and VXA-2 drives. It seems to me that if you have a drive with good
hardware compression, there would be little point in using software
compression as well. It seems to me that it would just gobble up processor
power for something the dedicated compression processing in the tape drive
might do better.

I never looked at DB2 data on disk to see how well they compress. I assume
someone must have done this by now. Exabyte seem to think that most data
compress to 1/2 original size. This is sure variable. Some manufacturers
seem to think most data compress to 1/3 original size. But stuff like JPEG
do not compress all that much and other stuff compresses more.

An Exabyte VXA-2 tape can hold 80 GByte uncompress and 160 GByte if you
get 2:1 compression.

Not only does DB2 compression use more CPU cycles, we have found that it
needs significantly more memory. We had to increase the utility heap size
to approximately 4x its previous value to prevent "not enough memory"
errors. If you can afford the memory and the cycles I'd still recommend
using it however.

Do you recommend software compression over hardware compression?
Because my guess is that a tape drive with hardware compression costs
around $1000 (e.g., for VXA-2 from Exabyte), where enough memory, and
maybe an extra processor, probably cost more than that. And from the $1000
you should subtract the cost of a non-compressing tape drive of similar
capability.

--
.~. Jean-David Beyer Registered Linux User 85642.
/V\ Registered Machine 241939.
/( )\ Shrewsbury, New Jersey http://counter.li.org
^^-^^ 15:00:00 up 1 day, 25 min, 4 users, load average: 2.17, 2.17, 2.10

[Stanley Sinclair]

Gee, Blair, I'm no longer sure who's responding to whom in this
thread.

If anyone's interested in my setup, I'm on 32-bit Windows 2003 Server
with 6 HDDs in RAID5+0 and tape drive on independent SCSI card.
(Changed from previous post.) Also have ArcServe. To refine original
question:

-- Should I use DB2 backup utility (with compression) directly to tape
and use ArcServe for everything else?
-- Should I use DB2 backup to the RAID 50 array and then use ArcServe
to copy files to tape?
-- Should I just use ArcServe alone to backup whatever?
-- What about interim backup of just logs during the day using
ArcServe? Using DB2 backup?
-- Other thoughts, including CPU time as mentioned by others?

I'm thinking about the ease of restore of DB2 should it be necessary.

Stan
________

Blair Adamache <ba*******@2muchspam.yahoo.com> wrote in message news:<ca**********@hanover.torolab.ibm.com>...

Are you on a 32-bit or 64-bit DB2 instance?

Philip Nelson wrote:

Jean-David Beyer wrote:

Stanley Sinclair wrote:

I just brought a new small server online. It has two disks in RAID1
(mirrored) for the operating system and logs and DB2 DBMS. DB2 data
on a separate RAID 5EE array on four disks. (IBM ServeRAID 6i
controller.)

I don't expect more than 200 GB total data ever in the DB2 database
(starting with 120 MB), and 150 GB in other stuff.

Want to backup the database separately, and then everything else. Now
that DB2 has a backup utility that uses compression, should I use it?
Also have ArcServe on the server.

Have a good tape system on its own SCSI card. Win 2003 server.

Suggestions?

Some tape drives already have hardware compression. E.g., my Exabyte VXA-1
and VXA-2 drives. It seems to me that if you have a drive with good
hardware compression, there would be little point in using software
compression as well. It seems to me that it would just gobble up processor
power for something the dedicated compression processing in the tape drive
might do better.

I never looked at DB2 data on disk to see how well they compress. I assume
someone must have done this by now. Exabyte seem to think that most data
compress to 1/2 original size. This is sure variable. Some manufacturers
seem to think most data compress to 1/3 original size. But stuff like JPEG
do not compress all that much and other stuff compresses more.

An Exabyte VXA-2 tape can hold 80 GByte uncompress and 160 GByte if you
get 2:1 compression.

Not only does DB2 compression use more CPU cycles, we have found that it
needs significantly more memory. We had to increase the utility heap size
to approximately 4x its previous value to prevent "not enough memory"
errors. If you can afford the memory and the cycles I'd still recommend
using it however.

Phil

[Mark A]

"Stanley Sinclair" <st*************@bellsouth.net> wrote in message
news:6f**************************@posting.google.c om...

Gee, Blair, I'm no longer sure who's responding to whom in this
thread.

If anyone's interested in my setup, I'm on 32-bit Windows 2003 Server
with 6 HDDs in RAID5+0 and tape drive on independent SCSI card.
(Changed from previous post.) Also have ArcServe. To refine original
question:

-- Should I use DB2 backup utility (with compression) directly to tape
and use ArcServe for everything else?
-- Should I use DB2 backup to the RAID 50 array and then use ArcServe
to copy files to tape?
-- Should I just use ArcServe alone to backup whatever?
-- What about interim backup of just logs during the day using
ArcServe? Using DB2 backup?
-- Other thoughts, including CPU time as mentioned by others?

I'm thinking about the ease of restore of DB2 should it be necessary.

Stan
________

You can easily determine the difference in elapsed time when using DB2
compression. Just run a test with and without. Some systems are CPU bound
and others are not, so you should test this yourself.

If you backup directly to tape, it will take a long time and will be tape
I/O bound. DB2 compression will likely not be a factor in this situation.

Backing up DB2 directly to tape will not use ArcServe. You must have DB2
backups and not just OS file backups. I would test both a backup and restore
to/from tape because it can be a little tricky. Try it out on the sample
database (including doing a restore).

Backing up to disk and then copying to tape is fast (for the backup) and
relatively safe if you have enough disk space. You must have enough disk
space to copy the backup to disk before a restore. You would definitely use
DB2 compression in this instance.

Whether you backup the logs depends on whether you have circular logging or
archive logging. With circular logging, there is no need to backup the logs.
With archive logging, when you restore an online backup or roll forward to
the current time (after restoring the backup) you MUST have the logs.

[Jean-David Beyer]

Stanley Sinclair wrote:

Gee, Blair, I'm no longer sure who's responding to whom in this
thread.

I agree. When some people obey Microsoft rules and top post, when most of
the rest obey Netiquette and bottom post, it really mixes things up.

--
.~. Jean-David Beyer Registered Linux User 85642.
/V\ Registered Machine 241939.
/( )\ Shrewsbury, New Jersey http://counter.li.org
^^-^^ 22:25:00 up 1 day, 7:50, 8 users, load average: 4.10, 4.11, 4.04