Contents

Schemas in a database table will be different in each database according to the configurations provided in the <dbtype>_jdbc.cfg file. The columns in the table and the various fields for the schema are listed below:

WF_INST_EVENT_HISTORY table

Column Name

Type

Description

EVENT_ID

INTEGER

Auto Generated

WORKFLOW_INSTANCE_ID

VARCHAR(255)

Auto Generated

WORKFLOW_ID

VARCHAR(255)

GUID of the corresponding Application

USER_DEFINED_DOC_ID

VARCHAR(255)

Can be set by the user

SERVICE_INST_ID

VARCHAR(255)

Service instance name

STATE_ID

VARCHAR(255)

Port name

STATE_COMMENT

VARCHAR(255)

Description of the workflow item

STATE_EVENT_DATE

VARCHAR(255)

Date at which message reached the port

DOCUMENT_ID

VARCHAR(255)

Auto Generated

WORKFLOW_STATUS

VARCHAR(255)

EXECUTED or EXECUTING

IN_TIME

TIMESTAMP

Set if its inport

OUT_TIME

TIMESTAMP

Set if its outport

TOTAL_TIME

VARCHAR(255)

Time spend between inport and outport

DOCUMENT

IMAGE

BLOB of actual message data

EVST View: The views presented in EVST and dashboard uses the above schema in logical groupings.
Application view: Lists all workflows for a given application.
Workflow: All entries in event history table for a given workflow ID are grouped together as a workflow. The status of the workflow is the status of the entry with the latest time stamp. The cycle time is the sum of all total times.
Document view: Lists all the documents for a given workflow. This contains one item per entry in the event history table. The details have one-to-one correspondence.
Document: Shows the actual message content. This provides a view of the BLOB data present in archive table.

Structure of IMAGE/BLOB field

The IMAGE/BLOB field is a serialized form of an object of the class fiorano.jms.services.msg.def.FioranoMessage (packaged in $FIORANO_HOME/fmq/lib/common/fmq-common-msg-impl.jar). To de-serialize the field, a user may use an API available in Enterprise Server's SBW module. Please refer to the sample named 'SBWDataReader.java' located under $FIORANO_HOME/esb/samples/DocTracking which provides sample usage of this API.

Following information is available in a BLOB field.

  • Document Information: All the other fields of the document tracking table as explained in the section above. For example, Source Peer Name, Event Process Name, Service Instance Name, Port Name, In Time, Out Time, Document ID, Workflow Instance ID etc.
  • Header Information: Other message header properties represented by a java.util.HashMap object of property name vs. property value.
  • Carry Forward Context: This information is present as an object of type fiorano.esb.util.CarryForwardContext. This object contains the following information:
    • Application Context (if defined)
    • Carry Forward Properties (The message properties carried forwarded from the message received at last doc tracked port)
    • An Enumeration of fiorano.esb.util.SourceContext containing information about the output port of the components from where the message has traveled so far.
  • Attachment(s): This information is present as an object of type java.util.Hashtable<String, byte[]>. The String part represents the attachment name and byte[] represents the contents of the file in byte[] form.
  • Message Text: Message Text can be retrieved using the API named MesageUtil.getTextData(message) which returns a String containing the message text.

Representable Data Types

Document Tracking feature allows to store messages in representable data formats. In previous installers, the "DOCUMENT" field contains information about Message Headers, Message Text, Attachments, and Carry Forward Context in binary format. With this feature, the DOCUMENT field is split into five different objects and stores those objects in five different columns in XML format.

Column Name

Type

Description

MESSAGE

XML/CLOB

Message Body

MESSAGE_HEADERS

XML/CLOB

Message Headers

MESSAGE_ATTACHMENTS

XML/CLOB

Message Attachments

MESSAGE_CFWD_PROPERTIES

XML/CLOB

Message Carry Forward Properties

MESSAGE_CFWD_SRC_CONTEXT

XML/CLOB

Message Carry Forward SourceContext

This feature can be enabled by changing REPRESENTABLE_DATA_TYPES property value to "true", which is present in the sbwdb.cfg file under FIORANO_INSTALL_DIR\esb\server\profiles\<profilename>\FES\conf. By default, it is set to "false" storing the messages in binary format.

Documents can be searched based on the message body, message headers, and attachment names as well.

Message Body


Message Headers


Message Attachments

Below-mentioned are a few XML queries to extract the message, headers, and attachment details from Oracle and MSSQL database.

Data Retrieval from Oracle Database

Query: 1

Result

Query: 2

Result

Query: 3

Result

Query: 4

Result

An error saying "ORA-31167: XML nodes over 64K in size cannot be inserted exception" is possible. This error indicates that the processing data size is more than 64k, which cannot be processed by Oracle 10g. This bug is fixed in Oracle 11g. However, in the event of this error, convert the XMLType to CLOB in order to display the record contents.

Query: 5

Icon

This query won't format the data, but displays the whole XML as String. To display in proper XML format, write customized views.

Data Retrieval from SQL Server Database

Query: 1

Result

Query: 2

Result

Query: 3

Result

Query: 4

Result

Adaptavist ThemeBuilder EngineAtlassian Confluence