The Blueprint

This post is the result of a recent conversation about the importance of proper data modeling with my long time friend and teammate Dan Cruikshank.

For the readers who are not familiar with Dan, he is the person who invented the concept of database modernization and the methodology to pull it off in a responsible way; one of the many contributions Dan has provided to his appreciative clients around the world. We euphemistically refer to the IBM i database modernization process as "the Dan Plan".

Dan, the blog is yours...

______


About 15 years ago my wife and I jumped on an opportunity to buy a used home that was in foreclosure. The price was right, the location was fantastic; unfortunately the house was in rough shape.

There was no flooring in the main rooms; several of the walls had holes, who knew what the infrastructure was like.  We were looking at years of reconstruction and possibly 10’s of thousands of dollars in cost.

As we were going through the closets we discovered a set of the original blueprints. Suddenly years of work was now looking like weeks, and at a cost of 1’s of thousands of dollars.

About this same time my career at IBM took on a new slant. I was beginning to see that many of the performance issues I was then dealing with all seemed to be rooted around the same cause – a poor database design. IBM Rochester was launching the new SQE engine, which boasted brand new SQL capabilities that took advantage of the IBM i integrated relational data base "DB2 for i". Unfortunately many of the IBM i heritage customers were still using traditional record level access, let alone having a database that was properly designed for SQL set based access.

“Oh woe is me”, cried those customers who were now faced with a reconstruction nightmare – how to bring their applications and data into the new millennium without taking years of effort or spending millions of dollars on “modernization”. 

“If only we had been more diligent on documenting our applications”, lamented the growing number of CIOs who were now tasked with groveling for more budget dollars. “If only we had a blueprint!” they cried.

Never fear, there is a silver lining in this story. Hidden away, in a secret closet within the Rational suite of development tools, is something called the Data Perspective. The Data Perspective comes with Rational Business Developer, InfoSphere Data Architect and other bundled products; and it is included in the free (yes free) download of IBM Data Studio.

Within the Data Perspective is the Data Design Project tool. Using a Data Design Project, a database engineer can reverse engineer an existing set of tables (or DDS files) into a graphical physical data model, in other words a blueprint! This can be done for an entire schema (library) or for only those core files required to support the business.

But wait, there’s more.

Unlike the pencil drawings of yore, or the collection of shapes in a presentation tool, with a touch of a button the database engineer can generate the SQL Data Definition Language (DDL) statements from the physical data model. And, let me catch my breath, the DDL will be generated no matter if the originating source was DDS or SQL DDL. That is too cool.

And I almost left out the best part – the compare tool.

Imagine if I could have taken those original blueprints of the house, changed them and then pushed a button and my home would magically be transformed to match the blueprint. Not possible with home re-engineering projects but it is available with the Data Perspective. I can compare the graphical model or blueprint to the physical implementation and the tool will generate the appropriate ALTER, DROP and/or CREATE DDL statements, in either direction. I can apply emergency changes to the DB2 for i database and then sync up the model.

Of course having a blueprint is one thing, getting the re-engineering process right is another.

All projects are going to require some boots on the ground. In other words, the developers who have to make the changes. These "engineers" will require a little more detail, especially when ripping apart existing programs to expose the inner workings.

Oh joy, there is another secret closet in the Rational Developer tool box – The Visual Application Diagram maker. This device can take RPG or COBOL code and present it in graphical form. And what’s more, the engineer can click anywhere in the diagram to display the underlying code.

 
Whether you’re a database engineer or application developer, it is now time for you to take your skills to the next level. If you are not using the Rational tools then acquire them now. If you are using them, then don’t be afraid to explore some of those secret closets.

And if you're afraid of the dark, please reach out, we can provide some hand holding.