While talking to friend of mine, I learned that DB2 10 Temporal Tables continue to be a big incentive to migrate as soon as possible to DB2 10 either from DB2 Version 9 or even DB2 Version 8. DB2 Temporal Tables can dramatically improve performance of your applications that are already maintaining timestamps or dates that represent temporal type information. The DB2 Temporal Table performance improvement comes from eliminating the extra processing that your applications are doing and more efficient SQL.
Let’s say your application maintains its product, policy, customer or other effective dates. Many shops have many instances of this type of processing that can easily be replaced by DB2 Temporal Tables. These dates are usually maintained through regular processing and also through additional stored procedures or triggers. These extra processes are common for this type of date maintenance because using triggers and stored procedures centralizes the business rules processing. Two quick DB2 Catalog queries against SELECT * FROM SYSIBM.SYSROUTINES and SELECT * FROM SYSIBM.SYSTRIGGERS easily identify all the extra triggers, user defined functions, cast functions and stored procedures against a table that contains your effective dates and that can be potentially replaced by DB2 Temporal Tables.
DB2 10 Temporal Tables improve performance by eliminating all these extra calls within your application processing and automatically takes care of the dates through the built-in DB2 temporal table infrastructure. The extra work to maintain your existing dates is eliminated from core processing within your system, saving you time and CPU costs. Additionally, DB2 10 Temporal Table processing maintains your data properly by keeping the current information in the main table and pushing all the old processed data into the DB2 temporal history table.
The application SQL processing is also improved when using DB2 Temporal Tables. Instead of multiple SQL WHERE clause predicates expressing a date range, much simpler SQL with the new DB2 10 – “FOR PORTION OF BUSINESS_TIME FROM value1 TO value2” phrase can provide the application query results. This new phrase greatly simplifies the SQL and of course the DB2 optimizer recognizes the new keywords with a good access processing path. Also the “AS OF SQL” keywords for processing against DB2 Temporal Tables provide ease of use and help to the developer by improving their testing and implementation time.
So analyze the dates within your tables and their supporting applications and you might find a large number of triggers and stored procedures that can be eliminated with DB2 Temporal Tables. Also remember there are still a number of restrictions around DB2 Temporal Tables, but the performance benefit and SQL improvements can make it worthwhile.
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.
Leave a Reply