PTOM: Black-box analysis of legacy applications

It seems like such a great situation, you’ve been tasked with replacing an old legacy system with a shiny new Web 3.0 AJAX-ified replacement.  It’s your chance for the limelight as the knight in shining armor to come charging in with a new system that will *surely* blow away the old one.  That is, until you get mired in the problem of requirements coming in of “make it work like the other system”.

As a consultant, it can take quite a while to extract what “success” means, especially as you might have so many different roles with widely differing opinions of what the new system should do.  The CIO wants whatever the buzzword of the month is, probably SOA or “Services”.  The sales team wants something “frickin sweet” and “snazzy”, while the IT department likely hates your guts, as you’re replacing the system they built.  Talk to the people actually using the existing system, and they probably hate it, but don’t want to change.  Listen solely to them and you’ll get another mainframe application, developed in .NET.

With a large existing system, replacing what already exists can’t come in one fell swoop.  Often, you’ll need to carve out subsystems of functionality, each of which likely needed its own system to begin with.  I never fault the original developers of these systems, as many were built at least 10 years ago, some 20.  It’s hard to build an application to last 20 years, especially if your business grows beyond what anyone might have imagined.

Back to replacing a legacy system, we have to understand what the existing Godillapp does.  Recently, I worked on a project to replace a large chunk of a huge legacy system.  Some stats of the existing codebase were:

  • 1 million lines of SQL
  • 1.2 million lines of COBOL….NET
  • 800K lines of VB.NET

This system did everything and was the lifeblood of a billion-dollar corporation.  Of course, it wasn’t a billion-dollar corporation when the system was first written 20 years ago, and “replaced” 5 years ago, but it did take the company a long way.  As a part of this experience, we gained a few nuggets and strategies for dealing with these types of engagements in the future, and what follows is a list of those lessons learned.

Lesson #1 – What it does versus how it’s used

When we talked to various people about this application, we encountered two different stories.  One was from stakeholders who created the application, and one was from the people who used it.  The people who created it could tell us exactly what the application did.  They talked about major areas, features, database tables, screens and so on.

But one thing was missing – how did people use the system?  For that, we talked to the actual end users.

Which showed a completely different story.  There was a lot of “swivel chair” integration going on, and users’ cubicles were covered in little post-it notes, cheat sheets and reference guides to serve as a living instruction manual.  Does that customer have a discount?  Well, put a note in the order comments field so that Cheryl in accounting remembers to note that in the invoice.  These insights into end-user behavior gave us knowledge that merely looking at the application, code and database would have obscured and even misled.  This, combined with input from all stakeholders, along with vision of where the company wanted the application to go, gave us direction into the system we needed to build.

But we still needed to replicate existing functionality.

Lesson #2 – Craft your own story of the data

The application’s database was about the most interesting I’d ever seen.  It was ported from a mainframe, pretty much straight over.  This meant:

  • Lots of fixed-width columns
  • No referential integrity
  • Evolving architectural style

For the core tables, we might have OLD_NMNG_OF_CLMNS, where as new tables followed tbl_hungarian notation.  To get around this, we exported the schema into an entirely new database, and created associations between tables as we found them.  We didn’t bother with all of the original columns, just associations, so that we could understand what related to what.  We couldn’t modify the existing database, but a playground of just the schema let us create reference diagrams of the “real” relationships of the tables.

Next, we created searchable schema.  We created a table that included:

  • Database name
  • Table name
  • Column name
  • Column data type

Yes, I know this was available from the schema views, but we needed something that was super simple to query from.  With a system of hundreds and hundreds of tables, it helped to do things like “SELECT * FROM dbSchema WHERE colname LIKE ‘%PRODUCT_NUM%’”.  We didn’t know what was related to a product, but we could make an educated guess.  Since the original database had zero foreign key constraints and thousands of stored procedures, we needed to be able to take large guesses of how data was related to each other.  Existing developers might be able to give us direction, but with a system this big, we could only really rely on the actual system.

Lesson #3 – The data don’t lie

This one’s from the phrase “ball don’t lie”.  You come to the business owners and ask, “what shipping codes do we need to support?”.  The answer is “all of them”.  There aren’t that many shipping services in the world, yet you find a hundred different ship codes in the database lookup table.  Do you really need to support all of these shipping codes?  Probably not.

But to answer this, we need to gather more information on what these values mean.  Are some of these just test shipping codes that some developer put in the system six years ago?  Are some for obsolete shipping methods?  Again, we turned to analyzing the existing data.  It took a long time to run this batch process (around 4 hours), but we crafted another table that contained:

  • Column name
  • Column value
  • Number of occurrences
  • Percentage of total
  • Last date of value

Essentially, we would group a table by a column, one by one, gathering statistics on how much each value was used, and when it was last used.  This gave us the hard data that said, “see that code ‘OC4’? It was last used 5 years ago, and only twice”.  With that information, we could dive in to the original database, and find that the person who created these orders was “Cosmo Kramer”.  With this information, we could figure out exactly what kinds of things we needed to support.

Additionally, we were able to provide hard data on who used what features.  We might find a certain shipping code used only 100 times, a fraction of the total, but these were used by one of the company’s biggest customers, so it suddenly jumped in importance.  It was still the customer who decided what to support, but we were able to give them a much, much better picture of the data than many of their seasoned veterans did not know.

If you can’t run a big batch data mining process against the database, I suggest you learn two SQL techniques: the GROUP BY clause, and derived tables.  The general idea is to first create an inner query where you group by values, and then an outer query that performs additional grouping and statistics.  This gives you quick information like “what percentage of total orders used the OC4 shipping code?”  Very useful information for decision makers.

Lesson #4 – Profile, profile, profile

In a codebase that’s large enough, debugging code is counterproductive.  Instead, we can take the route of poking an application on one side, and seeing what comes out the other, while listening in to the conversation using a SQL profiler (or network profiler).  In this manner, we can do things like add a line item to an order, and see how the application performs that operation.  In one example, we added a single line item to an order and over 280 queries executed to perform that one operation.

We might find common stored procedure names, and go back the code armed with a textual search to see exactly what code was executed.  While the SQL codebase had over a million lines, profiling told us that only a fraction of this was actually used.  Because there was no database change management system in place, we saw a hundred different sprocs to retrieve an order.  Instead of using an existing sproc, it was easier just to create a new one.

As we profiled more and more, we combined this knowledge back into our metadata database, adding comments to what the purpose of each table was.  The list of “important” tables was eventually whittled down to what you might expect, and many were just a bunch of features that never panned out, cancelled projects and so on.  With all of our information, we could almost look backwards in time to see the entire history of the company.

Lesson #5 – Dump the negativity

Although it’s easy to trash-talk the existing system, it won’t make you any friends with the existing IT folks.  No one likes having their baby called ugly, even it fell off the ugly tree and hit every branch on the way down.  You don’t know the political situations for the last 20 years, nor the different forces at play.  These systems put a lot of food on a lot of people’s plate, but no longer help the company grow.  The only thing you can criticize is a failure to recognize when the existing system no longer matches the company’s needs.  Other than that, best to keep positive, stay focused on delivering value, and just *quietly* make notes for future Daily WTF contributions.

Wrapping it up

Replacing a legacy system is costly and risky.  Anyone involved with projects like these can attest to the frequency of failures in this arena.  But armed with a well-thought out strategy, we can maximize the possibility of success.  Nothing’s guaranteed of course, but at the very least, we’ll have vastly increased the knowledge of the existing system.

Related Articles:

Post Footer automatically generated by Add Post Footer Plugin for wordpress.

About Jimmy Bogard

I'm a technical architect with Headspring in Austin, TX. I focus on DDD, distributed systems, and any other acronym-centric design/architecture/methodology. I created AutoMapper and am a co-author of the ASP.NET MVC in Action books.
This entry was posted in Legacy Code. Bookmark the permalink. Follow any comments here with the RSS feed for this post.
  • Neil

    Great article.

  • http://zvolkov.com zvolkov

    At one point I promised to myself to never ever take on projects like this. If you do take it though, make sure you charge 3 times more $$.

  • http://www.blogcoward.com jdn

    Great post. Like the ugly tree analogy…lol

  • http://www.preemptive.com Joe Kuemerle

    I would love to hear your perspective on how one of my companies products may help with this style of project.

    It is called Runtime Intelligence and it provides a way to inject new functionality into existing applications (either .NET or Java only at the moment) so that they send their usage data back to a central collection and reporting portal at runtime. This would allow you to actually track what the users are realy doing within the application. The reporting gives an overview of the usgae of the application, the relative popularity of each instrumented feature and can even provide metrics on how long users spend inside of particular methods. Using the data you can even recreate the actual execution path in chronological order that a given user took in a specified runtime instance of the application.

    Since this new functionality is injected directly into the application binary (in a similar manner to an AOP frameworks post compile weaving implementation) the application can be instrumented to report on its usage without requiring any source code changes.

    Since I am a developer on this product and not in sales or marketing I look forward to hearing feedback and discussion on this idea as I think it could be beneficial. I also would appreciate any direct feedback on other ideas that could be implemented with this technology as I’m always looking for use cases.

    For a direct conversation I can be reached directly at my work email : jkuemerle at preemptive . com .

    As for the obligatory product plug this functionality is not only available in our commercial products (Dotfuscator and DashO) but is also now inside of the new version of Dotfuscator Community Edition shipping for free in Visual Studio 2010 (and is in the current public beta). Not only is the code injection functionality in there but a free version of the data collection and reporting portal is also available so you can see some of the reports that are available out of the box without having to do any sort of registration.

  • http://www.maketechnologies.com Greg Ogburn

    If you encounter another project like this, I’d love to talk to you about it before you get started. Have a look at http://www.maketechnologies.com for legacy modernization software solution that implements some of your ideas repeatably.