Hi All
I have a client who has DB2 on an AS400. In DataStage, using the DB2 Plugin
or ODBC to extract the data and load it into DB2 on NT, runs at an average
of 200 rows per second. The same thing using DTS runs at average of 4000
rows per second. Another test was done where DataStage extracted from Sql
Server and did a bulk load to Sql server using the bulk loader stage. Again,
this was done using DTS and DTS beat DataStage badly. DataStage ran at
around 2000 rows per sec while DTS ran at 8000 rows per sec. I tried to
isolate the problem by making DataStage extract from AS400/DB2 and write to
a sequential file....200 rows/second. Tried extracts from AS400/DB2 and
write to a hash file ...160 rows/second.
Im at a bit of a loss because I dont know why the extraction from the
AS400/DB2 is so slow. DataStage is running on a dedicated dual cpu 4GB ram
NT box, with a 100mb direct connection between the servers. Apparently, so I
was told, DTS has a native connection to DB2 and thats why it outperforms
DataStage. This seems to imply there is something wrong with the DataStage
connection set-up to DB2.
When extracting from AS400/DB2 and DataStage server is running on an NT box,
what/where are the settings one can have a look at? Also, what must be
loaded on the DataStage server box for this connection to work?
Thanks for your input..
Mark Ewart-Phipps
Centerfield Software (Pty) Ltd
Tel +27 11 462 7003
Fax +27 11 462 7392
Cell +27 83 380 2335
DB2 and DataStage vs DB2 and DTS...
What type of connection is DTS using, is it using the same ODBC driver
or is it OLEDB?
-----Original Message-----
From: Mark Ewart-Phipps [mailto:mark@centerfieldsoftware.co.za]
Sent: Friday, September 19, 2003 4:16 AM
To: DataStage User Group (E-mail 2)
Subject: DB2 and DataStage vs DB2 and DTS...
Hi All
I have a client who has DB2 on an AS400. In DataStage, using the DB2
Plugin
or ODBC to extract the data and load it into DB2 on NT, runs at an
average
of 200 rows per second. The same thing using DTS runs at average of 4000
rows per second. Another test was done where DataStage extracted from
Sql
Server and did a bulk load to Sql server using the bulk loader stage.
Again,
this was done using DTS and DTS beat DataStage badly. DataStage ran at
around 2000 rows per sec while DTS ran at 8000 rows per sec. I tried to
isolate the problem by making DataStage extract from AS400/DB2 and write
to
a sequential file....200 rows/second. Tried extracts from AS400/DB2 and
write to a hash file ...160 rows/second.
Im at a bit of a loss because I dont know why the extraction from the
AS400/DB2 is so slow. DataStage is running on a dedicated dual cpu 4GB
ram
NT box, with a 100mb direct connection between the servers. Apparently,
so I
was told, DTS has a native connection to DB2 and thats why it
outperforms
DataStage. This seems to imply there is something wrong with the
DataStage
connection set-up to DB2.
When extracting from AS400/DB2 and DataStage server is running on an NT
box,
what/where are the settings one can have a look at? Also, what must be
loaded on the DataStage server box for this connection to work?
Thanks for your input..
Mark Ewart-Phipps
Centerfield Software (Pty) Ltd
Tel +27 11 462 7003
Fax +27 11 462 7392
Cell +27 83 380 2335
or is it OLEDB?
-----Original Message-----
From: Mark Ewart-Phipps [mailto:mark@centerfieldsoftware.co.za]
Sent: Friday, September 19, 2003 4:16 AM
To: DataStage User Group (E-mail 2)
Subject: DB2 and DataStage vs DB2 and DTS...
Hi All
I have a client who has DB2 on an AS400. In DataStage, using the DB2
Plugin
or ODBC to extract the data and load it into DB2 on NT, runs at an
average
of 200 rows per second. The same thing using DTS runs at average of 4000
rows per second. Another test was done where DataStage extracted from
Sql
Server and did a bulk load to Sql server using the bulk loader stage.
Again,
this was done using DTS and DTS beat DataStage badly. DataStage ran at
around 2000 rows per sec while DTS ran at 8000 rows per sec. I tried to
isolate the problem by making DataStage extract from AS400/DB2 and write
to
a sequential file....200 rows/second. Tried extracts from AS400/DB2 and
write to a hash file ...160 rows/second.
Im at a bit of a loss because I dont know why the extraction from the
AS400/DB2 is so slow. DataStage is running on a dedicated dual cpu 4GB
ram
NT box, with a 100mb direct connection between the servers. Apparently,
so I
was told, DTS has a native connection to DB2 and thats why it
outperforms
DataStage. This seems to imply there is something wrong with the
DataStage
connection set-up to DB2.
When extracting from AS400/DB2 and DataStage server is running on an NT
box,
what/where are the settings one can have a look at? Also, what must be
loaded on the DataStage server box for this connection to work?
Thanks for your input..
Mark Ewart-Phipps
Centerfield Software (Pty) Ltd
Tel +27 11 462 7003
Fax +27 11 462 7392
Cell +27 83 380 2335
I had that same question in my mind. Please let us know what driver you are
using and the source of the driver.
Bob Zurek
VP Advanced Technologies
Ascential Software
-----Original Message-----
From: Shawn Ramsey [mailto:SRamsey@starbucks.com]
Sent: Friday, September 19, 2003 11:45 AM
To: datastage-users@oliver.com
What type of connection is DTS using, is it using the same ODBC driver or is
it OLEDB?
-----Original Message-----
From: Mark Ewart-Phipps [mailto:mark@centerfieldsoftware.co.za]
Sent: Friday, September 19, 2003 4:16 AM
To: DataStage User Group (E-mail 2)
Subject: DB2 and DataStage vs DB2 and DTS...
Hi All
I have a client who has DB2 on an AS400. In DataStage, using the DB2
Plugin
or ODBC to extract the data and load it into DB2 on NT, runs at an
average
of 200 rows per second. The same thing using DTS runs at average of 4000
rows per second. Another test was done where DataStage extracted from
Sql
Server and did a bulk load to Sql server using the bulk loader stage.
Again,
this was done using DTS and DTS beat DataStage badly. DataStage ran at
around 2000 rows per sec while DTS ran at 8000 rows per sec. I tried to
isolate the problem by making DataStage extract from AS400/DB2 and write
to
a sequential file....200 rows/second. Tried extracts from AS400/DB2 and
write to a hash file ...160 rows/second.
Im at a bit of a loss because I dont know why the extraction from the
AS400/DB2 is so slow. DataStage is running on a dedicated dual cpu 4GB
ram
NT box, with a 100mb direct connection between the servers. Apparently,
so I
was told, DTS has a native connection to DB2 and thats why it
outperforms
DataStage. This seems to imply there is something wrong with the
DataStage
connection set-up to DB2.
When extracting from AS400/DB2 and DataStage server is running on an NT
box,
what/where are the settings one can have a look at? Also, what must be
loaded on the DataStage server box for this connection to work?
Thanks for your input..
Mark Ewart-Phipps
Centerfield Software (Pty) Ltd
Tel +27 11 462 7003
Fax +27 11 462 7392
Cell +27 83 380 2335
using and the source of the driver.
Bob Zurek
VP Advanced Technologies
Ascential Software
-----Original Message-----
From: Shawn Ramsey [mailto:SRamsey@starbucks.com]
Sent: Friday, September 19, 2003 11:45 AM
To: datastage-users@oliver.com
What type of connection is DTS using, is it using the same ODBC driver or is
it OLEDB?
-----Original Message-----
From: Mark Ewart-Phipps [mailto:mark@centerfieldsoftware.co.za]
Sent: Friday, September 19, 2003 4:16 AM
To: DataStage User Group (E-mail 2)
Subject: DB2 and DataStage vs DB2 and DTS...
Hi All
I have a client who has DB2 on an AS400. In DataStage, using the DB2
Plugin
or ODBC to extract the data and load it into DB2 on NT, runs at an
average
of 200 rows per second. The same thing using DTS runs at average of 4000
rows per second. Another test was done where DataStage extracted from
Sql
Server and did a bulk load to Sql server using the bulk loader stage.
Again,
this was done using DTS and DTS beat DataStage badly. DataStage ran at
around 2000 rows per sec while DTS ran at 8000 rows per sec. I tried to
isolate the problem by making DataStage extract from AS400/DB2 and write
to
a sequential file....200 rows/second. Tried extracts from AS400/DB2 and
write to a hash file ...160 rows/second.
Im at a bit of a loss because I dont know why the extraction from the
AS400/DB2 is so slow. DataStage is running on a dedicated dual cpu 4GB
ram
NT box, with a 100mb direct connection between the servers. Apparently,
so I
was told, DTS has a native connection to DB2 and thats why it
outperforms
DataStage. This seems to imply there is something wrong with the
DataStage
connection set-up to DB2.
When extracting from AS400/DB2 and DataStage server is running on an NT
box,
what/where are the settings one can have a look at? Also, what must be
loaded on the DataStage server box for this connection to work?
Thanks for your input..
Mark Ewart-Phipps
Centerfield Software (Pty) Ltd
Tel +27 11 462 7003
Fax +27 11 462 7392
Cell +27 83 380 2335
Hi Mark,
Yes, sounds like something wrong with the connection.
We have Datastage AS400/DB2->Win200/Oracle using iseries connector and
we are able to get 5,000 rows per second on a single processor test
win2000 system just over the corporate lan running at 100mb. Thats
about as fast as you would expect it to go. So its not datastage per
se.
You havent described how you are connected to the AS400. We are just
running iseries ODBC connections over ip. Nothing special, and our
source machine is just a small test as400 as well. If you are using the
same we can send you our connection parameters.
Best Regards
Peter Nolan
Www.peternolan.com
-----Original Message-----
From: Mark Ewart-Phipps [mailto:mark@centerfieldsoftware.co.za]
Sent: 19 September 2003 14:16
To: DataStage User Group (E-mail 2)
Subject: DB2 and DataStage vs DB2 and DTS...
Hi All
I have a client who has DB2 on an AS400. In DataStage, using the DB2
Plugin or ODBC to extract the data and load it into DB2 on NT, runs at
an average of 200 rows per second. The same thing using DTS runs at
average of 4000 rows per second. Another test was done where DataStage
extracted from Sql Server and did a bulk load to Sql server using the
bulk loader stage. Again, this was done using DTS and DTS beat DataStage
badly. DataStage ran at around 2000 rows per sec while DTS ran at 8000
rows per sec. I tried to isolate the problem by making DataStage extract
from AS400/DB2 and write to a sequential file....200 rows/second. Tried
extracts from AS400/DB2 and write to a hash file ...160 rows/second.
Im at a bit of a loss because I dont know why the extraction from the
AS400/DB2 is so slow. DataStage is running on a dedicated dual cpu 4GB
ram NT box, with a 100mb direct connection between the servers.
Apparently, so I was told, DTS has a native connection to DB2 and thats
why it outperforms DataStage. This seems to imply there is something
wrong with the DataStage connection set-up to DB2.
When extracting from AS400/DB2 and DataStage server is running on an NT
box, what/where are the settings one can have a look at? Also, what must
be loaded on the DataStage server box for this connection to work?
Thanks for your input..
Mark Ewart-Phipps
Centerfield Software (Pty) Ltd
Tel +27 11 462 7003
Fax +27 11 462 7392
Cell +27 83 380 2335
Yes, sounds like something wrong with the connection.
We have Datastage AS400/DB2->Win200/Oracle using iseries connector and
we are able to get 5,000 rows per second on a single processor test
win2000 system just over the corporate lan running at 100mb. Thats
about as fast as you would expect it to go. So its not datastage per
se.
You havent described how you are connected to the AS400. We are just
running iseries ODBC connections over ip. Nothing special, and our
source machine is just a small test as400 as well. If you are using the
same we can send you our connection parameters.
Best Regards
Peter Nolan
Www.peternolan.com
-----Original Message-----
From: Mark Ewart-Phipps [mailto:mark@centerfieldsoftware.co.za]
Sent: 19 September 2003 14:16
To: DataStage User Group (E-mail 2)
Subject: DB2 and DataStage vs DB2 and DTS...
Hi All
I have a client who has DB2 on an AS400. In DataStage, using the DB2
Plugin or ODBC to extract the data and load it into DB2 on NT, runs at
an average of 200 rows per second. The same thing using DTS runs at
average of 4000 rows per second. Another test was done where DataStage
extracted from Sql Server and did a bulk load to Sql server using the
bulk loader stage. Again, this was done using DTS and DTS beat DataStage
badly. DataStage ran at around 2000 rows per sec while DTS ran at 8000
rows per sec. I tried to isolate the problem by making DataStage extract
from AS400/DB2 and write to a sequential file....200 rows/second. Tried
extracts from AS400/DB2 and write to a hash file ...160 rows/second.
Im at a bit of a loss because I dont know why the extraction from the
AS400/DB2 is so slow. DataStage is running on a dedicated dual cpu 4GB
ram NT box, with a 100mb direct connection between the servers.
Apparently, so I was told, DTS has a native connection to DB2 and thats
why it outperforms DataStage. This seems to imply there is something
wrong with the DataStage connection set-up to DB2.
When extracting from AS400/DB2 and DataStage server is running on an NT
box, what/where are the settings one can have a look at? Also, what must
be loaded on the DataStage server box for this connection to work?
Thanks for your input..
Mark Ewart-Phipps
Centerfield Software (Pty) Ltd
Tel +27 11 462 7003
Fax +27 11 462 7392
Cell +27 83 380 2335
We are experiencing the same issues as Mark, except we are running DataStage 6.0 on Unix - a dedicated Sun v880, 8 proc, 8 Gb ram (Solaris) against AS400/DB2. We are using the wired protocol driver provided out-of-the-box by Ascential (VMdb218.so). This connection typically obtains 200-400 rows/sec. We have tested connectivity with IBMs client access connection from a WIN box and could easily get 1000+ rows/sec. The network is gigabit and we have profiled the traffic and have determined it is not our bottleneck. We are also at a loss as to why we cannot achieve better performance.
-----Original Message-----
From: Peter Nolan [mailto:pnolan@attglobal.net]
Sent: Friday, September 19, 2003 11:03 PM
To: datastage-users@oliver.com
Subject: RE: DB2 and DataStage vs DB2 and DTS...
Hi Mark,
Yes, sounds like something wrong with the connection.
We have Datastage AS400/DB2->Win200/Oracle using iseries connector and
we are able to get 5,000 rows per second on a single processor test
win2000 system just over the corporate lan running at 100mb. Thats
about as fast as you would expect it to go. So its not datastage per
se.
You havent described how you are connected to the AS400. We are just
running iseries ODBC connections over ip. Nothing special, and our
source machine is just a small test as400 as well. If you are using the
same we can send you our connection parameters.
Best Regards
Peter Nolan
Www.peternolan.com
-----Original Message-----
From: Mark Ewart-Phipps [mailto:mark@centerfieldsoftware.co.za]
Sent: 19 September 2003 14:16
To: DataStage User Group (E-mail 2)
Subject: DB2 and DataStage vs DB2 and DTS...
Hi All
I have a client who has DB2 on an AS400. In DataStage, using the DB2
Plugin or ODBC to extract the data and load it into DB2 on NT, runs at
an average of 200 rows per second. The same thing using DTS runs at
average of 4000 rows per second. Another test was done where DataStage
extracted from Sql Server and did a bulk load to Sql server using the
bulk loader stage. Again, this was done using DTS and DTS beat DataStage
badly. DataStage ran at around 2000 rows per sec while DTS ran at 8000
rows per sec. I tried to isolate the problem by making DataStage extract
from AS400/DB2 and write to a sequential file....200 rows/second. Tried
extracts from AS400/DB2 and write to a hash file ...160 rows/second.
Im at a bit of a loss because I dont know why the extraction from the
AS400/DB2 is so slow. DataStage is running on a dedicated dual cpu 4GB
ram NT box, with a 100mb direct connection between the servers.
Apparently, so I was told, DTS has a native connection to DB2 and thats
why it outperforms DataStage. This seems to imply there is something
wrong with the DataStage connection set-up to DB2.
When extracting from AS400/DB2 and DataStage server is running on an NT
box, what/where are the settings one can have a look at? Also, what must
be loaded on the DataStage server box for this connection to work?
Thanks for your input..
Mark Ewart-Phipps
Centerfield Software (Pty) Ltd
Tel +27 11 462 7003
Fax +27 11 462 7392
Cell +27 83 380 2335
-----Original Message-----
From: Peter Nolan [mailto:pnolan@attglobal.net]
Sent: Friday, September 19, 2003 11:03 PM
To: datastage-users@oliver.com
Subject: RE: DB2 and DataStage vs DB2 and DTS...
Hi Mark,
Yes, sounds like something wrong with the connection.
We have Datastage AS400/DB2->Win200/Oracle using iseries connector and
we are able to get 5,000 rows per second on a single processor test
win2000 system just over the corporate lan running at 100mb. Thats
about as fast as you would expect it to go. So its not datastage per
se.
You havent described how you are connected to the AS400. We are just
running iseries ODBC connections over ip. Nothing special, and our
source machine is just a small test as400 as well. If you are using the
same we can send you our connection parameters.
Best Regards
Peter Nolan
Www.peternolan.com
-----Original Message-----
From: Mark Ewart-Phipps [mailto:mark@centerfieldsoftware.co.za]
Sent: 19 September 2003 14:16
To: DataStage User Group (E-mail 2)
Subject: DB2 and DataStage vs DB2 and DTS...
Hi All
I have a client who has DB2 on an AS400. In DataStage, using the DB2
Plugin or ODBC to extract the data and load it into DB2 on NT, runs at
an average of 200 rows per second. The same thing using DTS runs at
average of 4000 rows per second. Another test was done where DataStage
extracted from Sql Server and did a bulk load to Sql server using the
bulk loader stage. Again, this was done using DTS and DTS beat DataStage
badly. DataStage ran at around 2000 rows per sec while DTS ran at 8000
rows per sec. I tried to isolate the problem by making DataStage extract
from AS400/DB2 and write to a sequential file....200 rows/second. Tried
extracts from AS400/DB2 and write to a hash file ...160 rows/second.
Im at a bit of a loss because I dont know why the extraction from the
AS400/DB2 is so slow. DataStage is running on a dedicated dual cpu 4GB
ram NT box, with a 100mb direct connection between the servers.
Apparently, so I was told, DTS has a native connection to DB2 and thats
why it outperforms DataStage. This seems to imply there is something
wrong with the DataStage connection set-up to DB2.
When extracting from AS400/DB2 and DataStage server is running on an NT
box, what/where are the settings one can have a look at? Also, what must
be loaded on the DataStage server box for this connection to work?
Thanks for your input..
Mark Ewart-Phipps
Centerfield Software (Pty) Ltd
Tel +27 11 462 7003
Fax +27 11 462 7392
Cell +27 83 380 2335
Sorry it took so long to get back to you. Here is the info wrt the driver:
Name Version Corporation
FileName Date
Client Access ODBC Driver (32bit) Version 8.00.04.08 IBM Corporation
CWBPDBC.DLL 2001/05/04
Thanks
-----Original Message-----
From: bob.zurek@ascentialsoftware.com
[mailto:bob.zurek@ascentialsoftware.com]
Sent: Friday, September 19, 2003 6:22 PM
To: datastage-users@oliver.com
Subject: RE: DB2 and DataStage vs DB2 and DTS...
I had that same question in my mind. Please let us know what driver you are
using and the source of the driver.
Bob Zurek
VP Advanced Technologies
Ascential Software
-----Original Message-----
From: Shawn Ramsey [mailto:SRamsey@starbucks.com]
Sent: Friday, September 19, 2003 11:45 AM
To: datastage-users@oliver.com
What type of connection is DTS using, is it using the same ODBC driver or is
it OLEDB?
-----Original Message-----
From: Mark Ewart-Phipps [mailto:mark@centerfieldsoftware.co.za]
Sent: Friday, September 19, 2003 4:16 AM
To: DataStage User Group (E-mail 2)
Subject: DB2 and DataStage vs DB2 and DTS...
Hi All
I have a client who has DB2 on an AS400. In DataStage, using the DB2
Plugin
or ODBC to extract the data and load it into DB2 on NT, runs at an
average
of 200 rows per second. The same thing using DTS runs at average of 4000
rows per second. Another test was done where DataStage extracted from
Sql
Server and did a bulk load to Sql server using the bulk loader stage.
Again,
this was done using DTS and DTS beat DataStage badly. DataStage ran at
around 2000 rows per sec while DTS ran at 8000 rows per sec. I tried to
isolate the problem by making DataStage extract from AS400/DB2 and write
to
a sequential file....200 rows/second. Tried extracts from AS400/DB2 and
write to a hash file ...160 rows/second.
Im at a bit of a loss because I dont know why the extraction from the
AS400/DB2 is so slow. DataStage is running on a dedicated dual cpu 4GB
ram
NT box, with a 100mb direct connection between the servers. Apparently,
so I
was told, DTS has a native connection to DB2 and thats why it
outperforms
DataStage. This seems to imply there is something wrong with the
DataStage
connection set-up to DB2.
When extracting from AS400/DB2 and DataStage server is running on an NT
box,
what/where are the settings one can have a look at? Also, what must be
loaded on the DataStage server box for this connection to work?
Thanks for your input..
Mark Ewart-Phipps
Centerfield Software (Pty) Ltd
Tel +27 11 462 7003
Fax +27 11 462 7392
Cell +27 83 380 2335
Name Version Corporation
FileName Date
Client Access ODBC Driver (32bit) Version 8.00.04.08 IBM Corporation
CWBPDBC.DLL 2001/05/04
Thanks
-----Original Message-----
From: bob.zurek@ascentialsoftware.com
[mailto:bob.zurek@ascentialsoftware.com]
Sent: Friday, September 19, 2003 6:22 PM
To: datastage-users@oliver.com
Subject: RE: DB2 and DataStage vs DB2 and DTS...
I had that same question in my mind. Please let us know what driver you are
using and the source of the driver.
Bob Zurek
VP Advanced Technologies
Ascential Software
-----Original Message-----
From: Shawn Ramsey [mailto:SRamsey@starbucks.com]
Sent: Friday, September 19, 2003 11:45 AM
To: datastage-users@oliver.com
What type of connection is DTS using, is it using the same ODBC driver or is
it OLEDB?
-----Original Message-----
From: Mark Ewart-Phipps [mailto:mark@centerfieldsoftware.co.za]
Sent: Friday, September 19, 2003 4:16 AM
To: DataStage User Group (E-mail 2)
Subject: DB2 and DataStage vs DB2 and DTS...
Hi All
I have a client who has DB2 on an AS400. In DataStage, using the DB2
Plugin
or ODBC to extract the data and load it into DB2 on NT, runs at an
average
of 200 rows per second. The same thing using DTS runs at average of 4000
rows per second. Another test was done where DataStage extracted from
Sql
Server and did a bulk load to Sql server using the bulk loader stage.
Again,
this was done using DTS and DTS beat DataStage badly. DataStage ran at
around 2000 rows per sec while DTS ran at 8000 rows per sec. I tried to
isolate the problem by making DataStage extract from AS400/DB2 and write
to
a sequential file....200 rows/second. Tried extracts from AS400/DB2 and
write to a hash file ...160 rows/second.
Im at a bit of a loss because I dont know why the extraction from the
AS400/DB2 is so slow. DataStage is running on a dedicated dual cpu 4GB
ram
NT box, with a 100mb direct connection between the servers. Apparently,
so I
was told, DTS has a native connection to DB2 and thats why it
outperforms
DataStage. This seems to imply there is something wrong with the
DataStage
connection set-up to DB2.
When extracting from AS400/DB2 and DataStage server is running on an NT
box,
what/where are the settings one can have a look at? Also, what must be
loaded on the DataStage server box for this connection to work?
Thanks for your input..
Mark Ewart-Phipps
Centerfield Software (Pty) Ltd
Tel +27 11 462 7003
Fax +27 11 462 7392
Cell +27 83 380 2335
We found the same type issue occurring here. We were told by our consultants
on the project who specialize in DataStage that it was due to the connection
within Datastage for the DB2 scenario not being robust enough. We got around
the issue by taking our extract files to an AS400 system that was connected
directly to DS via ODBC driver and accessing files from this system within
datastage instead of from the production AS400 we were truly pulling data
from. This solved the time issue for us. However, I would be interested in
seeing the connection parms Peter is using as a cross check to ours to see
if this is a comparable situation - may point to the culprit if we are both
configured the same and are not having time issues in that scenario.
-----Original Message-----
From: Dale Lampe [mailto:dlampe@cariboulake.com]
Sent: Monday, September 22, 2003 11:25 AM
To: datastage-users@oliver.com
Subject: RE: DB2 and DataStage vs DB2 and DTS...
We are experiencing the same issues as Mark, except we are running DataStage
6.0 on Unix - a dedicated Sun v880, 8 proc, 8 Gb ram (Solaris) against
AS400/DB2. We are using the wired protocol driver provided out-of-the-box
by Ascential (VMdb218.so). This connection typically obtains 200-400
rows/sec. We have tested connectivity with IBMs client access connection
from a WIN box and could easily get 1000+ rows/sec. The network is gigabit
and we have profiled the traffic and have determined it is not our
bottleneck. We are also at a loss as to why we cannot achieve better
performance.
-----Original Message-----
From: Peter Nolan [mailto:pnolan@attglobal.net]
Sent: Friday, September 19, 2003 11:03 PM
To: datastage-users@oliver.com
Subject: RE: DB2 and DataStage vs DB2 and DTS...
Hi Mark,
Yes, sounds like something wrong with the connection.
We have Datastage AS400/DB2->Win200/Oracle using iseries connector and
we are able to get 5,000 rows per second on a single processor test
win2000 system just over the corporate lan running at 100mb. Thats
about as fast as you would expect it to go. So its not datastage per
se.
You havent described how you are connected to the AS400. We are just
running iseries ODBC connections over ip. Nothing special, and our
source machine is just a small test as400 as well. If you are using the
same we can send you our connection parameters.
Best Regards
Peter Nolan
Www.peternolan.com
-----Original Message-----
From: Mark Ewart-Phipps [mailto:mark@centerfieldsoftware.co.za]
Sent: 19 September 2003 14:16
To: DataStage User Group (E-mail 2)
Subject: DB2 and DataStage vs DB2 and DTS...
Hi All
I have a client who has DB2 on an AS400. In DataStage, using the DB2
Plugin or ODBC to extract the data and load it into DB2 on NT, runs at
an average of 200 rows per second. The same thing using DTS runs at
average of 4000 rows per second. Another test was done where DataStage
extracted from Sql Server and did a bulk load to Sql server using the
bulk loader stage. Again, this was done using DTS and DTS beat DataStage
badly. DataStage ran at around 2000 rows per sec while DTS ran at 8000
rows per sec. I tried to isolate the problem by making DataStage extract
from AS400/DB2 and write to a sequential file....200 rows/second. Tried
extracts from AS400/DB2 and write to a hash file ...160 rows/second.
Im at a bit of a loss because I dont know why the extraction from the
AS400/DB2 is so slow. DataStage is running on a dedicated dual cpu 4GB
ram NT box, with a 100mb direct connection between the servers.
Apparently, so I was told, DTS has a native connection to DB2 and thats
why it outperforms DataStage. This seems to imply there is something
wrong with the DataStage connection set-up to DB2.
When extracting from AS400/DB2 and DataStage server is running on an NT
box, what/where are the settings one can have a look at? Also, what must
be loaded on the DataStage server box for this connection to work?
Thanks for your input..
Mark Ewart-Phipps
Centerfield Software (Pty) Ltd
Tel +27 11 462 7003
Fax +27 11 462 7392
Cell +27 83 380 2335
on the project who specialize in DataStage that it was due to the connection
within Datastage for the DB2 scenario not being robust enough. We got around
the issue by taking our extract files to an AS400 system that was connected
directly to DS via ODBC driver and accessing files from this system within
datastage instead of from the production AS400 we were truly pulling data
from. This solved the time issue for us. However, I would be interested in
seeing the connection parms Peter is using as a cross check to ours to see
if this is a comparable situation - may point to the culprit if we are both
configured the same and are not having time issues in that scenario.
-----Original Message-----
From: Dale Lampe [mailto:dlampe@cariboulake.com]
Sent: Monday, September 22, 2003 11:25 AM
To: datastage-users@oliver.com
Subject: RE: DB2 and DataStage vs DB2 and DTS...
We are experiencing the same issues as Mark, except we are running DataStage
6.0 on Unix - a dedicated Sun v880, 8 proc, 8 Gb ram (Solaris) against
AS400/DB2. We are using the wired protocol driver provided out-of-the-box
by Ascential (VMdb218.so). This connection typically obtains 200-400
rows/sec. We have tested connectivity with IBMs client access connection
from a WIN box and could easily get 1000+ rows/sec. The network is gigabit
and we have profiled the traffic and have determined it is not our
bottleneck. We are also at a loss as to why we cannot achieve better
performance.
-----Original Message-----
From: Peter Nolan [mailto:pnolan@attglobal.net]
Sent: Friday, September 19, 2003 11:03 PM
To: datastage-users@oliver.com
Subject: RE: DB2 and DataStage vs DB2 and DTS...
Hi Mark,
Yes, sounds like something wrong with the connection.
We have Datastage AS400/DB2->Win200/Oracle using iseries connector and
we are able to get 5,000 rows per second on a single processor test
win2000 system just over the corporate lan running at 100mb. Thats
about as fast as you would expect it to go. So its not datastage per
se.
You havent described how you are connected to the AS400. We are just
running iseries ODBC connections over ip. Nothing special, and our
source machine is just a small test as400 as well. If you are using the
same we can send you our connection parameters.
Best Regards
Peter Nolan
Www.peternolan.com
-----Original Message-----
From: Mark Ewart-Phipps [mailto:mark@centerfieldsoftware.co.za]
Sent: 19 September 2003 14:16
To: DataStage User Group (E-mail 2)
Subject: DB2 and DataStage vs DB2 and DTS...
Hi All
I have a client who has DB2 on an AS400. In DataStage, using the DB2
Plugin or ODBC to extract the data and load it into DB2 on NT, runs at
an average of 200 rows per second. The same thing using DTS runs at
average of 4000 rows per second. Another test was done where DataStage
extracted from Sql Server and did a bulk load to Sql server using the
bulk loader stage. Again, this was done using DTS and DTS beat DataStage
badly. DataStage ran at around 2000 rows per sec while DTS ran at 8000
rows per sec. I tried to isolate the problem by making DataStage extract
from AS400/DB2 and write to a sequential file....200 rows/second. Tried
extracts from AS400/DB2 and write to a hash file ...160 rows/second.
Im at a bit of a loss because I dont know why the extraction from the
AS400/DB2 is so slow. DataStage is running on a dedicated dual cpu 4GB
ram NT box, with a 100mb direct connection between the servers.
Apparently, so I was told, DTS has a native connection to DB2 and thats
why it outperforms DataStage. This seems to imply there is something
wrong with the DataStage connection set-up to DB2.
When extracting from AS400/DB2 and DataStage server is running on an NT
box, what/where are the settings one can have a look at? Also, what must
be loaded on the DataStage server box for this connection to work?
Thanks for your input..
Mark Ewart-Phipps
Centerfield Software (Pty) Ltd
Tel +27 11 462 7003
Fax +27 11 462 7392
Cell +27 83 380 2335
We loaded the DB2 stage patch from Ascential (DataStage 6 talking to DB2
version
. Then we put DB2 onto an Intel box and did and extract from DB2
and a load to DB2. It ran at 14,000 rows per second. So it seems the problem
is with the AS400, not necessarily with DB2. Are there any specific settings
the AS400 needs for DataStage to perform well? Not knowing the AS400 at all,
Im thinking along the lines of Unix where there are specific kernel
parameters which must be set.
PS:
I think its a good idea to have a look at the connection parms Peter is
using and do a cross check. Im not sure how much of a role the iseries ODBC
drivers play. Peter could you send me those connection parameters. It would
be greatly appreciated.
Thanks.
-----Original Message-----
From: Lori Hainey [mailto:lori.hainey@york.com]
Sent: Tuesday, September 23, 2003 9:09 PM
To: datastage-users@oliver.com
Subject: RE: DB2 and DataStage vs DB2 and DTS...
We found the same type issue occurring here. We were told by our consultants
on the project who specialize in DataStage that it was due to the connection
within Datastage for the DB2 scenario not being robust enough. We got around
the issue by taking our extract files to an AS400 system that was connected
directly to DS via ODBC driver and accessing files from this system within
datastage instead of from the production AS400 we were truly pulling data
from. This solved the time issue for us. However, I would be interested in
seeing the connection parms Peter is using as a cross check to ours to see
if this is a comparable situation - may point to the culprit if we are both
configured the same and are not having time issues in that scenario.
-----Original Message-----
From: Dale Lampe [mailto:dlampe@cariboulake.com]
Sent: Monday, September 22, 2003 11:25 AM
To: datastage-users@oliver.com
Subject: RE: DB2 and DataStage vs DB2 and DTS...
We are experiencing the same issues as Mark, except we are running DataStage
6.0 on Unix - a dedicated Sun v880, 8 proc, 8 Gb ram (Solaris) against
AS400/DB2. We are using the wired protocol driver provided out-of-the-box
by Ascential (VMdb218.so). This connection typically obtains 200-400
rows/sec. We have tested connectivity with IBMs client access connection
from a WIN box and could easily get 1000+ rows/sec. The network is gigabit
and we have profiled the traffic and have determined it is not our
bottleneck. We are also at a loss as to why we cannot achieve better
performance.
-----Original Message-----
From: Peter Nolan [mailto:pnolan@attglobal.net]
Sent: Friday, September 19, 2003 11:03 PM
To: datastage-users@oliver.com
Subject: RE: DB2 and DataStage vs DB2 and DTS...
Hi Mark,
Yes, sounds like something wrong with the connection.
We have Datastage AS400/DB2->Win200/Oracle using iseries connector and
we are able to get 5,000 rows per second on a single processor test
win2000 system just over the corporate lan running at 100mb. Thats
about as fast as you would expect it to go. So its not datastage per
se.
You havent described how you are connected to the AS400. We are just
running iseries ODBC connections over ip. Nothing special, and our
source machine is just a small test as400 as well. If you are using the
same we can send you our connection parameters.
Best Regards
Peter Nolan
Www.peternolan.com
-----Original Message-----
From: Mark Ewart-Phipps [mailto:mark@centerfieldsoftware.co.za]
Sent: 19 September 2003 14:16
To: DataStage User Group (E-mail 2)
Subject: DB2 and DataStage vs DB2 and DTS...
Hi All
I have a client who has DB2 on an AS400. In DataStage, using the DB2
Plugin or ODBC to extract the data and load it into DB2 on NT, runs at
an average of 200 rows per second. The same thing using DTS runs at
average of 4000 rows per second. Another test was done where DataStage
extracted from Sql Server and did a bulk load to Sql server using the
bulk loader stage. Again, this was done using DTS and DTS beat DataStage
badly. DataStage ran at around 2000 rows per sec while DTS ran at 8000
rows per sec. I tried to isolate the problem by making DataStage extract
from AS400/DB2 and write to a sequential file....200 rows/second. Tried
extracts from AS400/DB2 and write to a hash file ...160 rows/second.
Im at a bit of a loss because I dont know why the extraction from the
AS400/DB2 is so slow. DataStage is running on a dedicated dual cpu 4GB
ram NT box, with a 100mb direct connection between the servers.
Apparently, so I was told, DTS has a native connection to DB2 and thats
why it outperforms DataStage. This seems to imply there is something
wrong with the DataStage connection set-up to DB2.
When extracting from AS400/DB2 and DataStage server is running on an NT
box, what/where are the settings one can have a look at? Also, what must
be loaded on the DataStage server box for this connection to work?
Thanks for your input..
Mark Ewart-Phipps
Centerfield Software (Pty) Ltd
Tel +27 11 462 7003
Fax +27 11 462 7392
Cell +27 83 380 2335
version
. Then we put DB2 onto an Intel box and did and extract from DB2and a load to DB2. It ran at 14,000 rows per second. So it seems the problem
is with the AS400, not necessarily with DB2. Are there any specific settings
the AS400 needs for DataStage to perform well? Not knowing the AS400 at all,
Im thinking along the lines of Unix where there are specific kernel
parameters which must be set.
PS:
I think its a good idea to have a look at the connection parms Peter is
using and do a cross check. Im not sure how much of a role the iseries ODBC
drivers play. Peter could you send me those connection parameters. It would
be greatly appreciated.
Thanks.
-----Original Message-----
From: Lori Hainey [mailto:lori.hainey@york.com]
Sent: Tuesday, September 23, 2003 9:09 PM
To: datastage-users@oliver.com
Subject: RE: DB2 and DataStage vs DB2 and DTS...
We found the same type issue occurring here. We were told by our consultants
on the project who specialize in DataStage that it was due to the connection
within Datastage for the DB2 scenario not being robust enough. We got around
the issue by taking our extract files to an AS400 system that was connected
directly to DS via ODBC driver and accessing files from this system within
datastage instead of from the production AS400 we were truly pulling data
from. This solved the time issue for us. However, I would be interested in
seeing the connection parms Peter is using as a cross check to ours to see
if this is a comparable situation - may point to the culprit if we are both
configured the same and are not having time issues in that scenario.
-----Original Message-----
From: Dale Lampe [mailto:dlampe@cariboulake.com]
Sent: Monday, September 22, 2003 11:25 AM
To: datastage-users@oliver.com
Subject: RE: DB2 and DataStage vs DB2 and DTS...
We are experiencing the same issues as Mark, except we are running DataStage
6.0 on Unix - a dedicated Sun v880, 8 proc, 8 Gb ram (Solaris) against
AS400/DB2. We are using the wired protocol driver provided out-of-the-box
by Ascential (VMdb218.so). This connection typically obtains 200-400
rows/sec. We have tested connectivity with IBMs client access connection
from a WIN box and could easily get 1000+ rows/sec. The network is gigabit
and we have profiled the traffic and have determined it is not our
bottleneck. We are also at a loss as to why we cannot achieve better
performance.
-----Original Message-----
From: Peter Nolan [mailto:pnolan@attglobal.net]
Sent: Friday, September 19, 2003 11:03 PM
To: datastage-users@oliver.com
Subject: RE: DB2 and DataStage vs DB2 and DTS...
Hi Mark,
Yes, sounds like something wrong with the connection.
We have Datastage AS400/DB2->Win200/Oracle using iseries connector and
we are able to get 5,000 rows per second on a single processor test
win2000 system just over the corporate lan running at 100mb. Thats
about as fast as you would expect it to go. So its not datastage per
se.
You havent described how you are connected to the AS400. We are just
running iseries ODBC connections over ip. Nothing special, and our
source machine is just a small test as400 as well. If you are using the
same we can send you our connection parameters.
Best Regards
Peter Nolan
Www.peternolan.com
-----Original Message-----
From: Mark Ewart-Phipps [mailto:mark@centerfieldsoftware.co.za]
Sent: 19 September 2003 14:16
To: DataStage User Group (E-mail 2)
Subject: DB2 and DataStage vs DB2 and DTS...
Hi All
I have a client who has DB2 on an AS400. In DataStage, using the DB2
Plugin or ODBC to extract the data and load it into DB2 on NT, runs at
an average of 200 rows per second. The same thing using DTS runs at
average of 4000 rows per second. Another test was done where DataStage
extracted from Sql Server and did a bulk load to Sql server using the
bulk loader stage. Again, this was done using DTS and DTS beat DataStage
badly. DataStage ran at around 2000 rows per sec while DTS ran at 8000
rows per sec. I tried to isolate the problem by making DataStage extract
from AS400/DB2 and write to a sequential file....200 rows/second. Tried
extracts from AS400/DB2 and write to a hash file ...160 rows/second.
Im at a bit of a loss because I dont know why the extraction from the
AS400/DB2 is so slow. DataStage is running on a dedicated dual cpu 4GB
ram NT box, with a 100mb direct connection between the servers.
Apparently, so I was told, DTS has a native connection to DB2 and thats
why it outperforms DataStage. This seems to imply there is something
wrong with the DataStage connection set-up to DB2.
When extracting from AS400/DB2 and DataStage server is running on an NT
box, what/where are the settings one can have a look at? Also, what must
be loaded on the DataStage server box for this connection to work?
Thanks for your input..
Mark Ewart-Phipps
Centerfield Software (Pty) Ltd
Tel +27 11 462 7003
Fax +27 11 462 7392
Cell +27 83 380 2335
Hi Guys,
Peter asked me to describe the connection we use here to connect to the DB2
sitting on the AS400, so here goes:
Driver used: iSeries Access ODBC Driver 9.00.02.02
If you open ODBC Manager and select this driver, I changed the following
settings from the deafult (uur setup is on a Win2k machine):
Server tab: Check the Read only option
Under the Advanced tab: Read uncommitted checked
Packages tab: Check the Enable extended dynamic support
Performance tab:Check the Enable data compression
Under the Advanced tab: Check the Use blocking (32KB)
Please remember that I am not AS400 expert either, but with our ODBC
connection we get +- 7800 rows / second, so you should expect the same.
Hope this helps! If the e-mail is a bit unclear to understand, let me know
I I will send you a private e-mail with some screenshots, but I did not
want to put that onto the oliver list.
Cheers,
Tom.
Original Message:
-----------------
From: Mark Ewart-Phipps mark@centerfieldsoftware.co.za
Date: Thu, 25 Sep 2003 09:23:22 +0200
To: datastage-users@oliver.com
Subject: RE: DB2 and DataStage vs DB2 and DTS...
We loaded the DB2 stage patch from Ascential (DataStage 6 talking to DB2
version. Then we put DB2 onto an Intel box and did and extract from DB2
and a load to DB2. It ran at 14,000 rows per second. So it seems the problem
is with the AS400, not necessarily with DB2. Are there any specific settings
the AS400 needs for DataStage to perform well? Not knowing the AS400 at all,
Im thinking along the lines of Unix where there are specific kernel
parameters which must be set.
PS:
I think its a good idea to have a look at the connection parms Peter is
using and do a cross check. Im not sure how much of a role the iseries ODBC
drivers play. Peter could you send me those connection parameters. It would
be greatly appreciated.
Thanks.
-----Original Message-----
From: Lori Hainey [mailto:lori.hainey@york.com]
Sent: Tuesday, September 23, 2003 9:09 PM
To: datastage-users@oliver.com
Subject: RE: DB2 and DataStage vs DB2 and DTS...
We found the same type issue occurring here. We were told by our consultants
on the project who specialize in DataStage that it was due to the connection
within Datastage for the DB2 scenario not being robust enough. We got around
the issue by taking our extract files to an AS400 system that was connected
directly to DS via ODBC driver and accessing files from this system within
datastage instead of from the production AS400 we were truly pulling data
from. This solved the time issue for us. However, I would be interested in
seeing the connection parms Peter is using as a cross check to ours to see
if this is a comparable situation - may point to the culprit if we are both
configured the same and are not having time issues in that scenario.
-----Original Message-----
From: Dale Lampe [mailto:dlampe@cariboulake.com]
Sent: Monday, September 22, 2003 11:25 AM
To: datastage-users@oliver.com
Subject: RE: DB2 and DataStage vs DB2 and DTS...
We are experiencing the same issues as Mark, except we are running DataStage
6.0 on Unix - a dedicated Sun v880, 8 proc, 8 Gb ram (Solaris) against
AS400/DB2. We are using the wired protocol driver provided out-of-the-box
by Ascential (VMdb218.so). This connection typically obtains 200-400
rows/sec. We have tested connectivity with IBMs client access connection
from a WIN box and could easily get 1000+ rows/sec. The network is gigabit
and we have profiled the traffic and have determined it is not our
bottleneck. We are also at a loss as to why we cannot achieve better
performance.
-----Original Message-----
From: Peter Nolan [mailto:pnolan@attglobal.net]
Sent: Friday, September 19, 2003 11:03 PM
To: datastage-users@oliver.com
Subject: RE: DB2 and DataStage vs DB2 and DTS...
Hi Mark,
Yes, sounds like something wrong with the connection.
We have Datastage AS400/DB2->Win200/Oracle using iseries connector and
we are able to get 5,000 rows per second on a single processor test
win2000 system just over the corporate lan running at 100mb. Thats
about as fast as you would expect it to go. So its not datastage per
se.
You havent described how you are connected to the AS400. We are just
running iseries ODBC connections over ip. Nothing special, and our
source machine is just a small test as400 as well. If you are using the
same we can send you our connection parameters.
Best Regards
Peter Nolan
Www.peternolan.com
-----Original Message-----
From: Mark Ewart-Phipps [mailto:mark@centerfieldsoftware.co.za]
Sent: 19 September 2003 14:16
To: DataStage User Group (E-mail 2)
Subject: DB2 and DataStage vs DB2 and DTS...
Hi All
I have a client who has DB2 on an AS400. In DataStage, using the DB2
Plugin or ODBC to extract the data and load it into DB2 on NT, runs at
an average of 200 rows per second. The same thing using DTS runs at
average of 4000 rows per second. Another test was done where DataStage
extracted from Sql Server and did a bulk load to Sql server using the
bulk loader stage. Again, this was done using DTS and DTS beat DataStage
badly. DataStage ran at around 2000 rows per sec while DTS ran at 8000
rows per sec. I tried to isolate the problem by making DataStage extract
from AS400/DB2 and write to a sequential file....200 rows/second. Tried
extracts from AS400/DB2 and write to a hash file ...160 rows/second.
Im at a bit of a loss because I dont know why the extraction from the
AS400/DB2 is so slow. DataStage is running on a dedicated dual cpu 4GB
ram NT box, with a 100mb direct connection between the servers.
Apparently, so I was told, DTS has a native connection to DB2 and thats
why it outperforms DataStage. This seems to imply there is something
wrong with the DataStage connection set-up to DB2.
When extracting from AS400/DB2 and DataStage server is running on an NT
box, what/where are the settings one can have a look at? Also, what must
be loaded on the DataStage server box for this connection to work?
Thanks for your input..
Mark Ewart-Phipps
Centerfield Software (Pty) Ltd
Tel +27 11 462 7003
Fax +27 11 462 7392
Cell +27 83 380 2335
--------------------------------------------------------------------
mail2web - Check your email from the web at
http://mail2web.com/ .
Peter asked me to describe the connection we use here to connect to the DB2
sitting on the AS400, so here goes:
Driver used: iSeries Access ODBC Driver 9.00.02.02
If you open ODBC Manager and select this driver, I changed the following
settings from the deafult (uur setup is on a Win2k machine):
Server tab: Check the Read only option
Under the Advanced tab: Read uncommitted checked
Packages tab: Check the Enable extended dynamic support
Performance tab:Check the Enable data compression
Under the Advanced tab: Check the Use blocking (32KB)
Please remember that I am not AS400 expert either, but with our ODBC
connection we get +- 7800 rows / second, so you should expect the same.
Hope this helps! If the e-mail is a bit unclear to understand, let me know
I I will send you a private e-mail with some screenshots, but I did not
want to put that onto the oliver list.
Cheers,
Tom.
Original Message:
-----------------
From: Mark Ewart-Phipps mark@centerfieldsoftware.co.za
Date: Thu, 25 Sep 2003 09:23:22 +0200
To: datastage-users@oliver.com
Subject: RE: DB2 and DataStage vs DB2 and DTS...
We loaded the DB2 stage patch from Ascential (DataStage 6 talking to DB2
version
. Then we put DB2 onto an Intel box and did and extract from DB2and a load to DB2. It ran at 14,000 rows per second. So it seems the problem
is with the AS400, not necessarily with DB2. Are there any specific settings
the AS400 needs for DataStage to perform well? Not knowing the AS400 at all,
Im thinking along the lines of Unix where there are specific kernel
parameters which must be set.
PS:
I think its a good idea to have a look at the connection parms Peter is
using and do a cross check. Im not sure how much of a role the iseries ODBC
drivers play. Peter could you send me those connection parameters. It would
be greatly appreciated.
Thanks.
-----Original Message-----
From: Lori Hainey [mailto:lori.hainey@york.com]
Sent: Tuesday, September 23, 2003 9:09 PM
To: datastage-users@oliver.com
Subject: RE: DB2 and DataStage vs DB2 and DTS...
We found the same type issue occurring here. We were told by our consultants
on the project who specialize in DataStage that it was due to the connection
within Datastage for the DB2 scenario not being robust enough. We got around
the issue by taking our extract files to an AS400 system that was connected
directly to DS via ODBC driver and accessing files from this system within
datastage instead of from the production AS400 we were truly pulling data
from. This solved the time issue for us. However, I would be interested in
seeing the connection parms Peter is using as a cross check to ours to see
if this is a comparable situation - may point to the culprit if we are both
configured the same and are not having time issues in that scenario.
-----Original Message-----
From: Dale Lampe [mailto:dlampe@cariboulake.com]
Sent: Monday, September 22, 2003 11:25 AM
To: datastage-users@oliver.com
Subject: RE: DB2 and DataStage vs DB2 and DTS...
We are experiencing the same issues as Mark, except we are running DataStage
6.0 on Unix - a dedicated Sun v880, 8 proc, 8 Gb ram (Solaris) against
AS400/DB2. We are using the wired protocol driver provided out-of-the-box
by Ascential (VMdb218.so). This connection typically obtains 200-400
rows/sec. We have tested connectivity with IBMs client access connection
from a WIN box and could easily get 1000+ rows/sec. The network is gigabit
and we have profiled the traffic and have determined it is not our
bottleneck. We are also at a loss as to why we cannot achieve better
performance.
-----Original Message-----
From: Peter Nolan [mailto:pnolan@attglobal.net]
Sent: Friday, September 19, 2003 11:03 PM
To: datastage-users@oliver.com
Subject: RE: DB2 and DataStage vs DB2 and DTS...
Hi Mark,
Yes, sounds like something wrong with the connection.
We have Datastage AS400/DB2->Win200/Oracle using iseries connector and
we are able to get 5,000 rows per second on a single processor test
win2000 system just over the corporate lan running at 100mb. Thats
about as fast as you would expect it to go. So its not datastage per
se.
You havent described how you are connected to the AS400. We are just
running iseries ODBC connections over ip. Nothing special, and our
source machine is just a small test as400 as well. If you are using the
same we can send you our connection parameters.
Best Regards
Peter Nolan
Www.peternolan.com
-----Original Message-----
From: Mark Ewart-Phipps [mailto:mark@centerfieldsoftware.co.za]
Sent: 19 September 2003 14:16
To: DataStage User Group (E-mail 2)
Subject: DB2 and DataStage vs DB2 and DTS...
Hi All
I have a client who has DB2 on an AS400. In DataStage, using the DB2
Plugin or ODBC to extract the data and load it into DB2 on NT, runs at
an average of 200 rows per second. The same thing using DTS runs at
average of 4000 rows per second. Another test was done where DataStage
extracted from Sql Server and did a bulk load to Sql server using the
bulk loader stage. Again, this was done using DTS and DTS beat DataStage
badly. DataStage ran at around 2000 rows per sec while DTS ran at 8000
rows per sec. I tried to isolate the problem by making DataStage extract
from AS400/DB2 and write to a sequential file....200 rows/second. Tried
extracts from AS400/DB2 and write to a hash file ...160 rows/second.
Im at a bit of a loss because I dont know why the extraction from the
AS400/DB2 is so slow. DataStage is running on a dedicated dual cpu 4GB
ram NT box, with a 100mb direct connection between the servers.
Apparently, so I was told, DTS has a native connection to DB2 and thats
why it outperforms DataStage. This seems to imply there is something
wrong with the DataStage connection set-up to DB2.
When extracting from AS400/DB2 and DataStage server is running on an NT
box, what/where are the settings one can have a look at? Also, what must
be loaded on the DataStage server box for this connection to work?
Thanks for your input..
Mark Ewart-Phipps
Centerfield Software (Pty) Ltd
Tel +27 11 462 7003
Fax +27 11 462 7392
Cell +27 83 380 2335
--------------------------------------------------------------------
mail2web - Check your email from the web at
http://mail2web.com/ .