RE: Re: ASNA VRPG for .net or Websmart or RPGsp or somethingelse & DB Connectivity

David,

SELECT itemnumber, updesc 
FROM TITEMMASTER, meldbf.mpunpr@test400db
WHERE trim(itemnumber) = trim(uppart);

itemnumber is a column in the TITEMMASTER table in Oracle.

updesc is a column in the mpunpr table in library meldbf on our iSeries.

ITEMNUMBER      UPDESC
----------      -----------------
A633S025EC      BUSH
A633S02701      BUSH
A633S027BC      BUSH
A633S027CC      BUSH
A633S03401      BUSHING
(2264 other lines snipped ;-)

Works just fine when issued from Oracle.  The "test400db" is an Oracle 
"database link" that points to our iSeries and uses the IBM iSeries OLEDB 
driver.  Note that in our case, Oracle is running on a Windows PC.  The same 
thing can be done with an ODBC driver on Windows/Linux/UNIX/ect.

I'm 99.9% sure MS SQL Server allows the same thing via "linked tables".

This "linked database" capability via ODBC/OLEDB is built into both Oracle and 
SQL Server.  You don't have to buy anything extra.  Though Oracle and I assume 
MS have additional products that add functionality.

Remote databases work even better when dealing with homogeneous systems, ie. 
Oracle <--> Oracle or SQL Server <--> SQL Server.


HTH,

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

> -----Original Message-----
> From: web400-bounces@xxxxxxxxxxxx 
> [mailto:web400-bounces@xxxxxxxxxxxx]On
> Behalf Of dsager@xxxxxxxxxxxxxxxxx
> Sent: Tuesday, June 07, 2005 4:26 PM
> To: web400@xxxxxxxxxxxx
> Subject: [WEB400] Re: ASNA VRPG for .net or Websmart or RPGsp or
> somethingelse & DB Connectivity
> 
> 
> 
> Charles,
> 
> To my knowledge there is NO product out there that can hit multiple
> databases with a single SQL statement.  However, accessing multiple
> databases in a single application can be accomplished using 
> JDBC as you
> mentioned (see an example from IBM here -
> http://www-128.ibm.com/developerworks/websphere/library/techar
> ticles/0407_woolf/0407_woolf.html),
>  and while I cannot locate the exact inference of the 
> announcement, here's
> something that may help...
> http://www-106.ibm.com/developerworks/cn/db2/library/techartic
> les/dm-0411purnell/db2ii_planning_sizing_article_FINAL.pdf.
>   I guess the best thing to do would be to talk to your IBM 
> rep - I just
> remember reading the blurb and then forwarding it on to 
> someone involved in
> cross-platform development and support (I'm not currently working with
> Oracle, so I can only keep track of so much! :-).
> 
> David R. Sager
> ITS Project Leader, BPMS
> Office:  (847) 482-2627
> Cell:      (847) 489-4941
> AIM:  drsatpca
> 
> <snip>
> 
> What announcement are you referring to?
> 
> I know of no way for DB2/400 to interact "directly" with 
> Oracle unless one
> ponies up the big $$$ to purchase some extra software from 
> either Oracle or
> IBM.
> 
> An iSeries Java application can use Oracle's JDBC driver, but 
> I wouldn't
> call that DB2 acting "directly".  On the other hand, Oracle 
> can use IBM's
> ODBC or OLEDB driver to directly access iSeries Tables.
> 
> Note that my definition of "directly" is a single SQL 
> statement can join
> tables on separate systems.
> 
> Thanks,
> Charles
> 
> <snip>
> 
> -- 
> This is the Web Enabling the AS400 / iSeries (WEB400) mailing list
> To post a message email: WEB400@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/web400
> or email: WEB400-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/web400.
> 
>