Oracle BPEL Stats/Metrics using custom Java code and dehydration tables

Background:

One of my client requirement is to pull BPEL stats without using Standard OEM services, so did some analysis to meet those requirements. 

Analysis details:

(In my next post, I can/will provide some inputs to extract/trap OSB based metrics.  Stay tuned..

What are all the BPEL process statuses we should consider to collect Matrix
- Closed.Aborted

- Closed.Cancelled
- Closed.Completed
- Closed.Faulted
- Closed.Pending_Cancel
- Closed.stale
- Initiated
- Open.Faulted
- Open.Running
- Open.Suspended
- unknown

What are the composite status codes referred in Oracle SOA Java API ?

Answer:
import oracle.soa.management.facade.CompositeInstance;
public class Lab {
public static void main(String[] args) {
// TODO Auto-generated method stub
System.out.println(CompositeInstance.STATE_COMPLETED + " : State Completed");
System.out.println(CompositeInstance.STATE_COMPLETED_SUCCESSFULLY + " : State Completed Successfully");
System.out.println(CompositeInstance.STATE_FAULTED + " : State Faulted");
System.out.println(CompositeInstance.STATE_IN_FLIGHT + " : State Inflight");
System.out.println(CompositeInstance.STATE_RECOVERY_REQUIRED + " : State Recovery Required");
System.out.println(CompositeInstance.STATE_RUNNING + " : State Running");
System.out.println(CompositeInstance.STATE_STALE + " : State Stale");
System.out.println(CompositeInstance.STATE_SUSPENDED + " : State Suspended");
System.out.println(CompositeInstance.STATE_TERMINATED_BY_USER + " : State Terminated by User");
System.out.println(CompositeInstance.STATE_UNKNOWN + " : State Unknown");
}
}

2 : State Completed

2 : State Completed Successfully

3 : State Faulted

0 : State Inflight

1 : State Recovery Required

0 : State Running

6 : State Stale

5 : State Suspended

4 : State Terminated by User

-1 : State Unknown

What are all the states are available in SOA BPEL for BPEL Processes?

Answer:

select distinct state, state_text

from ( 

 select cxci.state state,

 case cxci.state

 when 0 then 'initiated'

 when 1 then 'open.running'

 when 2 then 'open.suspended'

 when 3 then 'open.faulted'

 when 4 then 'closed.pending_cancel'

 when 5 then 'closed.completed'

 when 6 then 'closed.faulted'

 when 7 then 'closed.cancelled'

 when 8 then 'closed.aborted'

 when 9 then 'closed.stale'

 else 'unknown'

 end  as state_text

 from cube_instance cxci)

group by state, state_text;

What is the query, we can use to collect the metrix at Composite level (not at individual BPEL process level)

Answer:

select distinct composite_name, count(*)

from cube_instance

where cmpst_id != cikey and

componenttype = 'bpel'

group by composite_name

order by composite_name;

What is the query, we can use to collect the metrix at each BPEL individual process level.

Answer:

select distinct composite_name, component_name, count(*)

from cube_instance

where cmpst_id = cikey and

componenttype = 'bpel'

group by composite_name, component_name

order by composite_name, component_name

When these queries may give wrong results?

Answer:

=Case1: If BPEL-Process-A is calling another BPEL-Process-B then, the "idempotent property" in the invoke activity to call Process-B can impact.

If IdempOtent="True"  then, it doesnot store the Instance to de-hydration store (by defautl idempotent = "true"). Usually this property set to "true" for EJB and WSIF invocations.

=Case2: If the BPEL process is defined as "Transient" transactions using below technique then, that BPEL process doesnot store in the de-hydration table.

<property name="bpel.config.inMemoryOptimization">true</property>

<property name="bpel.config.completionPersistPolicy">off</property>

What are the key out-of box Dehydration tables that the EM and Dehydration-API (java pojos) uses to provide the metrix in em-console?

Answer:

=Views : bpel_process_instances, bpel_faults_vw

=Tables : Cube_instance, audit_trail, audit_details, invoke_message (may more tables are there :)

What is the other approach to trap BPEL Runtime instances?

Answer:

BPEL PM Java APIs.  Packages required are : orabpel.jar, ejb.jar, orabpel-common.jar, oc4j-internal.jar, optic.jar

Where can I find above Jar files?

Answer:

//Oracle_SOA1/soa/modules/oracle.soa.bpel_xxx
/\Middleware\Oracle_SOA1\soa\modules\oracle.soa.fabric_11.1.1
\oracle_common\soa\modules\oracle.soa.mgmt_11.1.1

Display all BPEL domains ?

Answer:

Import pacakges:

import com.oracle.bpel.client.BPELDomainStatus;

import com.oracle.bpel.client.Server;

import com.oracle.bpel.client.auth.ServerAuth;

import com.oracle.bpel.client.auth.ServerAuthFactory;

Connect factory properties to connect BPEL Weblogic server:

Properties props = new java.util.Properties();

props.put("java.naming.factory.initial",

"com.evermind.server.rmi.RMIInitialContextFactory" );

props.put("java.naming.provider.url",

"opmn:ormi://phanikumar-vb:xxxx:home/orabpel" );

props.put("java.naming.security.principal", "weblogic" );

props.put("java.naming.security.credentials", "xxxxxx" );

String securityCredentials = "xxxxxx";

ServerAuth auth = ServerAuthFactory.authenticate(securityCredentials, null, props);

Server srv = new Server(auth);

BPELDomainStatus domainList[] = srv.getAllDomainStatus();

for (int i=0; i<domainList.length; i++ ) {

System.out.println( "Domain ID="+domainList[i].getDomainId() );

}