Admin Alert: Using FTP to Copy DB2 Files Between iSeries Machines

by Joe Hertvik

A few weeks ago I covered how to copy AS/400 IFS files from one iSeries to another. This week, I'll continue that theme with another primer that explains all the steps to transfer a native DB2/400 file between two OS/400 boxes. The process is easy to follow, and with a few minor changes you'll be able to duplicate this tutorial on your own machine.

One note: If you're unsure about which FTP commands perform which functions in this primer, read this article alongside "Admin Alert: The OS/400 FTP Subcommand Glossary," which contains all the FTP subcommands listed here, as well as several other useful subcommands.

Getting Started

For our example, let's assume that our remote AS/400 FTP server has an IP address of 192.1.1.1, with a host name of ftpsrv400.dummydomain.com, and that our client iSeries FTP server has an IP address of 192.1.1.2, with a host name of ftpcln400.dummydomain.com (of course, as you test this out, you'll want to substitute the IP addresses or host names for your own servers). To perform the FTP transfers listed here, make sure that both OS/400 FTP servers are active on your systems.

To provide a universal file for our transfer, I'll take a copy of the sample Client Access customer master file that IBM provides on most AS/400s and copy that between these machines. The name of this file is QCUSTCDT, and it resides in the QIWS library. For our testing, make a copy of that file in the QGPL library of the ftpcln400 machine by executing the following Copy File (CPYF) command:

CPYF FROMFILE(QIWS/QCUSTCDT) TOFILE(QGPL/QCUSTCDT) 
   MBROPT(*REPLACE) CRTFILE(*YES) 

Next, start an FTP session with the ftpcln400 server by using the File Transfer Protocol (FTP) green-screen command, as follows:

FTP RMTSYS(FTPSRV400.DUMMYDOMAIN.COM)

This command will open an FTP session with the ftpsvr400 server. A second way to open the session is by using the ftpsrv400 server's short name, if there's an entry in a corresponding DNS server or the iSeries host table. You can also open the session by using the server's IP address. To perform either of the functions, replace the previous FTP command with one of the following commands:

FTP RMTSYS(FTPSRV400)

Or

FTP RMTSYS(*INTNETADR) IPADDR('192.1.1.1')

Regardless of which FTP command you use, OS/400 FTP will present you with an FTP subcommand shell. An OS/400-based FTP session is simple because all you have to do is enter the right subcommands and FTP will do the rest.

The first thing that appears inside your FTP shell is a prompt asking for user ID. You can choose to just hit the Enter key without entering a valid user ID, and FTP will send the OS/400 user profile name of the ftpcln400 user that you started the FTP shell under; otherwise, you can enter another valid OS/400 ID that is specific to that machine and then press Enter.

FTP will then prompt you for the user profile's password. Enter it, and you'll be connected to an FTP job on the target ftpsrv400 server using the user profile you just signed on with.

Moving DB2 UDB Files Using PUT

Once the connection is made, it's just a simple matter of entering the proper subcommands to send your file between the servers. To copy QGPL/QCUSTCDT from ftpcln400 to ftpsrv400, enter the following FTP subcommands, which use a PUT function to copy the file:

NAMEFMT 0
EBCDIC
PUT QGPL/QCUSTCDT  QGPL/QCUSTCDT
QUIT

And this script will copy the file and end your client FTP session. Here's what each FTP subcommand does.

• NAMEFMT 0 changes the naming format of this particular FTP session to use the library/file.member format. NAMEFMT 0 assumes that you'll be copying files only out of the QSYS.LIB IFS file system, where all the DB2 UDB data is kept.
• EBCDIC tells OS/400 FTP that the transfer will copy the file between EBCDIC-based systems. For copying OS/400 physical files to another iSeries or AS/400, I recommend you use an EBCDIC session instead of a binary session (BIN subcommand) because if the target file doesn't already exist on your partner system FTP will choose its own record length for the new file when BIN is used and the FTP copy may not work correctly. If you use the EBCDIC subcommand, however, the file creation decisions will be made correctly. So even though a binary file transfer is generally quicker, using an EBCDIC copy for physical files that don't exist on the target system may be more accurate.
• PUT QGPL/QCUSTCDT QGPL/QCUSTCDT tells FTP to take the QGPL/QCUSTCDT file from the ftpcln400 FTP client machine and copy it to library QGPL on the target ftpsrv400 FTP server. PUT always copies a file from the local client to the remote server. Since we didn't specify a particular QCUSTCDT member to copy, FTP will copy the first member in the file. Also, PUT automatically replaces any existing file contents, and if the file doesn't exist PUT will create it correctly on the FTP server machine (which is why we specified EBCDIC as our file transfer format).
• QUIT ends the FTP session with the host, shuts down the FTP shell, and returns the user to the green-screen command line.

This first example explicitly copied the file between our two systems, where it specified the exact location of each file inside the PUT subcommand. Now I'll modify this example by performing an implicit copy that sets up working directories (libraries when you're working with DB2 UDB data) that are then referenced in FTP commands that need to specify an OS/400 location. Here's how you would implicitly specify the DB2 UDB libraries FTP uses through the CD and LCD subcommands:

NAMEFMT 0
CD QGPL
LCD QGPL
EBCDIC
PUT QCUSTCDT
QUIT

The changed commands for this FTP session are as follows:

• CD QGPL changes the working directory, on the ftpsrv400 FTP server to which you're attached, to be QGPL, our target library.
• LCD QGPL changes the local working directory (library) to QGPL. LCD does the same thing on the local client machine that CD does on your remote FTP server. An easy way to remember the difference between the two commands is to remember that the "L" in LCD stands for local (the FTP client).
• PUT QCUSTCDT uses the DB2 UDB libraries specified in the CD and LCD commands to determine which libraries to copy the file from and to, between the two machines. Notice this command is much shorter than the previous PUT command, because of two things. First, you don't have to specify the ftp client or ftp server libraries to copy to and from, because this PUT uses the libraries specified in the CD and LCD subcommands. Second, since we want the target file on the FTP host server to have the same name as the client, we don't have to enter the file name twice, as we did in the previous example. Entering the name once tells FTP to copy the file to the remote system and name it with the same file name as the local client system.

Moving DB2 UDB Files Using GET

Next, I'll change this transfer from a PUT (where the file is copied from the FTP client to the FTP server) to a GET (where the file is copied from the FTP server to the FTP client). Making this change is a simple matter of substituting the PUT statement in the previous script for a GET statement. Here are the revised commands you can run to perform a DB2 UDB GET:

NAMEFMT 0
CD QGPL
LCD QGPL
EBCDIC
GET QCUSTCDT (REPLACE
QUIT

In this case, the GET subcommand functions almost the same as a PUT, but the file transfer direction is reversed: The copied file is now coming from the FTP server to the FTP client. Also note that GET has a replacement function in case the file already exists on your local FTP client (the REPLACE parameter). To ensure that the file is overwritten when received at the local target, you add (REPLACE to the end of your GET statement, which clears your target file--if it exists, and if your signed-on FTP user has authority to it--before it starts copying the data. If your target file doesn't already exist on the FTP client, GET will create the file as it's copying it over.

The (REPLACE parameter is optional, but there isn't any downside to using it if the file doesn't already exist on your system.

Copying Specific File Members, Using FTP

The next most common use for FTP is to copy file members from one iSeries or AS/400 to another. (Remember, if you don't include a member name in GET and PUT transfers, FTP will copy the first member in the file.) Assume we want to copy the source code for our OS/400 startup program, QSTRUP in file QGPL/QCLSRC, between our two OS/400 boxes. Here is the script to copy the file, assuming QGPL/QCLSRC exists on both boxes:

NAMEFMT 0
CD QGPL
LCD QGPL
EBCDIC
PUT QCLSRC.QSTRUP
QUIT

The syntax for copying members is easy. You designate a member name to copy by appending the file member name, preceded by a period (.), to the file name in your PUT or GET statement. For a GET statement, you would use the following command:

GET QCLSRC.QSTRUP (REPLACE

Note that although I'm demonstrating this technique with source file members, these concepts also work when copying individual physical file members.

Copying Generic File Members, Using FTP

If you want to copy multiple file members with the same FTP subcommand, you can use the FTP MGET and MPUT subcommands. MGET and MPUT use a wildcard format to copy multiple members between two machines. For example, if you wanted to copy all the source file members that began with the letter C from the QGPL/QCLSRC file on your client FTP server to the QCLSRC source file in library QGPL on your remote FTP server, you could use the following subcommands:

NAMEFMT 0
CD QGPL
LCD QGPL
EBCDIC
MPUT QCLSRC.C* 
QUIT

To copy from the server to the client, I would replace the MPUT statement with MGET, as follows:

MGET QCLSRC.C*

If I wanted to copy all members from QCLSRC to the target machine, I would modify my MPUT or MGET statements in the following way:

MPUT QCLSRC.*

MGET QCLSRC.*

Again, these techniques also work when you're copying physical file members.

Notice one final thing about MPUT and MGET. Neither command contains a second file parameter for specifying the name of the file on the target system that you are copying over. All files are copied to the target FTP server's working directory--as specified in the LCD or CD FTP subcommand--with the same name as the file member that was copied over.

FTP with Impunity

Like most OS/400 processes, it's not difficult to FTP files between two iSeries or AS/400 machines. But using FTP to move DB2/400 files is definitely a case where a little knowledge goes a long way.

Sponsored By BYTWARE

It seemed like a normal day at the office. Little did anyone know that Jennifer in payroll was about to hand out an unexpected pay cut. With full unrestricted access to the company's accounting and payroll directories, Jennifer's after-lunch system cleaning tossed out not only some old PDFs but this week's payroll files, too. Don't send your employees into financial shock. Get Secure. Get StandGuard. www.bytware.com