[ts-gen] Unique Identifier for Orders

Bill Pippin pippin at owlriver.net
Fri Sep 11 16:53:22 EDT 2009


About identifying orders:

> I'd like to open a discussion of unique identifiers for orders.

In brief:

    1.  Though orders are supposedly uniquely identified by a
    triple of account code, connection id, and order id, and these
    triples may be useful after the fact for queries, you should
    not use them as operational keys.

    2.  Stick with the order key instead, the alphanumeric you
    supply in your "create item ..." command.  You'll probably also
    want to keep maps between those keys and the above triple, or
    maybe just the order id itself, since the other values are
    constant over a session.

    Your app can learn the mapping from order keys to order ids from
    the log by looking at the place order request text that follows
    the create command translation.

    3.  You can probably find other keys as well, but I claim it's
    a design mistake to use them as part of order submission and

In more detail, about order keys, and order ids:

You control the order key, and are responsible for keeping its values
unique per order over whatever order space of interest you choose to
define.  These might include account set, whether one only, or plural;
date range, whether indefinite, monthly, annual, whatever; database
version, whether current, or several, or forever; and so on.  Order
keys are yours.  Note that the database definition for the CreateEvent
table requires that order keys be unique, so if you want to reuse such
from one IB account to the next, you'll have to use multiple databases.

Order ids are comparatively tricky.  First, please consider the
following excerpt from the create table statement for CreateEvent,
in sql/xact.sql, focusing on the keys therein:

create table CreateEvent
    uid         int unsigned not null auto_increment primary key,
    var         varchar(96)  not null,             unique key(var),

    acc         int unsigned not null references  AccountCode(uid),
    client_id   int unsigned not null,
    order_tag   int unsigned not null,


The attributes above provide three candidate unique keys.  The uid;
order variable, or order key; and (acc, client_id, order_tag) triple
all work as unique keys under ordinary circumstances.  I'll consider
each in turn.

The uid attribute is internal to the shim, must be sequential, is
outside your control, and so should *not* be used as a key in your
app, except for the special case where you read in all orders over
a contiguous range, store that sequence in a vector, and index it
via the uid + some offset, with the offset being zero if you read
in all the orders.  Even there you'll probably want to use
information hiding to limit visibility of the object index to those
other database record/objects where it occurs as a foreign key, in
particular records from the ChangeOrder table.

As noted above, the order key can be your workhorse identifier for
orders, and it's *all* yours.  Just keep it within 96 chars length,
and limit it to the syntax suggested by exs/risk.rb, that is an
initial alpha with following alphanum or hyphen chars.

Finally, about the order id, the integral number that is used within
the IB tws api protocol to identify orders and other contract
occurrences once given the account key/code and connection id.  As
you note below, it's not within the downstream's control:

> ... I was a little surprised today to see order_tag go back to 1
> when I upgraded to 090904 - I was expecting 57.  It looks like a
> change in client_id from 8 to 4 may have been the culprit.

Connection ids each have their own distinct tick id number spaces.
Each order id, what I call an order_tag in the database, is drawn
from this number space, as are all the other tick ids used to 
identify a contract-related subscription or query request.  That is,
subscription, query, and order requests all are identified by
counting numbers drawn in sequence from the client id number space
for that session, and there is an absolute requirement that those
numbers must increase monotonically from one contract specific
request to the next in the order that those are made.

Now, given observation of the IB tws gui, it seems that the IB tws api
protocol calls for the upstream to offer the lowest positive non-zero
number not otherwise known to have already been used as a persistent
(order) identifier for that connection id.  So, for data mode, each
connection can start counting tick ids over from one, since no orders
are ever made for a data mode connection id, that is any of the numbers
from 1 through 7.

Risk mode connections, though, typically have to start tick ids at some
higher index, as indicated by the next id message received by the shim
at startup.  Here the shim takes what it can get, since attempting to
use a lower id would just lead to protocol errors.  And again, as you
note above, for a connection id that has not yet been used for orders,
counting starts from one.

When --- if ever --- do order ids reset?  I'm not sure what all the
limits are.  Clearly they reset whenever you blow away the account
specific directory of your IB java trading system directory, e.g.,
IBJts/darykq for the edemo account.  By the same token, they reset
when you upgrade to a new version of the IB tws, since there also
a new account-keyed directory is created.

Otherwise I'm not sure; I sense they may reset over a sufficiently
long time span, say 90 days, although I wouldn't swear to it, since
I routinely upgrade the IB tws to a newer version in less time than
that.  Of course different connections have distinct order id
namespaces, so if old orders become a problem, you can just start
using a new connection id.  Otherwise, it's not in your control.

> I thought I understood order_tag to be a shim-wide identifier for
> orders so I tried to surmise the next order_tag by SELECT
> MAX(order_tag)+1 - I was a little surprised today to see order_tag
> go back to 1 when I upgraded to 090904 - I was expecting 57.  It
> looks like a change in client_id from 8 to 4 may have been the
> culprit.

As noted above, the order id, which is named as the order_tag
attribute once stored to the journal, is not controlled by the shim.
The shim just starts as low as it can, that is with the next id
offered at handshake.  Your app should learn their values, if at all,
from the log stream, not from the database.

As for a risk mode connection with connection id of 4, you should leave
the client id values from 1--7 for the use of data-mode clients.  You'll
find that 8 is the default for risk mode, and otherwise you may set the
ClientId variable to whatever larger non-zero counting number you wish.
If you must use zero, be aware that such use is not supported, and that
there are known problems due to the IB tws gui's habit of confiscating
order ids from the connection-zero number space for its own use.

> Ultimately, what I am trying to grasp is the unique order identifier
> at these three levels:

> 1. My signal generator (my responsibility)
> 2. Shim - looks like order_id
> 3. TWS/IB - probably something other than order_id

Restating somewhat, and letting (acc, cid, tid) refer to account code,
client_id [connection id], order_tag [tick id] triples, your list
above may be expanded to the following:

    1.  Downstream: is in control of the order keys namespace, and also
        will probably need to map between order key and (acc, cid, tid)
        triples, since tick ids in particular occur in journal-related

        More precisely, the downstream should know the account code it
        is connecting to, and check it against the startup account data
        query answer; know the client id it is connecting under, and
        control it via the once option as needed, if in risk mode;
        extract the order id from the text of the related request, which
        follows the translation of the original create command in the
        log; and match that order id against order status messages as
        they arrive.

        Note that queued commands show up twice in the log, once as a
        comment when enqueued, and once soon after as a command when
        translated to a request, and that your app can only find an
        initial (tick id allocating) place order request after this
        second instance of the create command. 

    2.  trading-shim: minimizes the tick id starting value on a per
        session basis in response to the handshake next id message,
        and must map between order keys and (acc, cid, tid) triples
        so that the downstream can have its own namespace for orders.

        All order identification key information is discovered via
        some kind of input, whether from the database at startup,
        the downstream, or the upstream.  The shim's control over
        order keying is limited to starting and stopping: choosing
        to accept the handshake next id at startup, and refusing to
        accept next id jumps dictated by the upstream if they were
        to occur.

    3.  IB tws gui: has absolute control over tick ids, insists that
        they be used monotonically with a base value at least that
        of the handshake next id offer, and is free to dictate jumps
        as desired.

        Such gaps start races, and so the shim would reject them were
        they to occur.  It is fortunate, then, that for connect ids
        other than zero, where other sessions and gui usage may cause
        jumps, and as long as an api program does not itself use the
        next id request to force such a gap, closed sequences work
        just fine.

        That is, although IB docs make no promises, I've never
        seen an IB tws gui force a tick id jump once startup was done,
        or reject a proposed, sequentially indexed tick id as long as
        it started from the offered base value, had not yet been used
        by the shim in that session, and was at most 2^31 - 1 .

Note in the above that the shim next command purposely does not
expose the tick id gapping argument to the downstream, and need not
do so, since the downstream should be using order keys to identify

Note also that the api protocol mostly treats the account code as
contextual information; it presumes that you know what account an
IB tws gui is connected to, and that you know what you're doing with
your local machine or lan.

> I need to tie together all three order_id's in a one-to-one-to-one
> relationship, or I will get myself in trouble.  In particular, I
> care about binding 1. to 2. carefully ...

Keep a per-session pair of maps, from order keys to order ids and
back, and your downstream app should be fine.  Think some about
how you want to name order keys, in particular what info you want
to embed in the text, and that should be it.

> ... as my signal generator will generate a row in MySQL before
> the shim ... [increments the] order_id ...

I don't see why you want your app to duplicate the CreateEvent
table, but if you do, yes, this would be tricky.  I don't see that
it buys you anything.

Wait for the cmd translation/order request pairing to show up in
the log, check the CreateEvent table if you wish, wait for an order
status message to show up, and that should do it.  A robust client
app should check for each of the above state transitions, and
complain if an expected event is a no-show within some reasonable
time interval.

By the way, here's what FutureScalper has just said on the Yahoo list
about waiting for order feedback:

> I guess it's mainly the orderStatus ... [message].  Well, you should
> have some sort of state transition idea.  I mean, an order is alive
> until it's cancelled or completely filled.  You can assume the API
> will ... [send a message] ... for any significant state changes ...
> in theory, anything could go wrong, but doesn't usually.

> Usually you want to send command, check command success/failure if
> applicable, and then await command confirmation.  If confirmation
> doesn't come quickly enough which, for me would be a few seconds, then
> consider the order to be in question.  You can abandon it, or somehow
> try to figure out what's wrong with it, but that doesn't happen very
> often.

> If it's an attended system, then TWS can be a fallback to correct and
> check position, etc.

Now, Ken, back to your question:

> ... so I have a little bit of a chicken-and-egg problem here

Indeed, which is why the distinct cmd and req sides to the journal, and
the shim's control over same.  The shim makes atomic writes, and there's
no easy way for the downstream to duplicate this effort in any useful
way.  Note, by the way, that downstream apps may read but must not
write the order journal.

Finally, as for other keys, as alluded to at the beginning, the
various journal-related IB tws api messages include a number of
intriguing keys, including what is evidently a fix identifier.
The shim dutifully copies them to the journal, but they are
of absolutely no use in order management via the api, since they
never occur in requests.  They may be of use to you for order
reconciliation, or in case of problems where IB support may want
to know their values, but your downstream order submission app
can and should ignore them.



More information about the ts-general mailing list