In this article I discuss some tables from the SOAINFRA schema that might be most interesting to use when trying to find out why you don't see in Enterprise Manager what you expect.
Going from 11g to 12c, some things have significantly changed in the SOAINFRA schema. For example, your normal partners in helping with "what happened with my process?" type of queries, like the component_instance, and bpm_process tables, have become obsolete. On the other hand you have new friends with tables like sca_flow_instance, and sca_entity.
The following discusses some tables that you might want to look into when digging in the dirt of the SOA/BPM engine's intestines.
The tables I would like to discuss in more detail are:
- sca_flow_instance
- cube_instance
- wftask
- sca_entity
- bpm_cube_process
- bpm_cube_activity
Given that there is no official documentation on these tables, this is based on my observations an interpretations. No guarantee that these are flawless, so if you have anything to improve or add, let me know!
To better understand the data in the SOAINFRA in relation to an actual process, I used 1 composite with the following processes, that has two subprocesses (another BPM process and a BPEL process). The BPM subprocess has not been implemented as a reusable process (with a Call activity) but instead as a process-as-a-service.
As a side note: originally I created this process to be able to verify how the different states a process and its children can have, are represented in Enterprise Manager. The reason being that on one of my projects there were some doubts if this is always correct, given some issues in the past with 11g. With 12c I could find none. However, as the test case does not concern inter-composite interaction, nor does it include all types of technologies, you could argue that the test case is too limited to conclude anything from it. Also worth to mention is that the instances are ran on a server in development mode, and without in-memory optimization. I have heard rumors that you will observer different behavior when you disabled auditing completely. In some next posting I hope to discuss that as well.
I initiated several instances, for each possible state one:
When queried this looks similar to this:
select sfi.flow_id
, sfi.title
, sfi.active_component_instances
, sfi.recoverable_faults
, sfi.created_time
, sfi.updated_time
from sca_flow_instance sfi
order by sfi.created_time
(*) corresponds with the bpm_cube_process.scalabel
(**) equals sca_flow_instance.created_time
When queried this looks similar to this:
The query used is like this:
This table contains an entry for each open process activity and open or closed human activity. You might be interested in the following columns:
(*) : the type of activity, e.g. USER_TASK, INCLUSIVE_GATEWAY, END_EVENT
(**) not for human tasks
(***) e.g. Workflow, BPMN
(****) Corresponds with the activityid of bpm_cube_activity. The user activity and its corresponding human task appear to have the same activityid. After the human task is completed, the user activity disappears but the human task is kept with an null state.
(*****) e.g. OPEN for running activities, ASSIGNED for running human tasks. Other states are ABORTED, PENDING_MIGRATION_SUSPENDED, ERRORED, etc.
When queried this looks similar to this:
The query used is like this:
When queried this looks similar to this:
The query used is like this:
select sen.composite
, sen.id
, sen.label
from sca_entity sen
where sen.composite = 'FlowState'
order by sen.composite
When queried this looks similar to this:
The query used is like this:
The query used is like this:
Going from 11g to 12c, some things have significantly changed in the SOAINFRA schema. For example, your normal partners in helping with "what happened with my process?" type of queries, like the component_instance, and bpm_process tables, have become obsolete. On the other hand you have new friends with tables like sca_flow_instance, and sca_entity.
The following discusses some tables that you might want to look into when digging in the dirt of the SOA/BPM engine's intestines.
The tables I would like to discuss in more detail are:
- sca_flow_instance
- cube_instance
- wftask
- sca_entity
- bpm_cube_process
- bpm_cube_activity
Given that there is no official documentation on these tables, this is based on my observations an interpretations. No guarantee that these are flawless, so if you have anything to improve or add, let me know!
To better understand the data in the SOAINFRA in relation to an actual process, I used 1 composite with the following processes, that has two subprocesses (another BPM process and a BPEL process). The BPM subprocess has not been implemented as a reusable process (with a Call activity) but instead as a process-as-a-service.
As a side note: originally I created this process to be able to verify how the different states a process and its children can have, are represented in Enterprise Manager. The reason being that on one of my projects there were some doubts if this is always correct, given some issues in the past with 11g. With 12c I could find none. However, as the test case does not concern inter-composite interaction, nor does it include all types of technologies, you could argue that the test case is too limited to conclude anything from it. Also worth to mention is that the instances are ran on a server in development mode, and without in-memory optimization. I have heard rumors that you will observer different behavior when you disabled auditing completely. In some next posting I hope to discuss that as well.
I initiated several instances, for each possible state one:
sca_flow_instance
As the name already suggests, this table contains 1 entry for each flow instance. You might be interested in the following columns:- flow_id
- title
- active_component_instances
- recoverable_faults
- created_time
- updated_time
When queried this looks similar to this:
The query used is like this:
, sfi.title
, sfi.active_component_instances
, sfi.recoverable_faults
, sfi.created_time
, sfi.updated_time
from sca_flow_instance sfi
order by sfi.created_time
cube_instance
This table contains 1 entry for each component instance in the flow (e.g. bpmn, bpel). You might be interested in the following columns:
- flow_id
- composite_label (*)
- cpst_inst_created_time (**)
- composite_name
- composite_revision
- component_name
- componenttype
- state (of the component <== mention)
- creation_date (incl time)
- modify_date (incl time)
- conversation_id
(*) corresponds with the bpm_cube_process.scalabel
(**) equals sca_flow_instance.created_time
When queried this looks similar to this:
The query used is like this:
select cis.flow_id
, cis.componenttype
, cis.component_name
, cis.state
from cube_instance cis
order by cis.flow_id
wftask
This table contains an entry for each open process activity and open or closed human activity. You might be interested in the following columns:
- flow_id
- instanceid
- processname
- accesskey (not for human tasks) (*)
- createddate
- updateddate
- (only in case of human tasks, the flex fields)
- componentname
- compositename (not for human tasks)
- conversationid
- componenttype (***)
- activityname
- activityid (****)
- component_instance_id (only for human tasks)
- state (*****)
(*) : the type of activity, e.g. USER_TASK, INCLUSIVE_GATEWAY, END_EVENT
(**) not for human tasks
(***) e.g. Workflow, BPMN
(****) Corresponds with the activityid of bpm_cube_activity. The user activity and its corresponding human task appear to have the same activityid. After the human task is completed, the user activity disappears but the human task is kept with an null state.
(*****) e.g. OPEN for running activities, ASSIGNED for running human tasks. Other states are ABORTED, PENDING_MIGRATION_SUSPENDED, ERRORED, etc.
When queried this looks similar to this:
The query used is like this:
select wft.instanceid
, wft.processname
, wft.accesskey
, wft.createddate
, wft.updateddate
, wft.componentname
, wft.compositename
, wft.conversationid
, wft.componenttype
, wft.activityname
, wft.activityid
, wft.component_instance_id
, wft.state
from wftask wft
where wft.flow_id = 130001
order by wft.updateddate
sca_entity
This table contains an entry for each SCA entity (e.g. service, wire). The following column might be of use:- id
- composite (name)
- label (corresponds with the scalabel of bpm_cube_process)
When queried this looks similar to this:
The query used is like this:
, sen.id
, sen.label
from sca_entity sen
where sen.composite = 'FlowState'
order by sen.composite
bpm_cube_process
This table contains metadata. For each deployed composite it contains an entry for each BPM process. If 2 BPM processes in once composite: 2 entries. The following columns might be of use:- domainname
- compositename
- revision
- processid
- processname
- scalabel
- compositedn
- creationdate (incl time)
- undeploydate
- migrationstatus (*)
When queried this looks similar to this:
The query used is like this:
select bcp.domainname
, bcp.compositename
, bcp.revision
, bcp.processname
, bcp.processid
, bcp.scalabel
, bcp.compositedn
, bcp.creationdate
, bcp.undeploydate
, bcp.migrationstatus
from bpm_cube_process bcp
where bcp.compositename = 'FlowState'
order by bcp.processname
, bcp.creationdate
bpm_cube_activity
This table contains metadata, There is an entry for each individual activity, event, and gateway of a bpmn process. The following column might be of use:- processid (corresponds with the bpm_cube_process.processid)
- activityid
- activityname (technical, internal name can be found in the .bpmn source)
- activitytype (e.g. START_EVENT, SCRIPT_TASK, CALL_ACTIVITY, etc.)
- label (name as in the BPMN diagram)
select cbi.flow_id
, cbi.composite_label
, cbi.cpst_inst_created_time
, cbi.composite_name
, cbi.composite_revision
, cbi.component_name
, cbi.componenttype
, cbi.state
, cbi.creation_date
, cbi.modify_date
, cbi.conversation_id
from cube_instance cbi
order by cbi.creation_date
Obsolete Tables
The following table have become obsolete:- bpm_activity
- bpm_activity_instance
- bpm_cube_activity_instance
- bpm_process
- component_instance
