Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Newbie - using IBM.Data.DB2.iSeries

Posted on 2006-11-07
8
Medium Priority
?
1,200 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
[X]
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
8 Comments
 
LVL 46

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.



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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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
 
LVL 46

Accepted Solution

by:
Kent Olsen earned 1000 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 1000 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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

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 (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…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

618 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