Ember ORDERS Table Migration Guide
Overview
This guide maps the ORDERS table schema in Deltix UHF 4.3 with the new schema of ORDERS table used by Ember.
Schema Architecture Changes
New Temporal/Versioning Fields
The new schema introduces event sourcing concepts:
- Term - Event term identifier
- OpenSequence - Sequence number when record opened
- CloseSequence - Sequence number when record closed
- OpenTime - When order was created/opened (replaces SUBMIT_TIME)
- CloseTime - When order was last updated/closed (replaces UPDATE_TIME)
Field Mapping Reference
Direct Mappings with Data Type Changes
| Old Field | New Field | Type Change | Notes |
|---|---|---|---|
| SYMBOL | Symbol | VARCHAR(32) → VARCHAR(256) | |
| ACCOUNT | Account | VARCHAR(32) → VARCHAR(256) | |
| TRADER_ID | TraderId | VARCHAR(32) → VARCHAR(256) | |
| EXPIRATION | ExpireTime | TIMESTAMP → DATETIME | |
| SETTLE_DATE | SettlementDate | TIMESTAMP → DATETIME | |
| SUBMIT_TIME | OpenTime | TIMESTAMP → DATETIME | |
| UPDATE_TIME | CloseTime | TIMESTAMP → DATETIME | |
| MODULE_KEY | ModuleKey | VARCHAR(64) → VARCHAR(32) | Size reduced |
| PORTFOLIO_KEY | PortfolioKey | VARCHAR(32) → VARCHAR(32) | No change |
| REASON | UserData | VARCHAR(64) → VARCHAR(MAX) | Larger capacity |
Order Identification Changes
Ember uses composite order identity that combines identifier of order source (e.g. ID of client FIX session or execution algorithm) with free-text order identifier. Order identifier is unique only in scope of each order source. To some extent nullable field CLIENT_ID in old schema acted as order source.
| Old Field | Old Type | New Field(s) | New Type | Notes |
|---|---|---|---|---|
| ORDER_PK | BIGINT (PK) | OrderId | VARCHAR(256) | Now string-based identifier |
| CLIENT_ID | VARCHAR(32) | SourceId | CHAR(10) | System/client identifier |
| EXTERNAL_ID | ExternalOrderId | VARCHAR(64) → VARCHAR(256) |
Optional exchange-assigned "external" order ID remains, but should not be used as primary order identification mechanism.
Parent-Child order relationships
Ember has much cleaner parent-child reference model - old model did not provide a field to store parent order source (no PARENT_CLIENT_ID).
| Old Field | Old Type | New Field(s) | New Type | Notes |
|---|---|---|---|---|
| PARENT_FK | BIGINT (FK) | ParentSourceId + ParentOrderId | CHAR(10) + VARCHAR(256) | Now requires TWO fields |
Order Replacement chains
- Old schema represented each replacement request as separate record in ORDERS table.
- New schema stores single record for entire order replacement chain (New MESSAGES table has much more acccurate storage of replacement chain history than was previously available).
| Old Field | Old Type | New Field(s) | New Type | Notes |
|---|---|---|---|---|
| REPLACES_FK | BIGINT (FK) | (Removed) | N/A | Replacement history now stored in MESSAGES table |
Order Routing
| Old Field | Old Type | New Field(s) | New Type | Notes |
|---|---|---|---|---|
| DESTINATION | VARCHAR(64) | DestinationId | VARCHAR(10) | Identifies direct recepient of each order |
| EXCHANGE | VARCHAR(32) | ExchangeId | VARCHAR(10) | Optionally identifies execution exchange |
- In the old schema order's DESTINATION field supported path-based routing. For example DESTINATION value like "ULLink.CME" specified that order should be routed to CME via ULLink trade connector.
- In the new schema DestinationId identifies direct recepient of each order (execution algorithm, matching engine instance, or trade connector)
Numeric Precision Changes
| Old Field | New Field | Type Change | Notes |
|---|---|---|---|
| QTY | Quantity | DOUBLE PRECISION → DECIMAL(38,12) | Higher precision |
| DISPLAY_QTY | DisplayQuantity | DOUBLE PRECISION → DECIMAL(38,12) | Higher precision |
| MIN_QTY | MinQuantity | DOUBLE PRECISION → DECIMAL(38,12) | Higher precision |
| LIMIT_PRICE | LimitPrice | DOUBLE PRECISION → DECIMAL(38,12) | Higher precision |
| STOP_PRICE | StopPrice | DOUBLE PRECISION → DECIMAL(38,12) | Higher precision |
| EXEC_AVG_PRICE | AveragePrice | DOUBLE PRECISION → DECIMAL(38,12) | Higher precision |
| TOTAL_EXEC_QTY | CumulativeQuantity | DOUBLE PRECISION → DECIMAL(38,12) | Higher precision |
Encoded to Human-Readable Format
These fields changed from encoded values (CHAR/SMALLINT) to readable strings:
| Old Field | Old Type | New Field | New Type | Example Transformation |
|---|---|---|---|---|
| INSTR_TYPE | CHAR(1) | InstrumentType | VARCHAR(64) | 'E' → 'EQUITY', 'F' → 'FUTURE' |
| SIDE | CHAR(1) | Side | VARCHAR(64) | 'B' → 'BUY', 'S' → 'SELL' |
| TIF | SMALLINT | TimeInForce | VARCHAR(64) | 0 → 'DAY', 1 → 'GTC' |
| ORDER_TYPE | CHAR(1) | OrderType | VARCHAR(64) | 'L' → 'LIMIT', 'M' → 'MARKET' |
| STATUS | CHAR(1) | OrderStatus | VARCHAR(64) | 'N' → 'NEW', 'F' → 'FILLED' |
| CURRENCY | SMALLINT | Currency | VARCHAR(10) | 840 → 'USD', 978 → 'EUR' |
ALPHANUMERIC(10)
The following fields in new schema use ALPHANUMERIC(10) format that allows for compact INT64 in memory representation:
| Old Field | Old Type | New Field(s) | New Type | Notes |
|---|---|---|---|---|
| CLIENT_ID | VARCHAR(32) | SourceId | VARCHAR(10) | System/client identifier |
| DESTINATION | VARCHAR(64) | DestinationId | VARCHAR(10) | Identifies direct recepient of each order |
| EXCHANGE | VARCHAR(32) | ExchangeId | VARCHAR(10) | Optionally identifies execution exchange |
| CURRENCY | SMALLINT | Currency | VARCHAR(10) | ISO currency code |
Custom order attributes
Both old and new schemas allowed annotating order records with custom set of key-value pairs.
- Old schema supported two types of custom attributes:
- free text key to value
- numeric FIX tag to value
- New schema only supports FIX tag to value set of custom attributes. We suggest mapping keys to FIX tags (FIX protocol offers large universe of standard tags) or utilizing UserData field
| Old Field | New Field | Type Change |
|---|---|---|
| CUSTOM_INFO | Attributes+UserData | MEDIUMTEXT (XML) → VARCHAR (JSON) |
Fields REMOVED from New Schema
These fields no longer exist as separate columns:
Execution Details
- EXEC_QTY - Last execution quantity (different from TOTAL_EXEC_QTY)
- TOTAL_EXEC_VALUE - Total executed value (use AveragePrice*CumulativeQuantity)
Boolean Flags (CHAR(1))
- IS_CANCEL - Cancellation request flag
- IS_CANCEL_REJECT - Cancel rejection flag
- IS_EXTERNAL - External order flag
- IS_MANUAL - Manual entry flag
- IS_MM - Market maker flag
- BYPASS_LIMITS - Risk limit bypass flag
Other Removed Fields
- CORRELATION_ORD_ID (BIGINT) - Correlated order reference (correlation ID is available in MESSAGES table, no need for it in ORDERS table where each record now represents entire replacement chain)
- CLIENT_ORD_ID (BIGINT) - Client order identifier (no longer need for it ember stores client-providced order ID as a part of main order identity)
- CANCEL_CAUSE (VARCHAR) - Cancellation reason text (Can be added on request)
- FLAGS (BIGINT) - Bit flags field (Can be added on request)
- QUOTE_ID (VARCHAR) - Related quote identifier (Can be added on request)
- DISCR_OFFSET (DOUBLE) - Discretionary price offset (rarely used, moved into custom order attributes, FIX tag 389)
- ALGO_ID (BIGINT) - Algorithm identifier (This is now replaced with Order Source/Destination)
Fields ADDED in New Schema
New fields not present in old schema:
Event Sourcing
- Term (BIGINT) - Journal term (epoch time when "current" journal was created)
- OpenSequence (BIGINT) - Opening sequence number
- CloseSequence (BIGINT) - Closing sequence number
Clearing & Settlement
- ClearingAccount (VARCHAR(256)) - Clearing account identifier
- Party (VARCHAR(32)) - Counterparty identifier
- ClearingBroker (VARCHAR(32)) - Clearing broker
Rejection Codes
- VendorRejectCode (INT) - Numeric External venue rejection code (execution venue )
- DeltixRejectCode (INT) - Internal system rejection code
Critical Migration Queries
Example: Query Transformation
Old Query:
SELECT ORDER_PK, SYMBOL, SIDE, QTY, STATUS, SUBMIT_TIME
FROM ORDERS
WHERE STATUS = 'F' -- Filled
AND SIDE = 'B' -- Buy
AND INSTR_TYPE = 'E' -- Equity
New Query:
SELECT OrderId, Symbol, Side, Quantity, OrderStatus, OpenTime
FROM ORDERS
WHERE OrderStatus = 'FILLED'
AND Side = 'BUY'
AND InstrumentType = 'EQUITY'
Parent-Child Relationships
Old Query:
SELECT child.ORDER_PK, parent.ORDER_PK
FROM ORDERS child
JOIN ORDERS parent ON child.PARENT_FK = parent.ORDER_PK
New Query:
SELECT child.OrderId, parent.OrderId
FROM ORDERS child
JOIN ORDERS parent
ON child.ParentOrderId = parent.OrderId
AND child.ParentSourceId = parent.SourceId
Data Type Compatibility Notes
- DOUBLE PRECISION → DECIMAL(38,12): Queries using mathematical operations may need casting
- TIMESTAMP → DATETIME: Check timezone handling in your application
- Encoded values → Strings: All WHERE clauses with encoded values must be updated
- BIGINT PKs → VARCHAR: Join conditions and indexes affected