It’s been the case for every SQL database in practical use since E. F. Codd first came up with the concept: Records either exist or they don’t. When you run a SELECT statement, you’re querying the current state of the data. A state is either true or false.
As far back as 1993, efforts to incorporate some type of temporal query into SQL – some way of saying, “Tell me whether this event will be true three hours from now” – have proven successful only with add-ons and attachments. IBM’s new “Time Travel” aims to make this capability generally available.
With the general release of DB2 v. 10 for Windows on April 30, IBM will include a SQL interpreter capable of temporal operations. That is to say, when transactions are scheduled for some time other than now – perhaps the past, perhaps the future – a query can return the state of the data table at some other time.
These new elements of what had been called Temporal SQL have been given the more sci-fi-sounding name “Time Travel” by IBM.
Okay, so it doesn’t actually bend the laws of physics. But as IBM marketing director Bernie Spang explained to ReadWriteWeb using more down-to-Earth language, “It simplifies the development of applications that have to deal with data at different points in time, both past and future. Think of a travel agency that has itineraries for future trips, and you want to be able to recognize and flag cases where you have a hotel booked for a week in Rome, and at the same time have a car service booked for some of those days in New York City. Or if you’re an insurance provider, and you’re looking at a claim and need to understand what were the policy details in effect at the time of the original accident, which may be different than what they are today?”
The secret to temporal queries comes from the use of two time scales, whose timestamps now apply to each and every row in a table: the system time with which everyone is familiar, and the business time that can slide. A query may specify an interval of business time using the modifier FOR PORTION OF BUSINESS_TIME, which may include a FROM and TO range.
A white paper on “Time Travel” published by IBM for its z/OS version (which was released first) provides a very clear example. It almost needs no explanation:
UPDATE product FOR PORTION OF BUSINESS_TIME FROM '2012-03-01' TO '2012-04-01' SET price = 15.00 WHERE productID = 123;
You probably already interpreted it correctly: This instruction looks in the product table for any item keyed 123. Normally you would imagine there being just one record for that key, but under the temporal system, there may be different attributes for the record at different times. So from March 1 to April 1, when you look up the price for this item, you’ll see $15.
A portion of an IBM presentation on temporal SQL. [Courtesy IBM]
For comparison, an IBM study revealed that the equivalent business logic for older versions of SQL stored procedures may have required some 64 lines of code, and for a Java program may have required 180 lines to achieve the same functionality.
IBM’s long awaited edition comes as DB2 also gears up to accommodate different classes of data – other than just relational. Being added to the mix, Spang said, are both support for Hadoop unstructured data as well as the RDF Graph Store RDF triples, the sentence-like structure that links a related object to its subject using a qualifier (predicate), and which is the basis for semantic Web architectures. These are added to native XML data management, which was introduced in DB2 v.9.
“It’s bringing more tools to the table to analyze more types of information than ever before,” explained Spang. “What that does is generate even more insight from that analysis, which in effect is information itself.” Merging the insights from analysis of unstructured data in an Hadoop-based system with the traditional analytics insights from relational data, plus live insights from active streams (a new feature of DB2 v.10), will yield insights that were not capable until now, he adds.
“We are in a new era of data management. The answer isn’t always a relational data system,” he said. “The reality of it is, there are a number of types of systems I need to bring to bear to fully take advantage of all the different kinds of information available to me as a business.”
The new edition of DB2 Express v.10 will be free for single-CPU, dual-core deployments of up to 4 GB of data. Commercial editions will be deployable using IBM’s Workload Deployer for private cloud, or SmartCloud for public cloud.
