Using Eclipselink (Eclipse Persistence Services Project) with DB/2 on System i

Posted on by bill_blalock

This post is about Eclipselink and my experience using it with the System i. It also describes some problems I ran into so you can avoid them if you try Eclipselink.

Eclipselink is Eclipse Persistence Services Project, version 1.0 was released July 9, 2008. See http://www.eclipse.org/eclipselink/ I won’t go into the history and general explanations. For some background on Eclipelink see http://eclipse.dzone.com/articles/introducing-eclipselink. I will say that Eclipselink based on 12 years of commercial usage as Oracles’s Toplink product. Eclipselink is mature and solid.

The focus of this post is

  • Eclipselink is easy to use and works well with DB2 on System I and
  • solutions to some problems that I ran into.

One view of “Java persistence” is an alternative to dealing directly with JDBC. My perspective is that of an RPG programmer. Generally the data bases / external files already exist and programs are developed to use them.

I started exploring Java persistence with Hibernate. Two or three books later I switched to iBatis. iBatis worked well but I found that there was too much generated code, some of which had to be tweaked to work with DB/400 (I mean DB/2 Universal Database for System i but that is too much typing). When the underlying System i data base changes (as they frequently do in development) the choice was between either regenerate the iBatis code and re-tweak, or patch the generated code.

I tried Eclipselink to see whether data base maintenance would be easier than with iBatis. Eclispelink is definitely much better in this regard, at least in my environment.

Now I have a data access jar which uses Eclipselink to

  • read externally described files produced by FTP server and client exit point programs
  • read and update a table created with SQL DDL
  • read a table created with SQL DDL into a memory object for fast access
  • read a Microsoft SQL table for FTP server log entries on a Windows server

That is two externally described files (not SQL) in different libraries, one SQL schema which can be switched between test and production, and an SQL schema on an external Windows server.

This one data access jar file, set of classes, is used by a Java server program running on the System i and a rich desktop client program running on several PCs. The data access jar is configured by a property file which allows

  • the native db2 JDBC driver to be used by the server program and as400 JDBC driver to be used by the desktop,
  • selection of either the production or test SQL tables, and
  • the server program to use log4j to log the Eclipselink activity and the client programs to use the logging built into Eclipselink.

Once I got the data access set of classes (jar) working in unit tests I easily got them plugged into the server program, then into the client.

My point is that Eclipselink works with the System i. It is flexible and has many capabilities. Once you learn enough it is very fast to code.

There is no generated code with Eclipselink. To use Eclipselink I had to write

  • Java classes to represent a row in a table / record in a file (I recycled those from the iBatis version of the project)
  • persistence.xml
  • a subclass of an Eclipselink class to provide things like user name, password, JDBC url and the JDBC class (as400 or db2) to Eclipselink. The information can be included in the persistence.xml file but this subclass allows the program to provide it or override the values in the persistence.xml file.

The fields in the Java classes that represent a row or record are connected to the column name or DDS field name using either JPA annotations or in a file named orm.xml. I used the JPA annotations.

Here is an example for one field from my externally described FTP client log record:

    /**
     * This field corresponds to the database column
     * CPRSSECDTA.EXFTPCLG00.LXTMSP
     */
    @Id
    @Column(name = “LXTMSP”)
    @Temporal(TemporalType.TIMESTAMP)
    private java.util.Date ftpcTmsp;

@Column is the field name in the DDS

@Temporal tells Eclipselink that the underlying column is a time type (the Temporal) and specifically what type. The Eclipselink implementation for DB2 handles the conversion of System i timestamp to SQL Timestamp and back.

@Id – primary key, a pitfall to avoid. JPA says each table has a primary key. The primary key can be one field or a combination of fields. Sequential files on the System i, like my FTP client log, don’t have primary keys. To make JPA / Eclipselink work you have to invent a primary key. That key doesn’t have to be defined as a key to the file. You don’t have to use the @Id, it just has to be there. The timestamp seemed like a good choice.

Converting between System i field types and Java field types is easy. Most of the conversions are automatic. You can provide custom converters in the Java class as an annotation or referencing a converter class. This is a converter annotation to convert indicators stored in 1A fields, ‘1’ for true to Java Boolean:

@ObjectTypeConverter (
    name=“booleanConverter”,
    dataType=java.lang.String.class,
    objectType=java.lang.Boolean.class,
    conversionValues={
        @ConversionValue(dataValue=“1”, objectValue=“true”)
    },
    defaultObjectValue = “false” )

dataXxxxxx is the type or value in the System i data base. objectXxxx is the type or value of the Java class. If the data base field to be converted is ‘1’ then the Java Boolean object is true, otherwise it is false. The conversion automatically goes both ways. This is how the converter is used for a field:

/**
* This field corresponds to the database column
* XFRCTL00.MONITORXFR
*/
@Convert(“booleanConverter”)
private Boolean monitorXfr;

In the System i data base file MONITORXFR is 1A, in the Java class monitorXfr is Boolean. The @Column annotation wasn’t necessary since the Java class and data base column names are the same.

Eclispelink simplifies the data base maintenance. When fields are added or removed from the database only the Java class which represents a row / record for that database has to be changed.

Queries can be built dynamically by the code then executed. Queries that are used frequently can be defined in the Java class for row/record or in the orm.xml file. Here is one of the queries for the FTP client log file:

@NamedQuery(
	name=“Exftpclg00.selectRange”,
	query=“SELECT lg FROM Exftpclg00 lg ” +
           “WHERE lg.operId IN (6,7) AND ” +
	   “lg.ftpcTmsp BETWEEN :argBeginDate AND ” +
           “:argEndDate ORDER BY lg.ftpcTmsp” )

This is a named query. It selects FTP client log entries for PUT and GET in a date range. Here is the method which executes it:

public static List<Exftpclg00>
exftpclg00GetRange( Date argStartDate, Date argEndDate ) {
	Query query = getEmSysmon().
             createNamedQuery(“Exftpclg00.selectRange”);
	query.setParameter( “argBeginDate”, argStartDate );
	query.setParameter( “argEndDate”, argEndDate );
	List<Exftpclg00> resultList = query.getResultList();
	return resultList;
	}

Query is an Eclipselink class. getEmSysmon() returns an Eclipselink EntityManager object which was instantiated previously. The entity manager builds a query based on the @NamedQuery in the Java class. The strings “argBeginDate” and “argEndDate” match the parameter names in the @NamedQuery. The method returns a List collection of rows/records which satisfy the query.

Pitfall: This stuff is case sensitive for the Java fields and classes referenced. The named query looks a lot like SQL but isn’t, it is JPA query language. In this snippet:
lg.ftpcTmsp BETWEEN :argBeginDate

  • ftpcTmsp is case sensitive, must match the field defined in the Java class
  • BETWEEN is not case sensitive
  • :argBeginDate is case sensitive, must match the parameter name pushed into the Query when it is executed (see method).

This example uses the JPA query language. Native SQL can be used in the same way. The annotation is @NamedNativeQuery.

The hard part was learning JPA and Eclipselink, then tweaking it for the System i. Using Eclipselink is very straightforward once you have the basics down. Investing time in getting a feel for JPA before trying Eclipselink pays big dividends.

Eclipselink has 12 years of development behind it. The maturity of the product shows. It doesn’t require or is part of a framework like Hibernate / Spring. It will work with any Java IDE, even CodeEdit.

There are three ways to use Eclipselink.

  • using JPA annotations or orm.xml
  • using the Eclipselink workbench
  • using the Eclipselink APIs directly

I first tried using Eclipselink with its workbench and the APIs. Wrong! If you try Eclipselink save yourself some time, use JPA as the user’s guide strongly suggests. If you are not familiar with JPA don’t expect to learn it from the Eclispelink user’s guide. This is a very good overview: http://www.javabeat.net/articles/5-introduction-to-java-persistence-apijpa-1.html

I have mentioned three things that tripped me up:

  • each “entity” (table or file) has to have a primary key
  • JPA query language is case sensitive, having to match the java fields and classes referenced.
  • the Eclipselink user’s guide was confusing to me and is not the place to learn about JPA

Another caution is that JPA, thus Eclispelink, requires full commitment control for tables written to and updated. You can’t simply “write a record”. The pattern is

  • start a transaction
  • insert, update, or delete
  • commit or rollback (which completes the transaction)

Once the transaction is started it has to be completed before another is begun. That restriction doesn’t apply to read only queries. The FTP client and server exit point log files in my application are not under commitment control and can be read with Eclipselink.

Another thing to be aware of is that you may need to subclass DB2Platform and include it in the persistence.xml file. I have found two differences in the way DB2 is implemented on the System i that effected Eclipselink. Only a few Eclipselink functions are effected by this, most work fine using the DB2Platform class. My subclass of DB2Platform and persistenance.xml to use is at the end of this post.

You customize the database session by subclassing SessionCustomizer. You can put the JDBC url, user, password, logging class, and jdbc class into the persistence.xml file. That is fine for development but you don’t want your application locked into these values when you deploy it. These values are in in the persistence.xml for unit testing. The ones provided by the SessionCustomizer of the application override them (or remove them from the deployed persistence.xml file).

Write a subclass of SessionCustomizer to set these values programmatically. When Eclipselink opens the database session it gets an instance of SessionCustomizer. If your SessionCustomizer is present and loaded the customizer() method will programmatically set values which Eclipselink will use in the connection when the Database session is opened.

My SessionCustomizer is presented at the end of the post. I use it by

  • creating a Properties object from a properties file (different for each application the data access classes are used in), then
  • calling the static setter methods of my SessionCustomizer with values read from the properties file.

The Eclipselink creates the EntityManager, it gets an instance of my SessionCustomizer through the persistence.xml file. The customizer() method provides what EntityManager needs to customize the database session.

In conclusion, I have used three open source Java persistence frameworks with DB/400. Eclipselink meets my needs best of the three from the perspective of a RPG. It is easy to work with and very powerful.

persistenance.xml

<?xml version=“1.0” encoding=“UTF-8”?>
<persistence version=“1.0” xmlns=“http://java.sun.com/xml/ns/persistence” xmlns:xsi=“http://www.w3.org/2001/XMLSchema-instance” xsi:schemaLocation=“http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd”>
	<persistence-unit name=“sysmon” transaction-type=“RESOURCE_LOCAL”>
	<class>domain_model.XfrctlSelection</class>
	<class>domain_model.Xfrctl00</class>
	<class>domain_model.Msgdef00</class>
	<class>domain_model.Fslog00</class>
	<class>domain_model.Exftpclg00</class>
	<properties>
		<!-- default, can be overridden by SessionSysmonCustomizer -->
		<property name=“eclipselink.jdbc.url” value=“jdbc:as400://TUSMN1A4:prompt=false;date format=iso;naming=sql;libraries=*LIBL SYSMONDB$”/>
		<!-- default, can be overridden by SessionSysmonCustomizer -->
		<property name=“eclipselink.jdbc.user” value=“XXXXXXXXXX”/>
		<!-- default, can be overridden by SessionSysmonCustomizer -->
		<property name=“eclipselink.jdbc.password” value=“xxxxxxxxxx”/>
		<!-- default, can be overridden by SessionSysmonCustomizer -->
		<property name=“eclipselink.jdbc.driver” value=“com.ibm.as400.access.AS400JDBCDriver”/>
		<property name=“eclipselink.session.customizer” value=“eclipselink.SessionSysmonCustomizer”/>
		<property name=“eclipselink.target-database” value=“eclipselink.DB2UDBiSeriesPlatform”/>
		<!-- default, can be overridden by SessionSysmonCustomizer -->
	 	<property name=“eclipselink.logging.logger” value=“eclipselink.Log4jSessionLog”/>
		<property name=“eclipselink.logging.session” value=“true”/>
		<property name=“eclipselink.logging.level” value=“INFO”/>
		</properties>
	</persistence-unit>
	<persistence-unit name=“dataexpress” transaction-type=“RESOURCE_LOCAL”>
	<class>domain_model.Ftplog01</class>
	<properties>
		<property name=“eclipselink.jdbc.url” value=“jdbc:sqlserver://TUSHD2SS:1433;databaseName=FTPLOG”/>
		<property name=“eclipselink.jdbc.user” value=“XXXXXXXXXX”/>
		<property name=“eclipselink.jdbc.password” value=“xxxxxxxxxx”/>
		<property name=“eclipselink.jdbc.driver” value=“com.microsoft.sqlserver.jdbc.SQLServerDriver”/>
		<property name=“eclipselink.logging.session” value=“true”/>
		<property name=“eclipselink.logging.level” value=“INFO”/>
	</properties>
	</persistence-unit>
</persistence>

subclass of DB2Platform

import java.util.Vector;

import org.eclipse.persistence.internal.sessions.AbstractSession;
import org.eclipse.persistence.platform.database.DB2Platform;
import org.eclipse.persistence.queries.ValueReadQuery;

/**
 *    <B>Purpose</B>: Provides specific behavior for DB2 Universal Database for iSeries.<P>
 *    IBM iSeries has SYSIBM schema for compatibility with DB2.  However the primary schema
 *    for SQL system tables is QSYS2.  All tables, table structures and behavior of QSYS2
 *    tables are not duplicated in SYSIBM schema.<P>
 *    <B>Responsibilities</B>:
 *        <UL>
 *            <LI>Override getNativeTableInfo() using QSYS2 schema and different column name.</LI>
 *        		 <UL>
 *               <LI>SYSTABLES in QSYS2 schema, not in SYSIBM schema</LI>
 *               <LI>SYSTABLE = 'N' replaces TBCREATOR NOT IN ('SYS', 'SYSTEM')</LI>
 *               <LI>CREATOR replaces TBCREATOR (not defined in QSYS2/SYSTABLES catalog.</LI>
 *               </UL>
 *            <LI>Override getTimestampQuery() using QSYS2 schema instead of SYSIBM.
 *        </UL>
 *
 * @since Eclipselink 1.0
 * @author Bill Blalock
 */
@SuppressWarnings(“serial”)
public class DB2UDBiSeriesPlatform extends DB2Platform {

	public DB2UDBiSeriesPlatform() {
		super();
	}

	/**
     * Return the catalog information through using the native SQL catalog selects
     * of DB2 Universal Database for iSeries.
     * This is required because many JDBC driver do not support meta-data.
     * Wildcards can be passed as arguments.
     * @param table
     * @param creator
     * @param session
     * @return Catalog information returned from iSeries.
     * @overide
     */
    @SuppressWarnings(“unchecked”)
	public Vector getNativeTableInfo(String table, String creator, AbstractSession session) {
        String query = “SELECT * FROM QSYS2.SYSTABLES WHERE SYSTABLE = 'N'“;
        if (table != null) {
            if (table.indexOf('%') != -1) {
                query = query + “ AND TBNAME LIKE “ + table;
            } else {
                query = query + “ AND TBNAME = “ + table;
            }
        }
        if (creator != null) {
            if (creator.indexOf('%') != -1) {
                query = query + “ AND CREATOR LIKE “ + creator;
            } else {
                query = query + “ AND CREATOR = “ + creator;
            }
        }
        return session.executeSelectingCall(new org.eclipse.persistence.queries.SQLCall(query));
    }

    /**
     * This method returns the query to select the timestamp
     * from the DB2 Universal Database for iSeries server.
     * @return ValueReadQuery to retrieve current timestamp from iSeries.
     * @override
     */
    public ValueReadQuery getTimestampQuery() {
                if (timestampQuery == null) {
            timestampQuery = new ValueReadQuery();
            timestampQuery.setSQLString(“SELECT DISTINCT CURRENT TIMESTAMP FROM QSYS2.SYSTABLES”);
        }
        return timestampQuery;
    }
}

subclass of SessionCustomizer

// http://wiki.eclipse.org/Configuring_a_Session_%28ELUG%29#Configuring_a_Session_Customizer_Class

import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.security.AccessController;

import org.eclipse.persistence.config.SessionCustomizer;
import org.eclipse.persistence.internal.security.PrivilegedAccessHelper;
import org.eclipse.persistence.logging.SessionLog;
import org.eclipse.persistence.sessions.DatabaseLogin;
import org.eclipse.persistence.sessions.Session;
import org.eclipse.persistence.internal.security.PrivilegedClassForName;

public class SessionSysmonCustomizer implements SessionCustomizer {

	private static String jdbcDriverType;
	private static String subprotocol;
	private static String userName;
	private static String password;
	private static String host;
	private static String sysmonDatabase;
	private static String loggerClass;
	private static boolean urlChanged;
	private static String logFilename;

	public SessionSysmonCustomizer () {
		// when object initially created set default host
		// and sysmon database for jdbc url
		if ( host == null & sysmonDatabase == null & jdbcDriverType == null ) {
			host = “TUSMN1A4”;
			sysmonDatabase = “SYSMONDB$”;
			subprotocol = “as400”;
			urlChanged = false;
		}
	}

	public void customize(Session session) throws Exception {

		DatabaseLogin login = session.getLogin();

		// have to figure out how to get classloader
		// getClassLoader() in SessionFactory

		if ( loggerClass != null ) {
            // create a custom logger
			Class sessionLogClass;

            if (PrivilegedAccessHelper.shouldUsePrivilegedAccess()){
            	sessionLogClass = (Class)AccessController.doPrivileged(
            			new PrivilegedClassForName(
            					loggerClass, true, ClassLoader.getSystemClassLoader() ) );
            } else {
            	sessionLogClass = PrivilegedAccessHelper.getClassForName(
            			loggerClass, true, ClassLoader.getSystemClassLoader() );
            }
            SessionLog aCustomLogger = (SessionLog)sessionLogClass.newInstance();
            aCustomLogger.setLevel(SessionLog.INFO );
            session.setSessionLog(aCustomLogger);
		}

		if (logFilename != null ) {
			session.setLog(new OutputStreamWriter( new FileOutputStream( logFilename )));
		}

		if ( userName != null ) {
			login.setUserName( userName );
		}

		if ( password != null ) {
			login.setPassword( password );
		}

		// driverClassName already set to com.ibm.as400.access.AS400JDBCDriver
		// only need to change if running on System i
		if ( jdbcDriverType != null && jdbcDriverType.equalsIgnoreCase(“db2”) ) {
			login.setDriverClassName( “com.ibm.db2.jdbc.app.DB2Driver”);
			subprotocol = “db2”;
			host=“*local”;
		} else {
			login.setDriverClassName( “com.ibm.as400.access.AS400JDBCDriver”);
			subprotocol = “as400”;
			host = “//” + host;
		}

		if ( urlChanged ) {
			String jdbcUrl = “jdbc:” + subprotocol + “:” +
				host +
				“:prompt=false;date format=iso;naming=qsl;libraries=*LIBL “ +
				sysmonDatabase;
			login.setConnectionString(jdbcUrl);
		}

	}

	/**
	 * Customize login user name of Eclipselink session.
	 * @param name the DatabaseLogin name to set.
	 */
	static public void setUserName(String _userName) {
		SessionSysmonCustomizer.userName = _userName;
	}

	/**
	 * Customize login user password of Eclipselink session.
	 * @param password the DatabaseLogin password to set
	 */
	static public void setPassword(String _password) {
		password = _password;
	}

	/**
	 * Customize logger class of Eclipselink session.
	 * @param loggerClass custom logging class to override
	 * AbstractSessionLog
	 */
	static public void setLoggerClass(String _loggerClass) {
		loggerClass = _loggerClass;
	}

	/**
	 * Customize log to write to file for Eclipselink session.
	 * @param logFilename, relative to current directory or
	 * absolute path from the root, for log file.
	 */
	static public void setLogFilename(String _logFilename) {
		logFilename = _logFilename;
	}

	/**
	 * Override login driver class of Eclipselink session to native
	 * DB2 driver.
	 * The name of the JDBC driver class to be used
	 *  (e.g. “com.ibm.as400.access.AS400JDBCDriver”) is determined
	 *  by the driverType param.
	 * @param driverType “db2” to override to native driver.
	 */
	static public void setJdbcDriverType(String _jdbcDriverType) {
		if ( _jdbcDriverType.equalsIgnoreCase(“db2”) ) {
			jdbcDriverType = “db2”;
			urlChanged = true;
		}
	}

	/**
	 * Customize host part of login jdbc url of Eclipselink session,
	 * default value is TUSMN1A4.
	 * @param host the host to set
	 */
	static public void setHost(String _host) {
		host = _host;
		urlChanged = true;
	}

	/**
	 * Customize libraries part of login jdbc url of Eclipselink session,
	 * default value is SYSMONDB.
	 * @param sysmonDatabase the sysmonDatabase to set
	 */
	static public void setSysmonDatabase(String _sysmonDatabase) {
		sysmonDatabase = _sysmonDatabase;
		urlChanged = true;
	}
}

Leave a Reply

Your email address will not be published. Required fields are marked *