DB Adapter polling tricks..!!

The commonly used polling strategies with Oracle DB adapter are:

1. DeletePollingStrategy : This is simplest where we read all possible rows from table and delete them afterwards to ensure that they are only read once.

2. LogicalDeletePollingStrategy : This is a non-intrusive polling mechanism where we update a status column to mark records as read rather than deleting them. This uses 2 SQLs -- one for polling/reading the records and another after-read SQL to update/mark the records as read.

3. LastReadId or SequencingPollingStrategy : This takes help of an external sequencing/helper table. This assumes that all new rows are inserted with an increasing key so that when selected only records with key greater than the highest key previously processed are fetched. This last highest key is stored in the helper table.

TopLink is the technology on which the DbAdapter is built and the DBAdapter UI generates some files especially toplink metadata or -mappings.xml file which can be hand edited to extend the polling mechanisms.

There maybe cases where you would like to use logical delete with custom SQLs instead of Toplink generated SQL to poll DB tables.In order to achieve this please make sure below steps are followed:

1. Pure SQL will not be executed by Toplink if you choose the second option (Logical Delete) as the After Read Strategy in Adapter Configuration Wizard.

2. To make Pure SQL work with Logical Delete Strategy, you must choose the first option (Delete the rows) as the After Read Strategy in Adapter Configuration Wizard, then edit the toplink project directly by adding two custom SQLs, one for polling, the other for after reading operations.

3. For editing the toplink project open the -or-mappings.xml outside of Jdeveloper (the file is write protected) and add below entries:

?

1 2 3

<toplink:call xsi:type="toplink:sql-call"> <toplink:sql>Enter your SQL here</toplink:sql> </toplink:call>

When adding the <toplink:call> element, it must be under <opm:query>. Also immediately after <opm:queries>and before <opm:querying> add this element:

?

1 2 3 4 5

<toplink:delete-query xsi:type="toplink:delete-object-query"> <toplink:call xsi:type="toplink:sql-call"> <toplink:sql>Enter your After read SQL here</toplink:sql> </toplink:call> </toplink:delete-query>

There maybe cases where you would like to control the number of DB records which are polled at a time. The DB adapter wizard gives several configurable properties which can control this.

1. Polling frequency is the interval at which the DB adapter activation agent polls the new records.

2. Database Rows per Transaction (default value of 10) controls the number of records which are read at a time. For eg. if there are 1000 records to be read and we set the Database Rows per Transaction=10 , at the start of the polling interval the entire work is divided into 1000/10=100 transaction units and completes sequentially till all are processed. This property resolves to MaxTransactionSize in the jca file.

3. If we enable the distributed polling checkbox and set the MaxTransactionSize the behaviour changes. Here the entire work is divided into 100 transaction units but each unit is processed in a single polling interval i.e 1st polling interval 10 records are processed, rest 990 will be processed in subsequent intervals.