Exporting Data to AS400 from SQL2005 with SSIS is SLOWWWWW
-
I have hit a brick wall and need help.
I am getting terrible performance inserting records from a SQL table into an AS400 table.
For 45,000 records it is taking close to an hour and a half which is not workable.
After some initial trial and error I was able to get both the following providers to function.
But there seemed to be no real difference so SLOWWWWW.
* IBM DB2 UDB for iSeries IBMDASQL OLE DB Provider
* Microsoft OLE DB Provider for DB2 (One needs Enterprise or Developer SQL Editions)
I even tried SQL to Text to AS400 with no significant performance increase.
I even set the ValidateExternalMetadata = False, because I read somewhere that would help to no avail.
Any help would make me a hero and forever grateful.
Rick
-
Is this a straight import or do you have any data transformations?
-
First off, I've never had to move data from SQL Server 2005 to an AS400 system, but I have had to go between SQL Server 2005 and other non-SQL Server systems where throughput has been a concern and challenge. Most of the time, your limiting factor is the connection provider. With that said, I'm a bit surprised that the OLE DB connection to AS400 is that slow.
What other options, native to your AS400 system, do you have for loading the data quickly? The reason I ask is that you could use SSIS to create the necessary files to invoke an import process on the target system. For example, I had to interface a SQL Server 2005 with a Teradata warehouse. For accessing the Teradata system, I have 2 options for data providers, an ODBC connection or a .NET connection, both of which are painfully slow. Teradata has a utility native to it called Fast Export. I configured my SSIS package to create the Fast Export files on the file system and then I use an Execute Process Task in my control flow to call cmd.exe to invoke the Fast Export utility. It works pretty slick and beats the pants off of the ODBC and .NET providers in terms of speed. It's a night and day difference.
Not sure if that will help you, but I guess my point is that SSIS is quite versatile in terms of what it can do so you may have to think out of the box on how to interface it with a non SQL Server system and take advantage of any proprietary import/export tools native to your target system.
-
Steveb.Thanks for the quick responce,There are no transformations just a simple data flow task with a SQL server 2005 data source and a AS400 destination. I would hate to think how long it would take if I had to do any Lookups or Merge Joins.
-
Have you tried the commercial CozyRoc ODBC Destination ? If your ODBC driver supports the bulk-load interace, it will provide much better performance. If you are using DB2, you may also check CozyRoc DB2 Destination. It is 20x faster compared to the regular OLEDB Destination.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
Rick Bailey-440858 (1/6/2010)
Steveb.Thanks for the quick responce,There are no transformations just a simple data flow task with a SQL server 2005 data source and a AS400 destination. I would hate to think how long it would take if I had to do any Lookups or Merge Joins.The IBM native ODBC driver works so you need to change from OLE DB to ODBC if you are running it as a Job SQL Server 2005 require Admin permissions in AS400 also.
Kind regards,
Gift Peddie -
John, Thanks for the fast reply. I have not really looked into any third party tool or proprietary import/export tools yet and I have to admit I have been only thinking in the box vs. out. AS400 as a data source and SQL native client as a destination works acceptably. So maybe I need to work with the AS400 people to see if they have some settings that are slowing up the insert process. As you can tell from my last statement all I know about AS400 is basically how to spell it.
-
Rick Bailey-440858 (1/6/2010)
John, Thanks for the fast reply. I have not really looked into any third party tool or proprietary import/export tools yet and I have to admit I have been only thinking in the box vs. out. AS400 as a data source and SQL native client as a destination works acceptably. So maybe I need to work with the AS400 people to see if they have some settings that are slowing up the insert process. As you can tell from my last statement all I know about AS400 is basically how to spell it.No settings will help. The issue is the standard OLEDB Destination component. Unless the AS400 driver supports the special OLEDB fast load interface, the insert process is loading records with INSERT INTO one-by-one. This is the slow part.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
SSChasing Mays,
I am trying to give the cozyRoc component will little success. I installed IBM Data Server Runtime Client as directed. I had a live web support chat with a technicial and he informed me that in Order to use the CozyRoc DB2 Destination compoment I needed to use the ADO.NET IBM DB2 .NET Data Provider.
What is the trick to setting up this connection manager. The technician referrred me to IBM site for setting this up http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.dndp.doc/htm/frlrfIBMDataDB2.htm but I am not getting anywhere.
The AS400 destination server is :ORTPROD
The Library (Catalog) is TV81MMUSRF
And I have a working user and password.
Any help or documentaion you can provide would be really appreciated.
Thanks in Advance,
Rick
-
Is there an error message you are getting? Have you tried to make a post in the IBM DB2 forum?
Try posting your question here.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
SSChasing Mays,
I get the same error when I test the connection manager in SSIS using either of the following IBM . net Providers:
.Net Providers\IBM DB2 .NET Data Provider 9.5.0 or
.Net Providers for OleDb\IBM OLE DB Provider for DB2
-----------------------------------------------------------------------------------------------
Native OLE DB\IBM OLE DB Provider for DB2 SQL30081N A communication error has been detected.
Communication protocol being used: "TCP/IP". Communication API being used: “SOCKETS".
Location where the error was detected: "10.87.15.153".
Communication function detecting the error: "connect". Protocol specific error code(s): "10061", "*", "*". SQLSTATE=08001
-----------------------------------------------------------------------------------------------
Thanks in Advance.
I only briefly looked at the IBM DB2 Forum with nothing even remotely associated with SSIS.
I will post my question however.
-
10061 error code means connection refused. I think you will find this discussion useful. Verify if you have the correct port specified to your server.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
I, too, am having the same problem. I have over a million records I need to load from SQL2005 using SSIS to an AS400. It is taking 5 or 6 minutes to transfer a little under 8000 rows. I have tried using the open rowset using fast load. This doesn't work at all. I've checked the AS400 file and there are no triggers, keys or logicals to slow it down.
Have you had any more luck?
-
Brad,
I have had no luck but a ticket has been put in to IBM to see if there is something on the AS400 that is gumming up the works. See Below for an update as to where I am with this now.
The Following providers can connect but poor (dreadful) performance:
IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider
IBM DB2 UDB for iSeries IBMDASQL OLE DB Provider
Microsoft OLE DB Provider for DB2
iSeries Access for Windows ODBC data source
The following Providers I can not seem to get to work:
IBM OLE DB Provider for DB2
Connection String = Data Source=TV81mmusrf;User ID=rtcim;Provider=IBMDADB2.DB2COPY1;Persist Security Info=True;Location=ORTPROD;Extended Properties=;
IBM DB2 .NET Data Provider
ConnectionString = Server=ortprod;User ID=rtcim;Database=tv81mmusrf;
Both Generate the folowing error:
Test connection failed because of an error in initializing provider. SQL30081N A communication error has
been detected. Communication protocol being used: "TCP/IP". Communication API being used:
"SOCKETS". Location where the error was detected: "xx.xx.xx.xx". Communication function detecting
the error: "connect". Protocol specific error code(s): "10061", "*", "*". SQLSTATE=08001
Which is a connection refusal I am told.
I was told that the poor performance was due to a record by record insert vs. a bulk insert.
OK.... but why can I use the same method to put the same table in an Oracle and SQL database in minutes not hours ?
I will keep you posted please let me know if you find anything on your end.
-
Rick Bailey-440858 (1/13/2010)I was told that the poor performance was due to a record by record insert vs. a bulk insert.
OK.... but why can I use the same method to put the same table in an Oracle and SQL database in minutes not hours ?
I will keep you posted please let me know if you find anything on your end.
Is there some sort of bulk insert utility for the DB2 database on the AS400?
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply