More on Batch Processing in BPEL

I was asked a follow up question recently on my entry about batch processing in BPEL.  The individual had implemented a BPEL process similar to the one shown below.

A Use Case

The process needs to refresh the corporate phone book.  It does this by reading a file with the new phone details, deleting the old phone details from a database and then inserting the new phone details into the database.
A file is read into the process via ReceivePhoneBook activity.  The ClearPhoneBook activity uses custom SQL to truncate the phonebook table.  Finally after transforming the file format to database format the new phone details are inserted into the table using the InsertIntoPhoneBookActivity to insert multiple records into the table in a single call.

A Fly in the Ointment

This process works fine as long as all the records are received in a single message.  If they are received in multiple messages then not all the records will be written as multiple processes will receive part of the input file, but all the processes will truncate the table, resulting in the possibility of some records being inserted by one process and deleted by another process that starts a little later.  This is illustrated in the diagram below which shows three processes each processing a portion of the file.

Note that records 1-10 are written to the database at the same time as the table is truncated by the process receiving records 11-20, leaving the possibility that the write will complete only to be overridden by the truncate.  If records 1-10 are not overwritten by the second process they will definitely be overwritten by the third process which only starts truncating the table after records 1-10 have been inserted into the table.

A Solution

What we need to do is to separate the deletion of the existing data from the insertion of the new data.  We can do this by moving the truncation of the table (deleting the existing data) into a separate BPEL process.  Such a process is shown below.

For this to work we need to know when a new file starts to be loaded so that this process can be invoked before any record processing is performed.  Now there are lots of complicated ways that we could do this with singleton processes to maintain state and complex logic to make sure it all works.  Or we could use a newish feature of the BPEL process manager (introduced in 10.1.3.3 I believe) to get it to invoke our process.

Batch Manager

In the previous discussion I ignored the partner link that initiates the record deletion process.  This partner link implements the Batch Manager interface as specified in $ORACLE_HOME/bpel/system/xmllib/jca/BatchManager.wsdl.  To create the above process first create a new empty BPEL process and then in the services stream right click and select new partner link.  Click on the icon to browse for files from the local file system and select the BatchManager.wsdl file and choose to implement the BatchManagerInterfaceRole (i.e. choose this as "My Role" ).  This is the interface used by BPEL Process Manager to notify a process that a file has started to be read.  There are several methods available to receive different notifications

• onBatchReadStart - tells when a file is started to be read
• onBatchReadComplete - tells when a file has finished being read
• onBatchReadFailure - tells when a record or records cannot be processed by the adapter framework
  

For this scenario we are interested in onBatchReadStart.  Receiving this notification we know to truncate the table ready to receive the records.

Who to Tell?

How does the BPEL Process Manager know to call the notification process?  The answer is that it is configured as an activation agent property in the bpel.xml file of the process receiving the records from the file adapter.  To set up the notification it is necessary to add the following property tag to the bpel.xml at XPath location BPELSuitcase/BPELProcess/activationAgents/activationAgent :

• <property name="batchNotificationHandler">bpel://default|FileNotificationProcess</property>
  

Note that default is the name of your domain and FileNotificationProcess is the name of your process.  Adding this property will cause the BatchManager interface on the given process to be invoked when a file is read.

Final Steps

With notification configured we now need to modify our BPEL process to not truncate the table because this is being done via a separate process.  We also need to add a delay to the process to avoid race conditions that could cause records to be inserted into the database before the database has been truncated.  The modified process is shown below, complete with a 30 second delay to avoid problems with multiple processes being invoked at the same time.

A Worked Example

I have created a sample to let you explore how all this works.  To set it up do the following.

1. Download the project files in FileManipulation.zip.
2. Unzip FileManipulation.zip - this will create a FileManipulation directory with 3 sub-directories and a JDev 10.1.3.3 workspace
3. Open the workspace FileManipulation.jws in JDev 10.1.3.3
   
4. Create the following directories or modify the file adapter partner links in BigBatchProcess1.0 and BigBatchProcess1.1
   
• C:FilesInbound
• C:FilesOutbound
5. Create a test user in the database by running the script in FileManpulation/BigBatchProcess1.0/src/CreateUser.sql as a system user
6. Create a database connection in JDev called TestDS to connect to user Test (password test) in the database.  You may need to rerun the adapter wizard if you are not running XE database on port 1521
7. As user test run the script in FileManpulation/BigBatchProcess1.0/database/CreateTable.sql to create the phonebook table.
8. Deploy the BigBatchProcess1.0 to the BPEL process manager.
9. Test it by copying file FileManpulation/BigBatchProcess1.0/src/PhoneBook1.csv to C:FilesInbound.
10. Verify the number of records stored by executing command in database "select count(*) from phonebook.  There are 1000 records in the source file, you will probably receive a count less than 1000 due to race conditions around truncating the table and inserting records into it.  This is the problem we are trying to avoid.
    
11. Deploy the BigBatchProcess1.1 to the BPEL process manager as version 1.1.
12. Deploy the FileNotificationProcess1.0 to the BPEL process manager.
13. Test it by copying file FileManpulation/BigBatchProcess1.0/src/PhoneBook1.csv to C:FilesInbound.
14. Verify the number of records stored by executing command in database "select count(*) from phonebook.  There are 1000 records in the source file, you should now receive a count of 1000 in the database table, indicating thqat we have solved the problem.
    
15. Pat yourself on the back and have a nice drink.

Hope that some of you find the above useful.