[ts-gen] questions on journal tables

[Bill Pippin]

Nils,

About the journal tables:

> ... this set of tables ...  The meaning of OrderResult is not
> quite clear to me ...

All journal table structures are defined by the create table
statements in sql/xact.sql.  Leaving the session level Account
tables aside, since they settle down to a steady state after your
first logon to an account, there are two journal tables for
commands, and four for messages.

You've already considered the two command-related tables, 
CreateEvent and ChangeOrder, and seen that writes to CreateEvent
occur as part of a transaction, to ensure that every order created
has an initial value for the mutable values that are stored in
the ChangeOrder history, that is: order type, quantity, limit price,
and aux price.  The transaction for order creation is the only
multi-statement transaction; all other records are written by 
individual sql statements, which form their own transaction.

So, records in CreateEvent are designed to be one-to-one with orders.
You've noted that, in addition to the order key var attribute, which,
with the account code forms a unique key, there is also a client_id,
order_tag pair, and that such pairs occurs also in OrderStatus,
ActiveOrder, and OrderReport.

This brings me to the message-related journal tables, and what will
seem like a detour.  IB tws api messages map to the journal as
follows:

    ib api msg:    shim table:
    -----------    -----------
    Status         OrderStatus
    Open           ActiveOrder
    Execution      OrderResult
    Portfolio      OrderReport

As noted previously, each original message is written, essentially
as soon as it is received, with record blocking into batch inserts
limited to that already occurring in the input to the shim.  Only
the first three above map to specific orders, with the portfolio
records related instead to positions, and so about which no more.

Of the others, you ask about OrderResult.

IB designed the Status and Open order messages first, and there is
as far as I know, no way to trigger replay of Status messages.  Open
order messages I believe can be replayed via the open orders request.

In contrast, execution reports can be replayed to your heart's
content, filtered by symbol and time, and, given extensions to the
shim command language that have not been implemented yet, filtered
by other attributes as well.

Execution messages are to some degree complementary to the
OrderStatus and Open order messages; opens do not have execution
reports, and only the later states of order status show up in
those reports.  To the extent that they overlap, a number of
api developers seem to prefer execution reports as a source for
the shared attributes.

> ... I would be curious to know what is the rule
> that generates order_tag ...
 
Now, ending the detour, about the client_id - order_tag pair.  Just
as the the account and order key uniquely identifies an order to the
shim, the account and client_id - order_tag pair uniquely identifies
an order to your IB tws installation, at least as long as you don't
blow away the account-specific directory underneath the IBJts
directory for that install.

The account and client_id are session level, and prominently displayed
in the banner, and so about which in messages, no more.  You asked
about the client_tag.

The IB tws api protocol calls for a single, per-session, monotonic
integer key space with which to identify subscriptions, contracts,
orders, and the like.  I refer to instances from this counter as tick
ids, which is appropriate for market data subscriptions, though
somewhat misleading terminology for orders.  In any case, the shim
allocates a new tick id as needed for subscriptions, history queries,
contract data queries, and orders, and the IB tws strictly enforces the
requirement for monotonicity; each one the shim uses must be greater
than all that have gone before.

The IB tws also enforces a starting point for the tick id counter,
and it does so through the NextId message type.  At start up, it
sends a next id message following the handshake, and the shim reads
that to compute an offset for the tick id table.  After that, the
shim just counts from that starting point, although the api would
allow it to skip and leave gaps if desired.

For a data mode shim, since it is using a client_id that has only
been used by other data mode shims, the numbering will start at 1
at the beginning of the session.  We call the related NextId event
a "9-1-1" message, as the message id is 9, and the version is still
at 1.  Risk mode shims will reflect the order history for that
client_id, so the last number in the triple is likely higher.

You can see the next id messages in the log, at startup; below are
extracts from a data mode and risk mode shim, respectively:

    4|102| 0|# |4|102|0|23|1|44|20090504 15:40:17 EST|Connect with: \
                                      Connect with: cv 23, id 1, sv 44|
    3|  9| 1|1|
    3|  4| 2|      -1|2104|Market data farm connection is OK:usfarm|
    3|  6| 2|AccountCode                     |    DU4181|    |DU4181|

    4|102| 0|# |4|102|0|23|8|44|20090504 15:50:27 EST|Connect with: \
                                      Connect with: cv 23, id 8, sv 44|
    3|  9| 1|113|
    3|  4| 2|      -1|2104|Market data farm connection is OK:usfarm|

In the log extracts above, the data mode shim has a client id of 1, and
a 9-1-1 next id message, and tick ids start from 1; while the risk mode
shim has a client id of 8, and tick ids start from 113.  In this case,
the offset for the order history is low, since I deleted the account
specific directory to finesse an IB tws bug.

By the way, when your IB tws starts and the gui immediately disappears,
this is a symptom of errors in the logging by the tws during a previous
session, and the only solution I've found is to delete the
account-specific directory where those log files are kept.  If you once
have to do this, you might then have problems referring to old working
orders via the api using the cross-session capability of the shim; it
depends on how much order state IB keeps upstream, and how much the IB
tws stores on your local machine.  In the event of such problems, the
best option I know is to fall back on manual processing via the IB tws
gui.

In addition, the IB tws does on occasion reset the tick id sequence back
to 1, I suspect monthly or quarterly for inactive client ids.  In this
case you would need to either start using another client id for risk
mode connections --- the natural and straight forward approach, and
what the shim and api protocol have been designed to accomodate --- or
else consider unloading your journal, if you are bound and determined to
have all orders made using the same client id.  Otherwise, unique key
violations for the OrderStatus, ActiveOrder, and OrderReport tables
almost certainly would occur.

You ask also about the account code attribute, "acc".  In a common idiom
I use for text-keyed tables, tables with foreign keys to such a table
use just one such key, either the uid or text key, and I call that
foreign key by a single name, here "acc".  So, some of the order journal
tables have a character string for the account code, mapping back to
AccountCode.code, and some have a natural number, equivalent to
AccountCode.uid; and in each case, named "acc".   In the create table
statements, you can always see what the local attribute name of "acc"
maps to via references declarations, e.g., each of the following lines
occurs in sql/xact.sql , though in distinct tables:

    acc         int unsigned not null references  AccountCode(uid),
    ...
    acc         varchar( 16) not null references  AccountCode(code),

So, "acc" always refers to the same concept, a foreign key into the
AccountCode table, though which key it refers to varies.

> Earlier I was using columns order_tag, acc, client_id as primary
> key for joins on these tables. However, acc seems to have a completly
> different meaning as in ActiveOrder - where acc is simply the account
> number as it seems.    

No, (acc, client_id, order_tag) triples should make perfectly fine
unique keys as long as the IB tws has not reset your order_tag offset
back to 1.  CreateEvent.acc maps to AccountCode.uid, and as you say,
ActiveOrder.acc maps to AccountCode.code , and again, you can see this
via the references declarations.

> I am not quite shure how order_tag is generated - but I get not
> quite the expected result if I do for example 

>   select ...
>    where ...
>      and OrderStatus.acc = CreateEvent.acc
        -- ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ always false, given the
        --                                   char prefix to accounts
       ...

Try, for your select statement, e.g.:

    select CreateEvent.time,
           CreateEvent.oid,
           OrderStatus.filled,
           OrderStatus.avg_fill
      from CreateEvent, OrderStatus, AccountCode
     where OrderStatus.client_id = CreateEvent.client_id
       and OrderStatus.order_tag = CreateEvent.order_tag
       and OrderStatus.acc       = AccountCode.code
       and CreateEvent.acc       = AccountCode.uid
       and status = 'Filled';
 
I hope the above makes sense, and please feel free to ask related
questions if I missed some issue in the list you raised.  Otherwise,
I believe this reply covers each of the points you raised.

Thanks,

Bill