• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Use SQL to Send Database Files via FTP

    May 18, 2005 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

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Chilli IT

    Chilli is one of the UKs leading IBM support and management providers with 20 years’ experience in the power and storage industry. Our bespoke solutions for maintenance, security and infrastructure delivers a service which is cost effective, increases productivity and enhances efficiency. Our ethical approach and unrivalled knowledge has secured business partnerships with blue-chip companies in the technology, retail, banking and travel sectors.

    As an IBM Business Partner, we provide you with the peace of mind that you are working in partnership with a company accredited to the highest standard. Our team of experts have worked together for many years and deliver projects which include consolidation, High Availability, Operating System upgrades; and backup and recovery installations.

    Contact us to see how we can help your business with IBM support and management.

    www.chilli-it.co.uk

    info@chilli–it.co.uk

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Expand Simplifies Network Device Management AppWorx Brings Job Scheduler to OS/400

    Leave a Reply Cancel reply

Volume 5, Number 20 -- May 18, 2005
THIS ISSUE
SPONSORED BY:

WorksRight Software
Profound Logic Software
Guild Companies

Table of Contents

  • Prevent Access to System Request Menu
  • Use SQL to Send Database Files via FTP
  • Admin Alert: Configuring an i5/OS-based EIM Table for Single Sign-On

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • DRV Brings More Automation to IBM i Message Monitoring
  • Managed Cloud Saves Money By Cutting System And People Overprovisioning
  • Multiple Security Vulnerabilities Patched on IBM i
  • Four Hundred Monitor, June 22
  • IBM i PTF Guide, Volume 24, Number 25
  • Plotting A Middle Age Career Change To IBM i
  • What Is Code Transformation Even?
  • Guru: The CALL I’ve Been Waiting For
  • A Frank Solstice
  • The Inevitable Wave Of Power9 Withdrawals Begins

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2022 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.