RE: How do I connect from iSeries to MS SQL Server 2000?

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Joe Pluta
> Sent: Monday, June 06, 2005 9:46 PM
> To: 'Midrange Systems Technical Discussion'
> Subject: RE: How do I connect from iSeries to MS SQL Server 2000?
> 
> 
> Rob, I don't mean to argue here, but I want to make a point.  
> Folks, up
> until recently, the IBM midrange was a fairly closed box.  That was
> fine; we really didn't need anybody else.  However, over the 
> last decade
> or so, IBM has made incredible strides to open the machine.  To that
> end, you have literally dozens of ways to access the outside world.
> 
> And none of them are in base RPG.
> 
> Because RPG doesn't need them.
> 
> RPG is designed first and foremost to be the way that you 
> access DB2/400
> data as quickly and as efficiently as possible.  If you need to access
> data on another machine, you have a number of ways to do it, but it
> probably won't be in RPG.

I don't think any of us are asking for the ability to READ/CHAIN to another 
RDBMS...though that would be nice ;-)

What we want to be able to do is use SQL, including SQL embedded in RPG, to 
access data on a remote machine.  More specifically, what I want is to be able 
to do a CREATE ALIAS MYTABLE for REMOTE/SCHEMA/TABLE and be able to use it in a 
SQL statement as if it was a local table.

> 
> The primary reason for this is NOT because of IBM: it's because most
> non-IBM databases don't support open standards like DRDA and instead
> require vendor-written, platform-specific interfaces, and none of them
> write interfaces for the iSeries.

Actually, Oracle does.  Their Access Manager for AS/400 product installs on the 
iSeries and allows access to a remote Oracle database using the standard 
CONNECT TO statement in SQL.

>From what I understand, it is basically a DRDA Application Requestor (AR) that 
>converts the DRDA request into Oracle's protocol.

Still, the Oracle RDBMS doesn't actually support the server side (AS) of the 
open DRDA protocol.

> 
> However, there is a way around this... it's called Java.  Yes indeed,
> Java can access SQL (and a number of other databases besides!).  And
> it's on your iSeries today!  And once you learn enough Java 
> to access a
> remote database, you'll find that you can also use it to do other
> things, like send mail or create PDF documents and access web services
> and hosts of other things.

Sure Java provides a way for iSeries applications to access data on remote 
systems.

But that is not the same as providing the RDBMS a way to access remote systems 
directly.  Thus allowing your application to be written as if all the data was 
local no matter where it was.  This is the functionality that every other RDBMS 
out there has.

What's funny is that the iSeries actually can provide "local" access to remote 
data...but only if you're using native RPG I/O via DDM files.

> 
> When IBM saw what Java was doing for the IT community, they were quick
> to realize that by leveraging Java and writing a damned good JVM that
> they would also enable the iSeries to access the great wealth of
> multi-platform code that was being written.  And once you learn Java,
> the only thing you CANNOT access is a vendor who maliciously works to
> thwart Java access.  And even Bill Gates can't do that... SQL Server
> finally released a Type 4 JDBC driver that allows ANY machine 
> with a JVM
> to access SQL data.

I think IBM missed an opportunity.

Besides adding support of the "higher level of DRDA" that would DB2 on the 
iSeries to access multiple servers at once; IBM should have added a DRDA 
AR-->JDBC bridge.

So you could load that vendor provided JDBC driver; define the remote server to 
DB2 and access the remote tables (using SQL) as if they were local from your 
iSeries applications.




Charles Wilt
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121