Advertisements

Posts Tagged 'Poll'

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

Database Polling in OSB – Part III

In this post, we will have a look at another typical use case of polling DB View using DB adapter before proceeding to other polling strategies.

As we know, DB view restricts the update if it’s based on multiple tables so we would not be able to use ‘Logical Delete’ polling strategy in it’s original fashion. To overcome this, one of the common techniques is that use ‘Delete’ polling strategy and override the default delete query with update. This technique is well known among BPEL 10g developers.

For this post also, our use case remains same where we poll using DB view EMP_DETAILS_VIEW instead of table and inserts only active employees (ISACTIVE flag is Y).

DB View Definition:

SELECT e.employee_id, e.job_id, e.manager_id,
    e.department_id, e.isactive, d.location_id,
    l.country_id, e.first_name, e.last_name,
    e.salary, e.commission_pct, d.department_name,
    j.job_title, l.city, l.state_province,
    c.country_name, r.region_name
  FROM employees e, departments d,
    jobs j, locations l,
    countries c, regions r
  WHERE e.department_id = d.department_id
  AND d.location_id     = l.location_id
  AND l.country_id      = c.country_id
  AND c.region_id       = r.region_id
  AND j.job_id          = e.job_id;

Re-create DB adapter related files for polling using this DB view in JDeveloper and import them into OSB project as shown below.

dbview

PollStartegy

PollOptions

isactive

wherecondn

OSBImport

After importing into OSB project, open the mappings.xml file and override the delete query to perform the update as shown below.

override

Create new proxy service from JCA file and add message flow similar to previous scenarios to call business service that inserts data into target table.

msgflow

Deploy the jar file to OSB server and disable other proxy services except the recent one that we created, so that only one proxy service will be populating our target table. Update the ISACTIVE flag to Y in EMPLOYEES table.

Before polling:

beforeinview

beforetargettbl

After polling:

aftertblview

aftertargettbl

From the above screenshots, we can observe that ISACTIVE flag is set to N after polling the records from the DB view and also populated our target table as expected. The updated jar file can be found in the same location as given in previous posts.

Database Polling in OSB – Part II

Before proceeding to other polling strategies, we will look at effect of MaxTransactionSize and MaxRaiseSize parameter values in the JCA file. This post assumes the reader has the basic understanding of OSB log activity.

jcaparams

MaxTransactionSize determines number of records to be polled in each transaction and MaxRaiseSize determines batch size i.e. the number of records to be processed in a single XML document. In BPEL, we can observe this behavior clearly as number of BPEL instances will be created with each instance processing the number of records as indicated by MaxRaiseSize. Since OSB is stateless no instances will be created. So we apply simple technique using OSB log activity to observe this behavior.

Consider value 4 as MaxTransactionSize and 2 as MaxRaiseSize. These values can be directly modified in JCA file (see above screenshot) or in the proxy service directly by unchecking option ‘Always use configuration from JCA file’. I recommend modifying JCA file as these changes would be directly used by proxy service and will not create any confusion when JCA file is further modified by DB adapter wizard at later stages during maintenance.

proxyjcamodify

So modify the parameter values as shown below.

ModifyParaminJCA

Now modify the proxy service message flow to include a log activity as shown below to log the payload before the request is routed to business service.

logactivity

Deploy the project and inserts 4 rows in the source table to observe the behavior from the logs. The following is the log for the AdminServer.log file stored at location <DOMAIN_HOME>\servers\AdminServer\logs. From the log it’s clearly visible that 4 records have been processed in 2 attempts by OSB consists of 2 records in each time. We can observe the behavior from logs by modifying MaxRaiseSize to different values like 1, 3 etc..

####<Apr 9, 2013 5:26:49 PM IST> <Error> <ALSB Logging> <SVGONUGU-LAP> <AdminServer> <[ACTIVE] ExecuteThread: ’25’ for queue: ‘weblogic.kernel.Default (self-tuning)’> <<anonymous>> <BEA1-313CB0682087E97D79BE> <361a19a2fa60e1ab:7e5f3ca6:13ded702d81:-8000-0000000000006661> <1365508609568> <BEA-000000> < [RouteNode1, null, null, REQUEST] <pol:SiEmployeeCollection xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:pol="http://xmlns.oracle.com/pcbpel/adapter/db/top/PollDB">
  <pol:SiEmployee>
    <pol:employeeId>100</pol:employeeId>
    <pol:firstName>Steven</pol:firstName>
    <pol:lastName>King</pol:lastName>
    <pol:email>SKING</pol:email>
    <pol:phoneNumber>515.123.4567</pol:phoneNumber>
    <pol:hireDate>2003-06-17T00:00:00.000+05:30</pol:hireDate>
    <pol:jobId>AD_PRES</pol:jobId>
    <pol:salary>24000</pol:salary>
    <pol:commissionPct xsi:nil="true"/>
    <pol:managerId xsi:nil="true"/>
    <pol:isactive>Y</pol:isactive>
  </pol:SiEmployee>
  <pol:SiEmployee>
    <pol:employeeId>101</pol:employeeId>
    <pol:firstName>Neena</pol:firstName>
    <pol:lastName>Kochhar</pol:lastName>
    <pol:email>NKOCHHAR</pol:email>
    <pol:phoneNumber>515.123.4568</pol:phoneNumber>
    <pol:hireDate>2005-09-21T00:00:00.000+05:30</pol:hireDate>
    <pol:jobId>AD_VP</pol:jobId>
    <pol:salary>17000</pol:salary>
    <pol:commissionPct xsi:nil="true"/>
    <pol:managerId>100</pol:managerId>
    <pol:isactive>Y</pol:isactive>
  </pol:SiEmployee>
</pol:SiEmployeeCollection>>
####<Apr 9, 2013 5:26:49 PM IST> <Info> <JCA_FRAMEWORK_AND_ADAPTER> <SVGONUGU-LAP> <AdminServer> <[ACTIVE] ExecuteThread: ’25’ for queue: ‘weblogic.kernel.Default (self-tuning)’> <<anonymous>> <BEA1-313CB0682087E97D79BE> <361a19a2fa60e1ab:7e5f3ca6:13ded702d81:-8000-0000000000006661> <1365508609647> <BEA-000000> <servicebus:/WSDL/OSBPollingDB/BusinessService/insert [ {
http://xmlns.oracle.com/pcbpel/adapter/db/BlogWS/OSBPollingDB/insert}insert_ptt ]: Using JCA Connection Pool – max size = <unbounded>>
####<Apr 9, 2013 5:26:49 PM IST> <Info> <Common> <SVGONUGU-LAP> <AdminServer> <[ACTIVE] ExecuteThread: ’25’ for queue: ‘weblogic.kernel.Default (self-tuning)’> <<anonymous>> <BEA1-313CB0682087E97D79BE> <361a19a2fa60e1ab:7e5f3ca6:13ded702d81:-8000-0000000000006661> <1365508609689> <BEA-000628> <Created "1" resources for pool "eis/DB/LocalDBHR", out of which "1" are available and "0" are unavailable.>
####<Apr 9, 2013 5:26:49 PM IST> <Info> <JCA_FRAMEWORK_AND_ADAPTER> <SVGONUGU-LAP> <AdminServer> <[ACTIVE] ExecuteThread: ’25’ for queue: ‘weblogic.kernel.Default (self-tuning)’> <<anonymous>> <BEA1-313CB0682087E97D79BE> <361a19a2fa60e1ab:7e5f3ca6:13ded702d81:-8000-0000000000006661> <1365508609701> <BEA-000000> <<oracle.tip.adapter.db.DBAdapterConstants getUniqueProcessIdentifier> Unique process identifier will be _393083366_393083366
>
####<Apr 9, 2013 5:26:49 PM IST> <Info> <JCA_FRAMEWORK_AND_ADAPTER> <SVGONUGU-LAP> <AdminServer> <[ACTIVE] ExecuteThread: ’25’ for queue: ‘weblogic.kernel.Default (self-tuning)’> <<anonymous>> <BEA1-313CB0682087E97D79BE> <361a19a2fa60e1ab:7e5f3ca6:13ded702d81:-8000-0000000000006661> <1365508609710> <BEA-000000> <<oracle.tip.adapter.db.ox.TopLinkXMLProjectInitializer initialize> Initializing toplink project for use by the database adapter.
>
####<Apr 9, 2013 5:26:49 PM IST> <Error> <ALSB Logging> <SVGONUGU-LAP> <AdminServer> <[ACTIVE] ExecuteThread: ’25’ for queue: ‘weblogic.kernel.Default (self-tuning)’> <<anonymous>> <BEA1-313CB0682087E97D79BE> <361a19a2fa60e1ab:7e5f3ca6:13ded702d81:-8000-0000000000006661> <1365508609823> <BEA-000000> < [RouteNode1, null, null, REQUEST] <pol:SiEmployeeCollection xmlns:xsi="
http://www.w3.org/2001/XMLSchema-instance" xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:pol="http://xmlns.oracle.com/pcbpel/adapter/db/top/PollDB">
  <pol:SiEmployee>
    <pol:employeeId>102</pol:employeeId>
    <pol:firstName>Lex</pol:firstName>
    <pol:lastName>De Haan</pol:lastName>
    <pol:email>LDEHAAN</pol:email>
    <pol:phoneNumber>515.123.4569</pol:phoneNumber>
    <pol:hireDate>2001-01-13T00:00:00.000+05:30</pol:hireDate>
    <pol:jobId>AD_VP</pol:jobId>
    <pol:salary>17000</pol:salary>
    <pol:commissionPct xsi:nil="true"/>
    <pol:managerId>100</pol:managerId>
    <pol:isactive>Y</pol:isactive>
  </pol:SiEmployee>
  <pol:SiEmployee>
    <pol:employeeId>103</pol:employeeId>
    <pol:firstName>Alexander</pol:firstName>
    <pol:lastName>Hunold</pol:lastName>
    <pol:email>AHUNOLD</pol:email>
    <pol:phoneNumber>590.423.4567</pol:phoneNumber>
    <pol:hireDate>2006-01-03T00:00:00.000+05:30</pol:hireDate>
    <pol:jobId>IT_PROG</pol:jobId>
    <pol:salary>9000</pol:salary>
    <pol:commissionPct xsi:nil="true"/>
    <pol:managerId>102</pol:managerId>
    <pol:isactive>Y</pol:isactive>
  </pol:SiEmployee>
</pol:SiEmployeeCollection>>

Now insert 5 rows into the source table and observe the logs which are given below:

####<Apr 10, 2013 1:58:27 PM IST> <Error> <ALSB Logging> <SVGONUGU-LAP> <AdminServer> <[ACTIVE] ExecuteThread: ‘3’ for queue: ‘weblogic.kernel.Default (self-tuning)’> <<anonymous>> <BEA1-320F158CE2D2E97D79BE> <361a19a2fa60e1ab:7e5f3ca6:13ded702d81:-8000-000000000000dd83> <1365582507415> <BEA-000000> < [RouteNode1, null, null, REQUEST] <pol:SiEmployeeCollection xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:pol="http://xmlns.oracle.com/pcbpel/adapter/db/top/PollDB">
  <pol:SiEmployee>
    <pol:employeeId>100</pol:employeeId>
    <pol:firstName>Steven</pol:firstName>
    <pol:lastName>King</pol:lastName>
    <pol:email>SKING</pol:email>
    <pol:phoneNumber>515.123.4567</pol:phoneNumber>
    <pol:hireDate>2003-06-17T00:00:00.000+05:30</pol:hireDate>
    <pol:jobId>AD_PRES</pol:jobId>
    <pol:salary>24000</pol:salary>
    <pol:commissionPct xsi:nil="true"/>
    <pol:managerId xsi:nil="true"/>
    <pol:isactive>Y</pol:isactive>
  </pol:SiEmployee>
  <pol:SiEmployee>
    <pol:employeeId>101</pol:employeeId>
    <pol:firstName>Neena</pol:firstName>
    <pol:lastName>Kochhar</pol:lastName>
    <pol:email>NKOCHHAR</pol:email>
    <pol:phoneNumber>515.123.4568</pol:phoneNumber>
    <pol:hireDate>2005-09-21T00:00:00.000+05:30</pol:hireDate>
    <pol:jobId>AD_VP</pol:jobId>
    <pol:salary>17000</pol:salary>
    <pol:commissionPct xsi:nil="true"/>
    <pol:managerId>100</pol:managerId>
    <pol:isactive>Y</pol:isactive>
  </pol:SiEmployee>
</pol:SiEmployeeCollection>>
####<Apr 10, 2013 1:58:27 PM IST> <Error> <ALSB Logging> <SVGONUGU-LAP> <AdminServer> <[ACTIVE] ExecuteThread: ‘3’ for queue: ‘weblogic.kernel.Default (self-tuning)’> <<anonymous>> <BEA1-320F158CE2D2E97D79BE> <361a19a2fa60e1ab:7e5f3ca6:13ded702d81:-8000-000000000000dd83> <1365582507450> <BEA-000000> < [RouteNode1, null, null, REQUEST] <pol:SiEmployeeCollection xmlns:xsi="
http://www.w3.org/2001/XMLSchema-instance" xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:pol="http://xmlns.oracle.com/pcbpel/adapter/db/top/PollDB">
  <pol:SiEmployee>
    <pol:employeeId>102</pol:employeeId>
    <pol:firstName>Lex</pol:firstName>
    <pol:lastName>De Haan</pol:lastName>
    <pol:email>LDEHAAN</pol:email>
    <pol:phoneNumber>515.123.4569</pol:phoneNumber>
    <pol:hireDate>2001-01-13T00:00:00.000+05:30</pol:hireDate>
    <pol:jobId>AD_VP</pol:jobId>
    <pol:salary>17000</pol:salary>
    <pol:commissionPct xsi:nil="true"/>
    <pol:managerId>100</pol:managerId>
    <pol:isactive>Y</pol:isactive>
  </pol:SiEmployee>
  <pol:SiEmployee>
    <pol:employeeId>103</pol:employeeId>
    <pol:firstName>Alexander</pol:firstName>
    <pol:lastName>Hunold</pol:lastName>
    <pol:email>AHUNOLD</pol:email>
    <pol:phoneNumber>590.423.4567</pol:phoneNumber>
    <pol:hireDate>2006-01-03T00:00:00.000+05:30</pol:hireDate>
    <pol:jobId>IT_PROG</pol:jobId>
    <pol:salary>9000</pol:salary>
    <pol:commissionPct xsi:nil="true"/>
    <pol:managerId>102</pol:managerId>
    <pol:isactive>Y</pol:isactive>
  </pol:SiEmployee>
</pol:SiEmployeeCollection>>
####<Apr 10, 2013 1:58:27 PM IST> <Error> <ALSB Logging> <SVGONUGU-LAP> <AdminServer> <[ACTIVE] ExecuteThread: ‘3’ for queue: ‘weblogic.kernel.Default (self-tuning)’> <<anonymous>> <BEA1-3211158CE2D2E97D79BE> <361a19a2fa60e1ab:7e5f3ca6:13ded702d81:-8000-000000000000dd83> <1365582507526> <BEA-000000> < [RouteNode1, null, null, REQUEST] <pol:SiEmployeeCollection xmlns:xsi="
http://www.w3.org/2001/XMLSchema-instance" xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:pol="http://xmlns.oracle.com/pcbpel/adapter/db/top/PollDB">
  <pol:SiEmployee>
    <pol:employeeId>104</pol:employeeId>
    <pol:firstName>Bruce</pol:firstName>
    <pol:lastName>Ernst</pol:lastName>
    <pol:email>BERNST</pol:email>
    <pol:phoneNumber>590.423.4568</pol:phoneNumber>
    <pol:hireDate>2007-05-21T00:00:00.000+05:30</pol:hireDate>
    <pol:jobId>IT_PROG</pol:jobId>
    <pol:salary>6000</pol:salary>
    <pol:commissionPct xsi:nil="true"/>
    <pol:managerId>103</pol:managerId>
    <pol:isactive>Y</pol:isactive>
  </pol:SiEmployee>
</pol:SiEmployeeCollection>>

If we observe the logs, all the 5 rows have been picked up rather than 4 as mentioned by MaxTransactionSize parameter. This behavior is documented in DB Adapter documentation mentioning “If distributed polling is not set, then the adapter tries to process all unprocessed rows in a single polling interval”.

As FYI, distributed polling is an option at DB adapter level as shown below. Also observe polling SQL when distributed polling option is used.

distpolling

The OSB configuration jar can be downloaded from here.

Database Polling in OSB – Part I

Most of us have used DB Adapter in BPEL to perform operation in database. One of the features that is widely used is database polling when working with database. OSB also supports JCA DB adapter since it’s initial release in 11g. In a series of posts, i will show you different polling strategies in ACTION from OSB perspective.

I had used a use case of migrating data (smaller volume) from SI_EMPLOYEE table (source) to SI_EMPLOYEE_COPY (target) using DB adapter in OSB for this demo. And JDeveloper 11.1.1.6 and OSB 11.1.1.6 are used for this demo.

This post assumes that reader has basic understanding of working with DB Adapter in BPEL, OSB and using JDeveloper and Eclipse IDEs.

Create Business Service:

This business service merges the rows into target table using DB adapter. So create DB adapter files in JDeveloper by selecting the merge operation.

InsertDBOperation

In the next step of the wizard, select target table and finish the DB wizard to create all necessary files (.JCA, mappings, properties, WSDL and XSD). Import all of these files into Eclipse IDE and create business service from JCA file.

BusinessService

The JCA properties from configuration file can be verified and modified from the JCA transport tab of the business service.

JCA Transport

Delete Polling Strategy:

Create DB adapter files in JDeveloper by selecting Poll operation, source table and the polling strategy (After Read) as ‘Delete the Row(s) that were Read’.

Poll

table

AfterReadDelete

Import all of these files (JCA, mappings file, properties file, WSDL and XSD) into Eclipse IDE and create proxy service from .JCA file. Also the JCA properties can be verified in ‘JCA Transport’ tab including the polling strategy and the polling interval (60 sec in this scenario).

eclipsepoll

Complete the message flow in proxy service by adding Routing and Replace activity to transform $body as required by business service.

xquery

Deploy OSB project. Now insert a few rows in source table. Verify that polling happens and target table is populated with records and also the records got deleted from source table.

Before Polling:

b4poll

After Polling:

aftrpoll

Logical Delete Polling Strategy:

In this polling strategy, a column in the table is marked with particular value to designate the logical delete thus avoids DB adapter to pick it up the same record again and again. Currently our table does not have any column for this purpose, so add a column ISACTIVE VARCHAR2(1) in source table.

Create DB adapter files in JDeveloper by selecting Poll operation, source table and the polling strategy (After Read) as ‘Logical Delete’.

Poll

table

logdelete

Enter ‘Read Value’ as ‘N’ and ‘Unread Value’ as ‘Y’ so that DB adapter would only poll active records.

logdeleteac

In the next step of DB wizard, one can verify SQL statement used for polling and finish the wizard.

pollsql

Import all of these files (JCA, mappings file, properties file, WSDL and XSD) into Eclipse IDE and create proxy service from .JCA file. Also the JCA properties can be verified in ‘JCA Transport’ tab including the polling strategy and the polling interval (60 sec in this scenario).

logdelete1

Complete the message flow in proxy service by adding Routing and Replace activity to transform the payload as required by business service.

logdelete2

Deploy OSB project and disable the proxy DeleteRecordPS so that only our new proxy service will poll DB table.

disable

Insert a few rows in source table with value ‘Y’ for ‘ISACTIVE’ column.Verify that polling happens and target table is populated with records and also the records in source table are marked with value ‘N’.

Before Polling:

activewithY

withzero

After Polling:

withN

with107

The configuration jar of this demo OSB project can be downloaded from here.


Advertisements

Pages

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

Join 371 other followers

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

Advertisements