Alright, today let’s crack open this black box that is the Murex database! While all of you know that Murex doesn’t publish its database organization, sometimes there is no choice than go directly where the data is.
My rule of thumb is that if one can avoid it, going direct to the database should be avoided. Any problem caused while browsing will have impacts and cause problems in the environment. For reporting, dynamic tables or viewer reports are your friends. For filtering, list of fields is actually quite exhaustive. In many cases, you will find all the information you need without opening any single SQL client. But sometimes, for some filters (back to RQWHERE post!), for some reporting or for some DB cleaning, you’ll need to go through the database.
Working with Murex database is the same as working with any other trading system database: backup, test in test environments, test again, backup and it should work. The problem is that sometimes some fields are not very clear as to what their roles are and when trying to populate lines (insertion or update), this could turn out to be a real problem. Murex consultants are then the best suited to help you out, especially if you’re not sure your request is safe. In case of migrations, again, Murex consultants should be the ones to provide you with the right scripts, only write yours when you’re absolutely confident of what you’re doing.
Now from a Murex consultant point of view, it is not always easy either to determine what fields have what roles. But the first step is to understand what the other party is trying to do. Maybe SQL is not the best way forward and there could be an easier solution?
Then you can check what other people have done. It is rare to have a problem with only 1 customer that has not been encountered by somebody else.
I learned SQL while working at Murex and many times it actually sped up processes tremendously:
– Inserting in bulk some data (or duplicating records)
– Cleaning up unwanted data. Especially logs (or market data, much much faster)
– Building my own extractions when doing reconciliation reporting
But it also happened that my scripts did not work as expected (and lucky I had a backup and was doing it on a test environment): updates/delete without a correct where condition. I once removed all records from the transaction header!
If you’re working on a limited set of tables and you don’t want to call upon the DBAs to do the backup, then you
can should use the following tools: Help-Monitor-DPI info-Transfer from RDB to DBF. You will need an authorization code to proceed but then you can transfer the table from the database to a file in the application server file system. The step Transfer from DBF to RDB does just the opposite. So it gives you the flexibility to backup any table you want from the database to the file system and bring it back whenever required.
Note that you can use jokers in the name of the table you wish to transfer and you should not put _DBF but .dbf.
And you? What’s your relationship with SQL? Comments and experiences below if you wish!