Backup and Export

[David]

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.

[Philip Sherman]

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.

[David]

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.

[David]

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.

[Jean-David Beyer]

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

[David]

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.