Archive for April, 2013

Database Polling in OSB – Part III

Published April 23, 2013 OSB Leave a Comment
Tags: , , ,

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.

Advertisement

Database Polling in OSB – Part II

Published April 15, 2013 OSB Leave a Comment
Tags: , , ,

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.

Pages

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

Join 379 other subscribers

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

%d bloggers like this: