How to download data from AS 400

zachvaldez
zachvaldez used Ask the Experts™
on
What is the best method to download data from AS400?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dr. KlahnPrincipal Software Engineer

Commented:
Over a standard TCP/IP network?  FTP.

http://www.ortizonline.com/publications/FAQ_FTP_AS400.htm
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
You can use SSIS to fetch data from AS400 to MS SQL Server.
AS 400 offers ODBC and OLE DB drivers so you can access it from almost anywhere by any programming language which supports ODBC or OLE DB access, e.g. C#, FoxPro, VB etc.

Many drivers exist, you may look e.g. here: http://www.hitsw.com/products_services/odbc/odbc_for_i/odbc400.html

Microfocus also delivers drivers  https://www.microfocus.com

Of course, you should preferably ask your AS400 support for more info.

Connections strings are described here: https://www.connectionstrings.com/as-400/
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
We use a linked server.  Create a linked server(s) pointing to the AS400 you want to access.  Naturally you'll need an id on the AS400 that has access to what you want to read / write.

Then you use it in SQL just as you would any other linked server.
Gary PattersonVP Technology / Senior Consultant

Commented:
One time, or a repeated automated process?
IBM AS/400 DB2 data (tables/physical files), or flat files from the AS/400 IFS?
What format do you need the data in?
VP Technology / Senior Consultant
Commented:
FTP is a good solution for IFS files, but generally a bad solution for database data.

For database data:

ODBC/JDBC/OLEDB/ADO.NET connectors are all available for IBM i (iSeries, AS/400) DB2, so any tool you like that supports these protocols can be used, like SQL Server Linked Server or SSIS.

Most shops use IBM i Access for Windows emulator, which includes an easy interactive Data Transfer Function for uploading and downloading data to/from IBM i DB2 (easiest way to do "one time" or occasional transfers).  DTF supports a lot of different target formats, like Excel, CSV, and more.
How to download data from AS 400?

As you can see for yourself, there is a large number of tools, protocols and techniques available. Some were not mentioned (like ssh/sftp). The key parameters are:
* Is it a one time download?
* What size is the download?
* What is the data?
* Does it need conversion?
* How secure does it have to be?
* What platform is doing the downloading?

Author

Commented:
This suggestion will help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial