db2 export - how to make it faster

[maricel]

Is there anybody out there who have any idea why EXPORT is relatively slower
when putting the output file on a network drive - map drive from onother PC
compared to putting it on my local PC drive (faster). Is there any
workaround or tuning config that is available?

Your help would be highly appreciated.

maricel

[Jan M. Nelken]

maricel wrote:

Is there anybody out there who have any idea why EXPORT is relatively slower
when putting the output file on a network drive - map drive from onother PC
compared to putting it on my local PC drive (faster). Is there any
workaround or tuning config that is available?

Your help would be highly appreciated.

maricel

*BEFORE* I would start looking into DB2 tuning - I would verify that I
am really describing correctly the problem:

Run a benchmark writing a relatively large file - (say 10Mb) with a
relativel small records (say 100b) on local drive - at least 10 times.

Repeat the same using networked drive.

Have a DB2 export command creating on local drive result file of the
same size as benchmark above - also at least 10 times.

Repeat the same using networked drive.
Now you have a base to compare results - assuming that environment for
all 4 benchmark runs above was consistently the same.
Jan M. Nelken

[maricel]

Hi Jan,

Thanks for this. Actually, i'm the only one connected to the network, so, I
guess network load should not be the problem.
I have noted that the EXPORT IXF file type takes relatively longer to
transport via a network drive compared to export DEL file type.

Any idea on this?

maricel

"Jan M. Nelken" <Un**********@Invalid.Domain> wrote in message
news:ZI********************@news04.bloor.is.net.ca ble.rogers.com...

maricel wrote:

Is there anybody out there who have any idea why EXPORT is relatively slower when putting the output file on a network drive - map drive from onother PC compared to putting it on my local PC drive (faster). Is there any
workaround or tuning config that is available?

Your help would be highly appreciated.

maricel

*BEFORE* I would start looking into DB2 tuning - I would verify that I
am really describing correctly the problem:

Run a benchmark writing a relatively large file - (say 10Mb) with a
relativel small records (say 100b) on local drive - at least 10 times.

Repeat the same using networked drive.

Have a DB2 export command creating on local drive result file of the
same size as benchmark above - also at least 10 times.

Repeat the same using networked drive.
Now you have a base to compare results - assuming that environment for
all 4 benchmark runs above was consistently the same.
Jan M. Nelken

[Jan M. Nelken]

maricel wrote:

Hi Jan,

Thanks for this. Actually, i'm the only one connected to the network, so, I
guess network load should not be the problem.
I have noted that the EXPORT IXF file type takes relatively longer to
transport via a network drive compared to export DEL file type.

Any idea on this?

maricel

I was not thinking about network load. You do go through significantly
different code path of the underlying components of operating system
when writing to redirected drive.

As for second question:

D:\>db2 connect to sample

Database Connection Information

Database server = DB2/NT 8.1.4
SQL authorization ID = DB2ADMIN
Local database alias = SAMPLE
D:\>db2 export to employee.del of del select * from employee
SQL3104N The Export utility is beginning to export data to file
"employee.del".

SQL3105N The Export utility has finished exporting "32" rows.
Number of rows exported: 32

D:\>db2 export to employee.ixf of ixf select * from employee
SQL3104N The Export utility is beginning to export data to file
"employee.ixf".

SQL3105N The Export utility has finished exporting "32" rows.
Number of rows exported: 32

D:\>dir employee.*
Volume in drive D is Data Disk 1
Volume Serial Number is 7FEA-930E

Directory of D:\

31/01/2004 20:20 3,761 employee.del
31/01/2004 20:21 17,929 employee.ixf
2 File(s) 21,690 bytes

any thoughts about those results?

[Anton Versteeg]

Have a look at the file sizes:
IXF export files are usually bigger than DEL export files, so that might
explain the difference.

maricel wrote:

Hi Jan,

Thanks for this. Actually, i'm the only one connected to the network, so, I
guess network load should not be the problem.
I have noted that the EXPORT IXF file type takes relatively longer to
transport via a network drive compared to export DEL file type.

Any idea on this?

maricel

"Jan M. Nelken" <Un**********@Invalid.Domain> wrote in message
news:ZI********************@news04.bloor.is.net.c able.rogers.com...

maricel wrote:

Is there anybody out there who have any idea why EXPORT is relatively

slower

when putting the output file on a network drive - map drive from onother

PC

compared to putting it on my local PC drive (faster). Is there any
workaround or tuning config that is available?

Your help would be highly appreciated.

maricel

*BEFORE* I would start looking into DB2 tuning - I would verify that I
am really describing correctly the problem:

Run a benchmark writing a relatively large file - (say 10Mb) with a
relativel small records (say 100b) on local drive - at least 10 times.

Repeat the same using networked drive.

Have a DB2 export command creating on local drive result file of the
same size as benchmark above - also at least 10 times.

Repeat the same using networked drive.
Now you have a base to compare results - assuming that environment for
all 4 benchmark runs above was consistently the same.
Jan M. Nelken

--
Anton Versteeg
IBM Certified DB2 Specialist
IBM Netherlands

[PM \(pm3iinc-nospam\)]

If you search the net for windows and io performance, you'll find that
windows is slower when accessing shares, etc.
I guess that ntfs junctions and reparse points or pipes also suffer some
degradation.

Just for testing purpose, copy a big file to d:\ then on \\yourhost\d$\.
You'll notice a difference even if it's the same local location.

ex :
260mb
32 vs 36 sec

I can't provide you registry keys or any trick to improve this.
I'd have to check for myself first.

PM

"maricel" <ma*****@xtra.co.nz> a écrit dans le message de
news:%7********************@news.xtra.co.nz...

Is there anybody out there who have any idea why EXPORT is relatively slower when putting the output file on a network drive - map drive from onother PC compared to putting it on my local PC drive (faster). Is there any
workaround or tuning config that is available?

Your help would be highly appreciated.

maricel

[maricel]

I can see the big difference. It shows that IXF file format is at least 5x
bigger than DEL.
Any other thoughts on why IXF file export is slower on Network Drive
compared to IXF export on local drive?

"Jan M. Nelken" <Un**********@Invalid.Domain> wrote in message
news:Lz*****************@twister01.bloor.is.net.ca ble.rogers.com...

maricel wrote:

Hi Jan,

Thanks for this. Actually, i'm the only one connected to the network, so, I guess network load should not be the problem.
I have noted that the EXPORT IXF file type takes relatively longer to
transport via a network drive compared to export DEL file type.

Any idea on this?

maricel

I was not thinking about network load. You do go through significantly
different code path of the underlying components of operating system
when writing to redirected drive.

As for second question:

D:\>db2 connect to sample

Database Connection Information

Database server = DB2/NT 8.1.4
SQL authorization ID = DB2ADMIN
Local database alias = SAMPLE
D:\>db2 export to employee.del of del select * from employee
SQL3104N The Export utility is beginning to export data to file
"employee.del".

SQL3105N The Export utility has finished exporting "32" rows.
Number of rows exported: 32

D:\>db2 export to employee.ixf of ixf select * from employee
SQL3104N The Export utility is beginning to export data to file
"employee.ixf".

SQL3105N The Export utility has finished exporting "32" rows.
Number of rows exported: 32

D:\>dir employee.*
Volume in drive D is Data Disk 1
Volume Serial Number is 7FEA-930E

Directory of D:\

31/01/2004 20:20 3,761 employee.del
31/01/2004 20:21 17,929 employee.ixf
2 File(s) 21,690 bytes

any thoughts about those results?