Question

SQL joining tables across multiple AS/400's

AS/400, UDB for iSeries/i5, OS/400, iSeries, i5

I would like to execute the following query.

The problem is that the Table INVMST is on a different AS/400 than table CHURDLES.

INSERT INTO IDEASLNT/SHURDLES
SELECT I.IDEPT, I.ISDEPT, I.ICLAS, I.iSCLAS, I.ISTYL#,I.ASNUM,
CONCAT(CONCAT(Digits(I.IDept), '-'), Digits(I.ISDEPT)),
CONCAT(CONCAT(Digits(I.ASNUM), '-'), I.ISTYL#),
Max(C.Best), Max(C.MetStdTo), Max(C.MetStdFrm),
Max(C.BelowStd), Max(C.WOS),
Max(C.UBest), Max(C.UMetStdTo), Max(C.UMetStdFrm),
Max(C.UBelowStd)
FROM INVMST I, CHURDLES C
WHERE I.IDEPT = C.DEPT AND
CONCAT(CONCAT(Digits(I.IDept), '-'), Digits(I.ISDEPT))
= C.HLVAL
GROUP BY I.IDEPT, I.ISDEPT, I.ICLAS, I.iSCLAS, I.ISTYL#, I.ASNUM


I am running this on the AS/400 that has CHURDLES and we have INVMST set up as a DDM file on the same machine. However I noticed that STRSQL does not seem to recognize DDM files.

What functionality is available on the AS/400 for Distributed Database processing.

AP

Would this information help answer your question? I went to www.iseriesnetwork.com and requested a search for Distributed Database which resulted in the following:
Search Results for "Distributed Database"
9 articles found
Results are ordered by relevance. Sort by Date.
1. Middleware Unites Domino Database and DB2/400
Robert Anderson explains how Percussion Software's Notrix lets Germania Farm Mutual Insurance distribute AS/400 data to field employees using Notes.
( iSeries NEWS, May 1998 , p.77 )
2. More AS/400 Client/Server Applications Are Here!
Wayne Madden and Renee Munshi describe the client/server product offerings of application software vendors Camstar, Cantoc, JBA, Marcam, and Para Research.
( iSeries NEWS, January 1995 , p.117 )
3. NS/Router: The 32-bit Standard
With Version 2.2 of the 32-bit NS/Router, NetSoft is closing some holes in the product and tightening its position as the Windows-to-AS/400 connectivity method of choice for IBM and others. But Pam Jones reports that there's still room for competition.
( iSeries NEWS, February 1997 , p.27 )
4. An Introduction to Implementing DRDA
Need to incorporate remote DB2 UDB data into your applications? This overview of IBM's Distributed Relational Database Architecture can help you get started.
( iSeries NEWS, July 2002 , p.64 )
5. Tech Corner
( iSeries NEWS, December 2000 , p.95 )
6. Tighten Your DB2/400 Security
IBMer Carol Woodbury explains resource security, provides a strategy for securing logical files, discusses triggers' potential exposures, offers some ideas for securing interfaces such as DDM and ODBC, and touches on data encryption and auditing.
( iSeries NEWS, April 1996 , p.67 )
7. Accessing a Remote AS/400 Database via SQL
( iSeries NEWS, December 2000 , p.96 )
8. DB2/400 Coming Attractions
IBM's Kent Milligan outlines what you can look forward to in the V4 releases of DB2/400, including column-level security, check constraints, database validity checking, and a new SQL procedural language.
( iSeries NEWS, September 1997 , p.70 )
9. DB2/400, Informix, and Oracle Side-by-Side
Ramon Chen compares key features in three relational database management systems DB2/400, Informix, and Oracle. A comparative features chart is included.
( iSeries NEWS, February 1995 , p.67