Thursday, March 26, 2009

Cost vs. Advantage of Moving From IMS DB to DB2

As my regular readers know, every now and then I like to share Q+A exchanges I've had with folks. Today, the question I was asked is as follows:

My customer is wondering about the possible advantages of converting his IMS DB/DC system to IMS DC/DB2. The application currently performs well with an internal response time of less than .5 seconds on average.

Even with an arrival rate of 425 full-function transactions per second, the queue count rarely goes above 10. This system typically peaks at 12.5 million transactions per twelve-hour day against HDAM and HIDAM databases totaling close to 1 terabyte. The application itself is currently a bit over seven million lines of code.

Can you comment on the relative cost vs. advantage of moving an existing application from IMS DB to DB2 along with relative CPU capacity requirements?

And here is my short response:

Well, the main advantages of converting from IMS/DB to DB2 would be to gain better support for ad hoc queries, standard SQL (instead of non-standard DL/1) for writing queries and a deeper pool of talent to support the DB2 environment (there are more DB2 folks out there than IMS folks now-a-days).

The benefit of sticking with IMS is the good performance you currently enjoy as well as no need to convert the database structures or the 7 million lines of application code. Converting database structures is not horribly difficult, but there are some gotchas that can arise. The bigger problem is converting all of those DL/1 calls to appropriate SQL. This will not be a simple 1 to 1 conversion and it will very likely be quite time-consuming.

I guess it boils down to this: How happy are you with the current application, are you able to support it properly and how many other IMS/DB databases do you support? If this is the last IMS/DB database and you are looking to rid yourself of the IMS license, then it might make sense to convert. But you should do a project plan and cost/benefit analysis before making your final decision (conversion can be very costly). If you have a lot of other IMS/DB databases, then it probably doesn't make a lot of sense to convert to DB2 unless you cannot support the needs of your end users (management, ad hoc support, etc.) using IMS.

In terms of CPU requirements, DB2 will consume more CPU than IMS. DB2 optimizes queries internally whereas IMS programmers construct access paths to data. This additional requirement will cause DB2 to consume more CPU. But, of course, that additional CPU brings with it the enormous benefit of database optimization and better ad hoc query support.

You might also want to take a look at a product like DL/2. I have never used it so I cannot recommend for or against its functionality, but it looks like it might save you some work.


troycoleman said...

I like the way you put this. The one subject you didn't touch on is on going development. I worked at a place that stopped all changes to IMS data structures and required all enhancements to the system in DB2. When they ran out of filler in the IMS segment they had to create a DB2 table to hold any new columns. I remember when they didn't have the zip code on a customer segment for some reason. They had no filler left so they had to create a DB2 table to old this information. Now this existing IMS only transaction had to call DB2 for the zip code. If you are in this situation, at some point you have to make the move and rewrite the application I think, or it will just keep costing you more and more to maintain. It's more complex and error prone and in fact over time you are updating the same data in IMS and DB2 to keep systems synchronized.

Craig S. Mullins said...

Thanks for that war story, Troy. It is amazing the lengths that organizations will go to in order to avoid conversion. The case you describe is, indeed, one where it is time to "bite the bullet" and convert... or maybe, instead of cobbling together a solution requiring DB2 for some "fields" they should have hired an IMS consultant to extend the segment length to include more room (for the ZIP code and a healthy amount of filler)...