Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Import Data from AS400 to SQL Server

The process to import data from AS400  uses a SQL Server  Linked server. The Linked Server points to an ODBC Connection.

 The ODBC connection (DSN) uses drivers to enable the connection between SQL Server and AS/400.

 Once the connection is established , t-sql statements extract data from the AS400 files.

Pre checks:

Ensure the ODBC connection  is  created on the server and the Linked server called MYAS400_ODBC (for this example)  is created to point at that ODBC connection.

In the example below , the ODBC is in the Data Source section “USP37899”

As400 sql 

Having the right drivers for the ODBC is important. Assuming security and connectivity is OK , to the SQL Server and AS/400 , the wrong drivers for the ODBC is the most likely reason for failure. Find some other common errors and workarounds on SQL Server Import Data from DB2 AS400 iSeries

 Methods available for ODBC set up include:

a)       Install iSeries Navigator on the SQL server. This installs the relevant drivers

b)       Use  ODBC Driver for AS/400 from IBM

c)       OLE DB Provider for AS/400 from Microsoft

2. Import the Data from AS400 to SQL Server using OPENQUERY

OPENQUERY executes the  query on the configured linked server.

 

 

      INSERT STAGING_TABLE SELECT *
			FROM OPENQUERY (MYAS400_ODBC,''select              
				UNIQUE_ID,
				DOCUMENT_TITLE ,
				ACTIONED ,
				ACTIONED_BY ,
				ACTION_REQUIRED ,
				CATEGORY ,
				CREATED_BY ,
				FROM MYI90FILe.MYI90name

           
Author: Jack Vamvas (http://www.sqlserver-dba.com)

Comments

Posted by Fabrizio Faleni on 17 September 2011

Thanks for your article, Jack! A lot of young DBAs think of AS400 to some sort of dinosaur, so I'm sure this might help a lot of people. Here are my two cents about this.

As Phil Factor recently pointed out (www.sqlservercentral.com/.../8156178), OLEDB Will be deprecated in favor of ODBC for accessing databases of "alien" technolgoies.

So AS/400 (aka Sytem i, aka iSeries) ODBC will be the only technology: fortunately System i ODBC Drivers  have always been VERY performant and satisfactory.

For those who might be interested in more info, I suggest some interesting resources here:

Joe Pluta's "Accessing i Data from MS SQL Server"

www.mcpressonline.com/.../accessing-i-data-from-ms-sql-server.html

On how to setup iSeries ODBC Drivers

IBM Infocenter

publib.boulder.ibm.com/.../index.jsp

Shiva Blog Center

shivakamathblog.blogspot.com/.../configure-odbc-to-access-iseriesas400.html

Hope this helps!

Posted by jim 98251 on 8 March 2013

Lots of good info here - thank you. By chance would you happen to know of a way to specify the Member in an open query statement (where you do not have write capability on the iSeries AS400 - that is to say you can't use OVRDBF nor do you have the ability to create an Alias for each member.

Thanks

Leave a Comment

Please register or log in to leave a comment.