We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now


Newbie - using IBM.Data.DB2.iSeries

Camillia asked
Medium Priority
Last Modified: 2012-08-13
I'm new to AS400/iSeries... (I'm from SQL Server world)...

All my coding has to be done against AS400/iSeries and SQL Server...

My manager asked my coworker to write an application to select from 4 tables and dump the results in an excel sheet...

This is easy in VB.Net and SQL Server....

In AS400/iSeires, my coworker was saying he needs to use "old client access" and that he cant use ODBC or Ole connection to connect to AS400/iSeires and SQL Server....reason he gave "I have to issue commands like clear table that he cant do in iSeries" or something like that...

My thinking is that we can use IBM.Data.DB2.iSeries and then have the select statements in a stored proc...
1. But can users on old version of AS400 have IBM.Data.DB2.iSeries used against their database? Or is this only supported for V5R2 and later

2. If we dont use IBM.Data.DB2.iSeries, why cant we use ODBC or Ole providers to hit OLD and NEW versions of AS400?

I think the main issue is to get the app working with both  old AND new versions of AS400 since clients might have older versions of AS400...

any advice is appericiated.
Watch Question

Kent OlsenData Warehouse / Database Architect


I'm not aware of any way to read from DB2 on an AS400 directly into a Micro$oft spreadsheet.

Something that you might try is to run MS Access, create and ODBC connection to the AS400 DB2 instance, and perform the query.  You can save the results in a format (including text, csv, or a database file) that Excel understands and then open/import it to Excel.


you can use excel's
data -> import -> use database query
and just select the data from the odbc data source that represnt your db2
i don't think you would be able to call your stored procedure there
but you could just repeat this process 4 times



Thanks for your responses:

 My manager wants this to be an application, give it to the clients, click a button and have them create an excel...He wants an interface and make it easy for the clients...

 VB.Net HAS functionality to write to excel tho....

Maybe i wasnt clear being new to IBM stuff:
So, we can connect to AS400/iSeries, read the file/table (maybe use external stored proc in RPG or SQL stored proc), use VB.Net excel whatever object and create an excel sheet thru the code...

I think my main question is : Should we connect to As400/iseries using IBM.DB2.data.iSeries, then call a stored proc and create the excel thru VB.Net

**** OR ****
 Use ODBC connection to connect to AS400/iSeries, then call a stored proc and create the excel thru VB.Net...

****I dont understand why my coworker says he has to use "old client access" (whatever this is ) and he cant use ODBC to go against both AS400 (old and new version) and SQl Server 2000..****


i don't understand your coworker either
but i would go for the ODBC approch since it will be portable if you decide to use another database down the road

Data Warehouse / Database Architect

>>  Should we connect to As400/iseries using IBM.DB2.data.iSeries, then call a stored proc and create the excel thru VB.Net OR ....?

I'd think seriously about designing the VB.Net application to do everything.  That would likely mean that you'll use an ODBC connection.

That also puts you back in the "one button" to run the application.

Good Luck,

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts



 Let me ask one last question... ODBC connection CAN call a stored proc on as400 ...correct? we dont have to actually have "select' statements in the VB.Net code itself...right?

Now, can we have SQL stored proc on old versions of as400 (i believe as400 has SQL stored procs and External stored procs like RPG)...or should he do the stored proc in RPG to make sure it works for older versions of as400?

you can call a stored procedure from odbc
the question about using SQL or external stored procedure - if you wish your code to run on older bersions of db2 then you should use the external stored procedures,
otherwise you can use the SQL ones


if you are loading rows into excel, make sure you never expect to load more than 64,000 rows because that is the limit for excel
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.