Version: Upcoming

SpdrParentBrkrState

SpdrParentBrkrState records are created/published by SpiderRock Execution Engines. Each record describes the current state of one (or more) parent orders. If a parent order is canceled/replaced then entire chain is represented by a single broker state record.

METADATA

Attribute	Value
Topic	3985-parent-orders
MLink Token	ClientTrading
Product	SRTrade
accessType	SELECT

Table Definition

Field	Type	Key	Default Value	Comment
accnt	VARCHAR(16)	PRI, SEC	''
secKey_at	enum - AssetType	PRI	'None'
secKey_ts	enum - TickerSrc	PRI	'None'
secKey_tk	VARCHAR(12)	PRI	''
secKey_yr	SMALLINT UNSIGNED	PRI	0
secKey_mn	TINYINT UNSIGNED	PRI	0
secKey_dy	TINYINT UNSIGNED	PRI	0
secKey_xx	DOUBLE	PRI	0
secKey_cp	enum - CallPut	PRI	'Call'
secType	enum - SpdrKeyType	PRI	'None'
spdrSource	enum - SpdrSource	PRI	'None'
groupingCode	CHAR(19)	PRI	'0000-0000-0000-0000'
orderSide	enum - BuySell	PRI	'None'
clientFirm	VARCHAR(16)	PRI, SEC	''	SR client firm
userName	VARCHAR(24)	SEC	''	SR user name string
parentNumber	CHAR(19)		'0000-0000-0000-0000'	SR parent number
baseParentNumber	CHAR(19)		'0000-0000-0000-0000'
modifyNumber	CHAR(19)		'0000-0000-0000-0000'	most recent parent number affecting this order AddReplace or Modify
noticeNumber	CHAR(19)		'0000-0000-0000-0000'	SR noticeNumber required when parentOrderHandling BlockAuction BlockResponse
riskGroupId	CHAR(19)		'0000-0000-0000-0000'	all orders with the same riskGroupId share a common set of risk countersGrp risk limits apply to these shared counters
triggerGroupId	BIGINT		0
strategy	VARCHAR(36)		''	parent order strategy string can be user supplied
execBrkrCode	VARCHAR(16)		''	execBrkrCode attached to parent order if any
altOrderId	TINYTEXT		''	alternate order ID usually clOrdId from client
packageId	BIGINT		0	groups related orderbrokers together eg Legger wMLeg Parent
altAccnt	TINYTEXT		''	alternate client assigned long account string optional
altUserName	VARCHAR(24)		''	alternate client assigned user name optional
stageType	enum - SpdrStageType		'None'	parent is a staged order ToolVisible
isFlagged	enum - YesNo		'None'	broker state records is flagged for action user alert only
ticker_at	enum - AssetType		'None'	underlier ticker
ticker_ts	enum - TickerSrc		'None'	underlier ticker
ticker_tk	VARCHAR(12)		''	underlier ticker
uPrc	DOUBLE		0	underlier price usually midmarket
uPrcOrder	DOUBLE		0	underlier price at the time of last parent order arrival
nbboBid	DOUBLE		0	current option nbbo bid at time of record publish
nbboAsk	DOUBLE		0	current option nbbo ask at time of record publish
nbboBidSz	INT		0	current option nbbo cum bid size at time of record publish
nbboAskSz	INT		0	current option nbbo cum ask size at time of record publish
surfacePrc	FLOAT		0	SR surface price record publish
surfaceVol	FLOAT		0	SR surface volatility record publish
orderRefSDiv	FLOAT		0
orderRefSDivType	enum - RefSDivType		'None'
spdrBrokerStatus	enum - SpdrBrokerStatus		'None'
spdrOrderStatus	enum - SpdrOrderStatus		'PendNew'	status of the most recent parent order handled by this broker
spdrCloseReason	enum - SpdrCloseReason		'None'	close reason of most recent parent order
spdrRejectReason	enum - SpdrRejectReason		'None'	reject reason if any of most recent parent order
spdrRejectLevel	enum - SpdrRiskLevel		'None'	reject level if any of most recent parent order
isPermanentlyClosed	enum - YesNo		'None'	indicates that this broker is permanently closed cannot be cxlreplaced or reopened autohedge orders only
numExternRiskHold	INT		0	num external risk management rejects received
lastExternRiskText	TINYTEXT		''	last external exch risk management reject
lastExternRiskReject	DATETIME(6)		'1900-01-01 00:00:00.000000'
spdrComment	TINYTEXT		''
orderSize	INT		0	order size of most recent parent order
orderActiveSize	INT		0	total activated size total size released for execution 1 all available size
activeDuration	INT		0	optional number of seconds
updateDttm	DATETIME(6)		'1900-01-01 00:00:00.000000'
goodTillDttm	DATETIME(6)		'1900-01-01 00:00:00.000000'	order expiration datetime most recent parent order in cxlreplace chain
leavesQuantity	INT		0	current child order leaves quantity quantity actively working in the marketall child orders
avgFillPrice	DOUBLE		0	average fill price
avgFillUPrice	DOUBLE		0	average fill underlier price
cumFillQuantity	INT		0	cumulative fill quantity
avgFillVol	FLOAT		0	average fill volatility
cumFillVega	FLOAT		0	cumulative fill vega
cumFillTheta	FLOAT		0	cumulative fill theta
cumFillDDelta	FLOAT		0	cumulative fill Delta
cumExchFee	FLOAT		0	cumulative fill exch fee
cumM10Pnl	FLOAT		0
cumArrivalPnl	FLOAT		0
lastFillDttm	DATETIME(6)		'1900-01-01 00:00:00.000000'
riskLimitSize	INT		0	risk limit size maximum size to nearest risk limit
riskLimitDesc	enum - SpdrRisk		'None'	reason for size reduction
riskLimitLevel	enum - SpdrRiskLevel		'None'	risk limit level responsible for reduction
riskLimitDetail	TINYTEXT		''	risk limit detail
limitPrice	DOUBLE		0	simple limit
limitRefUPrc	DOUBLE		0
limitErr	enum - LimitError		'None'
limitErrDesc	TINYTEXT		''
bestLimitOffset	FLOAT		0	possible price improvement gap between best price and initlimit price FaceMatrix orders
makeProbLimit	FLOAT		0
makeSurfOffset	FLOAT		0
makeLimitPrice	DOUBLE		0	make limit
makeLimitErr	enum - LimitError		'None'
takeProbLimit	FLOAT		0
takeSurfOffset	FLOAT		0
takeLimitPrice	DOUBLE		0	take limit
takeLimitErr	enum - LimitError		'None'
autoHedge	enum - AutoHedge		'None'
hedgeInstrument	enum - HedgeInst		'None'	autohedge instrument from parent order
hedgeSecKey_at	enum - AssetType		'None'	autohedge sec key if any
hedgeSecKey_ts	enum - TickerSrc		'None'	autohedge sec key if any
hedgeSecKey_tk	VARCHAR(12)		''	autohedge sec key if any
hedgeSecKey_yr	SMALLINT UNSIGNED		0	autohedge sec key if any
hedgeSecKey_mn	TINYINT UNSIGNED		0	autohedge sec key if any
hedgeSecKey_dy	TINYINT UNSIGNED		0	autohedge sec key if any
hedgeSecType	enum - SpdrKeyType		'None'	autohedge sec type Stock or Future
hedgeBetaRatio	FLOAT		0	autohedge ratio from parent order if supplied or computed by SR
hedgeScope	enum - HedgeScope		'None'	hedge group scope RiskGroup or Accnt
hedgeSession	enum - MarketSession		'None'	market session for the autohedge order
externHedgeExDest	VARCHAR(16)		''	external broker exDest only used if orderHandlingExtern Should match FixRoutingTabledestination type eg Nighthawk
externHedgeParams	TINYTEXT		''	external algo namesparameters usually just an algo name usually copied from the FixRoutingTableexternParams
userData1	TINYTEXT		''	client supplied data field passes through to parent and child executions and reports as well as FIX drops
userData2	TINYTEXT		''	client supplied data field passes through to parent and child executions and reports as well as FIX drops
maxProgress	enum - MaxProgress		'None'	maximum point of progress for most recent parent order
maxProgressDetail	TINYTEXT		''	additional detail on point of maximum progress
maxProgressTime	DATETIME(6)		'1900-01-01 00:00:00.000000'	time of last max progress advance
brokerState	enum - BrokerState		'None'	broker monitor state
brokerCounter	INT		0	number of times order checked by broker monitor note most checks result in no action
makeState	enum - AlgoState		'None'	enum current state of nonmarketable order handler maker
makeCounter	INT		0	number of times order checked by post handler note most checks result in no action
takeState	enum - AlgoState		'None'	enum current state of marketable order handler taker
takeCounter	INT		0	number of times order checked by take handler note most checks result in no action
mktRespState	enum - MktRespState		'None'	enum current state of response handler if any
mktRespCounter	INT		0	number of times order checked by market response handler note most checks result in no action
mktCrossState	enum - MktCrossState		'None'	enum current state of mkt cross handler if any
mktCrossCounter	INT		0	number of times order checked by market cross handler note most checks result in no action
progressRuleDetail	TINYTEXT		''	TwapVwap progress detail
lastChildEvent	enum - ChildEvent		'None'	enum last event code from brokerchild order handlers
lastChildRejectDttm	DATETIME(6)		'1900-01-01 00:00:00.000000'	last child order reject dttm if any
lastChildRejectText	TINYTEXT		''	last child order reject reasontext if any
spdrMktState	enum - SpdrMarketState		'None'	enum market state
cpx1	FLOAT		0	child price level 1
csz1	INT		0	cumulative size represented at this price
cex1	INT UNSIGNED		0	bit mask of all exchanges where we are at this price
cnt1	TINYINT UNSIGNED		0	number of child orders at price level 1
cpx2	FLOAT		0	child price level 2
csz2	INT		0	cumulative size represented at this price
cex2	INT UNSIGNED		0	bit mask of all exchanges where we are at this price
cnt2	TINYINT UNSIGNED		0	number of child orders at price level 2
cMore	TINYINT UNSIGNED		0	number of additional child orders at inferior prices if any
expiryKey	VARCHAR(6)		''
cnVol	FLOAT		0
cnYrs	FLOAT		0
cnUPrc	FLOAT		0
cnDe	FLOAT		0	delta per contract
cnDd	FLOAT		0	delta per contract
cnVe	FLOAT		0	vega per contract
cnGa	FLOAT		0
cnTh	FLOAT		0
cnPr	FLOAT		0	premium per contract
ddMult	FLOAT		0
priceFormat	enum - PriceFormat		'None'	option price format code
uPriceFormat	enum - PriceFormat		'None'	underlier price format code
underliersPerCn	INT		0	option delivery underliers per contract
underlierType	enum - UnderlierType		'None'	type of underlier affects greek calculations
tickValue	FLOAT		0	NLV value of a single tick change in display premium pointValue tickValue tickSize
pointValue	FLOAT		0	NLV value of a single point change in display premium pointValue tickValue tickSize
pointCurrency	enum - Currency		'None'
theoVol	FLOAT		0
theoPrc	FLOAT		0
numNewOrders	INT		0	number of child orders generated by this broker all parent orders
numParentLimits	SMALLINT UNSIGNED		0	number of parent limit messages received for this broker
brokerVwapMark	FLOAT		0	trade weighted average price duration of parent order
brokerVwapUMark	FLOAT		0	optiontrade weighted average underlier price duration of parent order
brokerQwapMark	FLOAT		0	average quote midmarket duration of parent order
brokerQwapUMark	FLOAT		0	average underlier quote midmarket duration of parent order
vwapPnL	FLOAT		0	dir qty brokerVwapMark avgFillPrice
vwapDnPnL	FLOAT		0	vwapPnL uDriftVwapPnL
uDriftVwapPnL	FLOAT		0	dir de qty brokerVwapUMark avgFillUPrice
qwapPnL	FLOAT		0	dir qty brokerQwapMark avgFillPrice
qwapDnPnL	FLOAT		0	qwapPnL uDriftQwapPnL
uDriftQwapPnL	FLOAT		0	dir de qty brokerQwapUMark avgFillUPrice
numImprvChld	INT		0	number of child orders that improved an existing market
activeSeconds	FLOAT		0	number of seconds that the parent order was active
withSeconds	FLOAT		0	number of seconds with one or more working child orders that are with of nbbo
aloneSeconds	FLOAT		0	number of seconds with one or more working child orders that make up 100 of nbbo
workingSeconds	FLOAT		0	number of child order seconds can be greater than activeSeconds if working on more than one exchange
numRiskCancels	INT		0	number of child orders cancels for risk reasons
riskHoldSeconds	FLOAT		0	number of seconds with parent order active but on risk hold zero child order risk size
riskThrtlSeconds	FLOAT		0	number of seconds with parent order active but in risk throttle condition temporary hold after fastmass risk cancel
numMakeThrottleSkips	INT		0	number of childs orders not send to market dues to outbound lines being in a queuedthrottled state
sumMakeThrottleDelay	FLOAT		0	cumulative seconds of queue skip delay seconds that a child order was not in the market dues to outbound lines being in a queuedthrottled state
uPrcDrift	FLOAT		0	underlier drift since parent order start
sVolDrift	FLOAT		0	surface vol drift since parent order start
updateSrc	enum - UpdateSource		'None'
numUpdates	INT		0	number of record updates cumulative for the day
timestamp	DATETIME(6)		'1900-01-01 00:00:00.000000'	SR system timestamp

PRIMARY KEY DEFINITION (Unique)

Field	Sequence
accnt	1
secKey_tk	2
secKey_yr	3
secKey_mn	4
secKey_dy	5
secKey_xx	6
secKey_cp	7
secKey_at	8
secKey_ts	9
secType	10
spdrSource	11
groupingCode	12
orderSide	13
clientFirm	14

SECONDARY INDEX (AccntIndex) (Not Unique)

Field	Sequence
accnt	1

SECONDARY INDEX (ClientFirmIndex) (Not Unique)

Field	Sequence
clientFirm	1

SECONDARY INDEX (UserNameIndex) (Not Unique)

Field	Sequence
userName	1

CREATE TABLE EXAMPLE QUERY

CREATE TABLE `SRTrade`.`MsgSRParentBrkrState` (
    `accnt` VARCHAR(16) NOT NULL DEFAULT '',
    `secKey_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None',
    `secKey_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None',
    `secKey_tk` VARCHAR(12) NOT NULL DEFAULT '',
    `secKey_yr` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    `secKey_mn` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `secKey_dy` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `secKey_xx` DOUBLE NOT NULL DEFAULT 0,
    `secKey_cp` ENUM('Call','Put','Pair') NOT NULL DEFAULT 'Call',
    `secType` ENUM('None','Stock','Future','Option','MLeg') NOT NULL DEFAULT 'None',
    `spdrSource` ENUM('None','SpdrTicket','SpdrSingle','SRSE','FIX','HedgeTool','TradeHedge','OpenHedge','AutoHedge','Orphan','RiskManager','OrderManager','ManagedOrder','RFQRespSrvr','Legger','SRSEDrop','FixDrop','TicketDrop','SysTest','RFRResponse','AllocOmni','AllocClient','CertGateway','MLegResponse','LeggerX','DropManager','AutoHedgeSrvr','AuctionStrategySrvr','AllocBlockFace','AllocBlockCust','IceChatGateway','EXS2SRC','MLinkResponse','AutoResponderVD','AutoResponderRC','AutoResponderSN','AutoResponderBX','MLink') NOT NULL DEFAULT 'None',
    `groupingCode` CHAR(19) NOT NULL DEFAULT '0000-0000-0000-0000',
    `orderSide` ENUM('None','Buy','Sell') NOT NULL DEFAULT 'None',
    `clientFirm` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'SR client firm',
    `userName` VARCHAR(24) NOT NULL DEFAULT '' COMMENT 'SR user name string',
    `parentNumber` CHAR(19) NOT NULL DEFAULT '0000-0000-0000-0000' COMMENT 'SR parent number',
    `baseParentNumber` CHAR(19) NOT NULL DEFAULT '0000-0000-0000-0000',
    `modifyNumber` CHAR(19) NOT NULL DEFAULT '0000-0000-0000-0000' COMMENT 'most recent parent number affecting this order (AddReplace or Modify)',
    `noticeNumber` CHAR(19) NOT NULL DEFAULT '0000-0000-0000-0000' COMMENT 'SR noticeNumber [required when parentOrderHandling = BlockAuction | BlockResponse]',
    `riskGroupId` CHAR(19) NOT NULL DEFAULT '0000-0000-0000-0000' COMMENT 'all orders with the same riskGroupId share a common set of risk counters;.Grp. risk limits apply to these shared counters',
    `triggerGroupId` BIGINT NOT NULL DEFAULT 0,
    `strategy` VARCHAR(36) NOT NULL DEFAULT '' COMMENT 'parent order strategy string (can be user supplied)',
    `execBrkrCode` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'execBrkrCode attached to parent order (if any)',
    `altOrderId` TINYTEXT NOT NULL DEFAULT '' COMMENT 'alternate order ID (usually clOrdId from client)',
    `packageId` BIGINT NOT NULL DEFAULT 0 COMMENT 'groups related order/brokers together (eg. Legger w/MLeg Parent)',
    `altAccnt` TINYTEXT NOT NULL DEFAULT '' COMMENT 'alternate (client assigned) "long" account string (optional)',
    `altUserName` VARCHAR(24) NOT NULL DEFAULT '' COMMENT 'alternate (client assigned) user name (optional)',
    `stageType` ENUM('None','ModifyAny','ModifyAlgo') NOT NULL DEFAULT 'None' COMMENT 'parent is a staged order [ToolVisible]',
    `isFlagged` ENUM('None','Yes','No') NOT NULL DEFAULT 'None' COMMENT 'broker state records is flagged for action (user alert only)',
    `ticker_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT 'underlier ticker',
    `ticker_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None' COMMENT 'underlier ticker',
    `ticker_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'underlier ticker',
    `uPrc` DOUBLE NOT NULL DEFAULT 0 COMMENT 'underlier price (usually mid-market)',
    `uPrcOrder` DOUBLE NOT NULL DEFAULT 0 COMMENT 'underlier price at the time of last parent order arrival',
    `nbboBid` DOUBLE NOT NULL DEFAULT 0 COMMENT 'current option nbbo bid (at time of record publish)',
    `nbboAsk` DOUBLE NOT NULL DEFAULT 0 COMMENT 'current option nbbo ask (at time of record publish)',
    `nbboBidSz` INT NOT NULL DEFAULT 0 COMMENT 'current option nbbo cum bid size (at time of record publish)',
    `nbboAskSz` INT NOT NULL DEFAULT 0 COMMENT 'current option nbbo cum ask size (at time of record publish)',
    `surfacePrc` FLOAT NOT NULL DEFAULT 0 COMMENT 'SR surface price @ record publish',
    `surfaceVol` FLOAT NOT NULL DEFAULT 0 COMMENT 'SR surface volatility @ record publish',
    `orderRefSDiv` FLOAT NOT NULL DEFAULT 0,
    `orderRefSDivType` ENUM('None','Live','Locked') NOT NULL DEFAULT 'None',
    `spdrBrokerStatus` ENUM('None','Updating','Active','Closing','Closed','Rejected','ActiveHold') NOT NULL DEFAULT 'None',
    `spdrOrderStatus` ENUM('PendNew','New','PendClose','Closed','Rejected','SendReject') NOT NULL DEFAULT 'PendNew' COMMENT 'status of the most recent parent order handled by this broker',
    `spdrCloseReason` ENUM('None','Cancelled','Filled','Replaced','Expired','Limit','System','LegReject','DoneForDay','IOCExpire','UserCxl','NoProgress','TooManyRej','ReplReject','AlgoClose','Restart','InvldParentLimit','FilledRepl','ForceClose','DmaReject','DmaExpire','DmaBrkrCxl','ReviewReject','MarketState','AlgoReject','ReviewTimeout','ChildReject','ChildCancel','ReviewClose','UPrcRange','LegBrkrClosed','ExchRisk','CrossFailed') NOT NULL DEFAULT 'None' COMMENT 'close reason of most recent parent order',
    `spdrRejectReason` ENUM('None','AccntHold','UnknwnOpt','UnknwnStk','Expired','BadSize','BadOrdNum','DupOrdNum','BadVolPx','BadLmtType','BadMktPrc','BadLimit','SysReject','TestOrder','CustType','NoFirmPos','BadCent','NoSurface','UnknwnAcc','UnknwnRoot','OrdSpacing','BadOptMkt','BadStkMkt','SendFailed','BadExpose','BadPrem','NoOptLeg','Min2Leg','Max6Leg','BadStkSz','BadRatio','BadLegID','DupLegID','MixedRoots','OffMkt','CentVol','CentLeg','CentRng','CentSAtm','CentSpln','CentErr','NoRiskGrp','BadDDivs','NoPricer','CalcErr','TwapLate','TwapTime','NoAcctMap','UserUnk','UserHold','GTCHold','SysException','StkCrossed','BadOrdType','BadSSaleFlag','NoBorrow','NoFacilitateAccnt','UnknwnFut','MktNotOpen','NoRoutes','NoLocate','SSaleRstr','NoLegs','BadSide','BadLegKeyType','UnknwnRefStk','BadRefMktPrc','UnknwnRefFut','UnknwnRefAtm','BadRefVe','BadProdDef','StkOddLot','RiskException','MaxParticipation','MarExemptHold','NoMarRec','NullCounter','StkQty','FutQty','OptQty','StkDDelta','FutDDelta','OptDDelta','StkClsOnly','StkDisabled','FutClsOnly','FutDisabled','OptClsOnly','OptDisabled','OrderMargin','AccMargin','DayMargin','OpenExpose','DayLnDDelta','DayShDDelta','DayAbsDDelta','DayLnWtVega','DayShWtVega','DayAbsWtVega','DayLnNValue','DayShNValue','DayAbsNValue','AccFrozen','ConfigErr','NoFMFuture','NoLinkage','NoProdDef','BadRiskID','InvldAutoH','LegChange','CxlSpacing','BadHedgeInst','TooLateToReduce','PendCxlRepl','InvldCxlRepl','InvldCxl','DmaReject','NoUPrc','TwapSteps','UnknwnFirm','NoSSaleFlg','AccntNotMM','BadSource','VwapLate','AlreadyFilled','UnknownSecKey','InvldGTD','CutoffTime','ParentShape','NoFaceSide','NoFaceLegs','AuctionSize','InvldClFirm','BadLegBrkr','NoLegBrkr','StkLegNotAllowed','BadLeadSide','UnknwnRefUnd','ExecEngineType','NoExchAvail','SelfTrade','PointValueErr','StkCollar','FutCollar','OptCollar','LmtCollar','RefUPrcErr','InvldUser','InvldAccnt','UnknownCrossId','WidUMkt','UMktCls','UPrcRange','RiskCollarRng') NOT NULL DEFAULT 'None' COMMENT 'reject reason (if any) of most recent parent order',
    `spdrRejectLevel` ENUM('None','MarExsOnSponsLvl','MarExsOnSponsTkLvl','MarExsOnCoreLvl','MarExsOnCoreTkLvl','MarSponsOnCoreLvl','MarSponsOnCoreTkLvl','MarCoreOnCoreLvl','MarCoreOnCoreTkLvl','MarCoreOnSubLvl','MarCoreOnSubTkLvl','MarCoreOnAccntLvl','MarCoreOnAccntTkLvl','MarSubOnAccntLvl','MarSubOnAccntTkLvl','MarCoreOnCustomLvl','MarCoreOnCustomTkLvl','MarSubOnCustomLvl','MarSubOnCustomTkLvl','UserLvl','UserTkLvl','AccntLvl','AccntTkLvl','CFirmLvl','CFirmTkLvl','RiskGroup','HardLvl','SoftLvl','GblTkLvl') NOT NULL DEFAULT 'None' COMMENT 'reject level (if any) of most recent parent order',
    `isPermanentlyClosed` ENUM('None','Yes','No') NOT NULL DEFAULT 'None' COMMENT 'indicates that this broker is permanently closed (cannot be cxl/replaced or re-opened) [auto-hedge orders only]',
    `numExternRiskHold` INT NOT NULL DEFAULT 0 COMMENT 'num external risk management rejects received',
    `lastExternRiskText` TINYTEXT NOT NULL DEFAULT '' COMMENT 'last external (exch?) risk management reject',
    `lastExternRiskReject` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000',
    `spdrComment` TINYTEXT NOT NULL DEFAULT '',
    `orderSize` INT NOT NULL DEFAULT 0 COMMENT 'order size of most recent parent order',
    `orderActiveSize` INT NOT NULL DEFAULT 0 COMMENT 'total activated size (total size released for execution) (-1 = all available size)',
    `activeDuration` INT NOT NULL DEFAULT 0 COMMENT '[optional] (number of seconds)',
    `updateDttm` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000',
    `goodTillDttm` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'order expiration date/time (most recent parent order in cxl/replace chain)',
    `leavesQuantity` INT NOT NULL DEFAULT 0 COMMENT 'current child order leaves quantity (quantity actively working in the market;all child orders)',
    `avgFillPrice` DOUBLE NOT NULL DEFAULT 0 COMMENT 'average fill price',
    `avgFillUPrice` DOUBLE NOT NULL DEFAULT 0 COMMENT 'average fill underlier price',
    `cumFillQuantity` INT NOT NULL DEFAULT 0 COMMENT 'cumulative fill quantity',
    `avgFillVol` FLOAT NOT NULL DEFAULT 0 COMMENT 'average fill volatility',
    `cumFillVega` FLOAT NOT NULL DEFAULT 0 COMMENT 'cumulative fill vega',
    `cumFillTheta` FLOAT NOT NULL DEFAULT 0 COMMENT 'cumulative fill theta',
    `cumFillDDelta` FLOAT NOT NULL DEFAULT 0 COMMENT 'cumulative fill $Delta',
    `cumExchFee` FLOAT NOT NULL DEFAULT 0 COMMENT 'cumulative fill exch fee',
    `cumM10Pnl` FLOAT NOT NULL DEFAULT 0,
    `cumArrivalPnl` FLOAT NOT NULL DEFAULT 0,
    `lastFillDttm` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000',
    `riskLimitSize` INT NOT NULL DEFAULT 0 COMMENT 'risk limit size (maximum size to nearest risk limit)',
    `riskLimitDesc` ENUM('None','NullCounter','StkClsOnly','StkDisabled','FutClsOnly','FutDisabled','OptClsOnly','OptDisabled','OrderMargin','AccMargin','DayMargin','OpenExpose','DayLnDDelta','DayShDDelta','DayAbsDDelta','DayLnWtVe','DayShWtVe','DayAbsWtVe','DayLnNValue','DayShNValue','DayAbsNValue','StkImpact','FutImpact','OptImpact','NullMRC','NullSRC','DDeltaLn','DDeltaSh','DDeltaAbs','WtVegaLn','WtVegaSh','WtVegaAbs','NValueLn','NValueSh','NValueAbs','CtrlDisable','AccEmaDdLn','AccEmaDdSh','AccEmaWvLn','AccEmaWvSh','SymEmaDdLn','SymEmaDdSh','SymEmaWvLn','SymEmaWvSh','ExpDDeltaLn','ExpDDeltaSh','ExpWtVegaLn','ExpWtVegaSh','ExpRm6Ln','ExpRm6Sh','SymDDeltaLn','SymDDeltaSh','SymVegaLn','SymVegaSh','SymWtVegaLn','SymWtVegaSh','SymRm7Ln','SymRm7Sh','GrpDdLn','GrpDdSh','GrpOptQtyLn','GrpOptQtySh','GrpOptQtyAbs','GrpVegaLn','GrpVegaSh','GrpVegaAbs','GrpRm1Ln','GrpRm1Sh','GrpRm1Abs','GrpRm2Ln','GrpRm2Sh','GrpRm3Ln','GrpRm3Sh','GrpRm4Ln','GrpRm4Sh','GrpRm5Ln','GrpRm5Sh','UDn50','UUp50','UDn15','UUp15','OrderSize','SysErr','StkErr','FutErr','OptErr','SSaleErr','SSaleRstr','StkSellLmt','Locate','StkRstct','SizeCap','ZeroCross','PreOpnQ','TrgWait','WaitStart','WaitTrigger','LegStep','TwapStep','MktNotReady','MaxRisk','NoStkBrw','BadMkt','BadLmt','NoAltOrdId','InvldBrkr','MaxChildOrders','MktHalted','UPrcErr','MinUBid','MaxUAsk','PreStart','ClntRteDn','ActiveSize','MktClosed','NoDDeltaMult','InvldDelta','RiskCollarRng','DayFutCnBot','DayFutCnSld','DayFutCnAbs','AccFutCnAbs') NOT NULL DEFAULT 'None' COMMENT 'reason for size reduction',
    `riskLimitLevel` ENUM('None','MarExsOnSponsLvl','MarExsOnSponsTkLvl','MarExsOnCoreLvl','MarExsOnCoreTkLvl','MarSponsOnCoreLvl','MarSponsOnCoreTkLvl','MarCoreOnCoreLvl','MarCoreOnCoreTkLvl','MarCoreOnSubLvl','MarCoreOnSubTkLvl','MarCoreOnAccntLvl','MarCoreOnAccntTkLvl','MarSubOnAccntLvl','MarSubOnAccntTkLvl','MarCoreOnCustomLvl','MarCoreOnCustomTkLvl','MarSubOnCustomLvl','MarSubOnCustomTkLvl','UserLvl','UserTkLvl','AccntLvl','AccntTkLvl','CFirmLvl','CFirmTkLvl','RiskGroup','HardLvl','SoftLvl','GblTkLvl') NOT NULL DEFAULT 'None' COMMENT 'risk limit level responsible for reduction',
    `riskLimitDetail` TINYTEXT NOT NULL DEFAULT '' COMMENT 'risk limit detail',
    `limitPrice` DOUBLE NOT NULL DEFAULT 0 COMMENT 'simple limit',
    `limitRefUPrc` DOUBLE NOT NULL DEFAULT 0,
    `limitErr` ENUM('None','LmtPrcRng','NoStkNbbo','LowStkNbbo','WideStkNbbo','CrossStkNbbo','VolCalcErr','RefUPrcErr','RefDeltaRng','dUPrcRng','BadLmtType','LmtPrcErr','BadCent','CentVolLmt','BadSurf','CentPrcLmt','NoOptNbbo','WideOptNbbo','dYrsRng','BadRefDelta','BadStkNbbo','InvldMkt','BadVolLmt','CrossOptNbbo','NoFutNbbo','LowFutNbbo','WideFutNbbo','CrossFutNbbo','InvldRefMkt','TiedRtnRng','InvldAdjType','ProdDefErr','PrcBandViol','SysErr','NoQteProb','ProbRng','LmtClass','NoAlphaLvl','SurfErr','ProbErr','BadMult','NoUPrc','BadAlphaLvl','LegLimitErr','AuxLimit','NoFutMktSz','FutMktNotOpn','StaleProb','NoStkObj','NoFutObj','NoOptObj','dOpxGaRng','FadeProb','SurfNoTrd','SurfAge','SurfMktSess','SurfVolRng','SurfPrc','NoSurface','SurfLowCCnt','SurfLowPCnt','SurfFitPrcErr','SurfBidAskMiss','SurfLowCounter','SurfDefaultSkew','SurfSessionMiss','SurfBaseErr','SurfSwitchDelay','SurfWideMktV','SurfWideMktP','SurfUWidthEma','SurfCCntEma','SurfPCntEma','SurfVWidthEma','SurfPWidthEma','SurfWideUMkt','LowOptPrc','LegURng','WideMarket','dOpxThRng','NA','InvldMktWidth','Except','BadProbRecord','InterpErr','SurfImprErr','JoinImprErr','JoinItrpErr','JoinFadeErr','NoLimit') NOT NULL DEFAULT 'None',
    `limitErrDesc` TINYTEXT NOT NULL DEFAULT '',
    `bestLimitOffset` FLOAT NOT NULL DEFAULT 0 COMMENT '(possible price improvement) (gap between best price and init/limit price) (Face/Matrix orders)',
    `makeProbLimit` FLOAT NOT NULL DEFAULT 0,
    `makeSurfOffset` FLOAT NOT NULL DEFAULT 0,
    `makeLimitPrice` DOUBLE NOT NULL DEFAULT 0 COMMENT 'make limit',
    `makeLimitErr` ENUM('None','LmtPrcRng','NoStkNbbo','LowStkNbbo','WideStkNbbo','CrossStkNbbo','VolCalcErr','RefUPrcErr','RefDeltaRng','dUPrcRng','BadLmtType','LmtPrcErr','BadCent','CentVolLmt','BadSurf','CentPrcLmt','NoOptNbbo','WideOptNbbo','dYrsRng','BadRefDelta','BadStkNbbo','InvldMkt','BadVolLmt','CrossOptNbbo','NoFutNbbo','LowFutNbbo','WideFutNbbo','CrossFutNbbo','InvldRefMkt','TiedRtnRng','InvldAdjType','ProdDefErr','PrcBandViol','SysErr','NoQteProb','ProbRng','LmtClass','NoAlphaLvl','SurfErr','ProbErr','BadMult','NoUPrc','BadAlphaLvl','LegLimitErr','AuxLimit','NoFutMktSz','FutMktNotOpn','StaleProb','NoStkObj','NoFutObj','NoOptObj','dOpxGaRng','FadeProb','SurfNoTrd','SurfAge','SurfMktSess','SurfVolRng','SurfPrc','NoSurface','SurfLowCCnt','SurfLowPCnt','SurfFitPrcErr','SurfBidAskMiss','SurfLowCounter','SurfDefaultSkew','SurfSessionMiss','SurfBaseErr','SurfSwitchDelay','SurfWideMktV','SurfWideMktP','SurfUWidthEma','SurfCCntEma','SurfPCntEma','SurfVWidthEma','SurfPWidthEma','SurfWideUMkt','LowOptPrc','LegURng','WideMarket','dOpxThRng','NA','InvldMktWidth','Except','BadProbRecord','InterpErr','SurfImprErr','JoinImprErr','JoinItrpErr','JoinFadeErr','NoLimit') NOT NULL DEFAULT 'None',
    `takeProbLimit` FLOAT NOT NULL DEFAULT 0,
    `takeSurfOffset` FLOAT NOT NULL DEFAULT 0,
    `takeLimitPrice` DOUBLE NOT NULL DEFAULT 0 COMMENT 'take limit',
    `takeLimitErr` ENUM('None','LmtPrcRng','NoStkNbbo','LowStkNbbo','WideStkNbbo','CrossStkNbbo','VolCalcErr','RefUPrcErr','RefDeltaRng','dUPrcRng','BadLmtType','LmtPrcErr','BadCent','CentVolLmt','BadSurf','CentPrcLmt','NoOptNbbo','WideOptNbbo','dYrsRng','BadRefDelta','BadStkNbbo','InvldMkt','BadVolLmt','CrossOptNbbo','NoFutNbbo','LowFutNbbo','WideFutNbbo','CrossFutNbbo','InvldRefMkt','TiedRtnRng','InvldAdjType','ProdDefErr','PrcBandViol','SysErr','NoQteProb','ProbRng','LmtClass','NoAlphaLvl','SurfErr','ProbErr','BadMult','NoUPrc','BadAlphaLvl','LegLimitErr','AuxLimit','NoFutMktSz','FutMktNotOpn','StaleProb','NoStkObj','NoFutObj','NoOptObj','dOpxGaRng','FadeProb','SurfNoTrd','SurfAge','SurfMktSess','SurfVolRng','SurfPrc','NoSurface','SurfLowCCnt','SurfLowPCnt','SurfFitPrcErr','SurfBidAskMiss','SurfLowCounter','SurfDefaultSkew','SurfSessionMiss','SurfBaseErr','SurfSwitchDelay','SurfWideMktV','SurfWideMktP','SurfUWidthEma','SurfCCntEma','SurfPCntEma','SurfVWidthEma','SurfPWidthEma','SurfWideUMkt','LowOptPrc','LegURng','WideMarket','dOpxThRng','NA','InvldMktWidth','Except','BadProbRecord','InterpErr','SurfImprErr','JoinImprErr','JoinItrpErr','JoinFadeErr','NoLimit') NOT NULL DEFAULT 'None',
    `autoHedge` ENUM('None','Static','AutoMid','AutoCrx','AutoTrn','SpdrAuto','Spdr10S','Spdr30S','Spdr90S','Spdr5M','Spdr30M','SpdrDay','SmartFast','SmartNorm','FastCrx','FastDark','SlowDark','AlphaVwap1pct','AlphaVwap2pct','AlphaVwap5pct','AlphaVwap25pct','Custom','AwayAlgo') NOT NULL DEFAULT 'None',
    `hedgeInstrument` ENUM('None','Default','FrontMonth','Stock','Future') NOT NULL DEFAULT 'None' COMMENT 'auto-hedge instrument (from parent order)',
    `hedgeSecKey_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT 'auto-hedge sec key (if any)',
    `hedgeSecKey_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None' COMMENT 'auto-hedge sec key (if any)',
    `hedgeSecKey_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'auto-hedge sec key (if any)',
    `hedgeSecKey_yr` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'auto-hedge sec key (if any)',
    `hedgeSecKey_mn` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'auto-hedge sec key (if any)',
    `hedgeSecKey_dy` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'auto-hedge sec key (if any)',
    `hedgeSecType` ENUM('None','Stock','Future','Option','MLeg') NOT NULL DEFAULT 'None' COMMENT 'auto-hedge sec type (Stock or Future)',
    `hedgeBetaRatio` FLOAT NOT NULL DEFAULT 0 COMMENT 'auto-hedge ratio [from parent order (if supplied) or computed by SR]',
    `hedgeScope` ENUM('None','Accnt','RiskGroup') NOT NULL DEFAULT 'None' COMMENT 'hedge group scope [RiskGroup or Accnt]',
    `hedgeSession` ENUM('None','PreMkt','RegMkt','PostMkt','PreRegMkt','RegPostMkt','AllDay') NOT NULL DEFAULT 'None' COMMENT 'market session for the autohedge order',
    `externHedgeExDest` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'external broker exDest (only used if orderHandling=Extern)			# Should match FixRoutingTable.destination type (eg. #Nighthawk)',
    `externHedgeParams` TINYTEXT NOT NULL DEFAULT '' COMMENT 'external algo names/parameters (usually just an algo name)			# usually copied from the FixRoutingTable.externParams',
    `userData1` TINYTEXT NOT NULL DEFAULT '' COMMENT 'client supplied data field; passes through to parent and child executions and reports as well as FIX drops',
    `userData2` TINYTEXT NOT NULL DEFAULT '' COMMENT 'client supplied data field; passes through to parent and child executions and reports as well as FIX drops',
    `maxProgress` ENUM('None','Initialize','Rejected','PreOpen','RiskHold','Working','ChkRte','RteRej','WaitAction','InMarket','Filling','Filled','PendingClose','Closed','HedgeHold') NOT NULL DEFAULT 'None' COMMENT 'maximum point of progress for most recent parent order',
    `maxProgressDetail` TINYTEXT NOT NULL DEFAULT '' COMMENT 'additional detail on point of maximum progress',
    `maxProgressTime` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'time of last max progress advance',
    `brokerState` ENUM('None','Start','MarNotEnabled','CloseRequest','WaitTrigger','ChkBrkrFail','NotValid','StepChk','ResetOff','ResetOn','ResetActive','TwapChkWait','TwapChk','VwapChk','VwapVlmFail','CleanupBrkr','AlgoNormal','StkClosed','StkHalted','StkNotOpn','FutClosed','FutHalted','FutNotOpn','TryCxlChildrenCont','BrkrPendNew','BrkrPendClose','BrkrPreOpnQ','BrkrPendRpl','BrkrWaitTrig','TryCxlChildrenTerm','PendChildCxl','Done','BrkrClosing','BrkrClosed','BrkrRejected','MktHalted','LimitErr','RiskHold','SecTypeErr','MktDataErr','LmtErr','StkNotRdy','MinUPrcLmt','MaxUPrcLmt','InvldSysEnv','InvldRunStatus','InvldEngineType','MarketClosed','GoodTillDttmReached','TestExpired','AutoHedgeExpired','EngineClosed','EngPreOpn','OutsideMktDay','OutsideMktDay2','MaxChildRejects','IOCExpired','ProgressTimeout','NoStreetFixSession','StreetFixNotLoggedIn','StreetFixTimeout','HedgeHold','NewParent','AddUpdate','ClearAction','UpdateLegLimit','Exception','AlgoWorking','ReviewReject','ReviewPending','ReviewTimeout','PendTrigger','PendReview','PendCxlRpl','Suspended','LeggerWait','NoStkMkt','CkIncrProgress','TriggerHold','SwitchMOC','HoldMOC','VwapStepWait','MaxChildOrders','ActiveHold') NOT NULL DEFAULT 'None' COMMENT 'broker monitor state',
    `brokerCounter` INT NOT NULL DEFAULT 0 COMMENT 'number of times order checked by broker monitor (note: most checks result in no action)]',
    `makeState` ENUM('None','Posting','PendPost','OffMkt','BadFirmType','NoExchSet','BadOrder','NoPostSize','MaxActive','ActWait','SendHold','LmtErr','LmtRng','LmtPx','PendAct','MaxSize','OddLot','IWait','Quiet','Fade','InVld','MaxPSz','MaxOrd','MaxPxLv','MaxPec1','MaxPec2','MaxPec3','TryPxLvl1','TryPxLvl2','TryPxLvl3','DonePxLvl1','DonePxLvl2','DonePxLvl3','SendPxLvl1','SendPxLvl2','SendPxLvl3','NoExchQte','CrtErr','SizeWait','NbboHold','ExchHold','CxlRpl','SndErr','OldMdl','ExchRte','PrcHold','ModelErr','LowProb','PostWith','PostBhnd','NoModel','NoOptQte','NoStkQte','RiskHold','SurfHold','NoFutQte','NoStkPrt','PostClr','NoQte','PHold','StkEnv','OptEnv','FutEnv','TryExch','CkSend','PredMiss','SysEnv','NoExchAvail','PostWide','ZeroLmt','LockSkip','ProbLmt','PrcLmt','NoActSz','CkAdd','Locked','PendCxl','NewWait','EnterLimit','EnterPost','EnterFlow','EnterMake','EnterSeeker','EnterMatrix','EnterLegger','SendOK','SendFail','Except','Filled','TrySend','BadMkt','SysError','MktDataErr','TickSizeErr','NullParent','NoUPrc','TryTake','NoProb','SendThrtl','ProbErr','PendClear','MaxExCnt','MaxExpose','NoExDest','ReachDelay','ReachWait','ClearWait','CxlWait','LeavesErr','FeeWait','ActMaker','WthFade','SyncHold','AuctionDone','BadUDefKey','UDefReject','PendUDef','ExchRej','TrySendSprd','Working','RejDelay','NoStepSz','UpdtLegBrkr','LegNewWait','LegNoChng','LegBadMkt','LegImprvDelay','LegUpdtLmt','LegUpdtErr','NoExchList','PendTake','LeggerDone','MaxChildren','RiskCxlThrtl','FlashWait','NoImpRm','RollVlm','MoveVlm','AccelTake','NoMassQte','MktNotOpen','CreateSprd','InvldExch','RFQTimeout','TrySendRFQ','EnterResponder','CreateErr','LockFail','NoRoute','UnkCrossId','SelfTrade','ImpactHold','LegWait','TwapWait','InvalidSpread','PegHold','OffPeg','ChildCxlHold','CheckBrkr','FaceDone','CtrlSize','FacePromote','MatrixDone','TakeVlmHold','NoExchRoute','ExchNoMake','ExchWrking','ExchSingle','ExchMakeHold','ExchBrkrSkip','ExchNoQte','ExchMaxFee','ExchOffMkt','ExchNoImprv','FlashInitErr','FlashCalcErr','MarketClosed','ExchActOrd','ExchMaxFeeP','ExchNoRte','TakeFail','BestPxLmt','PennyProtected','WaitSquare','NotActive','RteOpnLmt','NoTiedMaker','WaitTrigger','CrossPending','NoResponders','SendThrtlHiLat','SendThrtlNewWin','SendThrtlRteQGap','SendThrtlRteQSz','SendThrtlRteQAge','ExRiskMgmt','RouteDayMax','ZeroChildSize','ExchTakeSkip','ExchNoSzSkip','ExchNoQteSkip','ExchNullRte','ExchRteFail','CxlCntWait','TryGetRoutes','GetMakePrice','MktPreOpen','MktClosed','MktHalted','PendCross','PendSweep','CrossMkt','Sweeping','SweepMiss','NoCrossSize','NoFace','ConfigErr','NExchOffMkt','CrossClear','LiqReqPend','CxlThrottle','RouteSafeMode','Crossing','CrossComplete','CrossExpired') NOT NULL DEFAULT 'None' COMMENT '[enum] current state of non-marketable order handler (maker)',
    `makeCounter` INT NOT NULL DEFAULT 0 COMMENT 'number of times order checked by post handler (note: most checks result in no action)',
    `takeState` ENUM('None','Posting','PendPost','OffMkt','BadFirmType','NoExchSet','BadOrder','NoPostSize','MaxActive','ActWait','SendHold','LmtErr','LmtRng','LmtPx','PendAct','MaxSize','OddLot','IWait','Quiet','Fade','InVld','MaxPSz','MaxOrd','MaxPxLv','MaxPec1','MaxPec2','MaxPec3','TryPxLvl1','TryPxLvl2','TryPxLvl3','DonePxLvl1','DonePxLvl2','DonePxLvl3','SendPxLvl1','SendPxLvl2','SendPxLvl3','NoExchQte','CrtErr','SizeWait','NbboHold','ExchHold','CxlRpl','SndErr','OldMdl','ExchRte','PrcHold','ModelErr','LowProb','PostWith','PostBhnd','NoModel','NoOptQte','NoStkQte','RiskHold','SurfHold','NoFutQte','NoStkPrt','PostClr','NoQte','PHold','StkEnv','OptEnv','FutEnv','TryExch','CkSend','PredMiss','SysEnv','NoExchAvail','PostWide','ZeroLmt','LockSkip','ProbLmt','PrcLmt','NoActSz','CkAdd','Locked','PendCxl','NewWait','EnterLimit','EnterPost','EnterFlow','EnterMake','EnterSeeker','EnterMatrix','EnterLegger','SendOK','SendFail','Except','Filled','TrySend','BadMkt','SysError','MktDataErr','TickSizeErr','NullParent','NoUPrc','TryTake','NoProb','SendThrtl','ProbErr','PendClear','MaxExCnt','MaxExpose','NoExDest','ReachDelay','ReachWait','ClearWait','CxlWait','LeavesErr','FeeWait','ActMaker','WthFade','SyncHold','AuctionDone','BadUDefKey','UDefReject','PendUDef','ExchRej','TrySendSprd','Working','RejDelay','NoStepSz','UpdtLegBrkr','LegNewWait','LegNoChng','LegBadMkt','LegImprvDelay','LegUpdtLmt','LegUpdtErr','NoExchList','PendTake','LeggerDone','MaxChildren','RiskCxlThrtl','FlashWait','NoImpRm','RollVlm','MoveVlm','AccelTake','NoMassQte','MktNotOpen','CreateSprd','InvldExch','RFQTimeout','TrySendRFQ','EnterResponder','CreateErr','LockFail','NoRoute','UnkCrossId','SelfTrade','ImpactHold','LegWait','TwapWait','InvalidSpread','PegHold','OffPeg','ChildCxlHold','CheckBrkr','FaceDone','CtrlSize','FacePromote','MatrixDone','TakeVlmHold','NoExchRoute','ExchNoMake','ExchWrking','ExchSingle','ExchMakeHold','ExchBrkrSkip','ExchNoQte','ExchMaxFee','ExchOffMkt','ExchNoImprv','FlashInitErr','FlashCalcErr','MarketClosed','ExchActOrd','ExchMaxFeeP','ExchNoRte','TakeFail','BestPxLmt','PennyProtected','WaitSquare','NotActive','RteOpnLmt','NoTiedMaker','WaitTrigger','CrossPending','NoResponders','SendThrtlHiLat','SendThrtlNewWin','SendThrtlRteQGap','SendThrtlRteQSz','SendThrtlRteQAge','ExRiskMgmt','RouteDayMax','ZeroChildSize','ExchTakeSkip','ExchNoSzSkip','ExchNoQteSkip','ExchNullRte','ExchRteFail','CxlCntWait','TryGetRoutes','GetMakePrice','MktPreOpen','MktClosed','MktHalted','PendCross','PendSweep','CrossMkt','Sweeping','SweepMiss','NoCrossSize','NoFace','ConfigErr','NExchOffMkt','CrossClear','LiqReqPend','CxlThrottle','RouteSafeMode','Crossing','CrossComplete','CrossExpired') NOT NULL DEFAULT 'None' COMMENT '[enum] current state of marketable order handler (taker)',
    `takeCounter` INT NOT NULL DEFAULT 0 COMMENT 'number of times order checked by take handler (note: most checks result in no action)',
    `mktRespState` ENUM('None','ChildCxlHold') NOT NULL DEFAULT 'None' COMMENT '[enum] current state of response handler (if any)',
    `mktRespCounter` INT NOT NULL DEFAULT 0 COMMENT 'number of times order checked by market response handler (note: most checks result in no action)',
    `mktCrossState` ENUM('None','Hold','NoHandler','OrderPending','CheckPending','BrkrNotActive','ExchHold','InvldBrkr','Filled','MktOpnCutoff','MktClsCutoff','InvldHandler','SendHold','NoAvailSize','NoRiskSize','LimitErr','MaxChildOrders','OpenOrder','InvldCrossType','CanSendFail','TrySend','CreateErr','SendErr','OrderPosted','ChildCxlHold','NoRoute','AuctionClosed','RespondWait','ZeroCustSize','AuctionPending','ChildCxlPending','TryCross','TryCrossFail','Exception','CustCancel','LockMiss','CrossPending','ChildCrossPending','ChildAllocPending','CrossClosed','ChildSweepPending','CustFilled') NOT NULL DEFAULT 'None' COMMENT '[enum] current state of mkt cross handler (if any)',
    `mktCrossCounter` INT NOT NULL DEFAULT 0 COMMENT 'number of times order checked by market cross handler (note: most checks result in no action)',
    `progressRuleDetail` TINYTEXT NOT NULL DEFAULT '' COMMENT 'Twap/Vwap progress detail',
    `lastChildEvent` ENUM('None','OK','ChildError','EnvNotValid','NoOrdSide','OptNotReady','ChildErrHold','ImmCxl','MaxChildOrders','RollVlm','MoveVlm') NOT NULL DEFAULT 'None' COMMENT '[enum] last event code from broker/child order handler(s)',
    `lastChildRejectDttm` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'last child order reject dttm (if any)',
    `lastChildRejectText` TINYTEXT NOT NULL DEFAULT '' COMMENT 'last child order reject reason/text (if any)',
    `spdrMktState` ENUM('None','PreOpen','Open','Closed','Halted','EngNotOpen','Exception') NOT NULL DEFAULT 'None' COMMENT '[enum] market state',
    `cpx1` FLOAT NOT NULL DEFAULT 0 COMMENT 'child price level #1',
    `csz1` INT NOT NULL DEFAULT 0 COMMENT 'cumulative size represented at this price',
    `cex1` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'bit mask of all exchanges where we are at this price',
    `cnt1` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'number of child orders at price level #1',
    `cpx2` FLOAT NOT NULL DEFAULT 0 COMMENT 'child price level #2',
    `csz2` INT NOT NULL DEFAULT 0 COMMENT 'cumulative size represented at this price',
    `cex2` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'bit mask of all exchanges where we are at this price',
    `cnt2` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'number of child orders at price level #2',
    `cMore` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'number of additional child orders at inferior prices (if any)',
    `expiryKey` VARCHAR(6) NOT NULL DEFAULT '',
    `cnVol` FLOAT NOT NULL DEFAULT 0,
    `cnYrs` FLOAT NOT NULL DEFAULT 0,
    `cnUPrc` FLOAT NOT NULL DEFAULT 0,
    `cnDe` FLOAT NOT NULL DEFAULT 0 COMMENT 'delta per contract',
    `cnDd` FLOAT NOT NULL DEFAULT 0 COMMENT '$delta per contract',
    `cnVe` FLOAT NOT NULL DEFAULT 0 COMMENT 'vega per contract',
    `cnGa` FLOAT NOT NULL DEFAULT 0,
    `cnTh` FLOAT NOT NULL DEFAULT 0,
    `cnPr` FLOAT NOT NULL DEFAULT 0 COMMENT 'premium per contract',
    `ddMult` FLOAT NOT NULL DEFAULT 0,
    `priceFormat` ENUM('None','N0','N1','N2','N3','N4','N5','N6','N7','F4','F8','Q8','F16','F32','H32','Q32','F64','H64','FullPenny','PartPenny','PartNickle','EQT','V1','V2','V3','V4','V5','V6','V7','V8','V9','V10','V11','V12','V13','V14','V15','A0','A1','A2','A3','A4','A5','A6','A7','E32') NOT NULL DEFAULT 'None' COMMENT 'option price format code',
    `uPriceFormat` ENUM('None','N0','N1','N2','N3','N4','N5','N6','N7','F4','F8','Q8','F16','F32','H32','Q32','F64','H64','FullPenny','PartPenny','PartNickle','EQT','V1','V2','V3','V4','V5','V6','V7','V8','V9','V10','V11','V12','V13','V14','V15','A0','A1','A2','A3','A4','A5','A6','A7','E32') NOT NULL DEFAULT 'None' COMMENT 'underlier price format code',
    `underliersPerCn` INT NOT NULL DEFAULT 0 COMMENT 'option delivery underliers per contract',
    `underlierType` ENUM('None','Equity','Other','FX') NOT NULL DEFAULT 'None' COMMENT 'type of underlier (affects $greek calculations)',
    `tickValue` FLOAT NOT NULL DEFAULT 0 COMMENT '$NLV value of a single tick change in display premium	(pointValue = tickValue / tickSize)',
    `pointValue` FLOAT NOT NULL DEFAULT 0 COMMENT '$NLV value of a single point change in display premium (pointValue = tickValue / tickSize)',
    `pointCurrency` ENUM('None','AUD','BRL','CAD','CHF','CNH','CNY','EUR','GBP','JPY','KRW','MXN','MYR','NOK','NZD','SEK','TRY','USD','USDCents','CZK','ZAR','HUF','USX','GBX') NOT NULL DEFAULT 'None',
    `theoVol` FLOAT NOT NULL DEFAULT 0,
    `theoPrc` FLOAT NOT NULL DEFAULT 0,
    `numNewOrders` INT NOT NULL DEFAULT 0 COMMENT 'number of child orders generated by this broker (all parent orders)',
    `numParentLimits` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'number of parent limit messages received for this broker',
    `brokerVwapMark` FLOAT NOT NULL DEFAULT 0 COMMENT 'trade weighted average price (duration of parent order)',
    `brokerVwapUMark` FLOAT NOT NULL DEFAULT 0 COMMENT 'option-trade weighted average underlier price (duration of parent order)',
    `brokerQwapMark` FLOAT NOT NULL DEFAULT 0 COMMENT 'average quote mid-market (duration of parent order)',
    `brokerQwapUMark` FLOAT NOT NULL DEFAULT 0 COMMENT 'average underlier quote mid-market (duration of parent order)',
    `vwapPnL` FLOAT NOT NULL DEFAULT 0 COMMENT 'dir * qty * (brokerVwapMark - avgFillPrice)',
    `vwapDnPnL` FLOAT NOT NULL DEFAULT 0 COMMENT 'vwapPnL - uDriftVwapPnL',
    `uDriftVwapPnL` FLOAT NOT NULL DEFAULT 0 COMMENT '(dir * (de * qty * (brokerVwapUMark - avgFillUPrice)))',
    `qwapPnL` FLOAT NOT NULL DEFAULT 0 COMMENT 'dir * qty * (brokerQwapMark - avgFillPrice)',
    `qwapDnPnL` FLOAT NOT NULL DEFAULT 0 COMMENT 'qwapPnL - uDriftQwapPnL',
    `uDriftQwapPnL` FLOAT NOT NULL DEFAULT 0 COMMENT '(dir * (de * qty * (brokerQwapUMark - avgFillUPrice)))',
    `numImprvChld` INT NOT NULL DEFAULT 0 COMMENT 'number of child orders that improved an existing market',
    `activeSeconds` FLOAT NOT NULL DEFAULT 0 COMMENT 'number of seconds that the parent order was active',
    `withSeconds` FLOAT NOT NULL DEFAULT 0 COMMENT 'number of seconds with one or more working child orders that are with of nbbo',
    `aloneSeconds` FLOAT NOT NULL DEFAULT 0 COMMENT 'number of seconds with one or more working child orders that make up 100% of nbbo',
    `workingSeconds` FLOAT NOT NULL DEFAULT 0 COMMENT 'number of child order seconds (can be greater than activeSeconds if working on more than one exchange)',
    `numRiskCancels` INT NOT NULL DEFAULT 0 COMMENT 'number of child orders cancels for risk reasons',
    `riskHoldSeconds` FLOAT NOT NULL DEFAULT 0 COMMENT 'number of seconds with parent order active but on risk hold (zero child order risk size)',
    `riskThrtlSeconds` FLOAT NOT NULL DEFAULT 0 COMMENT 'number of seconds with parent order active but in risk throttle condition (temporary hold after fast/mass risk cancel)',
    `numMakeThrottleSkips` INT NOT NULL DEFAULT 0 COMMENT 'number of childs orders not send to market dues to outbound lines being in a queued/throttled state',
    `sumMakeThrottleDelay` FLOAT NOT NULL DEFAULT 0 COMMENT 'cumulative seconds of queue skip delay (seconds that a child order was not in the market dues to outbound lines being in a queued/throttled state)',
    `uPrcDrift` FLOAT NOT NULL DEFAULT 0 COMMENT 'underlier drift (since parent order start)',
    `sVolDrift` FLOAT NOT NULL DEFAULT 0 COMMENT 'surface vol drift (since parent order start)',
    `updateSrc` ENUM('None','New','Done','Reject','BrokerStatus','CumActQty','CumFillQty','Dirty','OrderStatus','Refresh','StateReject','PermClosed','ParentLimit') NOT NULL DEFAULT 'None',
    `numUpdates` INT NOT NULL DEFAULT 0 COMMENT 'number of record updates (cumulative for the day)',
    `timestamp` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'SR system timestamp',
    CONSTRAINT nonnegative_groupingCode CHECK(ASCII(groupingCode) < 56),
    CONSTRAINT nonnegative_parentNumber CHECK(ASCII(parentNumber) < 56),
    CONSTRAINT nonnegative_baseParentNumber CHECK(ASCII(baseParentNumber) < 56),
    CONSTRAINT nonnegative_modifyNumber CHECK(ASCII(modifyNumber) < 56),
    CONSTRAINT nonnegative_noticeNumber CHECK(ASCII(noticeNumber) < 56),
    CONSTRAINT nonnegative_riskGroupId CHECK(ASCII(riskGroupId) < 56),
    PRIMARY KEY USING HASH (`accnt`,`secKey_tk`,`secKey_yr`,`secKey_mn`,`secKey_dy`,`secKey_xx`,`secKey_cp`,`secKey_at`,`secKey_ts`,`secType`,`spdrSource`,`groupingCode`,`orderSide`,`clientFirm`),
    KEY `AccntIndex` (`accnt`) USING HASH,
    KEY `ClientFirmIndex` (`clientFirm`) USING HASH,
    KEY `UserNameIndex` (`userName`) USING HASH
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='SpdrParentBrkrState records are created/published by SpiderRock Execution Engines. Each record describes the current state of one (or more) parent orders. If a parent order is canceled/replaced then entire chain is represented by a single broker state record.';

SELECT TABLE EXAMPLE QUERY

SELECT
    `accnt`,
    `secKey_at`,
    `secKey_ts`,
    `secKey_tk`,
    `secKey_yr`,
    `secKey_mn`,
    `secKey_dy`,
    `secKey_xx`,
    `secKey_cp`,
    `secType`,
    `spdrSource`,
    `groupingCode`,
    `orderSide`,
    `clientFirm`,
    `userName`,
    `parentNumber`,
    `baseParentNumber`,
    `modifyNumber`,
    `noticeNumber`,
    `riskGroupId`,
    `triggerGroupId`,
    `strategy`,
    `execBrkrCode`,
    `altOrderId`,
    `packageId`,
    `altAccnt`,
    `altUserName`,
    `stageType`,
    `isFlagged`,
    `ticker_at`,
    `ticker_ts`,
    `ticker_tk`,
    `uPrc`,
    `uPrcOrder`,
    `nbboBid`,
    `nbboAsk`,
    `nbboBidSz`,
    `nbboAskSz`,
    `surfacePrc`,
    `surfaceVol`,
    `orderRefSDiv`,
    `orderRefSDivType`,
    `spdrBrokerStatus`,
    `spdrOrderStatus`,
    `spdrCloseReason`,
    `spdrRejectReason`,
    `spdrRejectLevel`,
    `isPermanentlyClosed`,
    `numExternRiskHold`,
    `lastExternRiskText`,
    `lastExternRiskReject`,
    `spdrComment`,
    `orderSize`,
    `orderActiveSize`,
    `activeDuration`,
    `updateDttm`,
    `goodTillDttm`,
    `leavesQuantity`,
    `avgFillPrice`,
    `avgFillUPrice`,
    `cumFillQuantity`,
    `avgFillVol`,
    `cumFillVega`,
    `cumFillTheta`,
    `cumFillDDelta`,
    `cumExchFee`,
    `cumM10Pnl`,
    `cumArrivalPnl`,
    `lastFillDttm`,
    `riskLimitSize`,
    `riskLimitDesc`,
    `riskLimitLevel`,
    `riskLimitDetail`,
    `limitPrice`,
    `limitRefUPrc`,
    `limitErr`,
    `limitErrDesc`,
    `bestLimitOffset`,
    `makeProbLimit`,
    `makeSurfOffset`,
    `makeLimitPrice`,
    `makeLimitErr`,
    `takeProbLimit`,
    `takeSurfOffset`,
    `takeLimitPrice`,
    `takeLimitErr`,
    `autoHedge`,
    `hedgeInstrument`,
    `hedgeSecKey_at`,
    `hedgeSecKey_ts`,
    `hedgeSecKey_tk`,
    `hedgeSecKey_yr`,
    `hedgeSecKey_mn`,
    `hedgeSecKey_dy`,
    `hedgeSecType`,
    `hedgeBetaRatio`,
    `hedgeScope`,
    `hedgeSession`,
    `externHedgeExDest`,
    `externHedgeParams`,
    `userData1`,
    `userData2`,
    `maxProgress`,
    `maxProgressDetail`,
    `maxProgressTime`,
    `brokerState`,
    `brokerCounter`,
    `makeState`,
    `makeCounter`,
    `takeState`,
    `takeCounter`,
    `mktRespState`,
    `mktRespCounter`,
    `mktCrossState`,
    `mktCrossCounter`,
    `progressRuleDetail`,
    `lastChildEvent`,
    `lastChildRejectDttm`,
    `lastChildRejectText`,
    `spdrMktState`,
    `cpx1`,
    `csz1`,
    `cex1`,
    `cnt1`,
    `cpx2`,
    `csz2`,
    `cex2`,
    `cnt2`,
    `cMore`,
    `expiryKey`,
    `cnVol`,
    `cnYrs`,
    `cnUPrc`,
    `cnDe`,
    `cnDd`,
    `cnVe`,
    `cnGa`,
    `cnTh`,
    `cnPr`,
    `ddMult`,
    `priceFormat`,
    `uPriceFormat`,
    `underliersPerCn`,
    `underlierType`,
    `tickValue`,
    `pointValue`,
    `pointCurrency`,
    `theoVol`,
    `theoPrc`,
    `numNewOrders`,
    `numParentLimits`,
    `brokerVwapMark`,
    `brokerVwapUMark`,
    `brokerQwapMark`,
    `brokerQwapUMark`,
    `vwapPnL`,
    `vwapDnPnL`,
    `uDriftVwapPnL`,
    `qwapPnL`,
    `qwapDnPnL`,
    `uDriftQwapPnL`,
    `numImprvChld`,
    `activeSeconds`,
    `withSeconds`,
    `aloneSeconds`,
    `workingSeconds`,
    `numRiskCancels`,
    `riskHoldSeconds`,
    `riskThrtlSeconds`,
    `numMakeThrottleSkips`,
    `sumMakeThrottleDelay`,
    `uPrcDrift`,
    `sVolDrift`,
    `updateSrc`,
    `numUpdates`,
    `timestamp`
FROM `SRTrade`.`MsgSRParentBrkrState`
WHERE 
    /* Replace with a VARCHAR(16) */ 
    `accnt` = 'Example_accnt'
  AND
    /* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */ 
    `secKey_at` = 'None'
  AND
    /* Replace with a ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') */ 
    `secKey_ts` = 'None'
  AND
    /* Replace with a VARCHAR(12) */ 
    `secKey_tk` = 'Example_secKey_tk'
  AND
    /* Replace with a SMALLINT UNSIGNED */ 
    `secKey_yr` = 123
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `secKey_mn` = 1
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `secKey_dy` = 1
  AND
    /* Replace with a DOUBLE */ 
    `secKey_xx` = 4.56
  AND
    /* Replace with a ENUM('Call','Put','Pair') */ 
    `secKey_cp` = 'Call'
  AND
    /* Replace with a ENUM('None','Stock','Future','Option','MLeg') */ 
    `secType` = 'None'
  AND
    /* Replace with a ENUM('None','SpdrTicket','SpdrSingle','SRSE','FIX','HedgeTool','TradeHedge','OpenHedge','AutoHedge','Orphan','RiskManager','OrderManager','ManagedOrder','RFQRespSrvr','Legger','SRSEDrop','FixDrop','TicketDrop','SysTest','RFRResponse','AllocOmni','AllocClient','CertGateway','MLegResponse','LeggerX','DropManager','AutoHedgeSrvr','AuctionStrategySrvr','AllocBlockFace','AllocBlockCust','IceChatGateway','EXS2SRC','MLinkResponse','AutoResponderVD','AutoResponderRC','AutoResponderSN','AutoResponderBX','MLink') */ 
    `spdrSource` = 'None'
  AND
    /* Replace with a CHAR(19) */
    `groupingCode` = 'Example_groupingCode'
  AND
    /* Replace with a ENUM('None','Buy','Sell') */ 
    `orderSide` = 'None'
  AND
    /* Replace with a VARCHAR(16) */ 
    `clientFirm` = 'Example_clientFirm';

Doc Columns Query

SELECT * FROM SRTrade.doccolumns WHERE TABLE_NAME='SpdrParentBrkrState' ORDER BY ordinal_position ASC;

METADATA​

Table Definition​

PRIMARY KEY DEFINITION (Unique)​

SECONDARY INDEX (AccntIndex) (Not Unique)​

SECONDARY INDEX (ClientFirmIndex) (Not Unique)​

SECONDARY INDEX (UserNameIndex) (Not Unique)​

CREATE TABLE EXAMPLE QUERY​

SELECT TABLE EXAMPLE QUERY​

Doc Columns Query​