Problem solve Get help with specific problems with your technologies, process and projects.

VSAM to DB2 migration project design

Craig Mullins offers his opinion about a SearchDataManagement.com member's design for a migration of data from VSAM to DB2, which creates 4-5 MQTs that will hold data pertaining to 40+ DB2 tables.

I am currently working on a VSAM to DB2 migration project. As part of our data model, we have arrived at about 200 DB2 target tables from around 50 files. There is at least one COBOL program which needs to access 40+ DB2 tables to do core daily processing.

Given the number of tables to be accessed (40+), the idea we currently have is to use the DB2 V8 feature of MQTs....

So, we are planning to create 4 to 5 MQTs, which will hold data pertaining to the 40+ DB2 tables we have.

The point is, the MQTs will not really be used as Summary tables, but rather to just enable one SELECT operation to retrieve lots of columns. In terms of volume, we have close to 2.5MM rows.

How good a design is the above in terms of performance?

Well, in terms of query performance it sounds like a good decision. With an MQT, the data is gathered and stored physically so that the next time you access the data you do not have to go through the machinations of joining, etc. So the same query going against an MQT query can run faster than going against the base tables.

But a lot depends on how static the data is. Are you aware that an MQT is not updated immediately when the underlying base tables are updated? So if you have an MQT joining EMPLOYEE to DEPARTMENT, and then you delete an employee row, that employee will still show up in the MQT until you run a refresh of the data. If the data in the underlying tables is quite volatile, then MQTs are probably not the appropriate choice for you.

My first question would be this: did you test to make sure that performance will be a problem without the MQTs? It may be, but then again, it might not be. With proper database design and appropriate indexing you might be OK without the MQT. This is not a question that can be adequately addressed in an online forum. I suggest that you consider hiring a skilled DB2 consultant for a couple of days to present your design to him/her, and solicit feedback and suggestions. If you are looking for help on a good consultant, YL&A has some good DB2 talent.

This was last published in February 2007

Dig Deeper on Data modeling tools and techniques

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.