ExternAggGroupGateway

V8 Message Definiton

This table allows clients to enter start-of-day positions, and quantity bot/sld today for each market. AggGroup is a code issued by SpiderRock to indentify the aggregation group that the reported positions and trades belong to. This will usually be your SpiderRock assigned ClientFirm code unless your firm has more than one aggregation unit. Note that start-of-day positions can be truncated if the truncation will not affect order marking. Contact SpiderRock support for details if this is what you intend.

METADATA

Attribute	Value
Topic	5120-srse-gateway
MLink Token	Internal
Product	SRTrade
accessType	SELECT,UPDATE,INSERT,DELETE

Table Definition

Field	Type	Key	Default Value	Comment
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'	Security Type Stock Future Option
aggGroup	VARCHAR(16)	PRI	''	SpiderRockassigned aggGroup identifier up to 16 characters stkAggGroup futAggGroup or optAggGroup defaults to ClientFirm if blank
clientFirm	VARCHAR(16)		''	client firm of the user who entered this record via SRSEblank otherwise
openPosition	INT		0	startofday position aggregate firm position for all accnts in this aggGroup can be truncated if necessary
openPosType	enum - OpenPosType		'None'	note if set to Truncated trading will be suspended when total quantity traded today would flip position from long to short or visaversa
botQuantity	INT		0	quantity bot today can be zero if reported via execution drops
sldQuantity	INT		0	quantity sld today can be zero if reported via execution drops
timestamp	DATETIME(6)		'1900-01-01 00:00:00.000000'

PRIMARY KEY DEFINITION (Unique)

Field	Sequence
secKey_tk	1
secKey_yr	2
secKey_mn	3
secKey_dy	4
secKey_xx	5
secKey_cp	6
secKey_at	7
secKey_ts	8
secType	9
aggGroup	10

CREATE TABLE EXAMPLE QUERY

CREATE TABLE `SRTrade`.`MsgExternAggGroupGateway` (
    `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','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','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' COMMENT 'Security Type [Stock, Future, Option]',
    `aggGroup` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'SpiderRock-assigned aggGroup identifier (up to 16 characters) [stkAggGroup, futAggGroup, or optAggGroup] (defaults to ClientFirm if blank)',
    `clientFirm` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'client firm of the user who entered this record via SRSE;blank otherwise',
    `openPosition` INT NOT NULL DEFAULT 0 COMMENT 'start-of-day position (aggregate firm position for all accnts in this aggGroup) [can be truncated if necessary]',
    `openPosType` ENUM('None','Exact','Truncated') NOT NULL DEFAULT 'None' COMMENT 'note: if set to Truncated trading will be suspended when total quantity traded today would flip position from long to short or visa-versa',
    `botQuantity` INT NOT NULL DEFAULT 0 COMMENT 'quantity bot today (can be zero if reported via execution drops)',
    `sldQuantity` INT NOT NULL DEFAULT 0 COMMENT 'quantity sld today (can be zero if reported via execution drops)',
    `timestamp` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000',
    PRIMARY KEY USING HASH (`secKey_tk`,`secKey_yr`,`secKey_mn`,`secKey_dy`,`secKey_xx`,`secKey_cp`,`secKey_at`,`secKey_ts`,`secType`,`aggGroup`)
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='This table allows clients to enter start-of-day positions, and quantity bot/sld today for each market.  AggGroup is a code issued by SpiderRock to indentify the aggregation group that the reported positions and trades belong to. This will usually be your SpiderRock assigned ClientFirm code unless your firm has more than one aggregation unit.  Note that start-of-day positions can be truncated if the truncation will not affect order marking.  Contact SpiderRock support for details if this is what you intend.';

SELECT TABLE EXAMPLE QUERY

SELECT
    `secKey_at`,
    `secKey_ts`,
    `secKey_tk`,
    `secKey_yr`,
    `secKey_mn`,
    `secKey_dy`,
    `secKey_xx`,
    `secKey_cp`,
    `secType`,
    `aggGroup`,
    `clientFirm`,
    `openPosition`,
    `openPosType`,
    `botQuantity`,
    `sldQuantity`,
    `timestamp`
FROM `SRTrade`.`MsgExternAggGroupGateway`
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','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','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 VARCHAR(16) */ 
    `aggGroup` = 'Example_aggGroup';

UPDATE TABLE EXAMPLE QUERY

UPDATE `SRTrade`.`MsgExternAggGroupGateway` 
SET
    /* Replace with a VARCHAR(16) */ 
    `clientFirm` = 'Example_clientFirm',
    /* Replace with a INT */ 
    `openPosition` = 5,
    /* Replace with a ENUM('None','Exact','Truncated') */ 
    `openPosType` = 'None',
    /* Replace with a INT */ 
    `botQuantity` = 5,
    /* Replace with a INT */ 
    `sldQuantity` = 5,
    /* Replace with a DATETIME(6) */
    `timestamp` = '2022-01-01 12:34:56.000000'
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','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','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 VARCHAR(16) */ 
    `aggGroup` = 'Example_aggGroup';

INSERT TABLE EXAMPLE QUERY

INSERT INTO `SRTrade`.`MsgExternAggGroupGateway`(
    /* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */ 
    `secKey_at`,
    /* 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','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFE') */ 
    `secKey_ts`,
    /* Replace with a VARCHAR(12) */ 
    `secKey_tk`,
    /* Replace with a SMALLINT UNSIGNED */ 
    `secKey_yr`,
    /* Replace with a TINYINT UNSIGNED */ 
    `secKey_mn`,
    /* Replace with a TINYINT UNSIGNED */ 
    `secKey_dy`,
    /* Replace with a DOUBLE */ 
    `secKey_xx`,
    /* Replace with a ENUM('Call','Put','Pair') */ 
    `secKey_cp`,
    /* Replace with a ENUM('None','Stock','Future','Option','MLeg') */ 
    `secType`,
    /* Replace with a VARCHAR(16) */ 
    `aggGroup`,
    /* Replace with a VARCHAR(16) */ 
    `clientFirm`,
    /* Replace with a INT */ 
    `openPosition`,
    /* Replace with a ENUM('None','Exact','Truncated') */ 
    `openPosType`,
    /* Replace with a INT */ 
    `botQuantity`,
    /* Replace with a INT */ 
    `sldQuantity`,
    /* Replace with a DATETIME(6) */
    `timestamp`
) 
VALUES(
    'None',
    'None',
    'Example_secKey_tk',
    123,
    1,
    1,
    4.56,
    'Call',
    'None',
    'Example_aggGroup',
    'Example_clientFirm',
    5,
    'None',
    5,
    5,
    '2022-01-01 12:34:56.000000'
);

DELETE TABLE EXAMPLE QUERY

DELETE FROM `SRTrade`.`MsgExternAggGroupGateway` 
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','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','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 VARCHAR(16) */ 
    `aggGroup` = 'Example_aggGroup';

Doc Columns Query

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