Use SQL to Send Database Files via FTP

Hey, David:

I am looking for a way to send a native iSeries database file to a Unix system. The file needs to be sent in tab-delimited format. The end of line delimiter is variable (potentially different for each target system, which I already have defined in a file.) I need to take the output file and FTP it back to the Unix server. By default, when I FTP a (flat) file from the iSeries to a Unix box in ASCII mode, FTP adds CR/LF to the end of the record. I can use Copy To Import File (CPYTOIMPF) to create a flat file with the proper line feeds, but I would rather avoid this extra step.

--Mark

The code for this article is available for download.

It is good to hear from you again. (I recently sent Mark a program that checks for incoming files residing on other systems and pulls those files down to the local system, see Using FTP to Poll for a File). The Apache Software Foundation's Jakarta Commons Net package that I used for the polling program can also be used to send files to another system. I wrote a DatabaseFtp program to show you how to send an iSeries file to your Unix system.

The DatabaseFtp program takes information about the target system you will be sending data to along with an SQL select statement. The SQL select statement runs to select data from your iSeries system and place it in a buffer. While reading the data, another thread pulls data from the buffer and sends it to the target system.

The main processing occurs in the sendFile() method. That method opens a database connection to your iSeries database and runs the select statement. The next step is to open an FTP connection to the target system and create the target directory. If these steps complete without errors, a new PipedOutputStream is connected to a PipedInputStream and a thread is started to fill the PipedInputStream from the database. The PipedOutputStream then feeds the FTP connection sending the output of the SQL statement to your target system.

Again, you will need to do some setup before you can run this program on your iSeries system. First, if you are not currently running production Java programs on your system, I recommend that you get your system up to date on PTFs. That means you should load the latest cumulative and group PTF packages. If possible, you should also make sure you are using version 1.4 of the Java Development Kit on your system. You can verify typing in GO LICPGM from an OS/400 command line. Select option 10 to see if licensed program 5722-JV1 option 6 (the JDK 1.4) is installed. If it is not installed, do so by selecting option 11 and placing a 1 next to 5722-JV1 option 6.

After verifying that your JDK and PTFs are up to date, save DatabaseFTP.java in an IFS directory and start Qshell using the QSH command. The comments at the top of the DatabaseFTP.java source describe the commands you need to use to set your class path, compile, and run DatabaseFTP.

The ability to convert an SQL select statement to a file stream and send it to another system gives you a lot of flexibility. SQL gives you the ability to join tables, convert data, and lots more. In addition to performance, the PipedOutputStream adds flexibility. For example, you can add compression, encryption, or send the data to another target by piping data through an appropriate class.

--David