There are many design aspects for DB2 Temporal Tables that I have discussed in some of my previous blog entries. While reviewing the design of a recent data warehouse system, it became apparent that many index design points weren’t evaluated and were causing overall poor application DB2 performance. These same index design points are especially important for application designs referencing DB2 Temporal Tables. Since DB2 Temporal Tables are adding the System-Time and Business-Time dimensions through their definitions, indexing these dimensions properly is essential for application DB2 performance.
First, understand the amount of duplicate values that are contained within the dimension columns. Since DB2 Temporal Table applications are going to be referencing the System-Time and/or Business-Time dimensions with their “AS OF SQL” keywords, make sure there are indexes over these dimension columns. Unfortunately, these dimension columns may contain the same date for a large portion of the table and indexes that only have these dimension columns in their definitions may contain too many duplicates and index access performance can suffer. Make sure to evaluate the number of rows referenced when the application uses these System-Time and/or the Business-Time dimensions within DB2 Temporal Tables.
Second, combine the dimension columns with another commonly used unique column or columns. DB2 Temporal Table performance, just like regular DB2 table performance needs unique indexes to provide cardinality and responsive application access. Find the most popular unique key used within the application and combine it with the System-Time and/or the Business-Time dimension columns to create a unique dimensional index if possible. Evaluate this newly combined index further to determine whether it can also be defined as the clustering index within the DB2 Temporal Table. Clustering definitions should be used within all your table designs and especially to help your DB2 Temporal Table performance.
Third, understand the DB2 10 advantages/restrictions about DB2 Temporal Tables and their indexes. The DB2 10 advantages are that the unique column definition with the new INCLUDE feature allows additional non-unique columns added into a unique index definition. DB2 10 allows the System-Time and/or the Business-Time dimension columns to be INCLUDEd columns within these new index definitions. This provides extra index filtering and improved performance by referencing all the SQL’s WHERE clause query columns within a single index. This is another reason to quickly migrate to DB2 10 to leverage this INCLUDE feature within all your tables and especially to improve your DB2 Temporal Table index performance as much as possible.
_____________________________________________________
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”. The new meeting agenda information should be posted soon but more information can be found at: http://www.bwdb2ug.org/index.htm
Coming up Sept. 29th DB2 10 for z/OS Performance Training and 30th SQL Performance Training two great DB2 classes are being offered in Washington DC: Sept. 29th DB2 10 for z/OS Performance Training and 30th SQL Performance Training. Get more information here (http://davebeulke.com/db2-performance-tuning-and-sql-training/)
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