How to store/read data from FIX Messages to/from DB

Overview

FIXEdge offers a flexible solution for storing information that goes through the Business Layer of FIXEdge – Histories. ODBCHistory is one of the histories type which allows storing information into the database.

Preparation phase

  1. Make a backup copy of your current configuration;
  2. Make sure you have sufficient administration permissions for further uninstall/install procedures;
  3. Install the required DataBase;
  4. Make sure that DB driver is installed on the machine, that you are going to connect to;
  5. Make sure that you have your database connection parameters (host, port (if it is different from default), server name, username, password);
  6. Define the data which should be stored to the DataBase and create corresponding scheme;

    Take into account that ODBCHistory supports the following types of fields:

    1. Int
    2. Float
    3. Numeric. Additional required attributes for this type are Precision (max number of decimal digits, must have the value from 1 to 38) and Scale (max number of decimal digits that can be stored to the right of decimal point, should be not less than 0 and not greater that Precision)
    4. Date
    5. DateTime
    6. String – default field type
  7. Create the history definition in the Business Layer Configuration file according to the steps below:
    1. Specify <History> element. The following attributes should be defined for the <History> element:

      Name of the attribute
      Required
      Description
      NameYName of the history, is used to refer to history from XML actions or JavaScript functions.
      StorageTypeY, StorageType = “ODBC”Type of the storage.
      ClearTimeNDefines when every-day clear procedure is started. The format is HH:MM:SS. Clear procedure erases obsolete records.
      TableName YName of the table in the database. History will be reflected in this table.
      ColumnSizeNDefault size of history string fields.
      ConnectionStringYODBC connection string
    2. Specify mapping rules for each element in the <History> and for "KeyField" and "Field" (KeyField should be a key field in the database table) .

    Below is the example of ODBCHistory definition:

    // Defines history "SampleODBCHistory" that reflects on the Orders table in database SampleBase.
    // Following FIX message tags mapped into the Orders table fields:
    // ClOrdID(11) -> ClOrdID, type - string
    // SenderCompId(49) -> SenderCompId, type - string[512]
    // TargetCompId(56) -> TargetCompId, type - string
    // SettlDate(64) -> SettlDate, type - date
    // Price(44) -> Price, type - numeric [20,6]
    // History maps the record's ExpireDateTime attribute to the database field TransactTime
    <History Name="SampleODBCHistory"
             StorageType="ODBC"
             TableName="SampleBase.dbo.Orders"
             ClearTime="22:00:00"
             ColumnSize="256"
             ConnectionString="DSN=SampleBase;UID=test;PWD=test123;">
       <KeyField ColumnName="ClOrdID">11<KeyField>
       <Field ColumnName="SenderCompID" ColumnSize="512">49</Field>
       <Field ColumnName="TargetCompID">56</Field>
       <Field ColumnName="Price" DataType="Numeric" Precision="20" Scale="6">44</Field>
       <Field ColumnName="SettlDate" DataType="Date" ColumnSize="10">64</Field>
       <Field ColumnName="TransactTime">ExpireDateTime</Field>
    </History>

How to store data to DB

In order to store some data from FIX messages in DB there is a need to specify the rules in the Business Layer Configuration file.

Actions defined in the business rule are executed synchronously.

There are two ways to call the command that stores data from the handled message in the DB storage:


  1. XML actionSaveToHistory;
  2. JavaScript functionsaveToHistory.

XML SaveToHistory action

Below is the example of invoke of the SaveToHistory XML action:

<Rule Enabled="false" Description="Override for new order factory rule, when send to CME">
    <Source>
        <Session Name=".*" />
    </Source>
    <Condition>
        <MatchField Tag="35" Value="D|AB" />
        <EqualField Tag="100" Value="CME" />
    </Condition>
    <Action>
        <Convert TargetProtocol="CME_FIX42" />
        <SaveToHistory Name="SampleODBCHistory"/>
        <Send>
            <Session Name="CME">
        </Send>
    </Action>
</Rule>

JavaScript saveToHistory function

Below is the example of invoke of the saveToHistory JavaScript function in the Rule:

<Rule Description="Save MDReqID from Market Data subscription request to MDReqID history">
    <Source Name="Instruments" />
    <Condition>
        <EqualField Field="35" Value="V"/>
        <EqualField Field="263" Value="1"/>
    </Condition>
    <Action>
        <Script Language="JavaScript" FileName="saveMDReqIDToHistory.js"/>
        <Send Name="Prices"/>
    </Action>
</Rule>

Below is the example of the saveMDReqIDToHistory.js:

//Save MDReqID for Symbol
handle = getGroup("146")
tag_55 = getStringField(handle, 0, "55");
key = new Array(tag_55);
tag_262 = getStringField("262");
id = new Array(tag_262);
saveToHistory ("MarketDataRequestID_Map", key, id, "");

Refer to the saveToHistory article for JavaScript code details.

How to read data from DB

In order to get the stored data from DB there is a need to specify the rules in the Business Layer Configuration file.

There are two JavaScript functions that allow to get data from the DB history:

  • getFromHistory – locates a record in the history by key and retrieves the value of the record field
  • getRecordFromHistory – locates a record in the history by key and returns it

Below is the example of invoke of the getFromHistory JavaScript function:

<Rule Description="Get MDReqID from the history when Market Data unsubscription request is received">
    <Source Name="Instruments"/>
    <Condition>
        <EqualField Field="35" Value="V"/>
        <EqualField Field="263" Value="2"/>
    </Condition>
    <Action>
        <Script Language="JavaScript" FileName="getMDReqIDFromHistory.js"/>
        <Send Name="Prices"/>
    </Action>
</Rule>

Below is the example of the getMDReqIDFromHistory.js:

//Get MDReqID for Symbol
handle = getGroup("146")
tag_55 = getStringField(handle, 0, "55");
key = new Array(tag_55);
tag_262 = getFromHistory ("MarketDataRequestID_Map", key, "262");
setStringField(262, tag_262);

Refer to articles getFromHistory and getRecordFromHistory for JavaScript code details.

How to manipulate data stored in DB

In order to manipulate stored data there is a need to specify the rules in the Business Layer Configuration file.

There are several ways to manipulate data from DB:

The rules of invoke of these JavaScript functions are the same as in the examples above. Refer to the linked articles for the JavaScript code details.

FAQ

What happens to FIX Messages if there is a failure writing to the database or a database outage? Would the writing fail at that point?

If there is a failure writing to the database, OnRuleFailEvent will be generated. The FixEdge behaviour on handling messages that failed to reach the database can be defined by specifying particular OnRuleFailEvent for them.

Would the message failed to reach the database be queued internally and written when the database would be available without affecting the message?

If the work with the database is kept within a separate rule, then the database failure shall have no impact onto the main rule for message processing, since rules are executed independently. However, there is no internal queue to postpone messages till the database is up.