Temporal Table Trouble?

One of the exciting new features in IBM i 7.3 is the addition of temporal queries in the integrated DB2 for i database. The so-called “time travel” query capability will let users ask how a particular piece of data looked at some time in the past, using good old SQL. While this new capability is included with the standard version of the database, a key feature that makes temporal useful costs extra.

The temporal query functionality was one of the big new features in IBM i 7.3 and brings the DB2 for i database up to par with the temporal functionalities found in other relational databases. What’s cool about the new feature is that it is a function of the database itself and doesn’t require users to refactor their data into any special data types or formats. All the magic happens inside the database, which is the way it should be–on this platform, perhaps, more than any other.

The temporal function is deceptively simple. All a user has to do to create a temporal table out of an existing database table (either an SQL table or a DDS file) is to add three new columns, which track the transaction ID for the entry, when it came into being, and when it ceased to exist. Users can then query the data as it looked “in the past,” essentially, using standard SQL.

But there’s a “gotcha” involved with the temporal table that IBM i shops should be aware of. IBM advises customers that, in order to keep the newly formed temporal tables from growing too large and consuming too much DASD, that they use database partitioning functionality that’s available for DB2 for i.

“We suspect that a lot of people who use the capability will want to use partitioning on their history table and either save off and then get rid of the partition or detach the partition after some period in time just to keep the amount of storage that they have on their system down,” Mark Anderson, the chief architect for DB2 for i and a distinguished engineer at IBM, told IT Jungle in an interview last month.

The problem is, database partitioning is only available to customers who license DB2 Multisystem from IBM, and DB2 Multisystem ain’t cheap.

This fact rubs Charles Wilt, an IT manager at Miami-Luken of Springboro, Ohio, the wrong way. “That particular [licensed program] was a $25,000 option for the P05 tier Power8 box I just bought,” Wilt says. “Obviously, that’s bit hard to swallow when the rest of the software plus hardware was only $35,000.”

Local partitioning is but one of the features that comes with DB2 Multisystem, a parallelized version of the database that enables a single database to be split up among up to 32 separate nodes of a cluster in a shared-nothing manner. The results of a query against this distributed database may appear to be coming from a single, local machine, when in fact the data may reside on a geographically remote server.

IBM i professionals have tried before to get the local partitioning feature unbundled from DB2 Multisystem, to no avail. In a recent post to the MIDRANGE-L discussion list, IBM i professional Rob Berendt posted IBM’s response to a request to separate portioning, which was made formally through COMMON‘s requirement program.

“If unbundling equates to allowing local partitioning to be used without having to purchase DB2 Multisystem, we are not going to unbundle it,” IBM wrote, per Berendt’s February 3, 2016, post. “DB2 Multisystem has unique value.”

It doesn’t appear that IBM has any intent to unbundle local partitioning, no matter how important that feature is to the temporal query feature that ships with every server equipped with IBM i version 7.3.

“Yes, DB2 Multisystem (a charged feature of the IBM i operating system) is required to use range partitioning,” an IBM spokesperson told IT Jungle. “The good news is that we recently created a way for clients to evaluate this option, prior to purchasing.”

So, while IBM i professionals can try the local partitioning function of DB2 Multisystem for 70 days, on the 71st, they will need to pony up the extra cash to keep the temporal tables from consuming inordinate amounts of storage.

It’s not clear if IBM intended on using the new temporal query feature to drive sales of DB2 Multisystems. If so, the approach may backfire, as that form of clustering is, ostensibly, used only by the largest shops with the most demanding database needs.

That’s because small and midsized shops that have a real need for the temporal query functionality and whose businesses will benefit from it, nevertheless won’t be able to justify a five-figure purchase, just to keep the sizes of their new temporal tables from getting out of hand. They might even just keep using the journaling functionality to track changes to files (which can also eat up DASD), even if it’s a less-elegant and harder-to-use approach.

The good news is that if enough IBM i professionals let IBM know how they feel about this, that IBM can change it. They have reconsider their approaches time and time again, and could change their mind on this–provided unbundling local database partitioning doesn’t cause unintended consequences.

You can submit an IBM i requirement to IBM at requirements.common.org.

RELATED STORY

Inside the New Analytic Functions of IBM i 7.3