6 Replies Latest reply on Sep 12, 2013 8:28 AM by mbierenfeld

DB2 Datasource Problem

mbierenfeld Sep 10, 2013 8:41 AM

Hello,

I installed the DB2 Drivers as two separate JBOSS Modules. One for the "app"-Driver and one for the "jcc" driver.

The app Driver is needed because of a special requirement in the application.

So I have 2 Modules in

jboss-as-7.1.1.Final/modules/com/ibm/db2/9/7/app/main

and

jboss-as-7.1.1.Final/modules/com/ibm/db2/9/7/type4/main

These are used in 2 Datasources/Drivers and are defined in standalone.xml as

         <datasource jndi-name="java:jboss/jdbc/DB2LV1871_APP" pool-name="vvsk1_app" enabled="true" use-java-context="false" use-ccm
="false">
                    <connection-url>jdbc:db2:lvmbi</connection-url>
                    <connection-property name="currentSchema">
                        test
                    </connection-property>
                    <connection-property name="dateFormat">
                        3
                    </connection-property>
                    <driver>com.ibm.db2.app</driver>
                    <driver-class>COM.ibm.db2.jdbc.app.DB2Driver</driver-class>
                    <security>
                        <user-name>test</user-name>
                        <password>tset</password>
                    </security>

                </datasource>
                <datasource jta="false" jndi-name="java:jboss/jdbc/DB2LV1871_TYPE4" pool-name="vvsk1_type4" enabled="true" use-java-context
="false" use-ccm="false">
                    <connection-url>jdbc:db2://ryker.muc.lv1871.de:50003/vvsk1</connection-url>
                    <connection-property name="currentSchema">
                        test
                    </connection-property>
                    <connection-property name="dateFormat">
                        3
                    </connection-property>
                    <driver>com.ibm.db2.jcc</driver>
                    <driver-class>com.ibm.db2.jcc.DB2Driver</driver-class>
                    <security>
                        <user-name>test</user-name>
                        <password>tset</password>
                    </security>

using these drivers definition

               <drivers>
                    <driver name="com.ibm.db2.jcc" module="com.ibm.db2.9.7.type4">
                        <driver-class>com.ibm.db2.jcc.DB2Driver</driver-class>
                    </driver>
                    <driver name="com.ibm.db2.app" module="com.ibm.db2.9.7.app">
                        <driver-class>COM.ibm.db2.jdbc.app.DB2Driver</driver-class>
                    </driver>
                </drivers>

When I do a lookup on "java:jboss/jdbc/DB2LV1871_TYPE4" and then a getConnection() I do receive :

3:37:25,707 WARN [org.jboss.jca.core.connectionmanager.pool.strategy.OnePool] (VAGen1-49-de.lv1871.app.lvme.MENUA-zeuszeus4711) IJ000604: Throwable while attempting to get a new connection: null: javax.resource.ResourceException: Could not create connection
        at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.getLocalManagedConnection(LocalManagedConnectionFactory.java:277)
        at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.createManagedConnection(LocalManagedConnectionFactory.java:235)
        at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreArrayListManagedConnectionPool.createConnectionEventListener(SemaphoreArrayListManagedConnectionPool.java:761)
        at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreArrayListManagedConnectionPool.getConnection(SemaphoreArrayListManagedConnectionPool.java:343)
        at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getSimpleConnection(AbstractPool.java:397)
        at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getConnection(AbstractPool.java:365)
        at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.getManagedConnection(AbstractConnectionManager.java:329)
        at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.getManagedConnection(AbstractConnectionManager.java:302)
        at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.allocateConnection(AbstractConnectionManager.java:464)
        at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:129)
        at com.shark.vagen.framework.comm.DBPool.getConnectionType4(DBPool.java:57) [CSP_Common.jar:]
        at com.shark.vagen.framework.application.Application.getConnectionType4(Application.java:1618) [CSP_Common.jar:]
        at com.shark.vagen.framework.records.cache.SqlParameterCache.loadParameterMetaData(SqlParameterCache.java:64) [CSP_Common.jar:]
        at com.shark.vagen.framework.records.cache.SqlParameterCache.getMetaData(SqlParameterCache.java:45) [CSP_Common.jar:]
        at com.shark.vagen.framework.records.SqlRecord.getParameterMetaData(SqlRecord.java:2777) [CSP_Common.jar:]
        at com.shark.vagen.framework.records.SqlRecord.getParameterMetaData(SqlRecord.java:2760) [CSP_Common.jar:]
        at com.shark.vagen.framework.records.SqlRecord.setInquiryInner(SqlRecord.java:1440) [CSP_Common.jar:]
        at com.shark.vagen.framework.records.SqlRecord.inquiry(SqlRecord.java:813) [CSP_Common.jar:]
        at com.shark.vagen.framework.records.SqlRecord.inquiry(SqlRecord.java:766) [CSP_Common.jar:]
        at de.lv1871.app.lvme.MENUA.menup01(MENUA.java:513) [LV1871_Src.jar:]
        at de.lv1871.app.lvme.MENUA.access$3(MENUA.java:482) [LV1871_Src.jar:]
        at de.lv1871.app.lvme.MENUA$1.run(MENUA.java:201) [LV1871_Src.jar:]
        at com.shark.vagen.framework.application.Application.mainRun(Application.java:2938) [CSP_Common.jar:]
        at com.shark.vagen.framework.application.Application.main(Application.java:2743) [CSP_Common.jar:]
        at com.shark.vagen.framework.application.MainApplication.runApplication(MainApplication.java:54) [CSP_Common.jar:]
        at com.shark.vagen.framework.application.ApplicationHandler.dxfr(ApplicationHandler.java:256) [CSP_Common.jar:]
        at com.shark.vagen.framework.application.ApplicationSession$ApplicationRunner.run(ApplicationSession.java:141) [CSP_Common.jar:]
        at java.lang.Thread.run(Thread.java:738) [vm.jar:1.6.0]
Caused by: javax.resource.ResourceException: Wrong driver class [class COM.ibm.db2.jdbc.app.DB2Driver] for this connection URL [jdbc:db2://ryker.muc.lv1871.de:50003/vvsk1]
        at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.getLocalManagedConnection(LocalManagedConnectionFactory.java:251)
        ... 27 more

I have no clue whats going on since I am using "java:jboss/jdbc/DB2LV1871_TYPE4" that should use "<driver>com.ibm.db2.jcc</driver>. This driver refers to

<driver-class>com.ibm.db2.jcc.DB2Driver</driver-class>

Any help is appretiated

1. Re: DB2 Datasource Problem

mbierenfeld Sep 11, 2013 4:40 AM (in response to mbierenfeld)

I think its a JBOSS Bug Pls see :

    public int getCountRows (String tableName) throws Exception {
    
        int anzahl = 0;
        
        DataSource dsource = null;
        DataSource dsource2 = null;
        Connection conn = null;
        Connection conn2 = null;
        PreparedStatement stmt = null;
        InitialContext ctx = new InitialContext ();;

        // dsource = (DataSource) ctx.lookup ("java:jboss/jdbc/vvsk1");
        // dsource = (DataSource) ctx.lookup ("jboss/jdbc/vvsk1");
        
        dsource = (DataSource) ctx.lookup ("java:jboss/jdbc/DB2LV1871_APP");
        dsource2 = (DataSource) ctx.lookup ("java:jboss/jdbc/DB2LV1871_TYPE4");
        
        conn = dsource.getConnection ();
        conn2 = dsource2.getConnection ();

        stmt = conn.prepareStatement ("select count(*) from TEST." + tableName);
        ResultSet result = stmt.executeQuery ();
        
        if (result.next ()) {
            
            anzahl = result.getInt(1);
        }

        result.close();
        stmt.close();
        conn.close ();
        conn2.close ();
         
        return anzahl;
        
    }

This reconstructs the Problem.

Kind regards

Michael

2. Re: DB2 Datasource Problem

ctomc Sep 11, 2013 6:26 AM (in response to mbierenfeld)

This looks strange,

can you try with 7.2.0 / EAP 6.1 that you can find at http://www.jboss.org/jbossas/downloads/
or even try with latest wildfly http://www.wildfly.org/download/

as there ware many things fixed in datasources subsystem since 7.1.1
Actions

3. Re: DB2 Datasource Problem

mbierenfeld Sep 11, 2013 9:14 AM (in response to ctomc)

The problem ist the same with 7.2. I have not tried wildfly yet.

5:08:05,586 WARN  [org.jboss.jca.core.connectionmanager.pool.strategy.OnePool] (EJB default - 10) IJ000604: Throwable while attempting to get a new connection: null: javax.resource.ResourceException: Could not create connection                                                                                          
        at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.getLocalManagedConnection(LocalManagedConnectionFactory.java:282)                   
        at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.createManagedConnection(LocalManagedConnectionFactory.java:240)                     
        at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreArrayListManagedConnectionPool.createConnectionEventListener(SemaphoreArrayListManagedConnectionPool.java:781)                                                                                                                                              
        at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreArrayListManagedConnectionPool.getConnection(SemaphoreArrayListManagedConnectionPool.java:344)                                                                                                                                                              
        at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getSimpleConnection(AbstractPool.java:397)                                                   
        at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getConnection(AbstractPool.java:365)                                                         
        at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.getManagedConnection(AbstractConnectionManager.java:329)                             
        at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.getManagedConnection(AbstractConnectionManager.java:302)                             
        at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.allocateConnection(AbstractConnectionManager.java:464)                               
        at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:139)                                                             
        at de.lv1871.hello.EchoBean.getCountRows(EchoBean.java:69) [EjbHelloWorld.jar:]

and then later :

Caused by: javax.resource.ResourceException: Wrong driver class [class COM.ibm.db2.jdbc.app.DB2Driver] for this connection URL [jdbc:db2://ryker.muc.lv1871.de:50003/vvsk1]                                                                                                                                                   
        at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.getLocalManagedConnection(LocalManagedConnectionFactory.java:256)                   
        ... 63 more

Very strange. This is akiller bug for our porting project to JBOSS7 :-(

4. Re: DB2 Datasource Problem

mbierenfeld Sep 12, 2013 5:22 AM (in response to mbierenfeld)

I checked it on AIX, Windows and Linux. Seems to be a bug pretty sure.
Actions
5. Re: DB2 Datasource Problem

ctomc Sep 12, 2013 5:51 AM (in response to mbierenfeld)
Hi, i missed it previously, there is bug in your configuration...

                <datasource jta="false" jndi-name="java:jboss/jdbc/DB2LV1871_TYPE4" pool-name="vvsk1_type4" enabled="true" use-java-context
="false" use-ccm="false">
                    <connection-url>jdbc:db2://ryker.muc.lv1871.de:50003/vvsk1</connection-url>
                    <connection-property name="currentSchema">
                        test
                    </connection-property>
                    <connection-property name="dateFormat">
                        3
                    </connection-property>
                    <driver>com.ibm.db2.jcc</driver>
                    <driver-class>com.ibm.db2.jcc.DB2Driver</driver-class>
                    <security>
                        <user-name>test</user-name>
                        <password>tset</password>
                   </security>

driver-class should be com.ibm.db2.jcc.DB2Driver

--
tomaz
Actions

6. Re: Re: DB2 Datasource Problem

mbierenfeld Sep 12, 2013 8:28 AM (in response to ctomc)

?? I do not see the mistake ??

But I have found that if you change the order how the datasources are used / loaded you get rid of he Error

        dsource2 = (DataSource) ctx.lookup ("java:jboss/jdbc/DB2LV1871_TYPE4");
        dsource = (DataSource) ctx.lookup ("java:jboss/jdbc/DB2LV1871_APP");

        conn2 = dsource2.getConnection ();
        conn = dsource.getConnection ();

        stmt = conn.prepareStatement ("select count(*) from TEST." + tableName);
        ResultSet result = stmt.executeQuery ();

works like a charm, I have swapped the order

NOT WORKING is :

        dsource = (DataSource) ctx.lookup ("java:jboss/jdbc/DB2LV1871_APP");
        dsource2 = (DataSource) ctx.lookup ("java:jboss/jdbc/DB2LV1871_TYPE4");
        
        conn = dsource.getConnection ();
        conn2 = dsource2.getConnection ();

        stmt = conn.prepareStatement ("select count(*) from TEST." + tableName);
        ResultSet result = stmt.executeQuery ();

I will try and play with preloaded Pools in order to get rid of that Problem. Strange it is somehow related to the "Driver load ordering". Definitly a Bug.

Go to original post