Advertisements



Database Polling in OSB – Part IV

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.

poll strats. 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.

Seq Table

Click on Next, observe Polling SQL and After Read SQL statements.

poll sql

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.

proxyservice

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.

insertion1 

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;

insertion2

poll tab1

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.

external table

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.

file

The updated configuration jar can be downloaded from here.

Advertisements

0 Responses to “Database Polling in OSB – Part IV”



  1. Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Advertisements

Pages

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 341 other followers

Enter your email address to follow this blog and receive notifications of new posts by email.