Method for nightly import from AS400 to MySQL on Linux

I need to do a nightly import of a few tables from our AS400 to a MySQL database on our Linux server.  The Linux box has no GUI and should run unattended.  For various reasons, any setup or changes to the AS400 should be minimal or zero if possible.

The best-sounding suggestions I've found so far involve writing a small Java program and running it on the Linux machine with a cron job to connect to the AS400, pull the recordsets, and then connect to the MySQL database and drop in the results.  I'm willing to do this, but have no experience with Java.  So if there's a more simple approach, I'd love to hear it.

Not looking for a full-walkthrough to implementation; points will be awarded for pointing me in the right direction-- which may just be the aforementioned Java solution.  Of course, a point in the direction of a walkthrough would earn my undying gratitude.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You don't need Java.

Everything with import-export may be done via command line. So shell scripting is enough for you.
Do you have MySQL on AS400 or some other database?

If yes, you may dump your database remotely (man mysqldump), running mysqldump on Linux:

mysqldump --host=AS400.IP --user=remote-user --password=remote-pass --opt --databases remote-db-name > database.sql
then you may insert probagate to your table locally (drop is already there, so data will be overwritten, not appended):
       mysql  database < backup-file.sql
       mysql  -e 'source /patch-to-backup/backup-file.sql' database

examples are in man mysqldump

If you have questions about shell scripting, don't hesitate to ask.

RakafkavenAuthor Commented:
Unfortunately, the AS400 is running the native DB2 database.  Is there a command-line method to connect to a remote database of a different format?
RakafkavenAuthor Commented:
For lack of any answer, I'm just going with Java.  I would've given the points to anyone who even said "Yeah, Java's the way to go", just because that would've provided peace of mind.  Oh, well.  I'll leave it open for a while on the off chance that someone has a brilliant idea that will at least help out the Knowledge Base.
RakafkavenAuthor Commented:
For posterity, here's the Java program to do this.  To run, it needs the AS400 driver and the MySQL driver for Java, and they need to be referenced in the classpath when the program is compiled and run.

import java.sql.*;

public class TestDBDriver {
static Connection con;
static Statement stmt;
static ResultSet rs;

public static void main(String[] args) {
} //main

static void loadDrivers() {
try {
} catch(java.lang.ClassNotFoundException e) {
      System.err.print("ClassNotFoundException: ");
} //try
} //loadDriver

static void makeConnection() {
//the url contains an IP internal to a local network; the actual address of the AS400 and the actual
//database name should be substituted here
String url = "jdbc:as400://;database name=MYDB;
//the user and password to log in to the AS400 should be set here
String user = "MYUSRNAME";
String pwd = "MYPSWD";
try {
      con = DriverManager.getConnection(url, user, pwd);
} catch(SQLException ex) {
      System.err.println("database connection: " + ex.getMessage());
} //try
} //makeConnection

static void retrieveData() {
try {
DatabaseMetaData dmd = con.getMetaData ();
stmt = con.createStatement();
//write your select query here; the DATABASENAME.TABLENAME structure helps eliminate ambiguity
//you can also (assuming the AS400 user you're logged in as has appropriate permissions)
//use queries that write to the database once you've got your connection made in the makeConnection()
      + "FROM DATABASENAME" + dmd.getCatalogSeparator() + "TABLENAME";
rs = stmt.executeQuery(opSQL);
} catch(SQLException exrd) {
      System.err.println("retrieve failed: " + exrd.getMessage());
} //try
} //retrieveData

static void makeMSConnection() {
//this url assumes the MySQL database is on the local machine, and ends with the
//database name
String murl = "jdbc:mysql://localhost/MSDBNAME";
String muser = "MSUSER";
String mpass = "MSPSWD";
try {
//notice that we're reusing the con variable here to connect to the MySQL server.  This
//isn't a problem unless you forget and try to use it again later to connect to the AS400.
//If you're less lazy than me, you can keep them both by adding another variable.
      con = DriverManager.getConnection(murl, muser, mpass);
} catch(SQLException ex) {
      System.err.println("database connection: " + ex.getMessage());
} //try
} //makeMSConnection

static void uploadData() {
try {
stmt = con.createStatement();
//my goal is emptying out a table every time the program is run, and replacing it with
//the values taken from the AS400.  TRUNCATE clears the MySQL table data but leaves the
//structure.  Your mileage may vary.
//running this as a PreparedStatement increases speed and lets the values contain quotes
//and other special characters without breaking the procedure
PreparedStatement pstmt;
String msql = "INSERT INTO MSTARGETTABLE VALUES (?, ?, ?, ?, ?, ?)";
pstmt = con.prepareStatement(msql);

while ( {
//note the setString/getString and setBigDecimal/getBigDecimal.  Pick your
//Java data type based on your expected field data type.
      pstmt.setString(1, rs.getString("FIELD1").trim());
      pstmt.setString(2, rs.getString("FIELD2").trim());
      pstmt.setBigDecimal(3, rs.getBigDecimal("FIELD3"));
      pstmt.setBigDecimal(4, rs.getBigDecimal("FIELD4"));
      pstmt.setBigDecimal(5, rs.getBigDecimal("FIELD5"));
        pstmt.setBigDecimal(6, rs.getBigDecimal("FIELD6"));      
} //while
} catch(SQLException exud) {
      System.err.println("upload failed: " + exud.getMessage());
} //catch
} //uploadData
} //class
Closed, 500 points refunded.
The Experts Exchange
Community Support Moderator of all Ages

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.