Solved

Newbie - using IBM.Data.DB2.iSeries

Posted on 2006-11-07
8
1,192 Views
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....

Issue:
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.
0
Comment
Question by:Camillia
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 45

Expert Comment

by:Kdo
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.



Kent
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17889840
hi

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

momi
0
 
LVL 7

Author Comment

by:Camillia
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 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..****





0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17889944
hi

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

momi
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 45

Accepted Solution

by:
Kdo earned 250 total points
ID: 17889980


>>  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,
Kent
0
 
LVL 7

Author Comment

by:Camillia
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?
0
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 250 total points
ID: 17890039
hi

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

momi
0
 
LVL 13

Expert Comment

by:ghp7000
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
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now