WPS: Multiple database lookup primitives in a mediation flow = XA

In IBM WebSphere Process Server, mediation flow is one of the two most used high-level building blocks (Process is another). And one of standard building blocks of a mediation flow is a Database Lookup primitive. It enriches your message by referencing a user-supplied database table.

One interesting fact is that if your mediation flow navigates through more then one database lookup against different databases, you must use XA data sources. The reason for this is that mediation flow runs in a single implicit transaction. When Database Lookup primitive executes, it first attempts to obtain a connection from configured data source. Transaction Manager attempts to enlist the data source into transaction. If this implicit transaction already has a resource (data source) enlisted, a different resource may only be enlisted if it supports XA. If it does not, your Database Lookup will fail right on this spot, unable to obtain a connection from data source. It is a little puzzling because you do not attempt to drive a transaction against the data source – you just want to perform a read-only lookup, but that’s how the primitive is coded.

Just to be clear, I’m talking this specific scenario: your mediation flow actually navigates through 2 database lookups that use different data sources – then you must use XA. If you have multiple Database Lookups in different branches of the flow that are not reached in the same invocation – no problem.

WID tips: limit amount of Java code in snippets

When you need to add custom functionality to an application you develop in WebSphere Integration Developer or extend its capabilities, you use Java. There are several ways to do this and at least one is available in each context:

  • on the module/assembly diagram level – create a Java component
  • inside a mediation flow – write your Java in a Custom Mediation primitive
  • in a BPEL process – use Snippet activity
  • in a BO map – use Custom transform

Best practice: only trivial code should go into Snippets and Custom Transform. Refactor more complex code into a utility Java class.

To promote code clarity and simplify maintenance, troubleshooting and debugging, I strongly urge limiting the amount of code you put in Custom Primitive, Snippet and Custom Transform to. Only trivial code should go there. Please do not implement complex or business-relevant logic there. These Java usages are not easily accessible (hidden behind layers of components), difficult to code-review and more cumbersome to step through when using a debugger. If you need to execute something more complex, refactor the logic into a separate utility class and limit your snippet (or custom transform) to a simple method call on the utility class. This approach adheres to many architectural best practices: separation of concerns (Information Expert pattern), promotion of reusability. Code quality improves and your application is easier to understand and maintain.

WPS: Querying human tasks with multiple custom properties

Suppose you have a human task application running on WebSphere Process Server. Your application requires creating, manipulating and assigning tasks based on certain criteria that are not exposed by HTM interface. Naturally, you add a custom property to your tasks to hold this “side” information. This process is described in this developerWorks article.

As an example, your query might look like this:
resultSet = htm.query(
"TASK_CPROP.NAME = 'branch'", …)

But what if you have more then 1 custom property? This Infocenter article gives an example where clause:

When do you need to add these ordinal numbers (like 1 and 2 in the example above) to the TASK_CPROP table name? Does the number has anything to do with the order in which the properties are defined on the task?


  1. If you only use a single custom property in a query, reference TASK_CPROP table without ordinal number.
    You should not use TASK_CPROP1, TASK_CPROP2 or 3,4,5…9 in this case.
  2. If you use more then one custom property in a query, differentiate between them by adding a number to TASK_CPROP table name. You can pick any numbers you want, but be consistent within each query: use TASK_CPROP1 for one parameter, TASK_CPROP2 for another and so on.
    The number has no relationship with the order in which parameters are defined on the task.

    For example, your task may have 3 properties defined in the following order: propcount, branch, costCenter.

    Task with 3 properties: propcount, branch, costCenter

    And the following query referencing “branch” as #1 and “costCenter” as #2 will be perfectly valid:
    "TASK_CPROP1.NAME='branch' and TASK.CPROP2.NAME='costCenter'", ...)

Underlying data for custom properties of human tasks is stored in database table TASK_INST_PROP_T and exposed through a database view TASK_CPROP. Both the table and the view are agnostic of property number. The view is documented here. The view contains only 4 columns: TKIID, NAME, DATA_TYPE and STRING_VALUE. There is only one table/view for custom properties and it has no room to store property’s ordinal number. TASK_CPROP1, TASK_CPROP2 and so on are aliases created in the FROM clause of the query, which is auto-generated behind the scenes based on SELECT and WHERE clauses. So this query:
"TASK_CPROP1.NAME='branch' and TASK.CPROP2.NAME='costCenter'", ...)

may be translated into SQL resembling this (my reconstruction, not actual SQL from WPS product):

and TASK.CPROP2.NAME='costCenter'

Same logic applies, mutatis mutandis, to task templates (TASK_TEMPL_CPROP view) and escalations (ESCALATION_CPROP view)

Coexistence of WebSphere Business Monitor and Process Server 6.2

UPDATE 11/12: The fix for the issue described below is included in WBM 6.2 Fixpack 2, now publicly available.

If you installed WebSphere Business Monitor (aka BAM) version 6.2 into the same directory as Process Server or WESB 6.2, you may run into problems with overlapping OSGI plugins.
Your WPS/WESB modules and mediations could fail with JXPath error like this:

[5/18/09 19:25:21:744 EDT] 0000005f ExceptionUtil E CNTR0020E: EJB threw an unexpected (non-declared) exception during invocation of method "transactionRequiredActivitySessionNotSupported" on bean "BeanId(TestFanoutApp#TestFanoutEJB.jar#Module, null)". Exception data:
Mediation primitive failure:
Mediation primitive: FanOut1
Component: TestFanout
Module: TestFanout
com.ibm.wsspi.sibx.mediation.MediationBusinessException: CWSXM3752E: Error using XPath expression [Ljava.lang.Object;@7d6e7d6e to locate repeating element: org.apache.commons.jxpath.JXPathException: No value for xpath: /body/print/input/orders. This has been reported by the following entity: ID=FanOut1,Request,print,PrintOrder,http://TestFanout/PrintOrder,TestFanout,TestFanout
at com.ibm.ws.sibx.mediation.primitives.util.ExceptionHelper.newMediationBusinessException(ExceptionHelper.java:128)
at com.ibm.ws.sibx.mediation.primitives.fan.FanOutMediation.locateRepeatingElement(FanOutMediation.java:726)
at com.ibm.ws.sibx.mediation.primitives.fan.FanOutMediation.performNonAggregationMediate(FanOutMediation.java:593)
at com.ibm.ws.sibx.mediation.primitives.fan.FanOutMediation.mediate(FanOutMediation.java:265)
at com.ibm.ws.sibx.scax.mediation.engine.JavaMediationPrimitive.performInvocation(JavaMediationPrimitive.java:630)
at com.ibm.ws.sibx.scax.mediation.engine.JavaMediationPrimitive.invoke(JavaMediationPrimitive.java:352)
at com.ibm.ws.sibx.scax.mediation.engine.MediationPrimitive.invokeConnections(MediationPrimitive.java:318)
at com.ibm.ws.sibx.scax.mediation.engine.JavaMediationPrimitive.fireOutputTerminals(JavaMediationPrimitive.java:728)
at com.ibm.ws.sibx.scax.mediation.engine.JavaMediationPrimitive.performInvocation(JavaMediationPrimitive.java:650)
at com.ibm.ws.sibx.scax.mediation.engine.JavaMediationPrimitive.invoke(JavaMediationPrimitive.java:352)
at com.ibm.ws.sibx.scax.mediation.engine.MediationPrimitive.invokeConnections(MediationPrimitive.java:318)
at com.ibm.ws.sibx.scax.mediation.engine.Input.invoke(Input.java:138)
at com.ibm.ws.sibx.scax.mediation.engine.RequestFlow.invokeFlow(RequestFlow.java:132)
at com.ibm.ws.sibx.scax.mediation.engine.MediationFlow.invokeRequestFlow(MediationFlow.java:145)
at com.ibm.wsspi.sibx.mediation.flow.ejb.MediationFlowBean.invokeRequestFlow(MediationFlowBean.java:231)
at com.ibm.wsspi.sibx.mediation.flow.ejb.EJSLocalStatelessTestFanout_c53bef64.invokeRequestFlow(EJSLocalStatelessTestFanout_c53bef64.java:127)
at com.ibm.ws.sibx.scax.mediation.component.ejb.EJBMediationFlowComponentImpl.invokeRequestFlow(EJBMediationFlowComponentImpl.java:223)
at com.ibm.ws.sibx.scax.runtime.handler.MFCImplementationHandler.processMessage(MFCImplementationHandler.java:199)
at com.ibm.ws.sca.internal.message.impl.MessageDispatcherImpl.processMessageWithPCI(MessageDispatcherImpl.java:715)
at com.ibm.ws.sca.internal.message.impl.MessageDispatcherImpl.processMessage(MessageDispatcherImpl.java:1167)
at com.ibm.ws.sca.internal.message.impl.ManagedMessageImpl.process(ManagedMessageImpl.java:843)
at com.ibm.wsspi.sca.ejb.module.impl.ModuleSessionBean.processUOWMessage(ModuleSessionBean.java:336)
at com.ibm.wsspi.sca.ejb.module.impl.ModuleSessionBean.transactionRequiredActivitySessionNotSupported(ModuleSessionBean.java:315)
at com.ibm.wsspi.sca.ejb.module.EJSLocalStatelessModule_43132892.transactionRequiredActivitySessionNotSupported(EJSLocalStatelessModule_43132892.java:233)
at com.ibm.ws.sca.internal.uow.handler.UOWStrategyImpl.transactionGlobalActivitySessionFalse(UOWStrategyImpl.java:311)
at com.ibm.ws.sca.internal.uow.handler.JoinUOWHandler.processMessage(JoinUOWHandler.java:165)

Or you may have classloading problems when trying to work with monitor models.

Same issues may occur with fixpack 1 of both products (WPS/WESB and WBMonitor
This is happening because WPS/WESB and WBM/BAM each comes with its own version of JXPath libraries. A fix for this issue will soon be publicaly available from the monitor team. It would restrict visibility of JXPath library packaged with WBM to monitor code.
If your symptoms match, please ask IBM support about JR33245.