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.<\/p>\n
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.<\/p>\n
Below is the soup to nuts simple class, just to get started.<\/p>\n
\r\nimport java.sql.*;\r\n\r\npublic class db2RMSTables {\r\n public static void main (String args[]) {\r\n try {\r\n Class.forName(\"com.ibm.as400.access.AS400JDBCDriver\");\r\n System.out.println(\"getting connection...\");\r\n Connection c = DriverManager.getConnection(\"jdbc:as400:\/\/hostname:port;naming=system;errors=full\",\"username\",\"password\");\r\n System.out.println(\"got connection!!!\");\r\n DatabaseMetaData m = c.getMetaData();\r\n ResultSet tables = m.getTables(null, null, null, null);\r\n ResultSet rst = c.createStatement().executeQuery(\"select * from EXPDEVD\/\\\"MRCHCAT\\\"\");\r\n ResultSetMetaData rsmd = rst.getMetaData();\r\n while (rst.next()) {\r\n for (int i = 1; i <= rsmd.getColumnCount(); i++) {\r\n System.out.println(rsmd.getColumnName(i) + \" \" + rst.getString(i));\r\n }\r\n System.out.println(\"-----------------------------------------------------------\");\r\n }\r\n\r\n }\r\n catch (Exception e) {\r\n e.printStackTrace();\r\n }\r\n }\r\n}\r\n<\/pre>\nFor 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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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…<\/p>\n