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.
> 
> 


As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.