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 Vernon Hamberg
> Sent: Tuesday, June 07, 2005 9:12 AM
> To: Midrange Systems Technical Discussion
> Subject: RE: How do I connect from iSeries to MS SQL Server 2000?
> 
> 
> Responses inline with lots of snips:
> 
> At 07:44 AM 6/7/2005, you wrote:
> -snip-
> 
> >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.
> 
> As Rob has pointed out, our RPG2SQL does not use embedded SQL. It is, 
> however, similar to using CLI (common language interface), 
> which is the 
> iSeries equivalent of the open standard that MS calls ODBC. 
> And with our 
> product you can do exactly what you want - CREATE a table and 
> use it as you 
> want - read, write, etc.
> -snip-

Actually Vern, I'm going to be using you product in a consulting project I'm 
working on.

Downloaded the manual this past weekend in fact.

>From what I've seen so far, your product is not "exactly what I want."

It certainly provides a way for an application to access data on a remote 
machine.  But what I want is a way for DB2 to access data on a remote machine 
so that my application doesn't have to care where the data is.

I can for instance in Oracle replace a physical table with a link to a remote 
table ( or vice versa ) without affecting any applications.

> 
> >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.
> 
> Not completely true - if you define the remote iSeries with 
> WRKRDBDIRE, you 
> CONNECT TO and then all your embedded SQL runs against that 
> system - DDM is 
> involved under the covers, but that is just implementation. 
> No DDM files 
> involved - at least none that you need to define.

What I was referring to is the fact that using native I/O you can read/chain to 
a DDM file as if it was local.  Your RPG application doesn't know that the data 
is remote.

On the other hand, if you use embedded SQL you have to specify CONNECT TO.  Now 
your application is certainly aware that the data is remote.  Too add insult to 
injury, I can't do a join between local and remote tables since the iSeries can 
only be CONNECT'd TO one system at a time.


> 
> The issue of DUW still applies, and that is a problem, I 
> agree. Now, does 
> ODBC or OLE/DB allow distributed units of work, or whatever 
> equivalent term 
> would be used?

Yes, as does JDBC.  However, I think it is often limited by vendor 
implementations.  For example, setting up a link to DB2 from Oracle using ODBC 
or OLEDB doesn't support DUW.  Though from what I read in IBM's documentation, 
the latest ODBC does support DUW at least with MTS.  Oracle want you to 
purchase their big $$$ Transparent Gateway product.


> 
> > >
> > > 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.
> 
> I'm obviously listening to this thread and thinking - I think 
> we have an 
> excellent product for handling some of these concerns, and am 
> looking for 
> ways to make it better and more useful - hence more sellable, eh? ;-)
> -snip-
> 
> 

There's been talk of writing an open source DRDA-->JDBC bridge on one of the 
other lists.  Right now, I've just got to many other projects going in addition 
to my regular job.

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