6 Replies Latest reply on Jun 9, 2019 12:55 PM by Damir Dev

    OSB Db Adapter Polling one record per time not working

    Damir Dev

      Hello everybody.

       

      I have

      1. Domain: AdminServer + one ManagedServer

      2. Weblogic Server ver 10.3.6.0

      3. Weblogic OSB ver 11.1.1.6

       

      4. DBAdapter Connection Factory "eis/DB/test"

      dataSourceNamejava.lang.Stringjdbc/test
      defaultNCharjava.lang.Booleanfalse
      platformClassNamejava.lang.Stringorg.eclipse.persistence.platform.database.Oracle10Platform
      sequencePreallocationSizejava.lang.Integer50
      usesBatchWritingjava.lang.Booleantrue
      usesNativeSequencingjava.lang.Booleantrue
      usesSkipLockingjava.lang.Booleantrue
      xADataSourceNamejava.lang.String

       

      5. Proxy Service JCA:

      <connection-factory location="eis/DB/test" UIConnectionName="TestDb" adapterRef=""/>

        <endpoint-activation portType="ReadEventService_ptt" operation="receive">

          <activation-spec className="oracle.tip.adapter.db.DBActivationSpec">

            <property name="DescriptorName" value="ReadEventService.Events"/>

            <property name="QueryName" value="ReadEventServiceSelect"/>

            <property name="MappingsMetaDataURL" value="ReadEventService-or-mappings.xml"/>

            <property name="PollingStrategy" value="LogicalDeletePollingStrategy"/>

            <property name="MarkReadColumn" value="STATUS"/>

            <property name="MarkReadValue" value="P"/>

            <property name="MarkReservedValue" value="N${weblogic.Name-1}-${IP-2}"/>

            <property name="MarkUnreadValue" value="N"/>

            <property name="PollingInterval" value="60"/>

            <property name="MaxRaiseSize" value="1"/>

            <property name="MaxTransactionSize" value="1"/>

            <property name="NumberOfThreads" value="1"/>

            <property name="ReturnSingleResultSet" value="false"/>

          </activation-spec>

        </endpoint-activation>

       

      6. Test JDBC Data Source nonXA with default settings

       

      7. Proxy Service has WorkManager (MinTrhead=1, MaxTrhead=1) for DispatchPolicy and has active flag "Always use configuration from JCA file".

       

      Expected Result

      Proxy Service process (polling) one message only from DB every 60 sec.

       

      Actual Result

      Proxy Service process (polling) more than one message from DB every 60 sec.

       

       

      Could you please tell me how to get Expected Result?

        • 1. Re: OSB Db Adapter Polling one record per time not working
          Martien van den Akker

          Is this OSB running in a cluster with multiple OSB nodes?

          What do you notice in the behavior?

           

          A good article about the distributed polling options of the db-adapter I found here: http://www.ateam-oracle.com/db-adapter-distributed-polling-skip-locked-demystified Unfortunately at the moment it gives an error.

           

          What are, by the way, the reasons of your requirement on polling once per minute? I honestly am surprised when people come with requirements like this and use 1-thread workmanagers to drive OSB to work with one thing at a time. It's like having a ferrari with a speed-limiter so that you can't drive faster than 30 km. per hour.

           

          I had a customer that procesessed HR records for the employees and was afraid that records for on-boarding of an employee were passed by records about changing the roles. But records of different employees can pass eachother safely. So I created a view that only delivers records for a certain employee nr when there isn't a record 'in process' for that same employee. And then sorted by id or creation time.

           

          Regards,

          Martien

          • 2. Re: OSB Db Adapter Polling one record per time not working
            Damir Dev

            Hello Martien.

             

            Thank you for feeback.

            OSB will be running in a cluster with 1 node only. Because its develope enviroment, and I want understand how do properties MaxRaiseSize MaxTransactionSize work in real.

            When I set

                  <property name="PollingInterval" value="60"/>

                  <property name="MaxRaiseSize" value="1"/>

                  <property name="MaxTransactionSize" value="1"/>

                  <property name="NumberOfThreads" value="1"/>

            I expect that every node (one node only in my case) processes one row only from db table per every 60 sec. But I detect that node processes a lot of rows per every 60 sec actually.

            So MaxRaiseSize and MaxTransactionSize value make me confused.

            • 3. Re: OSB Db Adapter Polling one record per time not working
              Martien van den Akker

              HI,

               

              It's all described properly in http://www.ateam-oracle.com/db-adapter-distributed-polling-skip-locked-demystified

              It says:

              Once records appear in the database that match the polling SELECT statement, the real fun begins.  Each thread will wake up after sleeping, start a transaction, and issue the SELECT FOR UPDATE SKIP LOCK.  Again, a database cursor is returned but this time there are rows that match the SELECT criteria.  Each thread will now issue a FETCH for a number of rows defined by the MaxTransactionSize property.  It is the FETCH that will lock the rows in the database (SKIP LOCKED) preventing any other FETCH from retrieving those rows.  This allows each polling thread to concentrate only on SELECT, FETCH, and process without concern for duplicate processing.

              So the MaxTransactionSize defines how may rows are fetched and delivered to the destination (OSB Pipeline or BPEL Instance) and then commited.

              But this does not end the polling interval iteration!

              It then says:

              Now that each thread has its set of rows to work with, they will loop over the fetched rows and group them based on the MaxRaiseSize property.  Each grouping will be delivered to the configured destination and once all rows have been delivered successfully, the transaction is committed.  Each thread will then compare how many rows have been delivered to the value specified by the RowsPerPollingInterval property.  If the rows delivered are equal to/greater than the RPPI property value, the thread will sleep.  Otherwise, it will repeat the whole process over again.

              So the RowsPerPollingInterval defines how many rows are processed before the thread is going to sleep again. In your situation if you want to have only one row processed, you should set that one to 1 too.

               

              Note by the way, that if you have one managed server, your polling interval is 60 and both RowsPerPollingInterval and MaxTransactionSize is 1, and you have more than 60 rows created per hour, the rows are piling up.

               

              Also, RowsPerPollingInterval is a property you cannot set in the UI. You must add it to the jca file manually.

               

              Regards,
              Martien

               

              PS. SKIP_LOCK is a feature build into the database for the implementation of Advanced Queueing (if I recollect correctly). The Database development team would recommend using AQ, unless requirements dictate differently. And I would too.

              • 4. Re: OSB Db Adapter Polling one record per time not working
                Damir Dev

                Hello Martien.

                 

                Thank you for help.

                Rigth, when I set RowsPerPollingInterval =1, node get all rows, but  process one row only per interval in 60 sec. And MaxTransactionSize does not influence for how many rows the node processes per interval. So, polling capacity is

                NumberOfThreads x RowsPerPollingInterval / PollingInterval

                 

                I checked polling capacity for:

                One node only in cluster

                Read Proxy Service Work Manager (MinThreadCount=1 MaxThreadCount=1)

                NumberOfThreads=2

                RowsPerPollingInterval =1

                PollingInterval=60

                 

                Expected result

                node processes 2 rows per interval in 60 sec

                 

                Actual result

                node processes more than 2 rows per interval in 60 sec

                 

                Strange, let rty understand cause.

                • 5. Re: OSB Db Adapter Polling one record per time not working
                  Martien van den Akker

                  I don't know the calculation of the timing. But I would think that once the polling interval expires, it will start with doing a fetch, process the row and wait for the next polling interval expiry. But the two threads won't work synchronized. So you might have 3 rows processed in the interval of one minute but then in the following minute only one.

                  But on average you might expect it would be 2 per minute, I guess.

                   

                  Regards,
                  Martien

                  • 6. Re: OSB Db Adapter Polling one record per time not working
                    Damir Dev

                    Not sure. I set for JCA NumberOfThreads=2:

                          <property name="PollingInterval" value="60"/>

                          <property name="MaxRaiseSize" value="1"/>

                          <property name="MaxTransactionSize" value="1"/>

                          <property name="NumberOfThreads" value="2"/>

                          <property name="RowsPerPollingInterval" value="1"/>

                    and created 16 rows for polling in the table for one transacation.

                    Proxy service got all 16 rows from table for one moment.

                    And I detected that MaxTransactionSize does not work for not distributed-polling mode.