RE: SQL data access -- MIDRANGE-L

Sounds like the IBM WebSphere Information Integrator product.

The problem I have with that product is that if you have a table on the
iSeries and a table on SQL server, the way you have to run a query on
the iSeries that joins the two tables is:

CONNECT to the server running IBM WebSphere Information Integrator
Send the query to the remote server, which pulls the data from both the
iSeries and the SQL Server, joins the rows and sends the results back to
the iSeries.

So you see the iSeries data makes a round trip out and back.  Which
seems kind of silly to me.

The iSeries has no built in "federation" or "homogeneous access" SQL
access capability.

In contrast, DB2 LUW, SQL Server, Oracle, ect.. all allow running a
query that joins a remote table to a local table and  retrieve just the
remote DB server data and join the data locally. (Note that DB2 LUW at
least used to offer built in free federation, I don't know that it still
does.  With the latest versions you might have to buy the WebSphere
Information Integrator/DB2 Connect products to do this, I can't tell ;-)

Part of the trouble is of course the proprietary nature of non IBM DBs.
You have to have a driver written by MS or Oracle to talk to their DBs.
Whereas IBM's protocol (DRDA) is published and anybody can write a
driver.

On the other hand, part of the problem is IBM.  There's no reason why we
shouldn't be able to access local iSeries data and remote iSeries or LUW
DB2 data (or other DRDA sources) in the same SELECT statement.  It's
really disappointing that IBM allows DDM files to provide RPG record
level access to remote data homogeneously with local data, but doesn't
allow SQL queries to do the same.

I'm not really sure how the DB2 connect product you mention is used, but
the following was taken from here:
http://www-306.ibm.com/software/data/db2/db2connect/edition-uei.html
"Data Integration for iSeries
        With a single SQL request join DB2 UDB iSeries data to: DB2
tables from another or multiple iSeries servers or partitions; DB2
databases on Windows, UNIX, and Linux servers; Informix Dynamic Server
(IDS); DB2 data on zSeries mainframes when coupled with another DB2
Connect offering; Non-IBM data sources like Oracle, SQL Server, Sybase,
when coupled with WebSphere Information Integrator"

Notice that last line.  Then look here:
http://www-306.ibm.com/common/ssi/fcgi-bin/ssialias?subtype=ca&infotype=
an&appname=iSource&supplier=897&letternum=ENUS205-222 

Or http://tinyurl.com/y7zv9u

"To join data between the DB2 UDB for iSeries and a separate instance of
the DB2 UDB for iSeries with a single SQL request, you need DB2 Connect
Unlimited Edition for iSeries.

To join data between the DB2 distributed data and DB2 UDB for iSeries
with a single SQL request, you need DB2 Connect Unlimited Edition for
iSeries.

To join heterogeneous sources of data between non-IBM data sources like
Oracle, Microsoft Windows, Sybase, and Teradata with a single SQL
request to your DB2 UDB for iSeries data, you need both DB2 Connect
Unlimited Edition for iSeries and WebSphere Information Integrator. " 

I'm not really sure what the point is of DB2 Connect.  Like WebSphere
Information Integrator, it runs on an external server not the iSeries
itself.  DB2 Connect seems limited without WebSphere Information
Integrator.  But I don't see anything that leads me to believe that
WebSphere Information Integrator has similar limitations without DB2
Connect.

***WAIT***

This page, http://www-1.ibm.com/support/docview.wss?uid=swg27008401
Seems to indicate that for WebSphere Information Integrator to access
DB2 iSeries Data you need DB2 Connect.

So that answers that question.

The end result of all this?

If you're not strictly an iSeries shop, so you've got multiple DB
platforms, your are better off doing new development on anything but the
iSeries.  That way, if your application ever needs to access data on one
of the other platforms, you'll be able to do it easily.

That's too bad.



HTH,



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

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx 
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Glenn Gundermann
Sent: Friday, October 20, 2006 8:57 AM
To: Midrange Systems Technical Discussion
Subject: Re: SQL data access

IBM had a demo of connecting to two different databases, one 
being DB2 on
System i and another on a SQL Server and did one SELECT 
statement using a
join, and voila.  I saw it with my own eyes but I can't 
remember what he
was using.  Does DB2 Connect sound right?  There was some additional
software you had to buy which did the magic.

Hope this helps.

Glenn

"Mike Skvarenina" <mskvarenina@xxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
10/19/06 01:06 PM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
<MIDRANGE-L@xxxxxxxxxxxx>
cc

Subject
SQL data access


Is there a tool that could emulate an AS/400 database file

but really

points
to a table on a Windows server living in a SQL database so

an RPG program

could simply declare a file from a F spec?

I know the alternative is to invest in a product like

DataMirror or Mimix

to
create a duplicate copy of the data on my AS/400 but I'd

like to eliminate

the mirrored copy if possible.

-- 
This is the Midrange Systems Technical Discussion 
(MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.