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?
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.
Dig Deeper on Data modeling tools and techniques
Related Q&A from Craig S. Mullins
Our expert suggests that the best way to pass the SAP BW exam is to have hands-on job experience. But that's not all he has to say about ... Continue Reading
To export data from a DB2 table to a flat file, you need to run an export specifying the proper file format. The export utility exports data from a ... Continue Reading
Craig Mullins recommends two specific resources for learning how to create and support Binary Large Objects (BLOB) in DB2. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.