Newbie - using IBM.Data.DB2.iSeries

Posted on 2006-11-07
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.
Question by:Camillia
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
LVL 45

Expert Comment

by:Kent Olsen
ID: 17889782

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.

LVL 37

Expert Comment

ID: 17889840

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


Author Comment

ID: 17889897
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, 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..****

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

LVL 37

Expert Comment

ID: 17889944

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

LVL 45

Accepted Solution

Kent Olsen earned 250 total points
ID: 17889980

>>  Should we connect to As400/iseries using, 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,

Author Comment

ID: 17889986

 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?
LVL 37

Assisted Solution

momi_sabag earned 250 total points
ID: 17890039

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

LVL 13

Expert Comment

ID: 17892582
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

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Using select statement from input file with DB2 EXPORT 1 580
DB2 tablespace disk full error 33 1,212
MS/SQL OpenQuery to linked DB2 Server error 3 138
Shell Script on AIX 7 149
November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question