Database Table Structure
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
<SBWMessageBody>
<MessageBody MessageType="TextMessage" />
<Message>Input Text</Message>
</SBWMessageBody>
Message Headers
<SBWMessageHeader>
<MessageHeader Name="ESBX_SYSTEM_TOTAL_TIME" Type="5" Value="0" />
<MessageHeader Name="JMSX_LocalDestination" Type="8" Value="true" />
<MessageHeader Name="ESBX_SYSTEM_EVENT_TYPE" Type="2" Value="1" />
<MessageHeader Name="ESBX_SYSTEM_SINK" Type="7" Value="FES" />
<MessageHeader Name="ESBX_SYSTEM_EVENT_ID" Type="2" Value="1001" />
<MessageHeader Name="ESBX_SYSTEM_SOURCE" Type="7" Value="fps" />
</SBWMessageHeader>
Message Attachments
<SBWMessageAttachment>
<MessageAttachment Name="sample.gif" Value="eJxz93SzsEwUYBBgmMjEAAQzGaBA8ScLIwMDE4MOiAOSZ2DSC8kpVL+7R2aSgWXZymkZKzlb7mgw Psmcl7xqWq7mPl+pWrPw8svTkvv/fX5pIOfGyGANAHUCGcE=" />
</SBWMessageAttachment>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
select extractValue(MESSAGE,'/SBWMessageBody/MessageBody/@MessageType') as MessageType, extractValue(MESSAGE,'/SBWMessageBody/Message') as MessageBody from wf_inst_event_history where workflow_id like 'EVENT_PROCESS1'Result
Query: 2
With x1 as (select MESSAGE_HEADERS x2 from WF_INST_EVENT_HISTORY where document_id like 'Feeder1_EVENT_PROCESS1_1340717633859_35')
select (extractValue(value(x3),'/MessageHeader/@Name')) AS HEADER_NAME
from x1, table(xmlsequence(extract(x2,'/SBWMessageHeader/*')) ) x3Result
Query: 3
with x1 as (select MESSAGE_HEADERS x2 from WF_INST_EVENT_HISTORY where document_id like 'Feeder1_EVENT_PROCESS1_1340717633859_35')
select (extractValue(value(x3),'/MessageHeader/@Name')) AS HEADER_NAME , (extractValue(value(x3),'/MessageHeader/@Value')) AS HEADER_VALUE
from x1, table(xmlsequence(extract(x2,'/SBWMessageHeader/*')) ) x3Result
Query: 4
with x1 as (select MESSAGE_ATTACHMENTS x2 from WF_INST_EVENT_HISTORY where document_id like 'Feeder1_EVENT_PROCESS1_1340717633859_35')
select (extractValue(value(x3),'/MessageAttachment/@Name')) AS ATTACHMENT_NAME
from x1, table(xmlsequence(extract(x2,'/SBWMessageAttachment/*')) ) x3Result
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
select x.Message_Text.getCLOBVal() from wf_inst_event_history x where document_id like 'Feeder1_EVENT_PROCESS1_1340717633859_35'
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
SELECT MESSAGE.value( '(*/MessageBody/@MessageType)[1]' , 'varchar(max)') AS MESSAGE_TYPE,
MESSAGE.value( '(*/Message)[1]' , 'varchar(max)') AS MESSAGE_TEXT
FROM ABHINANDANA.DBO.WF_INST_EVENT_HISTORY;Result
Query: 2
SELECT T.Message_Headers.value( '(@Name)[1]' , 'varchar(max)') AS MESSAGE_HEADER
FROM ABHINANDANA.DBO.WF_INST_EVENT_HISTORY
CROSS APPLY MESSAGE_HEADERS.nodes('*/MessageHeader') as T(Message_Headers)
WHERE DOCUMENT_ID LIKE '%Display1_EVENT_PROCESS1_1340717633859_42%';Result
Query: 3
SELECT T.Message_Headers.value( '(@Name)[1]' , 'varchar(max)') AS MESSAGE_HEADER,
T.Message_Headers.value( '(@Value)[1]' , 'varchar(max)') AS HEADER_VALUE
FROM ABHINANDANA.DBO.WF_INST_EVENT_HISTORY
CROSS APPLY MESSAGE_HEADERS.nodes('*/MessageHeader') as T(Message_Headers)
WHERE DOCUMENT_ID LIKE '%Display1_EVENT_PROCESS1_1340717633859_42%';Result
Query: 4
SELECT T.Message_Attachments.value( '(@Name)[1]' , 'varchar(max)') AS ATTACHMENT_NAME
FROM ABHINANDANA.DBO.WF_INST_EVENT_HISTORY
CROSS APPLY MESSAGE_ATTACHMENTS.nodes('*/MessageAttachment') as T(Message_Attachments)
WHERE DOCUMENT_ID LIKE '%Display1_EVENT_PROCESS1_1340717633859_42%';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:
create table TES_SBW_SCHEMA_VERSION (VERSION integer)
create table TIFOSI_DB_HEALTH (HEALTH varchar(800))If REPRESENTABLE_DATA_TYPES is set to 'false', then use the following query to create WF_INST_EVENT_HISTORY table:
create table WF_INST_EVENT_HISTORY (EVENT_ID integer, WORKFLOW_INSTANCE_ID varchar(800), WORKFLOW_ID varchar(800), USER_DEFINED_DOC_ID varchar(800), SERVICE_INST_ID varchar(800), STATE_ID varchar(800), STATE_COMMENT varchar(800), STATE_EVENT_DATE varchar(800), DOCUMENT_ID varchar(800), WORKFLOW_STATUS varchar(800), IN_TIME DATE, OUT_TIME DATE, TOTAL_TIME varchar(800), WF_VERSION varchar(800), DOCUMENT BLOB, HAS_BEEN_RESET varchar(4))If REPRESENTABLE_DATA_TYPES is set to 'true', then use the following query to create WF_INST_EVENT_HISTORY table:
create table WF_INST_EVENT_HISTORY (EVENT_ID integer, WORKFLOW_INSTANCE_ID varchar(800), WORKFLOW_ID varchar(800), USER_DEFINED_DOC_ID varchar(800), SERVICE_INST_ID varchar(800), STATE_ID varchar(800), STATE_COMMENT varchar(800), STATE_EVENT_DATE varchar(800), DOCUMENT_ID varchar(800), WORKFLOW_STATUS varchar(800), IN_TIME DATE, OUT_TIME DATE, TOTAL_TIME varchar(800), WF_VERSION varchar(800), HAS_BEEN_RESET varchar(4))
ALTER TABLE WF_INST_EVENT_HISTORY ADD MESSAGE XMLTYPE XMLTYPE MESSAGE STORE AS CLOB
ALTER TABLE WF_INST_EVENT_HISTORY ADD MESSAGE_HEADERS XMLTYPE XMLTYPE MESSAGE_HEADERS STORE AS CLOB
ALTER TABLE WF_INST_EVENT_HISTORY ADD MESSAGE_ATTACHMENTS XMLTYPE XMLTYPE MESSAGE_ATTACHMENTS STORE AS CLOB
ALTER TABLE WF_INST_EVENT_HISTORY ADD MESSAGE_CFWD_PROPERTIES XMLTYPE XMLTYPE MESSAGE_CFWD_PROPERTIES STORE AS CLOB
ALTER TABLE WF_INST_EVENT_HISTORY ADD MESSAGE_CFWD_SRC_CONTEXT XMLTYPE XMLTYPE MESSAGE_CFWD_SRC_CONTEXT STORE AS CLOB