Thursday, October 10, 2013

Try out Java EE @DataSourceDefinition with DB2 on GlassFish 4

Came back from Java One 2013, learned some tricks from Adam Bien, I decided to give @DataSourceDefinition a try on GlassFish 4 with DB2 database, after a little bit bumps, I got it work:

  1. Copy your DB2 JDBC driver files(db2jcc4.jar and db2jcc_license_cu.jar) to glassfish/lib direcotry.
  2. Setup a singleton EJB to define your DataSourceDefinition:
  3. @DataSourceDefinition(
    public class DataSourceConfiguration {
  4. Add the JNDI reference in your persistence.xml:
  5. java:global/jdbc/APP
  6. In your Rest resource file
  7. ...
    EntityManager em;
    public String getXml() {
        Query q = em.createQuery("select c from Customer c");
        List customers = q.getResultList();
        return "" + customers.get(1).getId() + "";
There are a few catches I ran into:
  • Error:
  • Failure in loading native library db2jcct2, java.lang.UnsatisfiedLinkError
    You need to add the vendor specific properties: driverType=4 to force it use type 4 JDBC driver.

Thursday, January 26, 2012

Test DB2 JDBC Data source failed in WebSphere 7 cluster

As far as how to create a new DB2 datasource in WebSphere 7 is pretty straightforward, it contains two steps:
1. Create a new JAAS J2C data entry to hold your username and password.
2. Create a new DataSource by using the newly created JAAS J2C data entry.
That's all you need to do. But where may be a catch when you try to do it in a cluster environment, you may run into the following exepction when you try to test your newly created Data source:
java.lang.Exception: java.sql.SQLException: null userid not supported

My solution is to restart the nodeagents on the custer nodes.

Thursday, December 22, 2011

How to setup and use DB2Mon

I used to use TOAD to monitor the connection to Oracle, but when I moving to DB2 world, I am struggling to find out a similar tool to look at what kind connections/activities happen on the Database. One of my collegue suggested DB2Mon, I set off and decide to give it a try, here I record what I need to do:
1. Start IBM DB2 Configuration Assistant, right click, Add Database Using Wizard, Manually configure a connection to a database; setup your database connection; Test your connection by right click the newly created connection->TestConnection->CLI
2. Start your DB2Mon, you should see your newly created Database connection, select it and input your user/password.

Thursday, December 08, 2011

How to get a JDBC connection in EJB 3 running on WebSphere

There are several ways to get a JDBC connection in EJB3 running on WebSphere:
1. JNDI lookup
2. @Resource Injection
2.1 In ibm-ejb-jar-bnd.xml
<session name="OrderRepositoryBean">
<resource-ref name="DFSAdabasDataSource" binding-name="jdbc/DFS_ADABAS" />
2.2 In OrderRespositoryBean
@Resource(name = "DFSAdabasDataSource")
private DataSource ds;
3. NOT WORK!!! @Resource Injection by using mappedName, assuming the JNDI name is : jdbc/DFS_ADABAS
// In OrderRepositoryBean
@Resource(mappedName = "jdbc/DFS_ADABAS")
private DataSource ds;
4. Using OpenJPAEntityManager
@PersistenceContext(unitName = "CACSAMP")
private EntityManager emDFS;
(Connection) ((OpenJPAEntityManager) emDFS.getDelegate()).getConnection();

Wednesday, December 07, 2011

Configure IBM InfoSphere Classic JDBC Driver datasource in WebSphere

To setup the InfoSphere Classic JDBC Data source in WebSphere, you could follow the following steps:
1. Create new JDBC Provider
1.1 Resources->JDBC->JDBC providers->new
1.2 Database type: User-defined; Implementation class name: com.cac.jdbc.ConnectionPoolDataSource; Name: IBM InfoSphere Classic JDBC
1.3 Class path: ${User-defined_JDBC_DRIVER_PATH}/DataFederationServer/cacjdbc21.jar
1.4 Review summary->Finish->save
2. Create Java 2 Connector authentication data entry
2.1 Security->Global security; Under Authentication cache settings, click Java Authentication And Authorization Service->J2C authnetication data; new
2.2 Alias: ; UserID: ; Password: ; Apply-> save
3. Create new Data Source
3.1 Resources->JDBC->Data sources; new
3.2 Data source name: DFS_XXXX; JNDI name: jdbc/DFS_ADABAS
3.3 Select the JDBC provider created in step 1.
3.4 Data store helper class name:; Uncheck used for CMP check box.
3.5 Component-managed authentication alias: the one you created in step 2.

3.6 Review summary->Finish->save
3.7 setup the Custom properties for the newly create Data source by click on the Data source name -> Customer properties
3.7.1 URL: your Data Federation Server JDBC URL;
webSphereDefaultIsolationLevel: 1 (READ_UNCOMMITED); very tricky, I can only make it work by setting to 1. It turns out the backend (ADABAS) is set to support READ_UNCOMMITED.
3.8 If you run into "JDBC hung when try to close connection" in your application, try to turn off the statement cache; Search IBM fix pack change list for similar problem for Oracle, and Sybase JDBC connection.
You almost done, just restart your web sphere server and start to use the JDBC Data source.

How to currentSchema for DB2 DataSource in WebSphere

Without setting the currentSchema for DB2, you need to fully qualified the DB object (table, view etc) name.
According IBM documentation, currentSchema means "This allows the end user or application to name SQL objects without having to qualify them by schema name."

How to setup the crrentSchema for DB2 DataSource in Websphere?
Resources->JDBC->Click your Data Source name->Customer properties->currentSchema.
Restart your Websphere to make it take effective.
BTW: The DataSource currentSchema setting could be co-exist with the openjpa.jdbc.Schema setting in persistent.xml without intereference with each other.

Friday, November 18, 2011

How to get the Database connection URL from OpenJPA EJB

Normally you would like to have a quick and easy way to tell which DB your application is connect to. To get this kind of you information in an OpenJPA/EJB3 stack, you may be able to do the following:
public String getDataBaseURL() {
        final Connection conn = (Connection) ((OpenJPAEntityManager) em.getDelegate()).getConnection();
        String url;
        try {
            url = conn.getMetaData().getURL();
        } catch (final SQLException e) {
            url = "Failed to get the DB URL";
        return url;

Tuesday, September 13, 2011

Windows firewall blocks access to Apache Tomcat service from remote computer

Today, my colleague installed Tomcat as windows service on a Windows 7 box, strangely he could access via and http://ipofserver:8080, but he could not access http://ipofserver:8080 from a remote computer. After some digging, it turns out to be the Windows 7 firewall blocked the remote access traffic. The solution of the problem is simple: just manually start the tomcat7.exe, Windows 7 will show the firewall warning dialog, just allow access.;-)