Importing data from AS/400

Question

Importing data from AS/400

aaa121

SSCertifiable

Points: 5902
More actions
November 7, 2009 at 12:32 pm

#216524

I am soon going to be looking at incorporating an AS/400 mainframe system as a new data source for our SQL Server 2008 Data Warehouse.
Are you guys able to throw in any comments about things I should be aware of? Any gotchas or best practices?
Thanks

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gift Peddie SSC Guru Points: 73570 More actions · Answer 1

aaa-322853 (11/7/2009)
I am soon going to be looking at incorporating an AS/400 mainframe system as a new data source for our SQL Server 2008 Data Warehouse.
Are you guys able to throw in any comments about things I should be aware of? Any gotchas or best practices?
Thanks

Admin permissions required in both AS400 and SQL Server to use the Agent to automate the transfer new to SQL Server 2005. I have an existing thread that covers known issues in the automation but the actual transfer existed since SQL Server 7.0 using DTS.

Kind regards,
Gift Peddie

aaa121 SSCertifiable Points: 5902 More actions · Answer 2

Thanks Gift,

In terms of connectivity I know there are a couple of OLEDB drivers to use within SSIS. An IBM one and an MS one which is supposedly better but only comes with enterprise edition, am I right on this one?

Gift Peddie SSC Guru Points: 73570 More actions · Answer 3

aaa-322853 (11/8/2009)
Thanks Gift,
In terms of connectivity I know there are a couple of OLEDB drivers to use within SSIS. An IBM one and an MS one which is supposedly better but only comes with enterprise edition, am I right on this one?

Yes most users say the Microsoft OLE DB driver is better but you also have the option to use the IBM ODBC driver which also works and in SQL Server 2008 you could also try the IBM native ADO.NET provider which you can download from IBM site. Check the links below for some options and the Microsoft OLE DB driver download.

http://www.sqlservercentral.com/Forums/Topic734388-148-1.aspx

http://www.microsoft.com/downloads/details.aspx?familyid=50b97994-8453-4998-8226-fa42ec403d17&displaylang=en

Kind regards,
Gift Peddie

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 4

I have a SQL 2005 Linked Server defined to our AS400 that uses the IBM ODBC driver. However, we use it sparingly and do not use it to move a lot of data. The AS400 is our core transactional system and we don't want to bog it down with user reporting queries generated from outside of our core application. We dump out a DB snapshot nightly in another fashion that we query against. But that said, the ODBC driver has worked well for us when we need real-time data from the AS400 DB2 database.

Gift Peddie SSC Guru Points: 73570 More actions · Answer 5

Ed-86789 (11/9/2009)
I have a SQL 2005 Linked Server defined to our AS400 that uses the IBM ODBC driver. However, we use it sparingly and do not use it to move a lot of data. The AS400 is our core transactional system and we don't want to bog it down with user reporting queries generated from outside of our core application. We dump out a DB snapshot nightly in another fashion that we query against. But that said, the ODBC driver has worked well for us when we need real-time data from the AS400 DB2 database.

I don't remember if the driver used in our system was third party but we used both SQL Server 7.0 and SQL Server 2000 to move transactional data out of DB2 AS400 five hours a day five days a week. However it was sent to another OLTP system bank at home.

Kind regards,
Gift Peddie