2 Replies Latest reply: Jan 9, 2014 1:27 AM by Juw RSS

    select thousand rows with dbadapter

    Juw

      hi guys,

       

      anyone have tried to select thousand of rows data from database using soa db adapter ?

      everytime i tried to select more than 5000 rows, then it will turn into error.

      i'm using jdev 11.1.1.7 soa 11.1.1.7

      error code from console

       

      [code]

      <Jan 7, 2014 5:46:52 PM ICT> <Error> <EJB> <BEA-010026> <Exception occurred during commit of transaction Name=[EJB com.collaxa.cube.engine.ejb.impl.bpel.BPELDeliveryBean.handleInvoke(com.collaxa.cube.engine.dispatch.message.invoke.InvokeInstanceMessage)],Xid=BEA1-1E4FA285A12B8F14E263(57883289),Status=Rolled back. [Reason=weblogic.transaction.internal.TimedOutException: Transaction has timed out when making request to XAResource 'jdbc/apps_bpm_domain'.],numRepliesOwedMe=0,numRepliesOwedOthers=0,seconds since begin=307,seconds left=54,XAServerResourceInfo[SOADataSource_bpm_domain]=(ServerResourceInfo[SOADataSource_bpm_domain]=(state=rolledback,assigned=soa_server1),xar=SOADataSource,re-Registered = false),XAServerResourceInfo[jdbc/smhr_bpm_domain]=(ServerResourceInfo[jdbc/smhr_bpm_domain]=(state=rolledback,assigned=soa_server1),xar=jdbc/smhr,re-Registered = false),XAServerResourceInfo[jdbc/apps_bpm_domain]=(ServerResourceInfo[jdbc/apps_bpm_domain]=(state=rolledback,assigned=soa_server1)

      ,xar=jdbc/apps,re-Registered = false),SCInfo[bpm_domain+soa_server1]=(state=rolledback),properties=({weblogic.transaction.name=[EJB com.collaxa.cube.engine.ejb.impl.bpel.BPELDeliveryBean.handleInvoke(com.collaxa.cube.engine.dispatch.message.invoke.InvokeInstanceMessage)]}),local properties=({weblogic.jdbc.jta.SOADataSource=[ No XAConnection is attached to this TxInfo ], weblogic.jdbc.jta.jdbc/apps=[ No XAConnection is attached to this TxInfo ], weblogic.jdbc.jta.jdbc/smhr=[ No XAConnection is attached to this TxInfo ]}),OwnerTransactionManager=ServerTM[ServerCoordinatorDescriptor=(CoordinatorURL=soa_server1+10.206.131.27:8001+bpm_domain+t3+, XAResources={SOADataSource_bpm_domain, eis/Apps/Apps, eis/tibjms/Queue, eis/activemq/Queue, WLStore_bpm_domain__WLS_soa_server1, EDNDataSource_bpm_domain, WLStore_bpm_domain_SOAJMSFileStore, WLStore_bpm_domain_UMSJMSFileStore_auto_2, eis/webspheremq/Queue, eis/AQ/aqSample, eis/fioranomq/Topic, eis/aqjms/Queue, eis/sunmq/Queue, eis/pramati/Queue, j

      dbc/hr_bpm_domain, eis/tibjms/Topic, eis/tibjmsDirect/Queue, eis/jbossmq/Queue, WSATGatewayRM_soa_server1_bpm_domain, eis/wls/Queue, eis/tibjmsDirect/Topic, WLStore_bpm_domain_AGJMSFileStore, WLStore_bpm_domain_BPMJMSFileStore, eis/wls/Topic, WLStore_bpm_domain_PS6SOAJMSFileStore, eis/aqjms/Topic, jdbc/smhr_bpm_domain, jdbc/apps_bpm_domain},NonXAResources={})],CoordinatorURL=soa_server1+10.206.131.27:8001+bpm_domain+t3+): weblogic.transaction.RollbackException: Transaction has timed out when making request to XAResource 'jdbc/apps_bpm_domain'.

      [/code]

       

      pls throw some light.

      thanks

        • 1. Re: select thousand rows with dbadapter
          S.Ananth

          The error points to timeout being exceeded during the select of large no. of records. Try after setting/increasing the query timeout value in the db adapter (you can set this via the db adapter configuration wizard).

          • 2. Re: select thousand rows with dbadapter
            Juw

            by default the value of the timeout is zero (0). it has no timeout for my understanding.

             

            now i'm trying to find workaround.

            i'll create 2 soa composite. 1 for get 500rows of data every time invoked. and 1 to loop to call the 1st composite.

            this one work but have a intermittent error.

            1st issue is i have to allocate a lot of connection pool to handle lots of db connection based on the db row data.

            2nd issue i can't trace the xml in SOA EM composite request.

             

            is the only way is to let the copy data from 1 database to another database handled by the DB ? cannot utilize the soa ?