8 Replies Latest reply on May 27, 2008 8:03 PM by Len DiMaggio

    ESB and DB2 - DB config change needed to handle quartz?

    Len DiMaggio Apprentice

      I'm wondering if someone has already seen and worked thru this problem.

      I'm trying to use DB2 as the message store DB for ESB. I believe that I have various datasource and persistence XML files correct. (I'm intentionally only looking at the JBM Db right now - not jBPM.)

      The server starts up cleanly, but soom goes into a very tight loop with this SQLexception:

      16:32:07,607 INFO [Http11Protocol] Starting Coyote HTTP/1.1 on http-127.0.0.1-8080
      16:32:07,767 INFO [AjpProtocol] Starting Coyote AJP/1.3 on ajp-127.0.0.1-8009
      16:32:07,783 INFO [Server] JBoss (MX MicroKernel) [4.2.2.GA (build: SVNTag=JBoss_4_2_2_GA date=200710221139)] Started in 3m:47s:138ms
      16:32:25,278 WARN [JDBCSupport] SQLException caught, SQLState 07001 code:-313- assuming deadlock detected, try:1
      com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -313, SQLSTATE: 07001, SQLERRMC: null
      at com.ibm.db2.jcc.b.hh.b(hh.java:1369)
      at com.ibm.db2.jcc.b.hh.c(hh.java:1356)
      at com.ibm.db2.jcc.c.db.k(db.java:352)
      at com.ibm.db2.jcc.c.db.a(db.java:60)
      at com.ibm.db2.jcc.c.t.a(t.java:52)
      at com.ibm.db2.jcc.c.tb.b(tb.java:202)
      at com.ibm.db2.jcc.b.ih.ab(ih.java:1898)
      at com.ibm.db2.jcc.b.ih.d(ih.java:2467)
      at com.ibm.db2.jcc.b.ih.W(ih.java:525)
      at com.ibm.db2.jcc.b.ih.executeUpdate(ih.java:508)

      Files created/modified:

      1) New: server/default/deploy/db2-ds.xml
      2) Modify: server/default/deploy/jbossesb.sar/juddi-ds.xml
      3) Modify: server/default/deploy/jbossesb.sar/esb.juddi.xml
      4) Modify: server/default/deploy/jbossesb.esb/message-store-ds.xml
      5) Modify: server/default/deploy/jbossesb.esb/jbossesb-service.xml
      6) New: server/default/deploy/jbossesb.esb/message-store-sql/db2/create_database.sql
      7) New: server/default/deploy/jbossesb.esb/message-store-sql/db2/drop_database.sql
      8) New: server/default/deploy/jboss-messaging.sar/db2-persistence-service.xml

      And - I'm seeing these tables created in the DB:

      db2 => list tables
      Table/View Schema Type Creation time
      ------------------------------- --------------- ----- --------------------------
      ADDRESS SOAPUSER T 2008-05-15-16.29.42.258649
      ADDRESS_LINE SOAPUSER T 2008-05-15-16.29.43.100286
      AUTH_TOKEN SOAPUSER T 2008-05-15-16.30.02.727883
      BINDING_CATEGORY SOAPUSER T 2008-05-15-16.29.50.632705
      BINDING_DESCR SOAPUSER T 2008-05-15-16.29.51.632281
      BINDING_TEMPLATE SOAPUSER T 2008-05-15-16.29.49.796659
      BUSINESS_CATEGORY SOAPUSER T 2008-05-15-16.29.37.564684
      BUSINESS_DESCR SOAPUSER T 2008-05-15-16.29.36.597666
      BUSINESS_ENTITY SOAPUSER T 2008-05-15-16.29.35.716044
      BUSINESS_IDENTIFIER SOAPUSER T 2008-05-15-16.29.38.588355
      BUSINESS_NAME SOAPUSER T 2008-05-15-16.29.39.432440
      BUSINESS_SERVICE SOAPUSER T 2008-05-15-16.29.46.402271
      CONTACT SOAPUSER T 2008-05-15-16.29.40.408214
      CONTACT_DESCR SOAPUSER T 2008-05-15-16.29.41.432802
      DISCOVERY_URL SOAPUSER T 2008-05-15-16.29.45.580508
      EMAIL SOAPUSER T 2008-05-15-16.29.43.923577
      HILOSEQUENCES SOAPUSER T 2008-05-15-16.30.47.352144
      INSTANCE_DETAILS_DESCR SOAPUSER T 2008-05-15-16.29.54.633428
      INSTANCE_DETAILS_DOC_DESCR SOAPUSER T 2008-05-15-16.29.55.803235
      JBM_COUNTER SOAPUSER T 2008-05-15-16.29.07.928850
      JBM_DUAL SOAPUSER T 2008-05-15-16.29.01.086111
      JBM_MSG SOAPUSER T 2008-05-15-16.29.04.377379
      JBM_MSG_REF SOAPUSER T 2008-05-15-16.29.02.313723
      JBM_POSTOFFICE SOAPUSER T 2008-05-15-16.29.15.568942
      JBM_ROLE SOAPUSER T 2008-05-15-16.29.10.298685
      JBM_TX SOAPUSER T 2008-05-15-16.29.05.802298
      JBM_USER SOAPUSER T 2008-05-15-16.29.09.283838
      MESSAGE SOAPUSER T 2008-05-15-16.28.59.290604
      PHONE SOAPUSER T 2008-05-15-16.29.44.731446
      PUBLISHER SOAPUSER T 2008-05-15-16.30.01.771883
      PUBLISHER_ASSERTION SOAPUSER T 2008-05-15-16.30.01.294869
      SERVICE_CATEGORY SOAPUSER T 2008-05-15-16.29.48.077022
      SERVICE_DESCR SOAPUSER T 2008-05-15-16.29.47.247411
      SERVICE_NAME SOAPUSER T 2008-05-15-16.29.48.950602
      TMODEL SOAPUSER T 2008-05-15-16.29.56.601222
      TMODEL_CATEGORY SOAPUSER T 2008-05-15-16.29.58.473195
      TMODEL_DESCR SOAPUSER T 2008-05-15-16.29.57.423168
      TMODEL_DOC_DESCR SOAPUSER T 2008-05-15-16.30.00.429197
      TMODEL_IDENTIFIER SOAPUSER T 2008-05-15-16.29.59.450008
      TMODEL_INSTANCE_INFO SOAPUSER T 2008-05-15-16.29.52.750093
      TMODEL_INSTANCE_INFO_DESCR SOAPUSER T 2008-05-15-16.29.53.811966
      41 record(s) selected.

        • 1. Re: ESB and DB2 - DB config change needed to handle quartz?
          Jiri Pechanec Apprentice

          Hi,

          just for sure, do you have set transaction isolation to READ_COMMITTED level on all used datasources?

          J.

          • 2. Re: ESB and DB2 - DB config change needed to handle quartz?
            Kevin Conner Master

            Also, is there more to the stacktrace? If you can show us what code is calling the update then we may have a better chance of identifying any issues.

            • 3. Re: ESB and DB2 - DB config change needed to handle quartz?
              Len DiMaggio Apprentice

              Thanks Jirka - my *-ds files d not include:

              <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>

              I'll try that now...

              • 4. Re: ESB and DB2 - DB config change needed to handle quartz?
                Len DiMaggio Apprentice

                That - adding TRANSACTION_READ_COMMITTED - didn't help.

                Here's the stack trace:

                12:10:37,496 WARN [JDBCSupport] Trying again after a pause
                12:10:38,184 WARN [JDBCSupport] SQLException caught, SQLState 07001 code:-313- assuming deadlock detected, try:3
                com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -313, SQLSTATE: 07001, SQLERRMC: null
                at com.ibm.db2.jcc.b.hh.b(hh.java:1369)
                at com.ibm.db2.jcc.b.hh.c(hh.java:1356)
                at com.ibm.db2.jcc.c.db.k(db.java:352)
                at com.ibm.db2.jcc.c.db.a(db.java:60)
                at com.ibm.db2.jcc.c.t.a(t.java:52)
                at com.ibm.db2.jcc.c.tb.b(tb.java:202)
                at com.ibm.db2.jcc.b.ih.ab(ih.java:1898)
                at com.ibm.db2.jcc.b.ih.d(ih.java:2467)
                at com.ibm.db2.jcc.b.ih.W(ih.java:525)
                at com.ibm.db2.jcc.b.ih.executeUpdate(ih.java:508)
                at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:251)
                at org.jboss.messaging.core.impl.JDBCPersistenceManager$1AddReferenceRunner.doTransaction(JDBCPersistenceManager.java:1318)
                at org.jboss.messaging.core.impl.JDBCSupport$JDBCTxRunner2.execute(JDBCSupport.java:465)
                at org.jboss.messaging.core.impl.JDBCSupport$JDBCTxRunner2.executeWithRetry(JDBCSupport.java:503)
                at org.jboss.messaging.core.impl.JDBCPersistenceManager.addReference(JDBCPersistenceManager.java:1353)
                at org.jboss.messaging.core.impl.ChannelSupport.handle(ChannelSupport.java:226)
                at org.jboss.messaging.core.impl.postoffice.MessagingPostOffice.routeInternal(MessagingPostOffice.java:2203)
                at org.jboss.messaging.core.impl.postoffice.MessagingPostOffice.route(MessagingPostOffice.java:489)
                at org.jboss.jms.server.endpoint.ServerConnectionEndpoint.sendMessage(ServerConnectionEndpoint.java:741)
                at org.jboss.jms.server.endpoint.ServerSessionEndpoint.send(ServerSessionEndpoint.java:383)
                at org.jboss.jms.server.endpoint.advised.SessionAdvised.org$jboss$jms$server$endpoint$advised$SessionAdvised$send$aop(SessionAdvised.java:87)
                at org.jboss.jms.server.endpoint.advised.SessionAdvised$send_7280680627620114891.invokeNext(SessionAdvised$send_7280680627620114891.java)
                at org.jboss.jms.server.container.SecurityAspect.handleSend(SecurityAspect.java:157)
                at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
                at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
                at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
                at java.lang.reflect.Method.invoke(Method.java:585)
                at org.jboss.aop.advice.PerInstanceAdvice.invoke(PerInstanceAdvice.java:121)
                at org.jboss.jms.server.endpoint.advised.SessionAdvised$send_7280680627620114891.invokeNext(SessionAdvised$send_7280680627620114891.java)
                at org.jboss.jms.server.endpoint.advised.SessionAdvised.send(SessionAdvised.java)
                at org.jboss.jms.wireformat.SessionSendRequest.serverInvoke(SessionSendRequest.java:95)
                at org.jboss.jms.server.remoting.JMSServerInvocationHandler.invoke(JMSServerInvocationHandler.java:143)
                at org.jboss.remoting.ServerInvoker.invoke(ServerInvoker.java:795)
                at org.jboss.remoting.transport.local.LocalClientInvoker.invoke(LocalClientInvoker.java:101)
                at org.jboss.remoting.Client.invoke(Client.java:1634)
                at org.jboss.remoting.Client.invoke(Client.java:548)
                at org.jboss.remoting.Client.invoke(Client.java:536)
                at org.jboss.jms.client.delegate.DelegateSupport.doInvoke(DelegateSupport.java:189)
                at org.jboss.jms.client.delegate.DelegateSupport.doInvoke(DelegateSupport.java:160)
                at org.jboss.jms.client.delegate.ClientSessionDelegate.org$jboss$jms$client$delegate$ClientSessionDelegate$send$aop(ClientSessionDelegate.java:477)
                at org.jboss.jms.client.delegate.ClientSessionDelegate$send_6145266547759487588.invokeNext(ClientSessionDelegate$send_6145266547759487588.java)
                at org.jboss.jms.client.container.SessionAspect.handleSend(SessionAspect.java:632)
                at org.jboss.aop.advice.org.jboss.jms.client.container.SessionAspect28.invoke(SessionAspect28.java)
                at org.jboss.jms.client.delegate.ClientSessionDelegate$send_6145266547759487588.invokeNext(ClientSessionDelegate$send_6145266547759487588.java)
                at org.jboss.jms.client.container.FailoverValveInterceptor.invoke(FailoverValveInterceptor.java:92)
                at org.jboss.aop.advice.PerInstanceInterceptor.invoke(PerInstanceInterceptor.java:105)
                at org.jboss.jms.client.delegate.ClientSessionDelegate$send_6145266547759487588.invokeNext(ClientSessionDelegate$send_6145266547759487588.java)
                at org.jboss.jms.client.container.ClosedInterceptor.invoke(ClosedInterceptor.java:170)
                at org.jboss.aop.advice.PerInstanceInterceptor.invoke(PerInstanceInterceptor.java:105)
                at org.jboss.jms.client.delegate.ClientSessionDelegate$send_6145266547759487588.invokeNext(ClientSessionDelegate$send_6145266547759487588.java)
                at org.jboss.jms.client.delegate.ClientSessionDelegate.send(ClientSessionDelegate.java)
                at org.jboss.jms.client.container.ProducerAspect.handleSend(ProducerAspect.java:278)
                at org.jboss.aop.advice.org.jboss.jms.client.container.ProducerAspect46.invoke(ProducerAspect46.java)
                at org.jboss.jms.client.delegate.ClientProducerDelegate$send_3961598017717988886.invokeNext(ClientProducerDelegate$send_3961598017717988886.java)
                at org.jboss.jms.client.container.ClosedInterceptor.invoke(ClosedInterceptor.java:170)
                at org.jboss.aop.advice.PerInstanceInterceptor.invoke(PerInstanceInterceptor.java:105)
                at org.jboss.jms.client.delegate.ClientProducerDelegate$send_3961598017717988886.invokeNext(ClientProducerDelegate$send_3961598017717988886.java)
                at org.jboss.jms.client.delegate.ClientProducerDelegate.send(ClientProducerDelegate.java)
                at org.jboss.jms.client.JBossMessageProducer.send(JBossMessageProducer.java:164)
                at org.jboss.jms.client.JBossMessageProducer.send(JBossMessageProducer.java:207)
                at org.jboss.jms.client.JBossMessageProducer.send(JBossMessageProducer.java:145)
                at org.jboss.jms.client.JBossMessageProducer.send(JBossMessageProducer.java:136)
                at org.jboss.internal.soa.esb.couriers.JmsCourier.deliver(JmsCourier.java:250)
                at org.jboss.internal.soa.esb.couriers.JmsCourier.deliver(JmsCourier.java:211)
                at org.jboss.internal.soa.esb.couriers.TwoWayCourierImpl.deliver(TwoWayCourierImpl.java:199)
                at org.jboss.soa.esb.monitoring.server.DataFilerJob.execute(DataFilerJob.java:84)
                at org.quartz.core.JobRunShell.run(JobRunShell.java:203)
                at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:520)

                • 5. Re: ESB and DB2 - DB config change needed to handle quartz?
                  Andrig Miller Novice

                  This Microsoft support article just might be pointing to the root cause of the problem. Of course, it is reference DB2's OLEDB provider and not the JDBC driver, but I bet the problem is the same:

                  http://support.microsoft.com/kb/819528

                  • 6. Re: ESB and DB2 - DB config change needed to handle quartz?
                    Len DiMaggio Apprentice

                    Thanks for the pointer Andy,

                    I'm not sure that this is the same situation - the ESB server is able to create the DB tables - but a few minutes later - it generates this exception:

                    16:32:25,278 WARN [JDBCSupport] SQLException caught, SQLState 07001 code:-313- assuming deadlock detected, try:1
                    com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -313, SQLSTATE: 07001, SQLERRMC: null
                    at com.ibm.db2.jcc.b.hh.b(hh.java:1369)
                    at com.ibm.db2.jcc.b.hh.c(hh.java:1356)
                    at com.ibm.db2.jcc.c.db.k(db.java:352)
                    at com.ibm.db2.jcc.c.db.a(db.java:60)
                    at com.ibm.db2.jcc.c.t.a(t.java:52)
                    at com.ibm.db2.jcc.c.tb.b(tb.java:202)
                    at com.ibm.db2.jcc.b.ih.ab(ih.java:1898)
                    at com.ibm.db2.jcc.b.ih.d(ih.java:2467)
                    at com.ibm.db2.jcc.b.ih.W(ih.java:525)
                    at com.ibm.db2.jcc.b.ih.executeUpdate(ih.java:508)

                    - Len

                    • 7. Re: ESB and DB2 - DB config change needed to handle quartz?
                      Aleksandar Kostadinov Novice

                      If you try the db2 native (type 2) driver that could help. At least error message could be different to give new pointers. See out DB2 HowTo for host that you can try on and connection url.

                      • 8. Re: ESB and DB2 - DB config change needed to handle quartz?
                        Len DiMaggio Apprentice

                        Thanks Alex - I tried that - but no additional information was logged. What I think I need to to get a debug version of: WrappedPreparedStatement.java