Querying Composite Instance States in SOA-INFRA Dehydration Store for Oracle SOA Suite 11g Processes

Posted on by

If you have a large number of composite instances present for a single or multiple composites the Enterprise Manager will not be a very ideal place to look at if we want to know the states of these instances.

We can however connect directly to the SOA_INFRA schema for our Oracle SOA Suite installation to execute a few scripts to view instance states of various composites.

Here are the database scripts available to find composite instances in various states in the CUBE_INSTANCE table.

The table below shows the various State Value for composite instances in the CUBE_INSTANCE table of the SOA_INFRA schema in the dehydration store.

STATE VALUE STATE STATE MEANING
0 INITIATED

State value for an instance that has just been created. The instance will only have this value as its state after it has been created by the process domain.
1 OPEN AND RUNNING

State value for an instance that has been created and has active activities executing. The instance is not in an exception or error condition.
2 OPEN AND SUSPENDED

State value for an instance that is unavailable. Performers of any of the activities that belong to this instance cannot take any action until the instance has returned to the running state.
3 OPEN AND FAULTED

State value for an instance that has an activity that has thrown an exception. When an activity throws an exception, the instance is flagged as being in an exception state until the exception is bubbled up, caught and handled.
4 CLOSED AND PENDING

State value for an instance that has started its cancellation procedure. Since cancelling an instance may involve a great deal of business logic, the amount of time the entire cancellation process may take may be anywhere from seconds to days. During this time, the instance is said to be pending cancellation; an instance may not be acted upon during this time.
5 CLOSED AND COMPLETED

State value for an instance that has been completed. All activities belonging to this instance have also been completed.
6 CLOSED AND FAULTED

State value for an instance that has an activity that has thrown an exception while the instance is being cancelled. This state is equivalent to <code>STATE_OPEN_FAULTED</code> except that when the exception is resolved, the state transitions back to <code>CLOSED_PENDING_CANCEL</code> rather than <code>STATE_OPEN_RUNNING</code>
7 CLOSED AND CANCELED

State value for an instance that has been cancelled. All activities belonging to this instance have also been cancelled.
8 CLOSED AND ABORTED

State value for an instance that has been aborted due to administrative control. All activities belonging to this instance are also moved to the aborted state.
9 CLOSED AND STALE

State value for an instance who’s process has been changed since the process was last accessed. No actions may be performed on the instance. All activities that belong to this instance are also moved to the stale state.
10 NON RECOVERABLE

State value of instance that has failed and is marked as non recoverable.

The database query that can fetch and tabulate the number of instances in various states in the CUBE_INSTANCE table

SELECT (CASE WHEN STATE=1 THEN 'OPEN AND RUNNING'
WHEN STATE=2 THEN 'OPEN AND SUSPENDED'
WHEN STATE=3 THEN 'OPEN AND FAULTED'
WHEN STATE=4 THEN 'CLOSED AND PENDING'
WHEN STATE=5 THEN 'CLOSED AND COMPLETED'
WHEN STATE=6 THEN 'CLOSED AND FAUTED'
WHEN STATE=7 THEN 'CLOSED AND CANCELLED'
WHEN STATE=8 THEN 'CLOSED AND ABORTED'
WHEN STATE=9 THEN 'CLOSED AND STALE'
WHEN STATE=10 THEN 'NON-RECOVERABLE'
ELSE STATE || ''
END) AS STATE, COUNT(*) AS NUM_OF_CUBE_INST FROM CUBE_INSTANCE GROUP BY STATE;

In case you would like the same count of instances for a particular COMPOSITE application then extend the above sql script by adding additional filters to it.

You can add filters on CUBE_INSTANCE table based on COMPOSITE_NAME, COMPOSITE_REVISION, COMPONENTTYPE (BPEL/BPM/MEDIATOR) etc.

.

12 thoughts on “Querying Composite Instance States in SOA-INFRA Dehydration Store for Oracle SOA Suite 11g Processes”

  1. Hi Arun,
    I have a requirement,One interface say Hello world.How can I get the number request’s (count) for this interface and also in that count how to know how many are completed and how many are faulted.
    When I follow some blogs,they were say it’s possible with BAM.
    But I have no idea about BAM.If is there any alternative please give directions and instruction how to achieve this task.
    Thanks in advance.

    Regards
    Mani

    Like

    Reply

  2. Hi Arun,

    Thanks for ur nice post i helped me….If we want to u count number of instances for a particular composite u told to use filters.. can u plz post sample how to add a filter
    for a particular composite in SOA it will be great helpfull.. Thanks in advance

    Thanks,
    Raj

    Like

    Reply

    • By Filters i meant where conditions that you may use while running your database queries. For example in case you need to know all the instance for a composite named SampleSalesOrderService you can modify the query with a filter like below

      SELECT (CASE WHEN STATE=1 THEN ‘OPEN AND RUNNING’
      WHEN STATE=2 THEN ‘OPEN AND SUSPENDED’
      WHEN STATE=3 THEN ‘OPEN AND FAULTED’
      WHEN STATE=4 THEN ‘CLOSED AND PENDING’
      WHEN STATE=5 THEN ‘CLOSED AND COMPLETED’
      WHEN STATE=6 THEN ‘CLOSED AND FAUTED’
      WHEN STATE=7 THEN ‘CLOSED AND CANCELLED’
      WHEN STATE=8 THEN ‘CLOSED AND ABORTED’
      WHEN STATE=9 THEN ‘CLOSED AND STALE’
      WHEN STATE=10 THEN ‘NON-RECOVERABLE’
      ELSE STATE || ”
      END) AS STATE, COUNT(*) AS NUM_OF_CUBE_INST FROM CUBE_INSTANCE where COMPOSITE_NAME= ‘SampleSalesOrderService’ GROUP BY STATE;

      Like

      Reply

  4. Hi Arun thanks for nice article.

    I am able to get the single composite name by run the Query. How to to run this this by using DB adapter.I tried with Execute pure sql function but I got error

    BINDING.JCA-12563 Exception occured when binding was invoked. Exception occured during invocation of JCA binding: “JCA Binding execute of Reference operation ‘CountReq’ failed due to: Pure SQL Exception. Pure SQL Execute of SELECT (CASE WHEN STATE=1 THEN ‘OPEN AND RUNNING’ WHEN STATE=2 THEN ‘OPEN AND SUSPENDED’ WHEN STATE=3 THEN ‘OPEN AND FAULTED’ WHEN STATE=4 THEN ‘CLOSED AND PENDING’ WHEN STATE=5 THEN ‘CLOSED AND COMPLETED’ WHEN STATE=6 THEN ‘CLOSED AND FAUTED’ WHEN STATE=7 THEN ‘CLOSED AND CANCELLED’ WHEN STATE=8 THEN ‘CLOSED AND ABORTED’ WHEN STATE=9 THEN ‘CLOSED AND STALE’ WHEN STATE=10 THEN ‘NON-RECOVERABLE’ ELSE STATE || ” END) AS STATE, COUNT(*) AS NUM_OF_CUBE_INST FROM CUBE_INSTANCE GROUP BY STATE; failed. Caused by java.sql.SQLSyntaxErrorException: ORA-00911: invalid character . The Pure SQL option is for border use cases only and provides simple yet minimal functionality. Possibly try the “Perform an operation on a table” option instead. This exception is considered not retriable, likely due to a modelling mistake. To classify it as retriable instead add property nonRetriableErrorCodes with value “-911” to your deployment descriptor (i.e. weblogic-ra.xml). To auto retry a retriable fault set these composite.xml properties for this invoke: jca.retry.interval, jca.retry.count, and jca.retry.backoff. All properties are integers. “. The invoked JCA adapter raised a resource exception. Please examine the above error message carefully to determine a resolution.

    Any suggestions

    Thanks
    Mani

    Like

    Reply

  5. Arun,

    Does the fault policy works the same way for one way, Async and Synchronous process? I am having issues with one fault policy business fault, so this question.

    Like

    Reply

    • Hi,

      Fault Handling certainly depends upon the fact that whether the processes are sync or async and their level of dehydration. Oracle SOA Suite 11g will need the faulted instance to be dehydrated in order for certain fault policies to work. While other fault policies such as ora:retry may be applicable to sync, one way and async processes. Can you elaborate on the exact nature of your process and the type of fault handling you want to be applied for me to provide a more formidable answer.

      Cheers
      Arun

      Like

      Reply

  6. Hi Arun,

    Gr8 post…I have one question.Usually when we do development in a group we deploy soa projects into a common weblogic Server.Is there a way to find out who has the done the deployment.I mean is there any way we could get the hostname of the system from which deployment is done for a particular service for a particular version???

    Like

    Reply

    • Hi,

      Unfortunately this is not possible as far as my knowledge is concerned. You raise an important point though. However in most organizations only a handful of people (part of the platform administration group or QA) who have access to server beyond the development environment. They are responsible for builds and deployments.

      Thanks

      Like

      Reply

If you have any comments, suggestions or feedback about the post, please feel free to type it here and I will do my best to address them asap