DBADAPTER Settings and DistributedPolling

                        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.

4.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.

Using Logical delete with custom sql

5.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:

6.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.

Working with DB Adapter Polling Settings.

Polling Settings:

1.Database rows per xml;

“Database rows per xml” is the maximum number of payloads per BPEL instance.

    For ex: If it set as ‘1’ then for every record there will be a BPEL instance.

    If it set as ‘10’ then for every 10 record there will be one BPEL instance

2. Database rows per transaction;

“Database rows per transaction” sets the number of records polled per transaction.

3. Order by:

“Order by” will choose order the records in ascending order.

4.Delay commit

“Delay commit” This will delay the deletion of all rows until all have been read.

  

5. Distributed Polling

“Distributed Polling” will be used during the clustered environments. This will avoid the creation of multiple instances of BPEL. Means it will avoid the possibility of picking the same record multiple times in a clustered environment.

6. Use batch destroy;

“Use batch destroy” Use this to delete all rows at once after they have been read.

Scenario: Select the records one by one ordered by some field from the source database in a clustered environment.

For the above scenario we can try setting “Database rows per transaction” as ‘1’ and set the distributed polling. That will create the SQL query as shown below 

But if you check the SQL query carefully this will select the first record and then does the order by , which is of no use that won’t help in our scenario. I think that’s the bug in the tool. So using this option we can’t achieve our scenario. For achieving that we can use the “clusterGroupID” property in bpel.xml 

                   Custom SQL

This is a more complicated approach, this is more useful for more complex cases such as where the query is over a join. JDeveloper offers an interface to the Toplink descriptor file which allows you to use some custom SQL.

The logical delete polling strategy is too complex for the custom SQL, so we will need to be a bit sneaky here and take a basic delete polling strategy and bend it to perform an effective logical delete (or a logical logical delete if you’re into that sort of thing).

1.    The first step is to create the poller with a basic delete strategy (use the desired return fields but just the default selection logic for now). When you get to the last screen of the wizard where the representative SQL is displayed, copy the SQL statement before the WHERE clause to reuse in the selection component of your custom SQL (so that the Toplink mappings will still line up)

2.Next we will enter the Custom SQL query:

·      In JDeveloper you click on the project then select Application Sources>Toplink>your_poller_name_here

·      This will open a form Toplink descriptor tab. Change from the Application navigation pane to the Structure pane. Open the package and select the root table of the query.

This will change the descriptor to one specific for that table. Select the Queries tab and the Named Queries sub tab. In that tab select the named query referenced in the poller WSDL (will probably be the same name as the poller). Then select the Format sub tab and change type from Expression to SQL and enter the custom SQL in the space provided. 

3.Now we will use more custom SQL to make the delete behave as a logical delete:

·      Change the subtab from Named Queries to Custom SQL and select the Delete Tab. You can now enter a custom SQL UPDATE statement which will be performed at the delete stage of the poll (such as setting a status field to processed) thereby actually effecting a logical delete.

4. The final step is to reopen the partner link for the adapter and edit it – progressing (at least) until you can select the option to Finish. This forces regeneration of the relevant Toplink mappings files with the custom SQL handling.

5.       

Some things to keep in mind when formulating the custom SQL:

·      It looks like both the SQL and some of the Toplink handling is executed, which can lead to conflicts if the select statement tries to do something that the Toplink mapping is already doing (locking for instance).

·      Adapter errors will usually generate output in the default opmn logs on the application server, and result in the BPEL engine changing turning the BPEL driven by the adapter off to prevent an infinite failure loop.

·      If the adapter doesn’t throw and error, it may still not like your selection SQL. In this case, it appears to fall back on a SELECT ALL named query – so if your poller is just returning everything in the table and ignoring the selection criteria it’s probably a non-fatal conflict between Toplink and the SQL.

·      I also was unable to make the custom SELECT logic work with the Distributed Polling option checked in the wizard, so this option may not be appropriate in cases where this is required.

                   Directly Munge the Toplink Descripters

There are some Oracle workarounds that describe directly editing the Toplink descriptors. In most cases I would advise against this unless specifically advised by Oracle as the underlying implementation logic is not particularly transparent and this cam lead to unexpected effects. Caveat Emptor.

How to configure distributed polling to accomplish Logical Delete strategy for DB Adapter in HA environement?

Solution

There are two distributed polling strategies to use when LogicalDeletePolling option is selected in DB Adapter Creation wizard.

1. Reservation Distributed Polling.
2.  Select For Update No Wait Distributed Polling

Reservation Distributed Polling

------------------------------- ------------------------------------------------------------------------------------------

Reservation Distributed Polling is a two step polling strategy.

STEP1:The first step is to reserve the rows,

STEP2:the second step is for processing.

 Please note that each instance

should be configured with a unique MarkReservedValue(Not suitable for BPEL cluster deployment, MarkReservedValue results in the same row processed twice in a cluster env)

HOW IT WORKS

begin 

update MOVIES set deleted = 'MINE1' where deleted = 'FALSE' and 

rownum <= #MaxTransactionSize; 

commit; 

begin 

select * from MOVIES where deleted = 'MINE1' and rownum <= #MaxTransactionSize; 

<do either synchronous or asynchronous post to bpel/esb> 

update MOVIES set deleted = 'TRUE' where deleted = 'MINE1' and 

rownum <= #MaxTransactionSize; 

Select For Update No Wait Distributed Polling

--------------------------------------------- -----------------------------------------------------------------------------

With the select for update no wait, everything happens in one transaction, so no processing can occur in parallel, unless the process is asynchronous, in which case only the dbadapter processing is serial.

HOW IT WORKS

begin 

first select .. and (rownum <= #MaxTransactionSize) ... FOR UPDATE NO WAIT; 

<do either synchronous or asynchronous post to bpel/esb> Asyn is default for BPEL, Syn is default for esb. 

<after read - Sequencing, LogicalDelete, Delete> 

commit; 

How to define a distributed polling strategy

------------- --------------------------------------------------------------------------------------------------------------

Reservation Distributed Polling

-Specify a reserved value in DB Adapter Creation wizard, or accordingly add a MarkReservedValue

attribute in DB adapter partnerlink wsdl file.

Select For Update No Wait Distributed Polling

---------------------------------------------------------------------------------------------------------------------------

- If a reserved value is not specified, Select For Update No Wait will be used.

- As a known issue described in Bug 6690511, if you have MaxTransactionSize other than "unlimited" with LogicalDeletePollingStrategy, the for update NO Wait clause will be removed, as a result, the same rows will be possibly processed by multiple BPEL server. Need apply Patch 6690511 on top of 10.1.3.3 instance.

How to develop a Distributed Polling Sample

---------- -----------------------------------------------------------------------------------------------------------------

The bpel sample - 122.DBAdapter\advanced\polling\DistributedPolling

1. Create a new BPEL project to poll data from movies table.

2. DO NOT change any settings in toplink-mappings.xml

3. DO NOT specify any values for "Reserved Value" field, if you specify this attribute, the system

will be using reserved distributed polling strategy, (which has problems with clustered BPEL

environment, as it requires to define a unique reserved value for each instance)

4. Choose Distributed Polling Option in the wizard.

5. Edit oc4j-ra.xml and data-source.xml for DB Adapter

6. Restart one BPEL instance and test, you should see something like,

SELECT TITLE, DIRECTOR, STARRING, SYNOPSIS, GENRE, RUN_TIME, RELEASE_DATE, RATED, RATING,

VIEWER_RATING, STATUS, TOTAL_GROSS, DELETED, SEQUENCENO, LAST_UPDATED FROM MOVIES WHERE (DELETED =

?) ORDER BY TITLE ASC FOR UPDATE NOWAIT bind => [FALSE]

Abstract: MAX TRANSACTION SIZE WILL REMOVE NO WAIT FOR DISTRIBUTED

 POLLING

1)When setting up the database adapter for distributed polling in a cluster, selecting logical delete, setting a max row size, and distributed polling, the No wait will disappear from the query. If the max transaction size is unlimited the no wait will be present

.DIAGNOSTIC ANALYSIS:

1)      Its simple to see that NO Wait option disapears after setting a max transaction size. This was verified in the DB wizard in JdevERRORSTACK OBTAINED

2)       In Jdev start a DB wizard and select a database connectionStep 3, set poll for new records

Step 4, select tables

3)      Step 7, Select logical delete

Step 8, Add dumy values

Step 9, Set any number of rows (not unlimited) and set Distibuted pooling.Select next and No Wait is not present in query