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

    DB2 Datasource Problem

    Michael Bierenfeld Newbie

      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
          Michael Bierenfeld Newbie

          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
            Tomaz Cerar Master

            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

            • 3. Re: DB2 Datasource Problem
              Michael Bierenfeld Newbie

              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
                Michael Bierenfeld Newbie

                I checked it on AIX, Windows and Linux. Seems to be a bug pretty sure.

                • 5. Re: DB2 Datasource Problem
                  Tomaz Cerar Master

                  Hi, i missed it previously, there is bug in your configuration...

                   

                  1.                 <datasource jta="false" jndi-name="java:jboss/jdbc/DB2LV1871_TYPE4" pool-name="vvsk1_type4" enabled="true" use-java-context 
                  2. ="false" use-ccm="false"> 
                  3.                     <connection-url>jdbc:db2://ryker.muc.lv1871.de:50003/vvsk1</connection-url> 
                  4.                     <connection-property name="currentSchema"> 
                  5.                         test 
                  6.                     </connection-property> 
                  7.                     <connection-property name="dateFormat"> 
                  8.                         3 
                  9.                     </connection-property> 
                  10.                     <driver>com.ibm.db2.jcc</driver> 
                  11.                     <driver-class>com.ibm.db2.jcc.DB2Driver</driver-class> 
                  12.                     <security> 
                  13.                         <user-name>test</user-name> 
                  14.                         <password>tset</password> 
                  15.                    </security> 

                   

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



                  --

                  tomaz

                  • 6. Re: Re: DB2 Datasource Problem
                    Michael Bierenfeld Newbie

                    ?? 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.