Connecting to a DB2 table or native as400 from Java - There are two ways to do this:
1) Using JDBC which is basically SQL for Java. For “select” statements you simply get a ResultSet object which
you can loop through for each record/row.
2) Using record level access using IBM's Java classes. This requires creating objects for each field and for
each record format. However it does offer methods similar to chain, read, update and write. For me this
requires jumping through to many hoops, plus it’s is distinctly for DB2 tables only.
I prefer to use JDBC because it is much easier, plus it’s universal. By simply changing the database driver you
can connect to any SQL database, such as MySQL.
Before getting started with a Java JDBC program for processing DB2 files there are a few assumptions and steps
I’d like to cover first.
a) Java and its toolkit has been loaded on your AS400, iSeries, i5 or what ever new name IBM
will think of next.
b) All development, unit testing and debug is initially done on a remote PC using Eclipse, NetBeans or
Websphere. (or even TextPad). Eventually your Java code can end up running on the AS400 in the IFS
system and QSH environment, but as far as I know, there is no good IDE that runs directly over the IFS
system on the AS400. I use NetBeans, it’s free and does just about everything, including JSP and Web apps.
c) You need the jt400.jar file in your class path which means you need to copy it to your local
hard drive. This has all the classes and drivers needed to connect to the AS400 for just about
everything. Warning – it is big. Use Navigator or FTP to copy it down. You can find it in the
IFS system on your 400 and the path maybe different for various releases. For release 5.2 you
can find it in
/QIBM/ProdData/Http/Public/jt400/lib/jt400.jar
And while your at it, copy db2_classes.jar too. You should be able to find it in
/QIBM/ProdData/Java400/ext/db2_classes.jar
I’ll discuss this jar file later, but might as well copy it now while
you’re copying the other.
After you have copied the jar files you need to add them you your classpath. Do this within
your IDE under your current project. There should be options under properties to add jar files.
Just take that option and point it to where you copied them to.
d) CLASSPATH – Think of the classpath as a combination of library list and binding directory,
but a little trickier to get use to. When you add a jar file to your classpath you’re basically
binding it to your project. When you add a directory to your classpath you’re basically adding a
library to your library list.
e) Making the connection: You will need the following to make the connection to the AS400.
a. IP address
b. User profile
c. Password
d. Schema – Think of the schema as the library your files/table are in.
Otherwise you have to qualify them in the SQL statements.
e. File/tables to query.
Note: the user profile and password are only needs when connecting remotely from your PC. When
running on the 400 you are already logged in so you do not need to set them. There is a Boolean
method in the AS400 class called “isLocal”. I use this in my Java code to determine when and if
I need to set the user id and password. I also use this to determine which database driver to use.
If running remotely I use “jt400.jar”, if running local (on the 400) I use “db2_classes.jar”.
Why “db2_classes.jar” you ask?. It is lighting fast compared to “jt400.jar” however it does not worked
remotely. You need the jar on your workstation to compile the Java code but it will not work. At least I
could not get it to work. Also the increased in speed is mostly realized with insert and updates, not
so much for select statements. For example: I’ve written a multi-thread Java program that takes incoming
xml documents and then writes data out to a set of DB2 table. Using db2_classes.jar it can insert 1,500
to 2,000 records per second. Using jt400.jar is like 400 records per second.
f) The AS400 class vs. Connection class. These are two classes that can be used to connect to
the AS400 and each one has a different purpose. In my example I use both. The Connection
class is used just for JDBC and running sql statements. The AS400 class is used for everything
else like calling ILE programs, access to Data Queues and Data Areas and other AS400 specific
functions. Each one requires a user id and password to connect. If all you are running is sql
then you do not need the AS400 class. However I use it primarily for the “isLocal” method.
Plus there is a method for validating user profiles too.
g) Steps needed to connect and run a sql statement.
a. Create new objects for AS400 and Connection (AS400 is optional for sql)
b. Create a new properties object and set user id, password and schema for it.
c. Establish the connection to the database using the connection, database driver and
properties objects.
d. Query the table/file
i. Prepare an sql statement
ii. Execute the sql statement which creates a ResultSet object.
iii. Process the results and then close the cursor
e. Close the connection(s).
Take the JDBC sample program below and copy it into your IDE. There are constants in this program where you
put your settings like ip address, user id, password, schema and the sql select statement. The steps described
above are grouped into three subroutines. This example will list the records to the console. Give it a try.
Note: If you did not load the db2_connection.jar file then rem out the reference to it.
/*
* MyAS400jdbc.java
*
* Created on June 15, 2005, 1:25 PM
*/
import java.io.*;
import java.util.*;
import java.net.*;
import java.sql.*;
// Import the i5 tool kit.
import com.ibm.as400.access.*;
public class MyAS400jdbc {
// Define instance fields
private AS400 server = null;
private Connection connection = null;
private Properties prop = null;
// Define the constants
private static final String IP_ADDRESS = "what ever it is";
private static final String USER_ID = "what ever it is";
private static final String PASSWORD = "what ever it is";
private static final String SCHEMA = "what ever it is";
private static final String QUERY = "select * from yourfile";
/** Creates a new instance of MyAS400jdbc */
public MyAS400jdbc() {
}
//***********************************************************
// Define method to set items in the optional connection
// properties object.
//***********************************************************
public Properties setProperties(boolean local) {
// Create a properties object and if this in NOT
// running on the local system then user id and password
// to skip the sign-on prompting..
Properties prop = new Properties();
prop.put("naming", "system");
prop.put("errors", "full");
prop.put("libraries", SCHEMA + ", *LIBL");
if (!local) {
prop.put("user", USER_ID);
prop.put("password", PASSWORD);
}
return prop;
} // serverProperties
//***********************************************************
// Define method make the connection
//***********************************************************
public void makeConnection() {
// Create a server sign-on object to manage a set of socket
// connections to the iSeries, and to set sign-on or connection
// behavior.
server = new AS400(IP_ADDRESS);
// Setup the host connection
// Next see if this is running locally on
// the AS400. If not set login information.
if (!server.isLocal()) {
try {
System.out.println("Program is running remotely to the server/iSeries.");
server.setUserId(USER_ID);
server.setPassword(PASSWORD);
// Start services.
server.connectService(AS400.DATABASE);
}
catch(Exception bummer) {
System.out.println("Error: Creating a AS400 connection object failed: "
+ bummer.getMessage() + " - Program is terminating.");
System.exit(3);
}
}
// Next steps are defining the driver and then
// creating the connection with it. The driver
// and connection can be set in one of two ways:
// 1) native JDBC or 2) AS400 driver.
if (server.isLocal()) {
// Connect using native JDBC drivers
try {
// Use the native JDBC driver.
DriverManager.registerDriver(new com.ibm.db2.jdbc.app.DB2Driver());
prop = setProperties(server.isLocal());
// Now connect
connection = DriverManager.getConnection("jdbc:db2://" + IP_ADDRESS, prop);
System.out.println("Connection established using the JDBC native driver(s)");
}
catch(Exception jdbc) {
System.out.println("Error setting driver and connection using "
+ "JDBC native driver. The error message is as follows: "
+ jdbc.getMessage());
System.exit(2);
}
}
else {
// Connect using AS400 drivers
try {
// Use the AS400 driver.
DriverManager.registerDriver(new com.ibm.as400.access.AS400JDBCDriver());
prop = setProperties(server.isLocal());
// Now connect
connection = DriverManager.getConnection("jdbc:as400://" + IP_ADDRESS, prop);
System.out.println("Connection established using the AS400 driver(s)");
}
catch(Exception as400) {
System.out.println("Error setting driver and connection using "
+ "the AS400 drivers. The error message is as follows: "
+ as400.getMessage());
System.exit(2);
}
}
} // of connectionToDataBase
//***********************************************************
// This method will execute the SQL select statement
// and then print the records to the screen.
//***********************************************************
public void processSQL() {
// Trap the SQL exceptions.
try {
// Prepare the statement.
PreparedStatement select = connection.prepareStatement(QUERY);
// Now call the method with the prepared statement.
ResultSet qryResult = select.executeQuery();
if (qryResult == null) {
System.out.println("result is null");
return;
}
// Get the column count to the table we just queried.
int columnCount = qryResult.getMetaData().getColumnCount();
System.out.print("\n");
// Now loop through the rows.
while (qryResult.next()) {
// Loop though all the columns for this record.
for (int j = 1; j <= columnCount; j++)
System.out.print(qryResult.getString(j) + " ");
// End of record, start a new line.
System.out.print("\n");
}
// Done, close the cursor.
qryResult.close();
}
catch(SQLException nuts) {
System.out.println("Error trying to prepare and run a select statement. "
+ "The following is the error message "
+ nuts.getMessage());
}
// Done
} // of processSQL
//***********************************************************
// Define method close the connection(s)
//***********************************************************
public void closeConnection() {
// Try closing the iSeries connection.
if (connection != null) {
try {
server.disconnectAllServices();
connection.close();
System.out.println("Connection closed");
}
catch(Exception ugh) {
System.out.println("Error trying to close connection(s): "
+ ugh.getMessage());
}
}
} // of closeConnection
//***********************************************************
// Begin main method/program
//***********************************************************
public static void main(String args[]) {
System.out.println("In MyAS400jdbc now");
MyAS400jdbc iSeries = new MyAS400jdbc();
iSeries.makeConnection();
iSeries.processSQL();
iSeries.closeConnection();
System.exit(0);
} // end of main
}
|
