Data Transfer

muhammasif
muhammasif used Ask the Experts™
on
Hello,

There is a DB2 database on the 400. Some of the data needs to be automatically 'pushed' (or 'pulled') daily to another database on the same 400. Could anyone please provide a code example to do so?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Bob ButcheriSeries Engineer

Commented:
Maybe the easiest way to automatically update your file is to create a program that will connect to your AS400 via the FTP server. You could run this program in the IBM Job Scheduler - run cmd WRKJOBSCDE and press F6 to add an entry or command (call your program) that you'll create below. Just add the date and time you want it to run.

The code below shows how to build the code to do this:

FTP in batch is very simple.

You need three files.

1. A CL program.

2. A file containing FTP commands.

3. An empty file to recieve the FTP log messages.

The command and log files can have any name you want. Create 2 Physical files each with a single 80 character data field.

The command file in this example FTPCMD might look like this.


USERID PASSWORD Put your own userid and password here.

get LIBRARY/FILE.MEMBER LIBRARY/FILE.MEMBER  (REPLACE

CLOSE




The message file, in this example FTPMSG is empty.

The CL looks like this
PGM

CLRPFM FTPMSG

 OVRDBF     FILE(INPUT) TOFILE(TOLCLINIC/FTPCMD)

 OVRDBF     FILE(OUTPUT) TOFILE(TOLCLINIC/FTPMS)

FTP RMTSYS()

ENDPGM




When you run this program it will execute the commands in the INPUT file and write the log to the output file.

I have used this method to move source members from a test AS/400 to a production AS/400. When I tried to move a data file it didn't work right.


--------------------------------------------------------------------------------


In the TCP/IP guide there is an example of batch FTP. The Operating system support is extremely weak but here goes...

In your CL execute the following code:
OVRDBF INPUT TOFILE(MYFTP) TOMBR(MYFTPIN)
OVRDBF OUTOUT TOFILE(MYFTP) TOMBR(MYFTPOUT)
FTP 'xxx.xxx.xxx.xxx'

MYFTP is a source file created with at least a record length of 92

The MYFTPIN member contains a script of commands you want to execute. The first record must contain the user id and password to log in
-------------------------------------------
myid mypassword
binary
namefmt 1
mode s
mget remotefile.* qgpl/myfile
quit
----------------------------------------

The MYFTPOUT member will contain the response to your input script You must examine this file to determine if your script executed properly


--------------------------------------------------------------------------------


We do this exactly in our shop. Look at section 7-36 of the TCP/IP manual. You will see a similar example to what we do:
      /*-------------------------------------------------*/
      /* Process FTP commands to send file                  */
      /*-------------------------------------------------*/

                   OVRDBF     FILE(INPUT) TOFILE(QFTPSRC) MBR(FTPCMD)
                   OVRDBF     FILE(OUTPUT) TOFILE(QFTPSRC) MBR(FTPLOG)

                   FTP        RMTSYS(SYS01)

....

FTPCMD is a member which contains the ftp commands to process (we actually build this member on the fly as username, password and filenames change):
     username password
     put localfile remotefile
     quit

... Member FTPLOG produces output like this:
     Output redirected to a file.
     Input read from specified override file.
     Connecting to host name SYS01 at address xxx.xx.x.xxx using port
     21.
     220 sys01 FTP server (Version 4.29 Thu Jan 30 14:58:02 CST 1997)
     ready.
     215 UNIX Type: L8 Version: BSD-44
     Enter login ID (username):
     331 Password required for username.
     230 User username logged in.
     Enter an FTP subcommand.
     > put LOCALFILE REMOTEFILE
     200 PORT command successful.
     150 Opening data connection for LOCALFILE.
     226 Transfer complete.
     1219 bytes transferred in 0.745 seconds. Transfer rate 1.636
     KB/sec.
     Enter an FTP subcommand.
     >
     quit


Bob ButcheriSeries Engineer

Commented:
hope this helps!!!

Author

Commented:
Thanks. Does this example work on databases at 2 different 400s or a single 400 or it doesn't matter?
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Bob ButcheriSeries Engineer

Commented:
doesn't matter because you need to specify which system you are going to connect to via system name or ip address.

Author

Commented:
If the 400 system is the same, then is there a way to bypass FTP?
iSeries Engineer
Commented:
If you are using a regular AS400 file on the same system and you need it replaced or updated, you could use one of the AS400 copy commands and just replace the data.

Most likely CPYF if you are going with a regular AS400 file in a library. You would just specify the *replace option to replace the existing data to overwrite what is out there or else put it into a new member.

Bob ButcheriSeries Engineer

Commented:
Then put this command in a CL and use the IBM job schedule to automate the process.

Author

Commented:
Let me clarify what we are trying to achieve. There is a file that is being updated in real time with new data. On a daily basis, this new data is to be imported into another file without deleting/overwriting any data in both files.
Bob ButcheriSeries Engineer

Commented:
sure you can do that. use the cpyf command with mbropt parameter set at *ADD.

Commented:
Please post and let me know if you are still interested in a response and I will try to help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial