db2 backup, StorageManager

[Eric Herber]

I've a question regarding db2 (V8.1) and database backups
going to a storage manager like TSM for example.

As I can see in the storage manager if I backup the complete
database over the TSM API (no tablespace backups) the backup
images is stored as a single backup object.
Later I will be able to restore the complete database from
this image and I will also be able to restore single tablespaces
from this backup image as long as I archived my logs thru the
userexit program.

However if I've a really big database (let's say 1 TB), I would guess
that db2 always has to request the whole backup image from TSM.
So If I only want to restore a single tablespace, TSM has to read
1 TB probably from tape and has to transfer this 1 TB thru the
network to the dataseserver in order for db2 to be able to extract
the single tablespace and restore it to the database ?

If this is the case, database backups for large databases seem not
be a passable solution.

SAN and ExternalBackup (split mirror) is unfortunately currently
not a solution at this customer site.
Doing incremental/delta backups seem not the help in this particular
situation as the problem of retrieving the whole full backup images
persists.

If I backup a complete Informix instance thru TSM, the individual
dbspaces (db2: tablespaces) are created as separate backup objects
so I'm able to restore a single dbspace from this instance backup
without TSM having to transfer the complete set.
This seems to be the better approach because restoring a single
dbspace (tablespace) is normally needed more frequently as having
to restore the whole database.

Would somebody recommend to do tablespace backups instead of
database backups and are these tablespace backups manageable
from an administration point of view ?
If have some respect about the increased management overhead
resulting from taking tablespace backups as this is also
a partitioned database environment.

Thank you for any input.

Best regards

Eric
--
IT-Consulting Herber
Mobile: [phone]
***********************************************
Download the IFMX Database-Monitor for free at:
http://www.herber-consulting.de/BusyBee
***********************************************

[Eric Herber]

Eric Herber wrote:

I've a question regarding db2 (V8.1) and database backups
going to a storage manager like TSM for example.

As I can see in the storage manager if I backup the complete
database over the TSM API (no tablespace backups) the backup
images is stored as a single backup object.
Later I will be able to restore the complete database from
this image and I will also be able to restore single tablespaces
from this backup image as long as I archived my logs thru the
userexit program.

However if I've a really big database (let's say 1 TB), I would guess
that db2 always has to request the whole backup image from TSM.
So If I only want to restore a single tablespace, TSM has to read
1 TB probably from tape and has to transfer this 1 TB thru the
network to the dataseserver in order for db2 to be able to extract
the single tablespace and restore it to the database ?

If this is the case, database backups for large databases seem not
be a passable solution.

SAN and ExternalBackup (split mirror) is unfortunately currently
not a solution at this customer site.
Doing incremental/delta backups seem not the help in this particular
situation as the problem of retrieving the whole full backup images
persists.

If I backup a complete Informix instance thru TSM, the individual
dbspaces (db2: tablespaces) are created as separate backup objects
so I'm able to restore a single dbspace from this instance backup
without TSM having to transfer the complete set.
This seems to be the better approach because restoring a single
dbspace (tablespace) is normally needed more frequently as having
to restore the whole database.

Would somebody recommend to do tablespace backups instead of
database backups and are these tablespace backups manageable
from an administration point of view ?
If have some respect about the increased management overhead
resulting from taking tablespace backups as this is also
a partitioned database environment.

Thank you for any input.

Best regards

Eric

No comments on this ?
Any input is highly appreciated.
Thank you.

Eric

[Ian]

Eric Herber wrote:

Eric Herber wrote:

I've a question regarding db2 (V8.1) and database backups
going to a storage manager like TSM for example.

As I can see in the storage manager if I backup the complete
database over the TSM API (no tablespace backups) the backup
images is stored as a single backup object.
Later I will be able to restore the complete database from
this image and I will also be able to restore single tablespaces
from this backup image as long as I archived my logs thru the
userexit program.

However if I've a really big database (let's say 1 TB), I would guess
that db2 always has to request the whole backup image from TSM.
So If I only want to restore a single tablespace, TSM has to read
1 TB probably from tape and has to transfer this 1 TB thru the
network to the dataseserver in order for db2 to be able to extract
the single tablespace and restore it to the database ?

If this is the case, database backups for large databases seem not
be a passable solution.

SAN and ExternalBackup (split mirror) is unfortunately currently
not a solution at this customer site.
Doing incremental/delta backups seem not the help in this particular
situation as the problem of retrieving the whole full backup images
persists.

If I backup a complete Informix instance thru TSM, the individual
dbspaces (db2: tablespaces) are created as separate backup objects
so I'm able to restore a single dbspace from this instance backup
without TSM having to transfer the complete set.
This seems to be the better approach because restoring a single
dbspace (tablespace) is normally needed more frequently as having
to restore the whole database.

Would somebody recommend to do tablespace backups instead of
database backups and are these tablespace backups manageable
from an administration point of view ?
If have some respect about the increased management overhead
resulting from taking tablespace backups as this is also
a partitioned database environment.

Thank you for any input.

Best regards

Eric

No comments on this ?
Any input is highly appreciated.
Thank you.

Eric

I believe your assertion is correct (that DB2 would need to pull the entire
full backup from the TSM server in order to apply the necessary
tablespace), but I'm not 100% on this. I'm not sure if DB2 will stop
reading the backup image once it's restored the requested tablespace.
With that in mind, doing tablespace-level backups may be your best
solution.
What about getting a faster connection to the TSM server -- 1Tb would
take about 3 hours over GbE, right? Can't TSM LANfree do this (to
backup via fibre channel instead of LAN) and get more throughput?
Good luck,

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

[Fan Ruo Xin]

"Ian" <ia*****@mobileaudio.com> wrote in message
news:40**********@corp.newsgroups.com...

Eric Herber wrote:

Eric Herber wrote:

I've a question regarding db2 (V8.1) and database backups
going to a storage manager like TSM for example.

As I can see in the storage manager if I backup the complete
database over the TSM API (no tablespace backups) the backup
images is stored as a single backup object.
Later I will be able to restore the complete database from
this image and I will also be able to restore single tablespaces
from this backup image as long as I archived my logs thru the
userexit program.

However if I've a really big database (let's say 1 TB), I would guess
that db2 always has to request the whole backup image from TSM.
So If I only want to restore a single tablespace, TSM has to read
1 TB probably from tape and has to transfer this 1 TB thru the
network to the dataseserver in order for db2 to be able to extract
the single tablespace and restore it to the database ?

If this is the case, database backups for large databases seem not
be a passable solution.

SAN and ExternalBackup (split mirror) is unfortunately currently
not a solution at this customer site.
Doing incremental/delta backups seem not the help in this particular
situation as the problem of retrieving the whole full backup images
persists.

If I backup a complete Informix instance thru TSM, the individual
dbspaces (db2: tablespaces) are created as separate backup objects
so I'm able to restore a single dbspace from this instance backup
without TSM having to transfer the complete set.
This seems to be the better approach because restoring a single
dbspace (tablespace) is normally needed more frequently as having
to restore the whole database.

Would somebody recommend to do tablespace backups instead of
database backups and are these tablespace backups manageable
from an administration point of view ?
If have some respect about the increased management overhead
resulting from taking tablespace backups as this is also
a partitioned database environment.

Thank you for any input.

Best regards

Eric

No comments on this ?
Any input is highly appreciated.
Thank you.

Eric

I believe your assertion is correct (that DB2 would need to pull the

entire full backup from the TSM server in order to apply the necessary
tablespace), but I'm not 100% on this. =============
Like Ian, I am not 100% sure. But I also think db2 has to request the whole
backup image from TSM even when you only need to restore one tablespace.

But I don't think informix's approach will be suitable for db2 backup. First
I don't think informix's dbspaces is equal to db2's tablespace. DB2's
tablespace merge the concept of informix's dbspace and informix's
tablespace.
Anyway, let's only talk about db2. It is very normal that there are more
than 50, even more than 100 tablespaces on each db partitions. When we
backup this db partition, and if we hope each tablespace is created as a
separate backup objects. You will see this will cause a lot of overhead to
manage the backup objects. And another issue is by using EEE/multiple
partitions, you can build tablespaces span either all the nodes or only part
of the nodes. If use this approach, the number of backup objects on
different db partitions will be different. This also cause more manage cost.

For a big size database, either we use incremental backup, or only backup a
couple of tablespaces (instead of the whole db), the most important thing is
we are not trying to save the restore time. We are trying to save the
rolling forward time. But we save the total time which need to make a db
online.
I'm not sure if DB2 will stop
reading the backup image once it's restored the requested tablespace.
With that in mind, doing tablespace-level backups may be your best
solution.
What about getting a faster connection to the TSM server -- 1Tb would
take about 3 hours over GbE, right? Can't TSM LANfree do this (to
backup via fibre channel instead of LAN) and get more throughput?
Good luck,

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

[Eric Herber]

Ian wrote:

Eric Herber wrote:

Eric Herber wrote:

I've a question regarding db2 (V8.1) and database backups
going to a storage manager like TSM for example.

As I can see in the storage manager if I backup the complete
database over the TSM API (no tablespace backups) the backup
images is stored as a single backup object.
Later I will be able to restore the complete database from
this image and I will also be able to restore single tablespaces
from this backup image as long as I archived my logs thru the
userexit program.

However if I've a really big database (let's say 1 TB), I would guess
that db2 always has to request the whole backup image from TSM.
So If I only want to restore a single tablespace, TSM has to read
1 TB probably from tape and has to transfer this 1 TB thru the
network to the dataseserver in order for db2 to be able to extract
the single tablespace and restore it to the database ?

If this is the case, database backups for large databases seem not
be a passable solution.

SAN and ExternalBackup (split mirror) is unfortunately currently
not a solution at this customer site.
Doing incremental/delta backups seem not the help in this particular
situation as the problem of retrieving the whole full backup images
persists.

If I backup a complete Informix instance thru TSM, the individual
dbspaces (db2: tablespaces) are created as separate backup objects
so I'm able to restore a single dbspace from this instance backup
without TSM having to transfer the complete set.
This seems to be the better approach because restoring a single
dbspace (tablespace) is normally needed more frequently as having
to restore the whole database.

Would somebody recommend to do tablespace backups instead of
database backups and are these tablespace backups manageable
from an administration point of view ?
If have some respect about the increased management overhead
resulting from taking tablespace backups as this is also
a partitioned database environment.

Thank you for any input.

Best regards

Eric

No comments on this ?
Any input is highly appreciated.
Thank you.

Eric

I believe your assertion is correct (that DB2 would need to pull the
entire full backup from the TSM server in order to apply the necessary
tablespace), but I'm not 100% on this. I'm not sure if DB2 will stop
reading the backup image once it's restored the requested tablespace.
With that in mind, doing tablespace-level backups may be your best
solution.
What about getting a faster connection to the TSM server -- 1Tb would
take about 3 hours over GbE, right? Can't TSM LANfree do this (to
backup via fibre channel instead of LAN) and get more throughput?
Good luck,

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

Yes, LAN-Free Backup might be an option to think about.
Thank you Ian.

Best regards

Eric
--
IT-Consulting Herber
***********************************************
Download the IFMX Database-Monitor for free at:
http://www.herber-consulting.de/BusyBee
***********************************************

[Eric Herber]

Fan Ruo Xin wrote:

"Ian" <ia*****@mobileaudio.com> wrote in message
news:40**********@corp.newsgroups.com...

Eric Herber wrote:

> Eric Herber wrote:
>
>
>>I've a question regarding db2 (V8.1) and database backups
>>going to a storage manager like TSM for example.
>>
>>As I can see in the storage manager if I backup the complete
>>database over the TSM API (no tablespace backups) the backup
>>images is stored as a single backup object.
>>Later I will be able to restore the complete database from
>>this image and I will also be able to restore single tablespaces
>>from this backup image as long as I archived my logs thru the
>>userexit program.
>>
>>However if I've a really big database (let's say 1 TB), I would guess
>>that db2 always has to request the whole backup image from TSM.
>>So If I only want to restore a single tablespace, TSM has to read
>>1 TB probably from tape and has to transfer this 1 TB thru the
>>network to the dataseserver in order for db2 to be able to extract
>>the single tablespace and restore it to the database ?
>>
>>If this is the case, database backups for large databases seem not
>>be a passable solution.
>>
>>SAN and ExternalBackup (split mirror) is unfortunately currently
>>not a solution at this customer site.
>>Doing incremental/delta backups seem not the help in this particular
>>situation as the problem of retrieving the whole full backup images
>>persists.
>>
>>If I backup a complete Informix instance thru TSM, the individual
>>dbspaces (db2: tablespaces) are created as separate backup objects
>>so I'm able to restore a single dbspace from this instance backup
>>without TSM having to transfer the complete set.
>>This seems to be the better approach because restoring a single
>>dbspace (tablespace) is normally needed more frequently as having
>>to restore the whole database.
>>
>>Would somebody recommend to do tablespace backups instead of
>>database backups and are these tablespace backups manageable
>>from an administration point of view ?
>>If have some respect about the increased management overhead
>>resulting from taking tablespace backups as this is also
>>a partitioned database environment.
>>
>>Thank you for any input.
>>
>>Best regards
>>
>>Eric
>
>
> No comments on this ?
> Any input is highly appreciated.
> Thank you.
>
> Eric

I believe your assertion is correct (that DB2 would need to pull the

entire

full backup from the TSM server in order to apply the necessary
tablespace), but I'm not 100% on this.

=============
Like Ian, I am not 100% sure. But I also think db2 has to request the
whole backup image from TSM even when you only need to restore one
tablespace.

But I don't think informix's approach will be suitable for db2 backup.
First I don't think informix's dbspaces is equal to db2's tablespace.
DB2's tablespace merge the concept of informix's dbspace and informix's
tablespace.
Anyway, let's only talk about db2. It is very normal that there are more
than 50, even more than 100 tablespaces on each db partitions. When we
backup this db partition, and if we hope each tablespace is created as a
separate backup objects. You will see this will cause a lot of overhead to
manage the backup objects. And another issue is by using EEE/multiple
partitions, you can build tablespaces span either all the nodes or only
part of the nodes. If use this approach, the number of backup objects on
different db partitions will be different. This also cause more manage
cost.

For a big size database, either we use incremental backup, or only backup
a couple of tablespaces (instead of the whole db), the most important
thing is we are not trying to save the restore time. We are trying to save
the rolling forward time. But we save the total time which need to make a
db online.

I'm not sure if DB2 will stop
reading the backup image once it's restored the requested tablespace.
With that in mind, doing tablespace-level backups may be your best
solution.
What about getting a faster connection to the TSM server -- 1Tb would
take about 3 hours over GbE, right? Can't TSM LANfree do this (to
backup via fibre channel instead of LAN) and get more throughput?
Good luck,

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

The concept of an informix dbspace and a db2 tablespace
is pretty much the same.

I can't agree with the point that it would be a management overhead
if DB2 would create several tablespace backup objects from a single
database backup operation.
This is the reason why almost anybody who is dealing with large
database has a storage manager behind. The storage manager should
be able to handle these backup objects and DB2 should be able to
handle these too.

I've seen big Informix instances with several hundred dbspaces and
it worked well. The fact that some DB2 tablespaces might not exist
on all partitions should also not be a problem. Like DB2 EEE (or ESE)
you can create coserver-groups and dbslices with Informix XPS and
fragment your tables around these. So there might also be dbspaces
which are not located on every coserver.

However I appreciate your suggestions about doing tablespace backups
and trying to minimize the rollforward time.

Thank you very much for your input.

Best regards

Eric
--
IT-Consulting Herber
***********************************************
Download the IFMX Database-Monitor for free at:
http://www.herber-consulting.de/BusyBee
***********************************************

[Fan Ruo Xin]

"Eric Herber" <er**@I-hate-spam.org> wrote in message
news:c1**********@online.de...

Fan Ruo Xin wrote:

"Ian" <ia*****@mobileaudio.com> wrote in message
news:40**********@corp.newsgroups.com...

Eric Herber wrote:

> Eric Herber wrote:
>
>
>>I've a question regarding db2 (V8.1) and database backups
>>going to a storage manager like TSM for example.
>>
>>As I can see in the storage manager if I backup the complete
>>database over the TSM API (no tablespace backups) the backup
>>images is stored as a single backup object.
>>Later I will be able to restore the complete database from
>>this image and I will also be able to restore single tablespaces
>>from this backup image as long as I archived my logs thru the
>>userexit program.
>>
>>However if I've a really big database (let's say 1 TB), I would guess
>>that db2 always has to request the whole backup image from TSM.
>>So If I only want to restore a single tablespace, TSM has to read
>>1 TB probably from tape and has to transfer this 1 TB thru the
>>network to the dataseserver in order for db2 to be able to extract
>>the single tablespace and restore it to the database ?
>>
>>If this is the case, database backups for large databases seem not
>>be a passable solution.
>>
>>SAN and ExternalBackup (split mirror) is unfortunately currently
>>not a solution at this customer site.
>>Doing incremental/delta backups seem not the help in this particular
>>situation as the problem of retrieving the whole full backup images
>>persists.
>>
>>If I backup a complete Informix instance thru TSM, the individual
>>dbspaces (db2: tablespaces) are created as separate backup objects
>>so I'm able to restore a single dbspace from this instance backup
>>without TSM having to transfer the complete set.
>>This seems to be the better approach because restoring a single
>>dbspace (tablespace) is normally needed more frequently as having
>>to restore the whole database.
>>
>>Would somebody recommend to do tablespace backups instead of
>>database backups and are these tablespace backups manageable
>>from an administration point of view ?
>>If have some respect about the increased management overhead
>>resulting from taking tablespace backups as this is also
>>a partitioned database environment.
>>
>>Thank you for any input.
>>
>>Best regards
>>
>>Eric
>
>
> No comments on this ?
> Any input is highly appreciated.
> Thank you.
>
> Eric

I believe your assertion is correct (that DB2 would need to pull the entire

full backup from the TSM server in order to apply the necessary
tablespace), but I'm not 100% on this.

=============
Like Ian, I am not 100% sure. But I also think db2 has to request the
whole backup image from TSM even when you only need to restore one
tablespace.

But I don't think informix's approach will be suitable for db2 backup.
First I don't think informix's dbspaces is equal to db2's tablespace.
DB2's tablespace merge the concept of informix's dbspace and informix's
tablespace.
Anyway, let's only talk about db2. It is very normal that there are more
than 50, even more than 100 tablespaces on each db partitions. When we
backup this db partition, and if we hope each tablespace is created as a
separate backup objects. You will see this will cause a lot of overhead to manage the backup objects. And another issue is by using EEE/multiple
partitions, you can build tablespaces span either all the nodes or only
part of the nodes. If use this approach, the number of backup objects on
different db partitions will be different. This also cause more manage
cost.

For a big size database, either we use incremental backup, or only backup a couple of tablespaces (instead of the whole db), the most important
thing is we are not trying to save the restore time. We are trying to save the rolling forward time. But we save the total time which need to make a db online.

I'm not sure if DB2 will stop
reading the backup image once it's restored the requested tablespace.
With that in mind, doing tablespace-level backups may be your best
solution.
What about getting a faster connection to the TSM server -- 1Tb would
take about 3 hours over GbE, right? Can't TSM LANfree do this (to
backup via fibre channel instead of LAN) and get more throughput?
Good luck,

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

The concept of an informix dbspace and a db2 tablespace
is pretty much the same.

I can't agree with the point that it would be a management overhead
if DB2 would create several tablespace backup objects from a single
database backup operation.
This is the reason why almost anybody who is dealing with large
database has a storage manager behind. The storage manager should
be able to handle these backup objects and DB2 should be able to
handle these too.

===
I suppose dealing with 100 backup image should be much easier than 10000
backup images, if I have 100 db partitions. And on each db partition I build
100 tablespaces.
Anyway, I thought you can talk to IBM about your idea.

BTW, please see the following:

Eugene <eu****@compete.com> wrote: Can I recover a DB2 database to another machine from full set of tablespace level backups?
You can coerce DB2 into allowing you to do that in some cases, but it is
not a supported operation. You will need the assistance of DB2 Support to
do so and even then there are complications. I would definitely suggest
that you take the occasional full database backup to avoid getting into this
situation.

There is some impetus for getting this feature designed and written
properly in a future release, but I can't promise anything.

Fan Ruo Xin <fa*****@yahoo.com> wrote: I'd like to know if I backup only tablespaces (system catalog and all the user tablespaces), will those db configuration files be backuped?
A tablespace backup contains the same metadata that a database backup has.

dave

I've seen big Informix instances with several hundred dbspaces and
it worked well. The fact that some DB2 tablespaces might not exist
on all partitions should also not be a problem. Like DB2 EEE (or ESE)
you can create coserver-groups and dbslices with Informix XPS and
fragment your tables around these. So there might also be dbspaces
which are not located on every coserver.

However I appreciate your suggestions about doing tablespace backups
and trying to minimize the rollforward time.

Thank you very much for your input.

Best regards

Eric
--
IT-Consulting Herber
***********************************************
Download the IFMX Database-Monitor for free at:
http://www.herber-consulting.de/BusyBee
***********************************************