Method for nightly import from AS400 to MySQL on Linux

Posted on 2006-04-26
Last Modified: 2010-05-18
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.

Question by:Rakafkaven
    LVL 27

    Expert Comment

    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.

    LVL 4

    Author Comment

    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?
    LVL 4

    Author Comment

    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.
    LVL 4

    Author Comment

    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

    Accepted Solution

    Closed, 500 points refunded.
    The Experts Exchange
    Community Support Moderator of all Ages

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    I have been using r1soft Continuous Data Protection ( for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
    Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
    This video discusses moving either the default database or any database to a new volume.
    This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now