Spring cleaning, I know I’m a month early but purge is an important task and sometimes you need to make sure it is adapted to your environment and needs.
Purging the database will let you keep the database growth under control and ensure that you get the maximum performance out of the system. But there’s often a fear that purging will result in data loss and quickly you find yourself with massive retention periods, 7 years for trade, 2 years of daily market data and all logs.
The first to keep in mind: Murex is a production system for trading and processing, it is not a data repository system. You need to keep it running in top shape as to maximize the benefits you get from the system. If you need to retain some data stored in Murex, export and store it on your own system. It is much cheaper and more appropriate.
This might sound obvious but when talking about purge, regulation is often the first topic that comes and it blocks any further discussion as long as a solution for storing all the data to be purged has not been implemented.
Once everyone is convinced of the importance of the purge, there are multiple items to purge by importance:
– Documents and their entries (usually ranking at number 1 in DB usage)
– Market data (normally ranking at number 2)
– Trades
– Logs
– Static data
– The forgotten ones: view, layouts, filters
Documents
Purging Mxmlexchange is actually quite straight forward and is done through scripts provided by Murex. Just be very careful with the scripts and ensure that proper testing is done on test environments before deploying to production.
But if you test it properly and only purge the intermediate documents, it is quite straight forward without surprises
Market data
Market data is made of 2 parts. The visible side of the iceberg where you purge market data for dates you no longer need (good practice tends to let people keep the month end only for older dates and daily market data for few months (1-3 depending on your aggressiveness). This can be done through the GUI if you want, quite straight forward.
But there’s also a second part of market data purge which helps a lot: expired instruments (read Bonds and listed options mainly). By default, Murex automatically copies all market data entries from today to tomorrow as part of EOD. This automatic copy means you also have entries for expired listed options (ETOs), futures or bonds which keep being rolled. It might not sound like much but ETOs can quickly snowball especially if you trade very short dated ones such as intradays and overnight. Here, Murex can provide you with a script to clean them out. Symptom for this second one are tables such as MP*_GLOB and MP*_PRIC being large in size.
Trades
Trade purging makes sense especially when you do volume trading. The trade purging is done through the GUI (very important) and in such a fashion that all purged positions are getting aggregated to avoid any jump in cash balances.
The trade purge occurs in 2 steps: a logical one, where the trade is no longer read for reports and simulation but is still present in the database. All its contributions are stored and aggregated with other purged deals. It can be undone if required
The physical purge will effectively remove the trade from the system, you cannot anymore query it and it cannot be reversed.
Position and cash balances testing needs to be performed after each purge step. After the logical purge, it is the most important as Murex will no longer evaluate the trade but read directly its stored contribution. After the physical purge could almost be skipped as it does not affect anymore the aggregated results, it is simply removing the unused trade records.
Trade purging depends on the trade complexity, simple spot forwards can (and should) be purged much more aggressively than more structured deals
Logs and audit
Murex will give you the scripts for these, purge as requested and make a copy if you feel the need upfront. They don’t consume much space but clean logs make browsing through them a lot easier!
Static data
I am actually an advocate against purging static data. Murex often references static data under the purged deal contributions or in other places and removing them, will remove that link for Murex. One could always try to fix all the problems which ensure out of it but in my opinion, it is simply not worth it. The amount of problems generated (and which could come later during or after an upgrade) is not worth the small amount of DB it occupies.
Filter, layouts, views, etc…
These items should not be purged per se but should be kept under control. Restraining users from creating, duplicating is probably the way to go.
To clean them up would probably have not much of an impact on the database but you risk that an EOD report or a process would fail. Except if you have kept a very precise list of which items are used by what process (and if you did, kudos!), you probably have to leave them where they are or start a massive campaign identifying and decommissioning the unwanted ones.
In summary, if you concentrate on the top 4 items of this list, your DB should grow as expected when the hardware was planned with Murex and performances will remain optimal. Just keep an eye on the DB usage by table and if something grow too quickly, Murex will always be happy to sort you out!
If I forgot something or if you feel like to add something, please feel free to!