SQL Connect

[Stoney]

Is it possible to access local files whilst connected (using CONNECT) to a
remote database (another AS400)?
I want to update a remote machine with data from the local one - any
suggestions?

thanks

Stony

[Kent Milligan]

Yes, that's called Two Phase Commit - read the DRDA chapters of the "Advanced
Functions & Administration on DB2 UDB for iSeries" redbook at ibm.com/redbooks

[Stoney]

Thanks for the tip and it is an interesting read but...
it is more complicated than I want at the moment.

All I need to do is to find an easy way of inserting records onto a remote
file using the local file as the master copy. I currently create DDM files
and use CPYF but this is too labour intensive when a lot of data is
involved.

rgds stony

Post by Kent Milligan
Yes, that's called Two Phase Commit - read the DRDA chapters of the "Advanced
Functions & Administration on DB2 UDB for iSeries" redbook at

ibm.com/redbooks

Post by Kent Milligan
--
Kent Milligan, DB2 & BI team
PartnerWorld for Developers, iSeries

www.iseries.ibm.com/db2

(opinions stated are not necessarily those of my employer)

[Kent Milligan]

I don't think it's hard as you think. DB2 DataPropagator is also a solution
available to do this on a scheduled basis.

[Matt Haas]

Yes, you can do this. If you're using SQL to access the local file(s),
you just need to connect to the local machine to read from it and then
connect to the remote machine to write. Something along the lines of
this:

- connect to remote machine
- while not EOF
- set connection local machine
- read record
- set connection remote machine
- write record
- end while

You'll need to keep track of SQLCOD's or SQLSTT's for each file
seperately since those are global variables. If you commit each record
after the write, make sure you use COMMIT HOLD or the cursor for the
local file(s) will close.

Another approach is to not use SQL for the local file(s) and use
regular record level I/O but use SQL for the remote file.

Matt

Post by Stoney
Is it possible to access local files whilst connected (using CONNECT) to a
remote database (another AS400)?
I want to update a remote machine with data from the local one - any
suggestions?
thanks
Stony

[Stoney]

Thanks for that.

What I really wanted was to use interactive SQL.

Something like:-
"Insert into remotefile select *from localfile" like I can do when copying
between two libraries on the same box.

Any other takers?

Stoney

Post by Matt Haas
Yes, you can do this. If you're using SQL to access the local file(s),
you just need to connect to the local machine to read from it and then
connect to the remote machine to write. Something along the lines of
- connect to remote machine
- while not EOF
- set connection local machine
- read record
- set connection remote machine
- write record
- end while
You'll need to keep track of SQLCOD's or SQLSTT's for each file
seperately since those are global variables. If you commit each record
after the write, make sure you use COMMIT HOLD or the cursor for the
local file(s) will close.
Another approach is to not use SQL for the local file(s) and use
regular record level I/O but use SQL for the remote file.
Matt

Post by Stoney
Is it possible to access local files whilst connected (using CONNECT) to a
remote database (another AS400)?
I want to update a remote machine with data from the local one - any
suggestions?
thanks
Stony

You said earlier that you are currently using DDMF & CPYF. Why not create a
little cl program to create the DDMF copy & remove the DDMF ? You can then
create a command to simplify passing the parameters you want eg library,
file, remote system & library and the member names and an in or out option.
Maybe you will require mbropt(*add|&replace) as well ? Then your job should
be a doddle, just do wrkobjpdm press F16 & create an option which uses your
new command & specifies the remote system etc so simply select the file,
take the option & you are done.

Your program & command could even have a submit feature so if the submit
flag is yes it simply submits itself to batch (with the flag off) then you
can select the files & go make a coffee.
--
Jonathan.

Post by Stoney
Thanks for that.
What I really wanted was to use interactive SQL.
Something like:-
"Insert into remotefile select *from localfile" like I can do when copying
between two libraries on the same box.
Any other takers?
Stoney

Post by Matt Haas
Yes, you can do this. If you're using SQL to access the local file(s),
you just need to connect to the local machine to read from it and then
connect to the remote machine to write. Something along the lines of
- connect to remote machine
- while not EOF
- set connection local machine
- read record
- set connection remote machine
- write record
- end while
You'll need to keep track of SQLCOD's or SQLSTT's for each file
seperately since those are global variables. If you commit each record
after the write, make sure you use COMMIT HOLD or the cursor for the
local file(s) will close.
Another approach is to not use SQL for the local file(s) and use
regular record level I/O but use SQL for the remote file.
Matt

Post by Stoney
Is it possible to access local files whilst connected (using CONNECT)

to

Post by Stoney
a

Post by Matt Haas

Post by Stoney
remote database (another AS400)?
I want to update a remote machine with data from the local one - any
suggestions?
thanks
Stony

[Matt Haas]

You can't use interactive SQL to do this because you can only be
connected to one database (in the case of OS/400 until very recently,
there was just one database per machine) at a time.

If you don't want to write a program to do this, just use DDM files
and the CPYF command. Yes, it's slow but it does work.

An other option is to save files off to a save file and FTP the save
file to the second system.

Matt

Post by Stoney
Thanks for that.
What I really wanted was to use interactive SQL.
Something like:-
"Insert into remotefile select *from localfile" like I can do when copying
between two libraries on the same box.
Any other takers?
Stoney

Post by Matt Haas
Yes, you can do this. If you're using SQL to access the local file(s),
you just need to connect to the local machine to read from it and then
connect to the remote machine to write. Something along the lines of
- connect to remote machine
- while not EOF
- set connection local machine
- read record
- set connection remote machine
- write record
- end while
You'll need to keep track of SQLCOD's or SQLSTT's for each file
seperately since those are global variables. If you commit each record
after the write, make sure you use COMMIT HOLD or the cursor for the
local file(s) will close.
Another approach is to not use SQL for the local file(s) and use
regular record level I/O but use SQL for the remote file.
Matt

Post by Stoney
Is it possible to access local files whilst connected (using CONNECT) to

a

Post by Matt Haas

Post by Stoney
remote database (another AS400)?
I want to update a remote machine with data from the local one - any
suggestions?
thanks
Stony