Using custom SQL queries for Distributed polling in OSB 12C
In this post, we will learn how to use customized queries for Distributed polling in OSB 12C, there we go:
First, we need to know how to create a Db poller in OSB 12C using JDeveloper. Actually, I made a previous post about how to acomplish this step in the following link: create custom sql statements in polling DbAdapters with OSB 12c
So basically, after we created a Database poller with the option of physical delete (first option) then we will modify the xxx-mappings.xml file with a text editor because this file is protected in Jdeveloper.
<querying> <queries> <query name="customerPollerSelect" xsi:type="read-all-query"> <!--QUERY FOR POLLING--> <call xsi:type="sql-call"> <sql>SELECT * FROM CLIENTS WHERE POLLER_READ=0 AND TIMESTAMP <= SYSDATE FOR UPDATE SKIP LOCKED</sql> </call> <reference-class>customerPoller.Clients</reference-class> <lock-mode>lock-no-wait</lock-mode> <container xsi:type="list-container-policy"> <collection-type>java.util.Vector</collection-type> </container> </query> </queries> <delete-query xsi:type="delete-object-query"> <call xsi:type="sql-call"> <!--QUERY FOR UPDATING AFTER POLLING--> <sql>UPDATE CLIENTS SET POLLER_READ=1 WHERE ID=#ID</sql> </call> </delete-query> </querying>
In this piece of code, I’m specifying the query for polling and query for updating after polling. Here there is one important thing when we are handling distributed polling, I added “FOR UPDATE SKIP LOCKED”. This means that one managed server will process a database row and it won’t be processed by another manager server that is polling the same table.