SBW Database Table Structure
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
Data Retrieval from SQL Server Database
Query: 1
Result
Query: 2
Result
Query: 3
Result
Query: 4
Result
Creating SBW Tables Manually
If the server does not have enough permissions to create the sbw tables, then tables can be manually created using the following SQL queries:
If REPRESENTABLE_DATA_TYPES is set to 'false', then use the following query to create WF_INST_EVENT_HISTORY table:
If REPRESENTABLE_DATA_TYPES is set to 'true', then use the following query to create WF_INST_EVENT_HISTORY table: