In this final post of this series, we will look at remaining polling strategies in ACTION from OSB perspective. Based on my past experience, I would say these are the less frequent used polling strategies (Of course, you should not consider this as granted though).
All 3 strategies highlighted in the below screenshot are based on simple principle and can be used if we have any running sequence or date in the source table. Every time the row gets updated/inserted a new running sequence or date used in the respective table column.
. Once the polling is done, this running sequence can be stored external to the source table. So next time, when the polling happens it will be always that running sequence column value > stored value.
The only difference in these strategies is where we store the value that is used for polling query. In ‘Update a Sequence Table’ polling strategy, we use external table in same DB to store the value . In ‘Update an External Sequencing Table on a different Database’ polling strategy, we use external table in different database altogether. We can have a single global table that can be used for this purpose. In ‘Update a Sequencing File’ polling strategy, we use the a file in server file system to store the value rather than DB tables. This file will reside in SOA server file system.
For this post also, the use case remains same where we migrate the data from SI_EMPLOYEE (source table) to SI_EMPLOYEE_COPY (target table). I will demonstrate using only one of these strategies as all 3 are similar. We will use EMPLOYEE_ID column as the sequence i.e. every time we insert a sequence value in this column every time we insert a new record (it does not make sense for the update scenario, then we can add update_date as the column and follow this strategy).
Create a new table to store running sequence with following table definition
CREATE TABLE SI_POLL_TAB(TABLE_NAME VARCHAR2(30), SEQ NUMBER);
Create DB adapter files in JDeveloper by selecting the polling strategy ‘Update a Sequence Table’ as shown above. Click on Next and enter the values as shown below.
Click on Next, observe Polling SQL and After Read SQL statements.
Finish the DB adapter wizard, import the related files into OSB project and create a proxy service from the JCA file and complete the message flow to route the polled data to business service.
Now deploy configuration jar to OSB server and also disable all other proxy services polling on same DB table. Now do the insertion of a single row with using the following statement.
insert into si_employee(EMPLOYEE_ID, FIRST_NAME, LAST_NAME,EMAIL, PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT, MANAGER_ID)
select EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER, HIRE_DATE, JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID
from employees where employee_id = 100
After polling interval, verify that target table is populated this row along with SI_POLL_TAB.
Repeat this by inserting more number of records.
insert into si_employee(EMPLOYEE_ID, FIRST_NAME, LAST_NAME,EMAIL, PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT, MANAGER_ID)
select EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER, HIRE_DATE, JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID
from employees where employee_id between 101 and 105;
In case of ‘Update an External Sequencing Table on a different Database’, we also have to specify Data Source to connect to an external table in different DB as shown below. Except this, everything remains same as discussed above.
In case of ‘Update a Sequencing File’ polling strategy, we have to specify the location of file resides in SOA server as shown below. Except this, everything remains same as discussed above. Make sure that file location is writable.
The updated configuration jar can be downloaded from here.
Siva's Blog 
0 Responses to “Database Polling in OSB – Part IV”