Thursday, May 28, 2009

Querying the BPEL Process Manager instance table

When you have tens of thousands of instances logged and audited in your BPEL server, navigating the instances within BPEL Control (the BPEL console) becomes unrealistic.

You can use SQL to query the instance table in the ORABPEL schema in Oracle BPEL Process Manager 10g ( to narrow down your search.

SELECT process_id,
DECODE(TO_CHAR(state),'1','In-flight','5','Success','6','Warning','9','Stale','8','Error',TO_CHAR(state)) state,
modify_date-creation_date duration,
SUBSTR(REGEXP_SUBSTR(title, '[^ ]+', 1, 2), 2) instance_id
FROM cube_instance
ORDER BY creation_date DESC;
Here is an example of the output generated by the query above:
---------- ----- ---------------------------- ------------ -------------------------- -----------
HelloWorld Error 27-MAY-09 PM 1.0 +000000000 00:00:00.016000 200003
HelloWorld Stale 27-MAY-09 PM 1.0 +000000000 00:00:01.781000 200001
HelloWorld Success 26-MAY-09 PM 1.0 +000000000 00:07:24.969000 190003
To explain a few things regarding the query:
  • The STATE column stores values of 1 (In-flight), 5 (Success), 6 (Warning), 8 (Error), and 9 (Stale). Stale processes are those that have been undeployed.
  • The DURATION column is calculated by substracting the CREATION_DATE from the MODIFY_DATE. The CREATION_DATE is when the process was instantiated, and the MODIFY_DATE is when the last activity in the process was executed.
  • Per the SQL statement above, the INSTANCE_ID is actually parsed from the TITLE column.
With this query, you now have the power to easily access your BPEL instance information.

No comments: