12 Replies Latest reply on Apr 25, 2012 5:51 PM by user12222339

    OSB DB Adapter Polling Issue

      Hello Everybody,

      I am doing DB Adapter polling in OSB.I have a db adapter which polls order table whenever there is a record with status='N'.The OSB component is on single node of Weblogic Server.

      1.The db adapter picks the order data with status='N' in the proxy service
      2.It transforms to webservice xsd
      3.Invokes a webservice.
      4.The webservice returns acknowledgement number.
      5.The order table should be updated with status 'Z' and attribute1=acknowledgement number

      OSB Service Design
      1.Proxy service calls business service to poll the order table
      2.Converts the payload to the xsd format required by target
      3.Invokes the webservice on target
      4.Receives acknowledgement number
      5.calls a plsql procedure which updates all the records within this batch with the acknowledgement number and status='Z'

      My configurations in db adapter
      Logical delete option in db adapter.

      Unread Value :'N'
      Read Value : 'A'

      Polling frequency : 300 seconds

      no of databse rows per XML document : 10

      Database rows per transaction : 10

      1.My process after getting the acknowledgement number it updates the status with Z and attribute1=acknowledgement number.At the of this transaction I am seeing that the status is being updated by

      the adapter to 'A' based on read value.I want the read value only to be updated initially but as soon as my OSB process picks it for further processing it should not update again.How can i prevent

      the status from getting updated to 'A'.

      2.I have 40 records in order table.Per the database rows per transaction it should process 10 records at a time and for each batch of 10 i should get one acknowledgement number.Instead i see that

      all the 40 records are updated with the same acknowledgement number.Kindly help as to how I can resolve this situation.

      Thanks & Regards,
        • 1. Re: OSB DB Adapter Polling Issue

          You are using XA db transaction(and not non-Xa)?

          • 2. Re: OSB DB Adapter Polling Issue
            Hi Arik,

            Yes.I am using XA datasource.

            Thanks & Regards,
            • 3. Re: OSB DB Adapter Polling Issue

              The thing is, when you use Xa connection, the commit is global, means, you are polling data(connection 1, same datasource), invoke for DB action (connection 2, same datasource) and the commit happens after the invoke (if that's end your thread).
              In your DB Adapter(Poll) check the "Get Active UnitOfWork". This is one way to ensure that the current transaction will be committed.

              • 4. Re: OSB DB Adapter Polling Issue
                Hi Arik,

                Thank you for your inputs.In my case I am using (connection1) for polling data with status 'N'.Invoking webservice and then changing the status to 'Z' using connection2 on the EBS Database.But in the db adapter since I gave read value as 'A' at the end of the transaction it commits the record to 'A' and overriding my status change to 'Z'.Kindly let me know how i can avoid this.

                • 5. Re: OSB DB Adapter Polling Issue
                  Hi Radha,

                  As I mentiod above: In your DB Adapter(that Poll the data) check the "Get Active UnitOfWork" option. This is one way to ensure that the current transaction will be committed (meaning value will change to 'A', commit and then continue to you procedure that update to 'Z').
                  Another option is to use for the polling connection Non-XA transaction.

                  • 6. Re: OSB DB Adapter Polling Issue
                    Hi Arik,

                    Thanks for the update.I am able to find the option of getActive unit of work in db adapter with select but not when i am polling.Should i set this property in toplink myself outside jdev?Will that work.

                    • 7. Re: OSB DB Adapter Polling Issue
                      ....I don't think it will work. It should be checked for invoke only.
                      Two options: 1. Set the property "idempotent" to false - this should enforce the process to dehydrate and commit.
                      2. Use a Non-Xa connection. This will work for sure
                      • 8. Re: OSB DB Adapter Polling Issue
                        Hi Arik,

                        I tried setting the property(idempotent) in jca file of the polling adapter and it gave me below error.Kindly help

                        <adapter-config name="EBS_FETCHDATA" adapter="Database Adapter" wsdlLocation="EBS_FETCHDATA.wsdl" xmlns="http://platform.integration.oracle/blocks/adapter/fw/metadata">
                        <connection-factory location="eis/DB/newebs" UIConnectionName="EBS" adapterRef=""/>
                        <endpoint-activation portType="EBS_FETCHDATA_ptt" operation="receive">
                        <activation-spec className="oracle.tip.adapter.db.DBActivationSpec">
                        <property name="DescriptorName" value="EBS_FETCHDATA.XxcmfOtmOrderHdrIfaceTmp"/>
                        <property name="QueryName" value="EBS_FETCHDATASelect"/>
                        <property name="MappingsMetaDataURL" value="EBS_FETCHDATA-or-mappings.xml"/>
                        <property name="PollingStrategy" value="LogicalDeletePollingStrategy"/>
                        <property name="MarkReadColumn" value="OTM_INTERFACE_STATUS"/>
                        <property name="MarkReadValue" value="A"/>
                        <property name="MarkUnreadValue" value="N"/>
                        <property name="PollingInterval" value="60"/>
                        <property name="MaxRaiseSize" value="10"/>
                        <property name="MaxTransactionSize" value="300"/>
                        <property name="NumberOfThreads" value="1"/>
                        <property name="ReturnSingleResultSet" value="false"/>
                        <property name="idempotent" value="false"/>


                        Invalid JCA transport endpoint configuration, exception: javax.resource.ResourceException: BINDING.JCA-12532 Cannot set JCA WSDL Property. Error while setting JCA WSDL Property. Property setIdempotent is not defined for oracle.tip.adapter.db.DBActivationSpec Please verify the spelling of the property.

                        • 9. Re: OSB DB Adapter Polling Issue
                          You need to set it in the partner link poprties.
                          open the DB partner link->property tab-> choose idempotent and set the value
                          • 10. Re: OSB DB Adapter Polling Issue
                            hi arik,
                            I followed your instructtion and set the property idempotent in the db adapter property tab.But this property was not visible in the db polling adapter wsdl that I have imported into OSB.Kindly help.

                            • 11. Re: OSB DB Adapter Polling Issue
                              Yes, because it's on the composite component properties. Did you run the process?

                              * Can you use a Non-Xa connection for this purpose? I think it is the correct way to do it.....
                              • 12. Re: OSB DB Adapter Polling Issue
                                Hi Arik,

                                Thank you for the inputs.I worked around the problem by setting the read value to 'Z' and the reserved value to 'A'.So at the end of the transaction my status is 'Z'.I used XA data source.Another option of working around this problem could be is to use sequencing polling strategy.I agree that non-XA data source will work for sure.