Simplest way to access a DB2 database on iSeries from a Linux (ubuntu) machine?

Question

I would like to run an SQL query on an iSeries (...or "System i" or "AS/400"...) machine as part of a Nagios check, but haven't found a suitable way of interfacing the database yet.

IBM suggests using the ODBC driver of System i Access for Linux with unixODBC, but since both systems are new to me, I'd like to know if there are other ways of doing this.

Hacks involving telnet and expect are perfectly fine. :-)

Answer 1

Found at Nagios Exchange:
DB2 Checker
Download DB2 Checker

untested...

Answer 2

I think this would be the simplest ...
Same as Access for Linux but it is the open source version: JTOpen

example taken from iSeries Information Center:

 // Connect to the server.
 Connection c = DriverManager.getConnection("jdbc:as400://mySystem");

 // Create a Statement object.
 Statement s = c.createStatement();

 // Run an SQL statement that creates
 // a table in the database.
 s.executeUpdate("CREATE TABLE MYLIBRARY.MYTABLE (NAME VARCHAR(20), ID INTEGER)");

 // Run an SQL statement that inserts
 // a record into the table.
 s.executeUpdate("INSERT INTO MYLIBRARY.MYTABLE (NAME, ID) VALUES ('DAVE', 123)");

 // Run an SQL statement that inserts
 // a record into the table.
 s.executeUpdate("INSERT INTO MYLIBRARY.MYTABLE (NAME, ID) VALUES ('CINDY', 456)");

 // Run an SQL query on the table.
 ResultSet rs = s.executeQuery("SELECT * FROM MYLIBRARY.MYTABLE");

 // Close the Statement and the
 // Connection.
 s.close();
 c.close();

Answer 3

I had this source on my disk. It's a good example. For clarity, I did change the machine, catalog and table name in the source. Hope it helps. It uses the JDBC driver from the JTOpen project.

Notice that with this specific driver you can access the iSeries DB2 database as any other database. Also, the DB2 database on the iSeries is just one of the versions of the IBM Universal Database. However, you can do iSeries specific tricks, even with the JDBC driver. But if you want to stay SQL defaults only, that is fine too.

import java.sql.*;

public class TestSQL {

 public static void main(String[] args) {

  try {
   DriverManager
     .registerDriver(new com.ibm.as400.access.AS400JDBCDriver());
   Connection conn = DriverManager
     .getConnection("jdbc:as400://YOURISERIES/YOURLIBRARY");
   Statement select = conn.createStatement();
   ResultSet rs = select.executeQuery("select * from whatever");

   ResultSetMetaData rsmd = rs.getMetaData(); 
   while (rs.next()) {
    for (int x=1; x <= rsmd.getColumnCount(); x++) {
     System.out.print(rs.getString(x));
    }
    System.out.println();
   }
  } catch (Exception e) {
   System.out.println("error: " + e.getMessage());
  }
  System.exit(0);
 }
}