Introduction
In my last article, on Temporal Tables for DB2 for i, which can be found here, I gave a quick insight on how easy it was to create and report on time travelling queries.
Since this time, I gave the first workshop on Temporal Tables at the highly successful International i Power 2016 conference in the UK.
Here I would like to go through the questions, and answers, that the delegates asked during the workshop.
The main question, not only at the workshop, but the whole conference, was whether changing an existing table into a temporal table, i.e., adding the timestamp columns, would require me to recompile all my RPG programs to stop them failing with a level check error?
My response during the conference, was yes, once the tables where amended the record format ID on the table would change, and therefore any program that references that table would get the dreaded CPF4131 – level check message.
Having said this during the conference, I thought I had better go back and check the words that came out of my mouth!
The format level ID shown below is an existing table, no temporal aspects here.
Once we amend the table, the extra temporal timestamp columns are added, the fields total has increased by 3 and the level ID has changed. Just what we expect really.
So, a quick RPG program, doing nothing more than reading a table, produced the results I was expecting. Running the program over the original table worked fine, putting the temporal table at the top of the library list caused the program to failed.
The delegates on the workshop, also expected the same.
Running the program, we can see the offending message in the joblog.
And yes, we can change our tables to skip level checks, but no I would not recommend this under any circumstances!
This and other questions asked by the delegates can be seen in the table below, along with the response I received back from IBM.
Question
Amending existing tables to make them ‘Temporal’ causes a level check on programs accessing them, even if the additional columns are hidden. Is there any method to get around this?
IBM Response
Yes, use SQL and/or surround the PF with LF’s.
Question
Will there be changes so the history table can reside somewhere other than the same schema/library as the temporal table, specifically, another ASP or iASP, on slower disks?
IBM Response
History tables need to reside in the same schema/library as the temporal. There are no plans to change this restriction.
Question
Will the history table be made ‘read only’, like the history log, so no hacking can be performed?
IBM Response
We disallow revisions (inserts and updates) to the history table, but the owner of the history table or the *ALLOBJ user is allowed to prune old history.
Question
How do the timestamp values handle users & queries from different time zones?
IBM Response
The time of day is used for birth and death. Time zones influence time of day.
Question
Will green screen command, DSPDBR etc., be enhanced to know about temporal?
IBM Response
There are no plans to change this restriction. We recommend using the DB2 for i catalogs or Navigator.
Question
Will IBM be removing partitioning from DB2 multisystem, to allow greater use of partitioning?
IBM Response
There are no plans to change this priced option.
Question
Can the creation of a temporal table be enhanced to automatically create the history table as it requires all the same attributes, maybe a Y/N parameter on Navigator?
IBM Response
While it could be done, it seems low priority because of how easy it is to create a history table.
Question
Generating historical data will have a direct impact on storage utilisation. In the session we discussed archive using partitioning or standard OS save and delete. Any chance of providing a temporal archive tool that archives the historical data plus captures useful data that is written to a log. The new archive log could cover things like;
• Start date/time and End date/time of the historical data archived
• Date archived and removed from system
IBM Response
We recommend the use of partitioning and DETACH PARTITION to make old history ready for archiving.
If the client cannot afford to purchase DB2 Multisystem, they could archive row/row and prune rows.
Question
Will it be possible in the future to select which fields from a table you want temporal thus avoiding unnecessary disk space usage and maximising efficiency. For example, in a customer file I may not be interested if the address, telephone number or contact details change but I do want to know when the credit limit changed, who changed it and when
IBM Response
This is possible today if you change your data model. There are no plans to change Temporal processing to perform column by column analysis.
All very interesting!
The biggest hurdle for users to switch to temporal is that existing applications will need recompiling, a huge task when the source doesn’t exist, whether it has gone missing, or applications have been purchased without source.
Conclusion
We must all remember that this was a ‘first-cut’ on temporal tables on the IBM i, and no doubt there will be many enhancements in the future. Having said that, IBMs response was ‘There are no such enhancements being considered at this time.’ Since IBM are now working on the next two future releases of the operating system, it seems that the temporal features we have now, are the features we will be stuck with.
So, thank you IBM for providing the temporal features for us – keep up the good work!
I look forward to meeting you all at the next i-UG meeting on 19th October 2016, if i get asked back! Details can be found at https://www.i-ug.co.uk/#!i-ug-event-registration-page/c1pb7/?utm_source=SM&utm_medium=SM&utm_campaign=JUNE
Andy Youens is an IBM i consultant/instructor at Milton Keynes, UK-based FormaServe Systems.