OWB 11gR2 – Change Data Capture Web Services
By David Allan on Dec 14, 2009
Here's a quick post to illustrate the web services OWB exposes for Change Data Capture (CDC) it follows on from Mark Rittman's post on CDC using OWB 11gR2 to capture changes from SQL Server using the open connectivity code templates in 11gR2. The web services can be orchestrated in OWB's process flow or Oracle BPEL for example to script the CDC setup and initialization; starting the CDC, defining subscribers etc. In ODI you can define this kind of orchestration in the package object.
Using Mark's example, if you right-click on the module note you can create a bunch of web services for the module..not exactly obvious but essential and very useful. These web services will let you setup the CDC via process flow for example.
When you create as web service you will be prompted to select an application server to create the services in, I have previously created one named AGENT (and its location is DEFAULT_AGENT, the location referencing the default Control Center Agent/CCA).
If you open/edit the web service you will see the operations that are supported - all of the useful operations provided by the Change Data Capture design pattern (via the code template/knowledge module framework).
We see that the following operations are supported (as documented in the OWB documentation);
- subscribe - add a subscriber to the infrastructure
- unsubscribe - remove a subscriber from the infrastructure
- lock_subscriber - although the extend window is applied to the entire consistency set, subscribers consume the changes separately. This operation performs a subscriber(s) specific snapshot of the changes in the consistency window. This snapshot includes all the changes within the consistency window that have not been consumed yet by the subscriber(s).
- unlock_subscriber - This operation commits the use of the changes that were locked during the lock subscriber operations for the subscribers. It should be processed only after all the changes for the subscribers have been processed.
- extend_window - The Consistency Window is a range of available changes in all the tables of the consistency set for which insert, update, delete are possible without violating referential integrity. The extend window operation computes this window to take into account new changes captured since the latest Extend Window operation.
- start_cdc - setup the CDC infrastructure
- drop_cdc - remove the CDC infrastructure
- purge_cdc - After all subscribers have consumed the changes they have subscribed to, extra entries entries may still remain in the Capture tables and must be deleted. This is performed by the Purge Journal operation.
You can now deploy these web services to the agent, this will deploy the metadata needed for the tables being captured and also the code template that was used in the module (Mark used SQL Server Simple).
We are ready to start building process flows for example that use these services! Or even integrate into Oracle BPEL. Below you see a new process flow that has been created and the web service being dragged on to the canvas.
You will then be asked which operation to use, first we want to add the subscriber...
We repeat the addition and add start_cdc also to start the CDC process (for simple CDC code template this will create the trigger for example). We connect to activities together and then define the subscriber name we want to add. This is a parameter on the add subscriber activity. Below we add the subscriber name SUNOPSIS - just like Mark's example where it was set when the add subscriber operation was manually executed in the UI.
We can now deploy the process flow, its that simple! Executing the process flow will execute the activities we have in our process flow.
That's it, we have shown how to create a web service for the CDC operations in our system and orchestrate them from a process flow following on from an earlier post showing how it is all setup (thanks to Mark for that!) and we can use the CCA as a container for hosting such services. That's all for now!