Speed up backup

uthuras

Hi all,

I have DB2ESE version 8.1 with FP 4 on AIX 5.2. My database used to be
1.1TB. When the DB size is 1.1TB, it takes approximately 7 hours to
backup the entire database (online backup). The backup is directed to
disk and not tape.

Recently, we did data archival by purging some of the records. Now, the
size of the database is 500GB plus. That means, only data purged and we
never resize the tablespace. Therefore, the allocated database size
still 1.1TB.

When backup performed, it still takes 7 hours to backup 500GB plus
database. Even though, the allocated space is 1.1TB, the data contents
is only 500GB plus. In fact, the physical file shows only 500GB plus of
the backup database size. Therefore, I would assume db2 is not backing
up 1.1TB but only 500GB plus of data.

How do I speed up my backup? Shall I make use of COMPRESS option of
backup command? How about using buffers options for backup? Is there
any parameter value I can tune?

Prompt feedback is very much appreciated.

Please help!
Uthuras

Nov 12 '05 #1

Philip Nelson

ut*****@hotmail.com wrote:

Hi all,

I have DB2ESE version 8.1 with FP 4 on AIX 5.2. My database used to be
1.1TB. When the DB size is 1.1TB, it takes approximately 7 hours to
backup the entire database (online backup). The backup is directed to
disk and not tape.

Recently, we did data archival by purging some of the records. Now, the
size of the database is 500GB plus. That means, only data purged and we
never resize the tablespace. Therefore, the allocated database size
still 1.1TB.

When backup performed, it still takes 7 hours to backup 500GB plus
database. Even though, the allocated space is 1.1TB, the data contents
is only 500GB plus. In fact, the physical file shows only 500GB plus of
the backup database size. Therefore, I would assume db2 is not backing
up 1.1TB but only 500GB plus of data.

How do I speed up my backup? Shall I make use of COMPRESS option of
backup command? How about using buffers options for backup? Is there
any parameter value I can tune?

Prompt feedback is very much appreciated.

Please help!
Uthuras

Some thoughts -

Did you reorg since purging ? So you get the minimum number of used pages.

I think the COMPRESS would make things worse, since it takes more CPU cycles
to compress.

V8.2 (Fixpack 7a and above) does its own automatic backup tuning.

If you have more than one channel to you backup disks you could try
something like -

BACKUP DATABASE dbname TO /disk1, /disk2, ...

to make use of parallelism.

HTH

Phil

Nov 12 '05 #2

Serge Rielau

Philip Nelson wrote:

ut*****@hotmail.com wrote:

Hi all,

I have DB2ESE version 8.1 with FP 4 on AIX 5.2. My database used to be
1.1TB. When the DB size is 1.1TB, it takes approximately 7 hours to
backup the entire database (online backup). The backup is directed to
disk and not tape.

Recently, we did data archival by purging some of the records. Now, the
size of the database is 500GB plus. That means, only data purged and we
never resize the tablespace. Therefore, the allocated database size
still 1.1TB.

When backup performed, it still takes 7 hours to backup 500GB plus
database. Even though, the allocated space is 1.1TB, the data contents
is only 500GB plus. In fact, the physical file shows only 500GB plus of
the backup database size. Therefore, I would assume db2 is not backing
up 1.1TB but only 500GB plus of data.

How do I speed up my backup? Shall I make use of COMPRESS option of
backup command? How about using buffers options for backup? Is there
any parameter value I can tune?

Prompt feedback is very much appreciated.

Please help!
Uthuras

Some thoughts -

Did you reorg since purging ? So you get the minimum number of used pages.

I think the COMPRESS would make things worse, since it takes more CPU cycles
to compress.

V8.2 (Fixpack 7a and above) does its own automatic backup tuning.

If you have more than one channel to you backup disks you could try
something like -

BACKUP DATABASE dbname TO /disk1, /disk2, ...

to make use of parallelism.

HTH

Phil

Question from backstage:
-----
What is the exact BACKUP command that you are using? How many tablespaces
are in your database?

Don't use the COMPRESS option. This will make the backup smaller, but
will take longer. You could consider REORGing your tables in order to
make them somewhat less dispersed.

dave
-------

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Nov 12 '05 #3

uthuras

Thanks for the prompt feedback.

I have 158 tablespaces which inclusive of DATA and INDEX tablespaces.
This DB is used for DataWarehouse purpose and therefore each table is
huge and has its own dedicated tablespace.

I'm using the following command to do backup

db2 backup database DWDB online to /bckup/dwdb

Any suggestion??

Thanks
Uthuras

Serge Rielau wrote:

Philip Nelson wrote:

ut*****@hotmail.com wrote:

Hi all,

I have DB2ESE version 8.1 with FP 4 on AIX 5.2. My database used to be1.1TB. When the DB size is 1.1TB, it takes approximately 7 hours to
backup the entire database (online backup). The backup is directed todisk and not tape.

Recently, we did data archival by purging some of the records. Now, thesize of the database is 500GB plus. That means, only data purged and wenever resize the tablespace. Therefore, the allocated database size
still 1.1TB.

When backup performed, it still takes 7 hours to backup 500GB plus
database. Even though, the allocated space is 1.1TB, the data contentsis only 500GB plus. In fact, the physical file shows only 500GB plus ofthe backup database size. Therefore, I would assume db2 is not backingup 1.1TB but only 500GB plus of data.

How do I speed up my backup? Shall I make use of COMPRESS option of
backup command? How about using buffers options for backup? Is thereany parameter value I can tune?

Prompt feedback is very much appreciated.

Please help!
Uthuras

Some thoughts -

Did you reorg since purging ? So you get the minimum number of used pages.
I think the COMPRESS would make things worse, since it takes more CPU cycles to compress.

V8.2 (Fixpack 7a and above) does its own automatic backup tuning.

If you have more than one channel to you backup disks you could try
something like -

BACKUP DATABASE dbname TO /disk1, /disk2, ...

to make use of parallelism.

HTH

Phil

Question from backstage:
-----
What is the exact BACKUP command that you are using? How many

tablespaces are in your database?

Don't use the COMPRESS option. This will make the backup smaller, but will take longer. You could consider REORGing your tables in order to make them somewhat less dispersed.

dave
-------

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Nov 12 '05 #4

Pierre Saint-Jacques

I think that your major bottleneck is in your only one target device.
Given the size of of your db you should use:
db2 backup database DWDB online to /bckup/dwdb1,/bckup/dwdb2, ....
where is directory resides on a separate physical drive.
I also wonder, do you have DMS tablespaces and after the purge, you stil
have a high water mark that causes a lot of scanning of empty pages.
This might be but I'm not sure if the backup works like that. If so, you'll
probably need to reorg the tables before your next backup; or reorg each
table progressively thru a chain of backups.
HTH, Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
<ut*****@hotmail.com> a écrit dans le message de
news:11**********************@l41g2000cwc.googlegr oups.com...

Thanks for the prompt feedback.

I have 158 tablespaces which inclusive of DATA and INDEX tablespaces.
This DB is used for DataWarehouse purpose and therefore each table is
huge and has its own dedicated tablespace.

I'm using the following command to do backup

db2 backup database DWDB online to /bckup/dwdb

Any suggestion??

Thanks
Uthuras

Serge Rielau wrote:

Philip Nelson wrote:

ut*****@hotmail.com wrote:
>Hi all,
>
>I have DB2ESE version 8.1 with FP 4 on AIX 5.2. My database used to be>1.1TB. When the DB size is 1.1TB, it takes approximately 7 hours to
>backup the entire database (online backup). The backup is directed to>disk and not tape.
>
>Recently, we did data archival by purging some of the records. Now, the>size of the database is 500GB plus. That means, only data purged and we>never resize the tablespace. Therefore, the allocated database size
>still 1.1TB.
>
>When backup performed, it still takes 7 hours to backup 500GB plus
>database. Even though, the allocated space is 1.1TB, the data contents>is only 500GB plus. In fact, the physical file shows only 500GB plus of>the backup database size. Therefore, I would assume db2 is not backing>up 1.1TB but only 500GB plus of data.
>
>How do I speed up my backup? Shall I make use of COMPRESS option of
>backup command? How about using buffers options for backup? Is there>any parameter value I can tune?
>
>Prompt feedback is very much appreciated.
>
>Please help!
>Uthuras
Some thoughts -

Did you reorg since purging ? So you get the minimum number of used pages.
I think the COMPRESS would make things worse, since it takes more CPU cycles to compress.

V8.2 (Fixpack 7a and above) does its own automatic backup tuning.

If you have more than one channel to you backup disks you could try
something like -

BACKUP DATABASE dbname TO /disk1, /disk2, ...

to make use of parallelism.

HTH

Phil

Question from backstage:
-----
What is the exact BACKUP command that you are using? How many

tablespaces

are in your database?

Don't use the COMPRESS option. This will make the backup smaller,

but

will take longer. You could consider REORGing your tables in order

to

make them somewhat less dispersed.

dave
-------

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Nov 12 '05 #5