DB2 Temporal Data: Three More Reasons the Ultimate Server, z/OS, Leads the Industry

Having just designed and implemented a 22-billion-row data warehouse on DB2 z/OS I have seen first-hand the incredible performance of DB2 Version 9 with a z/10 configuration take report processes from 37 hours to minutes or sub-second response time. Within the new DB2 10 z/OS enhancements, the DB2 temporal data capabilities and the new accelerator analytics features, the biggest server of them all, z/OS, continues to lead the industry.

DB2 10 with its new DB2 temporal data capabilities provides great features that are unique among all DBMSs in the industry. Three of the many features within temporal tables provide temporal data functionality that everyone should start thinking of for their next database design.

First: DB2 temporal data can show you the state of the business at any point in time. With DB2 temporal data, the database designer and end-user can ask and process time-based queries against your business data. Showing the state of the business, customer, finances, policy or any type of situation can be specifically constrained to the past or the future. By being able to ask time based questions about the business, the end user can reconstruct or project the business occurrence and better understand the state of the business, its processes and scenarios that have led or will lead to a business situation. This temporal data knowledge provides business analysts and all types of users with new views of the state of the business and gives incredible insights and capabilities to help your business grow.

Second: DB2 temporal data and the new timestamp capabilities provide unique identifiers within the entire table. Within the definition of the new DB2 temporal tables, the database administrator can leverage the new timestamp definition of picoseconds and the attribute of “no overlapping of any values” (TIMESTAMP(12)WITH NO OVERLAP). This definition provides 12 unique positions to the right of the second value and DB2 guarantees a unique value for every row within the overall DB2 temporal table. With the maximum value of 9999-12-31-24:00:00.000000000000 a DB2 temporal table can handle temporal data today and well into the future.

Third: DB2 temporal data can also be supported by a separate history table. The history table contains all the previous states of the base business data. With the proper SQL, DB2 temporal tables provide the ability to go back in time and get the status of the business, customer, finances, policy or any pertinent state at a particular point in time. By using the temporal data in the history table, the business situation can be detailed for any compliance, privacy or regulatory requirement, removing the burden of complex reporting and archiving processes from IT personnel.

Think of DB2 temporal tables for your next system design to provide past and future insights into your business, unique transaction timestamps and great reporting and compliance capabilities built into your system. Remember that the ultimate server–DB2 10 for z/OS–provides these unique features, including temporal data analysis, and is another reason DB2 continues to lead the industry.


I will 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. I will also be presenting my “Database Performance Discoveries and Recommendations” on Tuesday. For more details on my presentation click here. (http://www.idug.org/e/in/eid=2&req=info&s=97&all=1) 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

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>