[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 847
  • Last Modified:

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.

Thanks!
0
Rakafkaven
Asked:
Rakafkaven
  • 3
1 Solution
 
NopiusCommented:
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
or
       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.


0
 
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?
0
 
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.
0
 
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.
AS400: http://www-03.ibm.com/servers/eserver/iseries/toolbox/
MySQL: http://dev.mysql.com/downloads/connector/j/3.1.html

import java.sql.*;

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

public static void main(String[] args) {
      loadDrivers();
      makeConnection();
      retrieveData();
      makeMSConnection();
      uploadData();
} //main


static void loadDrivers() {
try {
      Class.forName("com.ibm.as400.access.AS400JDBCDriver");
      Class.forName("com.mysql.jdbc.Driver");
} catch(java.lang.ClassNotFoundException e) {
      System.err.print("ClassNotFoundException: ");
      System.err.println(e.getMessage());
} //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://10.2.2.11;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()
//procedure
String opSQL = "SELECT FIELD1, FIELD2, FIELD3, FIELD4, FIELD 5 "
      + "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.
stmt.execute("TRUNCATE TABLE MSTARGETTABLE");
//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 (rs.next()) {
//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"));      
pstmt.executeUpdate();
} //while
} catch(SQLException exud) {
      System.err.println("upload failed: " + exud.getMessage());
} //catch
} //uploadData
} //class
0
 
GranModCommented:
Closed, 500 points refunded.
GranMod
The Experts Exchange
Community Support Moderator of all Ages
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now