May 28, 2009 at 8:02 am
In our old sql2000 system we imported a lot of AS400 tables using DTS created by import/export wizard. This worked very well, using an IBM Client Access driver and DSN.
We have a new SQL2005 (64 bit) setup which will be replacing the SQL2000 system.
For some reason, we cannot seem to Import export wizard to do this on our SQL2005 system. Client Access is installed and I created a DSN on the server, but Client Access does not show as one of the choices in import/export wizard.
Following an example I found on the internet, I did succeed in linking the AS400 using the provider: "IBM DB2 UDB for series IBMDASQL OLEDB"; and using the SELECT INTO and the openquery function. This works but is very, very slow, it takes about 4-5 times as long to transfer a table as DTS/Client Access on SQL2000. At the moment it is so slow that I am using the SQL2000 DTS to transfer from the 400, then doing a SELECT INTO from the SQL2000 machine to the 2005. This actually takes MUCH less time then getting the data directly.
This will not do for production. Perhaps if I can use Client Access as I did in SQL2000, but I'm not sure how to make that connection.
...
-- FORTRAN manual for Xerox Computers --
May 28, 2009 at 9:21 am
We have the same configuration: AS/400 and SQLServer 2005 x64.
ODBC has always been slow in our environment, so we had to buy a OLEDB provider, which is much faster.
Now we're using HitOLEDB provider for AS/400 and I must say that it has no problem and runs smoothly.
Did you try reading data into Excel or text file from your DSN, in order to exclude SQL Server from the possibile causes?
For what concerns Import/Export wizard, it doesn't recognise x64 providers if not run inside the SQLServer machine directly from command line (DTSWizard.exe), not from SSMS.
Hope this helps
Gianluca
-- Gianluca Sartori
May 28, 2009 at 9:30 am
Gianluca Sartori (5/28/2009)
We have the same configuration: AS/400 and SQLServer 2005 x64.ODBC has always been slow in our environment, so we had to buy a OLEDB provider, which is much faster.
Now we're using HitOLEDB provider for AS/400 and I must say that it has no problem and runs smoothly.
Did you try reading data into Excel or text file from your DSN, in order to exclude SQL Server from the possibile causes?
For what concerns Import/Export wizard, it doesn't recognise x64 providers if not run inside the SQLServer machine directly from command line (DTSWizard.exe), not from SSMS.
Hope this helps
Gianluca
Thanks for your feedback.
Actually we have been unable to connect to DSN from SQL, as the Client Access (perhaps because it's 32 bit) and resultant DSN are not visible from SQL (at least as far as I can figure out). I'm wondering if client access will work as well as it does with SQL2000 but I can't seem to use it.
If we use the same syntax to transfer the table from the linked SQL2000, the 3.8M row table takes about 5 minutes. From the AS400/OLEDB it takes over an hour.
...
-- FORTRAN manual for Xerox Computers --
May 28, 2009 at 9:35 am
I'm sorry I can't help you any further, 'cause I'm no AS400 expert, but I suggest you consider buying an OLEDB provider for this task.
-- Gianluca Sartori
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply