Skip to main content
Version: 8.4.08.4

ProductDefinitionV2

V8 Message Definiton

SpiderRock normalized exchange product definitions. Includes future, option, and spread definitions from a number of exchanges. TickerDefinitions, RootDefinitions and CCodeDefinitions are consistent with these records.

METADATA

AttributeValue
Topic4335-product-definition
MLink TokenFutureDefinition
ProductSRAnalytics
accessTypeSELECT

Table Definition

FieldTypeKeyDefault ValueComment
secKey_atenum - AssetTypePRI'None'SR Security Key can be partially filled in look at secType
secKey_tsenum - TickerSrcPRI'None'SR Security Key can be partially filled in look at secType
secKey_tkVARCHAR(12)PRI''SR Security Key can be partially filled in look at secType
secKey_yrSMALLINT UNSIGNEDPRI0SR Security Key can be partially filled in look at secType
secKey_mnTINYINT UNSIGNEDPRI0SR Security Key can be partially filled in look at secType
secKey_dyTINYINT UNSIGNEDPRI0SR Security Key can be partially filled in look at secType
secKey_xxDOUBLEPRI0SR Security Key can be partially filled in look at secType
secKey_cpenum - CallPutPRI'Call'SR Security Key can be partially filled in look at secType
secTypeenum - SpdrKeyTypePRI'None'Security Type Stock Future Option
securityIDTINYTEXT''unique exchange id exch assigned
ticker_atenum - AssetType'None'master underlier
ticker_tsenum - TickerSrc'None'master underlier
ticker_tkVARCHAR(12)''master underlier
SRspreadIDBIGINT0
productClassenum - ProductClass'None'
underlierIDBIGINT0underlier product id option only securityID of undKeyundType product
undKey_atenum - AssetType'None'SR Underlier Security Key can be partially filled in look at undType option only
undKey_tsenum - TickerSrc'None'SR Underlier Security Key can be partially filled in look at undType option only
undKey_tkVARCHAR(12)''SR Underlier Security Key can be partially filled in look at undType option only
undKey_yrSMALLINT UNSIGNED0SR Underlier Security Key can be partially filled in look at undType option only
undKey_mnTINYINT UNSIGNED0SR Underlier Security Key can be partially filled in look at undType option only
undKey_dyTINYINT UNSIGNED0SR Underlier Security Key can be partially filled in look at undType option only
undTypeenum - SpdrKeyType'None'Underlier Security Type Stock Future option only
productGroupVARCHAR(6)''Underlying product code IE All GE Eurodollar spreads options futures will be in the same productGroup This is the Asset field from the SecurityDefinition message
securityGroupVARCHAR(6)''Exchange specific code for a group of related securities that are all affected by market events IE All Emini weekly options EW This is SecurityGroup field from the SecurityDefinition messages
marketSegmentIDINT0Exchange specific market segment identifier
ricCodeVARCHAR(32)''Full RIC Code only provided for nonuser defined instruments
securityDescTINYTEXT''full exchange symbol
exchangeVARCHAR(8)''listing exchange
productTypeenum - ProductType'None'
productTermenum - ProductTerm'None'
productIndexTypeenum - ProductIndexType'None'
productRateFLOAT0
contractSizeFLOAT0
contractUnitenum - ContractUnit'None'
priceFormatenum - PriceFormat'None'
minTickSizeDOUBLE0
displayFactorDOUBLE0
strikeScaleDOUBLE0manual strike price adjustment multiplier used for some CME products if set otherwise displayFactor is used okeyxx strikePrice manualStrikeScale
minLotSizeSMALLINT0minimum lot size
bookDepthSMALLINT0levels in the Globex quote book
impliedBookDepthSMALLINT0levels in the globex implied quote book 0 if no implied depth
impMarketIndSMALLINT0implied market type 0 no implied 1 implied in 2 implied out 3 implied in out
minPriceIncrementAmountFLOAT0depricate minimum price amount points per handle
parValueFLOAT0per contract par value
contMultiplierFLOAT0contract deliverable multipler
cabPriceDOUBLE0depricate cabinet price minimum closing price for OOM options
tradeCurrenum - Currency'None'
settleCurrenum - Currency'None'
strikeCurrenum - Currency'None'
expirationDATETIME(6)'1900-01-01 00:00:00.000000'future expiration or option expiration if product is an option we use the last TRADING day as the expiration date
maturityDATE'1900-01-01'future maturity date or option maturity date this is the delivery month
exerciseTypeenum - ExerciseType'None'depricate in RootDefinition Exercise style
userDefinedenum - YesNo'None'
decayStartYearSMALLINT0
decayStartMonthTINYINT UNSIGNED0
decayStartDayTINYINT UNSIGNED0
decayQtyINT0daily decay quantity
priceRatioDOUBLE0price ratio for interest rate intercommodity spreads
timestampDATETIME(6)'1900-01-01 00:00:00.000000'
LegsListJSON'JSON_OBJECT()'

PRIMARY KEY DEFINITION (Unique)

FieldSequence
secKey_tk1
secKey_yr2
secKey_mn3
secKey_dy4
secKey_xx5
secKey_cp6
secKey_at7
secKey_ts8
secType9

JSON Block (LegsList)

FieldTypeComment
legIDstringleg SecurityId exch assigned
secKeyOptionKey
secTypeenum - secType
sideenum - side
ratioushort
refDeltafloat
refPrcdouble

CREATE TABLE EXAMPLE QUERY

CREATE TABLE `SRAnalytics`.`MsgProductDefinitionV2` (
`secKey_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT 'SR Security Key [can be partially filled in (look at secType)]',
`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' COMMENT 'SR Security Key [can be partially filled in (look at secType)]',
`secKey_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'SR Security Key [can be partially filled in (look at secType)]',
`secKey_yr` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'SR Security Key [can be partially filled in (look at secType)]',
`secKey_mn` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'SR Security Key [can be partially filled in (look at secType)]',
`secKey_dy` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'SR Security Key [can be partially filled in (look at secType)]',
`secKey_xx` DOUBLE NOT NULL DEFAULT 0 COMMENT 'SR Security Key [can be partially filled in (look at secType)]',
`secKey_cp` ENUM('Call','Put','Pair') NOT NULL DEFAULT 'Call' COMMENT 'SR Security Key [can be partially filled in (look at secType)]',
`secType` ENUM('None','Stock','Future','Option','MLeg') NOT NULL DEFAULT 'None' COMMENT 'Security Type [Stock, Future, Option]',
`securityID` TINYTEXT NOT NULL DEFAULT '' COMMENT 'unique exchange id (exch assigned)',
`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 'master underlier',
`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 'master underlier',
`ticker_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'master underlier',
`SRspreadID` BIGINT NOT NULL DEFAULT 0,
`productClass` ENUM('None','Equity','Index','Future','Option','Spread') NOT NULL DEFAULT 'None',
`underlierID` BIGINT NOT NULL DEFAULT 0 COMMENT 'underlier product id (option only) [securityID of undKey/undType product]',
`undKey_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT 'SR Underlier Security Key [can be partially filled in (look at undType)] (option only)',
`undKey_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 'SR Underlier Security Key [can be partially filled in (look at undType)] (option only)',
`undKey_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'SR Underlier Security Key [can be partially filled in (look at undType)] (option only)',
`undKey_yr` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'SR Underlier Security Key [can be partially filled in (look at undType)] (option only)',
`undKey_mn` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'SR Underlier Security Key [can be partially filled in (look at undType)] (option only)',
`undKey_dy` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'SR Underlier Security Key [can be partially filled in (look at undType)] (option only)',
`undType` ENUM('None','Stock','Future','Option','MLeg') NOT NULL DEFAULT 'None' COMMENT 'Underlier Security Type [Stock, Future] (option only)',
`productGroup` VARCHAR(6) NOT NULL DEFAULT '' COMMENT 'Underlying product code. I.E. All GE (Eurodollar) spreads, options, futures will be in the same productGroup - This is the Asset field from the SecurityDefinition message',
`securityGroup` VARCHAR(6) NOT NULL DEFAULT '' COMMENT 'Exchange specific code for a group of related securities that are all affected by market events. I.E. All E-mini weekly options (EW) - This is SecurityGroup field from the SecurityDefinition messages',
`marketSegmentID` INT NOT NULL DEFAULT 0 COMMENT 'Exchange specific market segment identifier',
`ricCode` VARCHAR(32) NOT NULL DEFAULT '' COMMENT 'Full RIC Code - only provided for non-user defined instruments',
`securityDesc` TINYTEXT NOT NULL DEFAULT '' COMMENT 'full exchange symbol',
`exchange` VARCHAR(8) NOT NULL DEFAULT '' COMMENT 'listing exchange',
`productType` ENUM('None','Outright','CalSpr','EqCalSpr','FXCalSpr','RedTick','BFly','Condor','Strip','InterCmd','Pack','MnPack','PackBFly','DblBFly','PackSpr','Crck','Bndl','BndlSpr','EnrStrp','BalStrp','UnbalStrp','EnICStrp','IRICStrp','ITRICSpr','UserDef','Combo','TAS','TASCalSpr','TAA','TIC','BIC','TAP','Index') NOT NULL DEFAULT 'None',
`productTerm` ENUM('None','Month','Day','Week','BalanceOfMonth','Quarter','Season','BalanceOfWeek','CalendarYear','Variable','Custom','SameDay','NextDay','Weekly','Pack','Bundle','IRSAndCDSTenor','Year') NOT NULL DEFAULT 'None',
`productIndexType` ENUM('None','NextDay','FirstOfMonth','VWA','Russel') NOT NULL DEFAULT 'None',
`productRate` FLOAT NOT NULL DEFAULT 0,
`contractSize` FLOAT NOT NULL DEFAULT 0,
`contractUnit` ENUM('None','AUD','BBL','BDFT','BRL','BU','CAD','CHF','CTRCT','CUR','CWT','CZK','EUR','GAL','GBP','HUF','ILS','IPNT','JPY','KRW','LBS','MMBTU','MWH','MXN','MYR','NOK','NZD','PLN','RMB','RUR','SEK','TON','TRY','TRYOZ','USD','ZAR') NOT NULL DEFAULT 'None',
`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',
`minTickSize` DOUBLE NOT NULL DEFAULT 0,
`displayFactor` DOUBLE NOT NULL DEFAULT 0,
`strikeScale` DOUBLE NOT NULL DEFAULT 0 COMMENT 'manual strike price adjustment multiplier (used for some CME products if set, otherwise displayFactor is used) (okey_xx = strikePrice * manualStrikeScale)',
`minLotSize` SMALLINT NOT NULL DEFAULT 0 COMMENT 'minimum lot size',
`bookDepth` SMALLINT NOT NULL DEFAULT 0 COMMENT 'levels in the Globex quote book',
`impliedBookDepth` SMALLINT NOT NULL DEFAULT 0 COMMENT 'levels in the globex implied quote book (0 if no implied depth)',
`impMarketInd` SMALLINT NOT NULL DEFAULT 0 COMMENT 'implied market type (0 = no implied, 1 = implied in, 2 = implied out, 3 = implied in & out)',
`minPriceIncrementAmount` FLOAT NOT NULL DEFAULT 0 COMMENT '(depricate) minimum price amount (points per handle)',
`parValue` FLOAT NOT NULL DEFAULT 0 COMMENT 'per contract par value',
`contMultiplier` FLOAT NOT NULL DEFAULT 0 COMMENT 'contract deliverable multipler',
`cabPrice` DOUBLE NOT NULL DEFAULT 0 COMMENT '(depricate) cabinet price (minimum closing price for OOM options)',
`tradeCurr` 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',
`settleCurr` 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',
`strikeCurr` 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',
`expiration` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'future expiration or option expiration (if product is an option). we use the last TRADING day as the expiration date.',
`maturity` DATE NOT NULL DEFAULT '1900-01-01' COMMENT 'future maturity date or option maturity date. this is the delivery month.',
`exerciseType` ENUM('None','American','European','Asian','Cliquet') NOT NULL DEFAULT 'None' COMMENT '(depricate; in RootDefinition) Exercise style',
`userDefined` ENUM('None','Yes','No') NOT NULL DEFAULT 'None',
`decayStartYear` SMALLINT NOT NULL DEFAULT 0,
`decayStartMonth` TINYINT UNSIGNED NOT NULL DEFAULT 0,
`decayStartDay` TINYINT UNSIGNED NOT NULL DEFAULT 0,
`decayQty` INT NOT NULL DEFAULT 0 COMMENT 'daily decay quantity',
`priceRatio` DOUBLE NOT NULL DEFAULT 0 COMMENT 'price ratio for interest rate intercommodity spreads',
`timestamp` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000',
`LegsList` JSON NOT NULL DEFAULT JSON_OBJECT() CHECK(JSON_VALID(LegsList)),
PRIMARY KEY USING HASH (`secKey_tk`,`secKey_yr`,`secKey_mn`,`secKey_dy`,`secKey_xx`,`secKey_cp`,`secKey_at`,`secKey_ts`,`secType`)
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='SpiderRock normalized exchange product definitions. Includes future, option, and spread definitions from a number of exchanges. TickerDefinitions, RootDefinitions and CCodeDefinitions are consistent with these records.';

SELECT TABLE EXAMPLE QUERY

SELECT
`secKey_at`,
`secKey_ts`,
`secKey_tk`,
`secKey_yr`,
`secKey_mn`,
`secKey_dy`,
`secKey_xx`,
`secKey_cp`,
`secType`,
`securityID`,
`ticker_at`,
`ticker_ts`,
`ticker_tk`,
`SRspreadID`,
`productClass`,
`underlierID`,
`undKey_at`,
`undKey_ts`,
`undKey_tk`,
`undKey_yr`,
`undKey_mn`,
`undKey_dy`,
`undType`,
`productGroup`,
`securityGroup`,
`marketSegmentID`,
`ricCode`,
`securityDesc`,
`exchange`,
`productType`,
`productTerm`,
`productIndexType`,
`productRate`,
`contractSize`,
`contractUnit`,
`priceFormat`,
`minTickSize`,
`displayFactor`,
`strikeScale`,
`minLotSize`,
`bookDepth`,
`impliedBookDepth`,
`impMarketInd`,
`minPriceIncrementAmount`,
`parValue`,
`contMultiplier`,
`cabPrice`,
`tradeCurr`,
`settleCurr`,
`strikeCurr`,
`expiration`,
`maturity`,
`exerciseType`,
`userDefined`,
`decayStartYear`,
`decayStartMonth`,
`decayStartDay`,
`decayQty`,
`priceRatio`,
`timestamp`,
`LegsList`
FROM `SRAnalytics`.`MsgProductDefinitionV2`
WHERE
/* 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';

Doc Columns Query

SELECT * FROM SRAnalytics.doccolumns WHERE TABLE_NAME='ProductDefinitionV2' ORDER BY ordinal_position ASC;