In previous blog entries I have talked about how to set up your DB2 temporal tables and how DB2 10 handles the temporal considerations nicely through the SYSTEM_TIME and BUSINESS_TIME definitions. Understanding all the implications of DB2 temporal or DB2 bi-temporal tables is something that you need to model properly to understand the implications of the various time dimensions that you want to examine against your data.
Since DB2 handles the temporal nature of the information through its standard temporal tables and its history tables, your database can reflect all temporal time aspects. When modeling the different time dimensions, three important ideas need to be analyzed to make sure your data reflects the values and time dimensions properly within your temporal tables.
First: You must identify all the time critical data within your temporal tables. This may seem obvious, but data modeling and over-normalization can split data apart. DB2 temporal tables business modeling use cases need to properly reflect the current, at-this-moment of time of the query state of the data. The SYSTEM_TIME and BUSINESS_TIME will give you the current data row so your business use case must identify all the critical application data dependent on temporal time key(s). By making sure everything is dependent on your temporal time SYSTEM_TIME and/or BUSINESS_TIME keys your DB2 temporal tables can give your business users the correct information.
Second: Make sure your data model reflects the temporal table value differences properly. DB2 temporal tables reflect time and what changed between now and then is a common question. This “what is different” question needs to be answered in some way to quantify the difference. Character code columns are good within a database design but a change in values from “AA” to “XX” does not easily reflect a quantifiable value. In-depth knowledge of the business representative values of “AA” and “XX” would be needed by the end user to properly understand the business value of the code change. Your DB2 temporal tables needs to represent the temporal data values properly, usually in numbers, so the data itself can demonstrate the intrinsic value between the different points in time within your DB2 temporal tables.
Third: Model and understand your data change frequency and the timing change. DB2 temporal tables are wonderful because they reflect the values of your data right now, last week and three years ago. Unfortunately, every single change in your time dependent data will be in your DB2 temporal tables. This can be an enormous amount of data since sometimes many departments or many processes in each of those enterprise departments update the DB2 temporal tables’ data. Each of these data updates are saved and reflected in the DB2 temporal tables, expanding the scope of the change grain within your DB2 temporal and history tables. Make sure to model each update and understand the number of update processes so you can build the DB2 temporal tables big enough to be able to hold and reflect the data that your business needs. Also understand what the business needs so that queries can reflect when all the critical processes are completed and all the temporal data is consistent for all your business use cases.
Modeling and designing your database environment is always a normalization discussion and exercise. DB2 temporal tables require you and your team to take a little extra time to analyze all its data points in time, understand how to quantify its differences and understand its update frequency and timing considerations. Once the analysis of these three critical issues is completed, you and your team will be on your way to success with DB2 temporal tables.
I am speaking at the upcoming Baltimore Washington DC DB2 Users group meeting September 14th where I will be presenting “DB2 Performance Database Discoveries and Recommendations Part 2.” If you are planning on attending, click here for the handout. The new meeting agenda information should be posted soon but more information can be found at: http://www.bwdb2ug.org/index.htm
Coming up: Two great DB2 classes are being offered in Washington DC: Sept. 29th – DB2 10 for z/OS Performance Training and on Sept. 30th – SQL Performance Training. Get more information here (http://davebeulke.com/db2-performance-tuning-and-sql-training/)
In addition to the DB2 community I also support the DAMA organization. As VP of DAMA in the National Capital Region I have helped organize the Washington DC DAMA Day Symposium – September 19th.
DAMA Day event has another spectacular line up of industry icons, industry award winners and experts. These presentations will help you understand the issues better, prepare you for upcoming discussions and help set your course for navigating the data management labyrinths.
It is another full day of great advice and techniques to take back to your companies and agencies. Please pass this agenda on to your friends and let your associates know about this outstanding event.
- Industry Icon John Zachman presenting his new “Intro to Sample ‘Primitive’ Models” from his Zachman Framework.
- Seth Grimes from Alta Plana Corporation presenting “Text, Content, and Social Analytics: BI for the New World”.
- Michael Simcock, from the U.S. Dept. of Homeland Security presenting “National Information Exchange Model (NIEM) and Enterprise Data Management”.
- Michele Koch, from Sallie Mae Governance presenting “How to Design Your Data Governance Program – Our Experiences at Sallie Mae”. Winner of the 2011 Data Governance Best Practice award.
- Micheline Casey, recently named one of the 2011 Top 25 Information Managers and advisor to the CIO of the Department of Energy, presenting “Leveraging Information for Governmental Transformation: the Colorado Story”
Free to Members! The fee for non-members is $70 for full year or $30 single meeting. Please note that there is a LIMITED space for attendees to this location and it will be first-come, first served.
All attendees need to register ahead of time (by September 16 at Noon – Sorry no exceptions) for security clearance into the meeting, so make sure to register today to reserve your spot.
Go to http://www.dama-ncr.org/regform.html to register to reserve your seat today!!
Also I will be presenting 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.
Come see me in any of these venues and ask me your data warehouse performance questions.
Leave a Reply