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 4 4705 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
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
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Andrew E |
last post by:
I just took over DBA responsibilities for an Oracle 8i database
running on Linux. Although I've been working with relational
databases for some time, I'm a bit green on Oracle so forgive me.
My...
|
by: Wouter |
last post by:
Hi,
I was wandering.
Does the query speed also depend on the colums that you don't use in the
query (so not in the where and not in the select part).
I have a lange unused backup field in...
|
by: Thiko |
last post by:
Hi
I take one nightly full database backup at 02:00 and backup the
transaction log to one backup set every 15mins.
The commands to do this are as follows and are set up to run as
database...
|
by: Michael Bourgon |
last post by:
Here's something I found a few months ago, and it seems to come in
handy pretty often, so I figured I'd share.
Disclaimer: you are modifying a SYSTEM TABLE. This may not work in
the next...
|
by: Eric Herber |
last post by:
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...
|
by: alex |
last post by:
Hi !
I couldn't make backups with our new system using db2 8.2.
Every time I trigger a backup I get this error message:
BACKUP DATABASE EBUERO2 ONLINE TO "/raid/backup/ebuero2/part1",...
|
by: Konstantin Andreev |
last post by:
Hello.
Some time ago I asked in this conference, - How to use an ONLINE BACKUP to restore database onto another system? - but got no answers. Therefore I can conclude it is not possible.
But......
|
by: Jana |
last post by:
Hi!
I have an Access 97 report based on tables linked to an SQL back end.
The report has 5 subreports in it (unfortunately unavoidable due to the
nature of the report) and performance is quite...
|
by: Ben |
last post by:
We recently upgraded our MS SQL Server 2000 to 2005.
Here is what we did:
1. Perform backup of the database from the old server.
2. Created a blank database in the new server.
3. Restored the...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |