While doing some research for a recent client, I came across a press release that announces the planned release of DB2 10 later this month (April 2012). At another previous client a couple years ago, I used DB2 9.5, and have not needed (or necessarily wanted) to look into DB2 again until now. Okay, so this is a proprietary relational database product. But it has some interesting features. I'm not going to write about all of these, but there are a few that strike a chord to some degree with me.
Starting with DB2 9.7, it sounds like IBM has been making it easier to migrate from Oracle stored procedures to DB2 stored procedures. I do not know about you, but I do not like stored procedures in general, and am architecturally wary of them because they provide a way to store an application layer within a data layer. At another recent client, there were hundreds of them (I do not remember the count anymore). One of the things I did over there was repackage them to make them easier to work with, especially with regard to database change management. Doing so did not make them more likeable, but as anyone working at that client can attest, they are definitely a constraint that needs to be reckoned with, and the same situation goes for other shops.
Unofficially, I am partial to the Oracle database. I have been for quite some time. So moving from Oracle to DB2 in itself does not sound like something I would ever want to do, if other options are viable. But in the case that a migration of Oracle stored procedures to DB2 is needed, it is good to know that DB2 now supposedly provides 98% compatibility at the source code level. I am sure there are a host of other incompatible factors beyond source code to keep in mind, such as those related to database tuning, that you will not find out about until you are further down the adoption path.
It is interesting that DB2 now offers bi-temporal features, especially after having just recently read one of the few texts available on the subject. The text is very weighty, and takes some time to get through. The authors discuss bi-temporal features that have not yet made it into the SQL standard, and propose their own solution. It is one of those books where you have to be able to follow along extremely closely from page to page, or you will get lost quickly. Similar to a series of math classes that you might have taken in college. The features that DB2 provides with regard to this feature set are together called "Time Travel Query".
IBM has a great slide presentation on this subject. The subtitle to this presentation mentions "DB2 for z/OS", because that version of the product was released in 2010. The upcoming release is for "DB2 for LUW" (Linux, Unix, and Windows), which has a different code base and is maintained by a different set of developers. This presentation sums up well what this release has to offer with regard to "Time Travel Query": Business Time, System Time, and Bi-temporal.
With Business Time, a pair of timestamps is set by the application: begin time (when the business deems the row valid) and end time (when the business deems that this row validity ends). This functionality permits queries against past, current, and future business time periods. System time is a pair of timestamps set by DB2 to every row: begin time (when the row was inserted to the database) and end time (when the row was modified or deleted). This functionality permits queries against past and current system time period. Bi-temporal includes both Business Time and System Time in each row.
One of the best aspects of this presentation is that the examples for Business Time and bi-temporal use an industry in which I have been working: healthcare insurance. Policies change over time, for example, and automating some of the functionality to keep track of these changes, while also permitting queries of data based on different time periods, all while minimizing risk with regard to whether a developer coded correctly, is a useful feature. And with System Time, the database moves historical records to separate database tables while not worrying about the need for developers to keep track of these different tables, since DB2 resolves for developers behind the scenes.
Another feature that peaked my interest is a new join method called zigzag join (ZZJOIN). Considering a star schema, DB2 provides two special join methods: semi-join with indexing (star join) and hub join (Cartesian join). Apparently, zigzag join has been introduced to expedite the processing of queries based on a star schema. This join method simultaneously filters the associated fact table on two or more dimension tables, skipping unproductive fact table probes in the process, and apparently has been shown to be an efficient method for querying large fact tables, significantly reducing I/O.
The interesting thing about this feature is that it is seemingly impossible to find any information on it, including syntax, even after searching DB2 manuals. The reference with the most substance, outside research papers, is located here. No examples. Presumably, more information will be available in conjunction with the upcoming LUW release, but information for the z/OS version is hard to come by as well, and it was released two years ago.
