473,376 Members | 1,218 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,376 software developers and data experts.

Backup and Export

I am a little confused by DB2 Backup and Export.

I used "db2 backup db QAS to /dev/rmt0" backuping 650GB database to a
IBM LTO 3581 (1 drive) only 11 hours. But I used "db2 export to
/dev/rmt0 of ixf messages MKPF.msg select * from mkpf" on the same
3581, and this table is only 5GB, but the export used 4 hours. What's
the diffirent between Backup and Export? I mean except backup is used
to backup whole database or tablespace, and export is used to backup
tables.

I did another test, and I used "db2 export to /backuptest of ixf
messages MKPF.msg select * from mkpf", it only used 30 minutes. What's
the big diffirent between export to tape and disk, I know disk is
faster than tape of couse.
Nov 12 '05 #1
5 10520
Backup processes tablespaces a physical block at a time. Export
processes a row at a time. Note that export involves the use of a SELECT
statement that can have predicates to control the rows that are exported.
Tape drives have (comparatively) long delay times to start and stop tape
movement. Gaps between physical records on the tape also can occupy a
significant amount of space on the tape. Tape performance, as a general
rule, is significantly improved by writing large physical blocks to the
drive. It's quite possible that your default for the tape drive is to
write physical records that match the size of the write request. In this
case, the export to tape will write a separate block for each row of the
table! UDB backups will write 4k blocks unless you've altered this in
the tablespace definition.

Check your operating system manuals for directions on how to set a
blocksize for tape devices. Use a large blocksize (32k/64k or larger if
supported) to optimize speed and quantity of data written to a tape.

on AIX, smit is the tool to use.

Phil Sherman
David wrote:
I am a little confused by DB2 Backup and Export.

I used "db2 backup db QAS to /dev/rmt0" backuping 650GB database to a
IBM LTO 3581 (1 drive) only 11 hours. But I used "db2 export to
/dev/rmt0 of ixf messages MKPF.msg select * from mkpf" on the same
3581, and this table is only 5GB, but the export used 4 hours. What's
the diffirent between Backup and Export? I mean except backup is used
to backup whole database or tablespace, and export is used to backup
tables.

I did another test, and I used "db2 export to /backuptest of ixf
messages MKPF.msg select * from mkpf", it only used 30 minutes. What's
the big diffirent between export to tape and disk, I know disk is
faster than tape of couse.


Nov 12 '05 #2
Philip, thank you so much and I will give it a try.

Philip Sherman <ps******@ameritech.net> wrote in message news:<oG*****************@newssvr33.news.prodigy.c om>...
Backup processes tablespaces a physical block at a time. Export
processes a row at a time. Note that export involves the use of a SELECT
statement that can have predicates to control the rows that are exported.
Tape drives have (comparatively) long delay times to start and stop tape
movement. Gaps between physical records on the tape also can occupy a
significant amount of space on the tape. Tape performance, as a general
rule, is significantly improved by writing large physical blocks to the
drive. It's quite possible that your default for the tape drive is to
write physical records that match the size of the write request. In this
case, the export to tape will write a separate block for each row of the
table! UDB backups will write 4k blocks unless you've altered this in
the tablespace definition.

Check your operating system manuals for directions on how to set a
blocksize for tape devices. Use a large blocksize (32k/64k or larger if
supported) to optimize speed and quantity of data written to a tape.

on AIX, smit is the tool to use.

Phil Sherman
David wrote:
I am a little confused by DB2 Backup and Export.

I used "db2 backup db QAS to /dev/rmt0" backuping 650GB database to a
IBM LTO 3581 (1 drive) only 11 hours. But I used "db2 export to
/dev/rmt0 of ixf messages MKPF.msg select * from mkpf" on the same
3581, and this table is only 5GB, but the export used 4 hours. What's
the diffirent between Backup and Export? I mean except backup is used
to backup whole database or tablespace, and export is used to backup
tables.

I did another test, and I used "db2 export to /backuptest of ixf
messages MKPF.msg select * from mkpf", it only used 30 minutes. What's
the big diffirent between export to tape and disk, I know disk is
faster than tape of couse.

Nov 12 '05 #3
Hi Philip,

I tried to setup the tape block size as following:
chdev -l rmt1 -a block_size=4096 (tried other like 10240,32768 etc.)

Once I initiated export:
db2 "export to /dev/rmt1 of ixf messages mkpf.msg select * from mkpf"
I got the error message:
SQL10018N The disk is full. Processing was ended.

Once I changed the block_size back to 0, it worked fine but slow.

How can I use the large block_size to boost my export? What block size
will export use? 4k page size same as tablespace? or just same as one
record length? I have no idea what to do next.

Philip Sherman <ps******@ameritech.net> wrote in message news:<oG*****************@newssvr33.news.prodigy.c om>...
Backup processes tablespaces a physical block at a time. Export
processes a row at a time. Note that export involves the use of a SELECT
statement that can have predicates to control the rows that are exported.
Tape drives have (comparatively) long delay times to start and stop tape
movement. Gaps between physical records on the tape also can occupy a
significant amount of space on the tape. Tape performance, as a general
rule, is significantly improved by writing large physical blocks to the
drive. It's quite possible that your default for the tape drive is to
write physical records that match the size of the write request. In this
case, the export to tape will write a separate block for each row of the
table! UDB backups will write 4k blocks unless you've altered this in
the tablespace definition.

Check your operating system manuals for directions on how to set a
blocksize for tape devices. Use a large blocksize (32k/64k or larger if
supported) to optimize speed and quantity of data written to a tape.

on AIX, smit is the tool to use.

Phil Sherman
David wrote:
I am a little confused by DB2 Backup and Export.

I used "db2 backup db QAS to /dev/rmt0" backuping 650GB database to a
IBM LTO 3581 (1 drive) only 11 hours. But I used "db2 export to
/dev/rmt0 of ixf messages MKPF.msg select * from mkpf" on the same
3581, and this table is only 5GB, but the export used 4 hours. What's
the diffirent between Backup and Export? I mean except backup is used
to backup whole database or tablespace, and export is used to backup
tables.

I did another test, and I used "db2 export to /backuptest of ixf
messages MKPF.msg select * from mkpf", it only used 30 minutes. What's
the big diffirent between export to tape and disk, I know disk is
faster than tape of couse.

Nov 12 '05 #4
Philip Sherman wrote:
Backup processes tablespaces a physical block at a time. Export
processes a row at a time. Note that export involves the use of a
SELECT statement that can have predicates to control the rows that are
exported.
Tape drives have (comparatively) long delay times to start and stop
tape movement. Gaps between physical records on the tape also can
occupy a significant amount of space on the tape. Tape performance, as
a general rule, is significantly improved by writing large physical
blocks to the drive. It's quite possible that your default for the tape
drive is to write physical records that match the size of the write
request. In this case, the export to tape will write a separate block
for each row of the table! UDB backups will write 4k blocks unless
you've altered this in the tablespace definition.

Check your operating system manuals for directions on how to set a
blocksize for tape devices. Use a large blocksize (32k/64k or larger if
supported) to optimize speed and quantity of data written to a tape.

on AIX, smit is the tool to use.

I run Red Hat Enterprise Linux, and ran other versions of Red Hat Linux in
the past. I do not know if the distribution here actually matters very much.

I have an Exabyte VXA-2 tape drive on this machine, where I am presently
running DB2 V8.1.6. Exabyte recommend writing tape block size (as seen by
the OS) of 65536 bytes for best results (probably related to the cache
size in the drive among other things; it has a 2 megabyte buffer inside).

When setting up a little shell script to backup my little database, I
simply wrote (among other things):

$DB2 BACKUP DATABASE stock TO $TAPE_DRIVE BUFFER 16 > $REPORT 2>&1
---------
to get the desired block size to transfer to the tape drive. The meanings
of the definitions of $DB2, etc., should be obvious.

Also, in Linux, I did:

mt -f /dev/st0 setblk 0 to allow DB2 to set the blocksize (4096 * 16).

As an aside, the VXA-2 is a streaming drive that does not stop between
tape blocks. If the source of the data is too slow (not likely when the
drive is on an Ultra/320 LVD scsi controller all its own, and the hard
drives containing the database are 10,000rpm Ultra/320 SCSI drives with 8
Megabyte caches on their own SCSI controller), the drive slows down to
match the incoming data rate. It does not need to stop, backspace, forward
space, and write as my old DDS-2 tape drive had to do. The large buffer in
the drive, and the technology used to read and write the tapes, can
tolerate a variable speed drive mechanism. The blocks actually written to
the tape are probably not 65536 bytes in size. They include 4 level
Reed-Solomon ECC, CRC, etc., and 24768 bytes of user data. One of the
tapes can hold 160GBytes of hardware compressed data (assuming 2:1
compressioin ratio) or 80GBytes uncompressed.

--
.~. Jean-David Beyer Registered Linux User 85642.
/V\ Registered Machine 241939.
/( )\ Shrewsbury, New Jersey http://counter.li.org
^^-^^ 07:40:00 up 22 days, 23:17, 3 users, load average: 4.04, 4.05, 4.08

Nov 12 '05 #5
Yeah, you are definitely right about the backup. But in my case, I
want to know how to setup the block size of export. Thanks anyway.

Jean-David Beyer <jd*****@exit109.com> wrote in message news:<10*************@corp.supernews.com>...
Philip Sherman wrote:
Backup processes tablespaces a physical block at a time. Export
processes a row at a time. Note that export involves the use of a
SELECT statement that can have predicates to control the rows that are
exported.
Tape drives have (comparatively) long delay times to start and stop
tape movement. Gaps between physical records on the tape also can
occupy a significant amount of space on the tape. Tape performance, as
a general rule, is significantly improved by writing large physical
blocks to the drive. It's quite possible that your default for the tape
drive is to write physical records that match the size of the write
request. In this case, the export to tape will write a separate block
for each row of the table! UDB backups will write 4k blocks unless
you've altered this in the tablespace definition.

Check your operating system manuals for directions on how to set a
blocksize for tape devices. Use a large blocksize (32k/64k or larger if
supported) to optimize speed and quantity of data written to a tape.

on AIX, smit is the tool to use.

I run Red Hat Enterprise Linux, and ran other versions of Red Hat Linux in
the past. I do not know if the distribution here actually matters very much.

I have an Exabyte VXA-2 tape drive on this machine, where I am presently
running DB2 V8.1.6. Exabyte recommend writing tape block size (as seen by
the OS) of 65536 bytes for best results (probably related to the cache
size in the drive among other things; it has a 2 megabyte buffer inside).

When setting up a little shell script to backup my little database, I
simply wrote (among other things):

$DB2 BACKUP DATABASE stock TO $TAPE_DRIVE BUFFER 16 > $REPORT 2>&1
---------
to get the desired block size to transfer to the tape drive. The meanings
of the definitions of $DB2, etc., should be obvious.

Also, in Linux, I did:

mt -f /dev/st0 setblk 0 to allow DB2 to set the blocksize (4096 * 16).

As an aside, the VXA-2 is a streaming drive that does not stop between
tape blocks. If the source of the data is too slow (not likely when the
drive is on an Ultra/320 LVD scsi controller all its own, and the hard
drives containing the database are 10,000rpm Ultra/320 SCSI drives with 8
Megabyte caches on their own SCSI controller), the drive slows down to
match the incoming data rate. It does not need to stop, backspace, forward
space, and write as my old DDS-2 tape drive had to do. The large buffer in
the drive, and the technology used to read and write the tapes, can
tolerate a variable speed drive mechanism. The blocks actually written to
the tape are probably not 65536 bytes in size. They include 4 level
Reed-Solomon ECC, CRC, etc., and 24768 bytes of user data. One of the
tapes can hold 160GBytes of hardware compressed data (assuming 2:1
compressioin ratio) or 80GBytes uncompressed.

Nov 12 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Sandra | last post by:
I have Oracle 8.1.7 for NT with one database that has many schemas, one for each project under development. I need some way to backup each one of those schemas, some of them should be copied...
3
by: Cristina | last post by:
Hallo i am a beginner into Oracle Technologies.I would like to make backup of my database,but i dont know how.Is there tools?Can i schedule the backup plan? thanks Cristina
3
by: GB | last post by:
Are all database configuration parameters included in the database backup or not ? If I restore the database from the backup are these restored also ? What is the best way to backup and restore...
4
by: Hardy | last post by:
hi gurus, now I have to backup and restore a 8 T size db2 database. from two s85 to two 670. the partitions,tablespaces of the db should be redesigned then I plan to use redirected restore. but...
6
by: Charles Morrall | last post by:
I have no experience with DB2 as such, but I've been tasked with configuring backup of a server running DB2 v8 on Windows Server 2003. I do have some experience with backups in general though. The...
5
by: Matt Brandes | last post by:
I am looking for a solution that would allow me to recursively backup a given registry key that I could then use to import into another machine. The problem that I keep having is the inability to...
14
by: Udo Kerwath | last post by:
Hi all, this might sound strange, but I'm desperately seeking for a solution for this (personal) issue. Ages ago, I built a DB2 database under OS/2, even with a VX-Rexx graphical interface. I...
0
by: duraisamy | last post by:
Hi, I studied about backup and recovery,but i have one doubt about backup in tape and export the database. please relply soon
0
by: debian75 | last post by:
Hi. I have two servers in the same MS network area: 1: MS 2000 Server, with an Oracle 9 installed (with license 1), and a DB1. 2: MS 2003 Enterprise R2, with an Oracle 10.2.0.3 installed (with...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.