Re: sql select from to servers

[Simon Coulter <shc@xxxxxxxxxxxxxxxxx>]

On 23/04/2005, at 4:53 AM, Raul A. Jager W. wrote:

> If I can connect to the two systems at once, my problem becomes 
> trivial.

I'll deal with this in a moment.

One reason SQL doesn't allow DDM files is because it handles connexions 
to remote systems via the CONNECT statement. It does this so it can 
send the SQL statement to the remote host and the remote system will 
run the statement. This is much more efficient than DDM because DDM 
generally** fetches all records back to the requesting system where 
selection is performed. SQL gets the remote system to do the 
work--which is as it should be because if the remote system has the 
data then it will also have the appropriate indices to allow the most 
efficient processing. Thus SQL only sends the result set over the 
network.

** there is at least one case where DDM returns only the selected 
records but generally selection occurs at the requesting system not the 
remote system.

Now to your problem. You can connect to multiple databases at the same 
same time time. You must ensure you are using Distributed Unit OF Work 
rather than Remote Unit Of Work. DUW is the default for embedded SQL 
programs. I can't find where to control this in STRSQL but if you check 
the connection status it will show the connexion method.

You cannot join local and remote files but you can certainly be 
connected to multiple systems and process result sets from them all.


Regards,
Simon Coulter.
--------------------------------------------------------------------
   FlyByNight Software         AS/400 Technical Specialists

   http://www.flybynight.com.au/
   Phone: +61 3 9419 0175   Mobile: +61 0411 091 400        /"\
   Fax:   +61 3 9419 0175                                   \ /
                                                             X
                 ASCII Ribbon campaign against HTML E-Mail  / \
--------------------------------------------------------------------