Query AS400/iSeries from JDBC

We are migrating an in house system from the AS400 to Oracle, and I needed to look at the data in its “raw” format to assist with a migration plan. I ended up using JDBC rather than the GUI provided by IBM. I have always found this investment to be worth my time. It eventually leads to reuse to perhaps do the actual migration, after the data has been mapped.

What I found is that it is fairly easy to get connected, but the terminology differs. For example, I assumed that TABLE_OWNER in the system catalog would be the schema to use to qualify the owner of a given table. As you can probably tell, this is not necessarily correct. In my case, I ended up querying the systables view in the catalog, and found a column named table_schema. The value in this column for the table of interest is, of course, the correct name to qualify the table.

Below is the soup to nuts simple class, just to get started.

import java.sql.*;

public class db2RMSTables {
  public static void main (String args[]) {
    try {
      Class.forName("com.ibm.as400.access.AS400JDBCDriver");
      System.out.println("getting connection...");
      Connection c = DriverManager.getConnection("jdbc:as400://hostname:port;naming=system;errors=full","username","password");
      System.out.println("got connection!!!");
      DatabaseMetaData m = c.getMetaData();
      ResultSet tables = m.getTables(null, null, null, null);
      ResultSet rst = c.createStatement().executeQuery("select * from EXPDEVD/\"MRCHCAT\"");
      ResultSetMetaData rsmd = rst.getMetaData();
      while (rst.next()) {
        for (int i = 1; i <= rsmd.getColumnCount(); i++) {
          System.out.println(rsmd.getColumnName(i) + " " + rst.getString(i));
        }
        System.out.println("-----------------------------------------------------------");
      }

    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
}

For the sake of completeness, I used the JTOpen driver. I simply placed the jt400.jar file in my CLASSPATH, and all was good with the world.