When I designed and implemented my first data warehouse for E.F. Hutton back in 1988, I quickly understood that the quality of the data answers were the most important measure of success. The quality was measured in terms of the completeness, timeliness, and how quickly the user could go from question to answer. DB2 10 temporal tables make achieving those measures of success easier, because they provide a substantial infrastructure within DB2 for the business to understand and get answers.
DB2 10 temporal tables help you provide these measures of success through the use of the BUSINESS and SYSTEM time dimensions. To design and set up these items in your DB2 10 temporal table you need to analyze your data answers from three different angles.
First: Analyze how complete your temporal data answers are and how they relate to other data components within your company. The DB2 temporal tables provide a great way to provide financial, inventory or policy types of information. The important analysis point is to make sure the temporal data can be related to a complete story or picture of the business situation.
For example the DB2 temporal table information can tell the status and type of insurance in force at any point in time. If the policy was changed over time your design needs to be able to tell how it got to that status or what events occurred. The DB2 temporal table policy holder history data and the stored keys that relate to other aspects of your business need to be design so the complete story can be told.
Next: Analyze how to define the best timeliness for data. DB2 10 temporal table components SYSTEM and BUSINESS time have similar design and syntax capabilities, but their temporal table attributes are processed in different ways.
When the processing happens the SYSTEM time is recorded. So your processing always needs to be real-time, on time or else the DB2 temporal table SYSTEM time won’t reflect the proper time for your data. Making sure your SYSTEM time period is appropriate for your application and making sure your application processing is on time is especially important for any data dependencies or batch processing within the system.
The DB2 temporal table BUSINESS time is set by the application processing and can be set to any time needed for the application regardless of the processing schedule. This flexibility provides the database design and application developer the ability to implement the solutions that provide the complete and best answers for end users.
Last: Analyze how much data is required for the answers or is being retrieved from your DB2 temporal table design. The time period or grain of your DB2 temporal table is set up through your SYSTEM and BUSINESS temporal time period definitions. DB2 10 temporal table designs can provide a variety of grains through the use of various DATE and TIMESTAMP definitions.
By understanding how much data is needed for your answers you will be able to determine if the complete answers can be built through referencing the main temporal table or through the DB2 temporal table and its history table. Making sure your answers are efficient is paramount to the overall performance of your DB2 temporal table design.
So look into these three aspects before defining your DB2 temporal tables. By analyzing these issues, your DB2 temporal tables and data warehousing application will have complete, timely and quick answers for all your business questions.
I will be speaking at the IBM Information on Demand Conference in Las Vegas. I will be presenting my “DB2 10 Temporal Database Designs for Performance” on Tuesday, October 25th, 10:00 AM -11:00 AM in the Mandalay North Convention Center Islander D. If you are registered for the conference, make sure to add this session to your schedule. For more details on my presentation click here. I am also arranging a get together for colleagues either Tuesday or Wednesday night before the IBM festivities start. Please let me know if you are attending and maybe we can talk about your application and performance issues.
I will also be speaking at the International DB2 User Group (IDUG) European Conference in Prague. I will be presenting my “DB2 10 Temporal Database Designs for Performance” on Mon, November 14, 1:30 PM – 2:30 PM. For more details on my presentation click here. I will also be involved in the Data Warehousing Special Interest Group (SIG) and the final discussion panel, so please join all the presentations, networking and discussions on DB2. Sign up now at www.idug.org
Finally, I am going to be presenting at the Midwest DB2 Users groups in the 4th quarter. I will be doing Minneapolis on December 6; Milwaukee, Wisconsin (http://www.wdug.com/) on December 7; and Chicago, Illinois (http://www.mwdug.org/) on December 8. I look forward to seeing everyone at the meetings to discuss all their plans for the 2012 year.
Come see me in any of these venues and ask me your DB2 performance or data warehouse performance questions.
Leave a Reply