CC Image courtesy of paille-fr on Flickr

Here’s a situation many companies face. Long ago, your company set up some software to support your business. After years of largely trouble-free operation, you need to make changes based on some new business activity. Now you find yourself saddled with a problem: although your IT department has kept the system running, the system vendor is no longer in business. Furthermore, when you discuss the problem internally, you are alerted to the concern that the software depends on outdated and fragile systems (e.g. OS and database). What should you do?

A recent approach in manufacturing

We have been contacted on a number of occasions by clients in exactly this scenario. Most recently, a manufacturer needed an improved dashboard to monitor newly agreed metrics for their business, but were struggling since the data capture system/platform – which had been working absolutely fine to that point – was “untouchable” for exactly these reasons.

On this occasion, we were able to help very quickly. Using the database structures and code, together with some reporting queries used by the client, we were able to work out how to obtain the information we needed. Then, we set up a small website to generate the required reporting, as well as automating and streamlining a previously time-consuming manual process that had been required to set up a critical spreadsheet used by the business. All of this was carried out without having to make any changes to the existing system, within an extremely tight timescale. This also gave them some breathing space to consider how to deal with the problem for the best – as well as a possible migration route, building on this stopgap solution which already has access to the historic data in the system.

How we did it

For the technically minded, here is how we dealt with the issue, which related to software based on a rather old, unsupported version of Oracle.

  1. We set up our own instance of Oracle, using the extremely useful Oracle Database Express 11G. Installation and setup were relatively straightforward. The two main benefits here were: although restricted slightly compared to their larger offerings, it is free to download and use; it is able to interoperate well with old versions of Oracle.
  2. We created a “database link” in our database, that connected to the client’s old Oracle database using the account details that their client software worked with. Through this link we were able to then retrieve any data needed by the application on demand. Crucially, no changes whatsoever were required in the client’s database.
  3. In our database, we created tables holding data that was required to “enrich” that stored in the old database. These were useful to e.g. identify key values we were interested in, add our own labels, and so on. We also set up a number of “views” to pull the data we needed and transform it using our local tables and other logic.
  4. The front-end (an ASP.Net web application) then used our Oracle instance as required, which in turn retrieved data from the client’s database.

The main pitfalls to watch out for relate to performance of queries that join tables in the old and new databases. For whatever reason, the database was at times inclined to choose an execution plan requiring a lot of back-and-forth communication between the servers. Although our link was fast, latency delay in these communications meant that queries could take very much too long to run. We dealt with this using some judicious “hinting” to encourage the behaviour we wanted. The hints we found most useful were:

  • /*+ INDEX(old_table idx_name) */ – to encourage index use rather than table scans
  • /*+ USE_HASH(alias1 alias2) */ – to discourage nested loops in favour of hash joins, where needed (loops across databases were particularly painful)
  • /*+ DRIVING_SITE(alias) */ – to encourage parts of the query to run in one database or another, where joining across databases
  • /*+ LEADING(alias1 alias2) */ – to suggest join ordering necessary for the performance we needed

 

Previous example in Marketing

When a similar problem came up on a previous occasion, we were were also able to help. The client had an important management system developed for them by a well-known software/systems company. After an extended period of performance and other problems with the system, and unhappy at the way that work was progressing, the client approach us to see if we could help in any way. This time, the “source code” for the system was available, meaning that we were able to actually take on development of the software. Starting from their working (but slow and problematic) system, we worked with the client to prioritise their issues and development needs, ending up with software that entirely met their ongoing requirements.

The platform and database were very different here – the client had a Delphi application on Windows using Oracle OLAP Express, which is no longer available as a stand-alone product, although the capability has been moved into Oracle Enterprise Edition where it continues to work very well! On this occasion, our preparation entailed:

  1. Setting up our own version control system into which we imported their code.
  2. Obtaining a development version of the database, so that we could run their system locally.
  3. Working through the system, and source code, to get a sense as to what it could do and how it did it.
  4. Liaising with their team to prioritise next steps based on what they needed and how much effort was required to make changes.