At my client, we had a unique situation occur when integrating a database and an export for the WebSphere Adapter for JDBC. In a ‘typical’ scenario, a single row is written into a staging table, an SQL trigger writes this single row into the adapter’s event table, the adapter polls, converts the data into a business object and starts the module.

In this migration scenario at my client, a single transaction was actually comprised of multiple rows written into the staging table in a single transaction. The previous implementation had a built-in concept of table scanning and aggregation which we need to reproduce in process server. Each row of data was a ‘delta’ and logic is then used to ‘flatten’ these rows into a single transaction. We need a way of telling the adapter that “these n rows are not unique transactions, but actually a single transaction”.

Out of the box, the adapter doesn’t have any feature to combine multiple rows like this. A proposal was to use a long-running BPEL process using correlation to aggregate the data, but that would induce a ton of overhead and complexity onto our servers. There’s a slightly more elegant solution.

The WebSphere Adapter can represent foreign key relationships between tables as containment business objects (Parent-Child). It also supports lists of children. So what we did at a high level was create a table representation of a wrapper type that defined a primary key. Then, all the children that relate to a single WPS transaction are given foreign key relationships to the wrapper row primary key. When the wrapper row is written to the event table, the adapter picks up all the related children and we get our desired behavior: A single WPS transaction containing multiple rows of data.

The drawback is that we had to add a little bit more logic to our trigger, but the benefits are that we aren’t overloading our server trying to do the event correlation ourselves. We’ve pawned it off on a system (database) that was designed for that kind of work.