« Database Polling in OSB – Part I
Database Polling in OSB – Part III »

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.

Advertisement

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

Feed for this Entry Trackback Address

  1. Leave a Comment

Leave a Reply

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

Gravatar
WordPress.com Logo

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

Facebook photo

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

Cancel

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

« Database Polling in OSB – Part I
Database Polling in OSB – Part III »

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: