Since DB2 v9.7 was released on June 19, 2009 there has been a lot of really good content trickling out. If you are interested you will find much of it on ChannelDB2.com. However, much of it has focused on describing the key feature of v9.7 Oracle Compatibility i.e. ability to enable applications written for Oracle to work on DB2. Notice I did not say “migrate” and that is for a reason.
But what if you are not in to Oracle anyway; no thank you. What is in the new version of DB2 9.7 that you have to look forward to? It turns out there is quite a bit. In this post I will will talk about one such new feature – “read currently committed” mode of the Cursor Stability isolation level. That’s a mouthful. Let me translate that in to English. “Read Currently Committed” makes busy applications (those with many users) go really fast without you changing any application code or even database configuration. Sounds good? I thought so. Let me explain how it works. If
- you have an application with a lot of concurrent users, and
- you use DB2 with a release number that is lower than v9.7, and
- your transaction isolation is Cursor Stability (90% certainty because CS is the default)
you will likely run in to a scenario where your application slows down dramatically under heavy load. This happens not because your code is not efficient and not because DB2 can’t deal with the volume of requests. It happens because the work done by some users interferes with requests from others (when using Cursor Stability isolation level). In other words, writers block readers from retrieving their data. When this happens, your users reading data will get delays waiting for writers to release locks on the data readers want. Why is that so? Well, it is because Cursor Stability semantics were designed to provide the absolute latest data to the readers and to get the latest you need to wait. That is actually the best case scenario. You will often get lock timeouts and deadlocks. If your application is not written to handle these conditions gracefully you are not going to win any popularity contests with your users. Luckily, v9.7 has this new “Currently Committed” semantics which can make you a star without you lifting a finger. When you use these high volume transaction applications DB2 will no longer wait until some transaction completes an update of the record that another user wants to read. It will return whatever data is already committed in the database to the reader without waiting for any updates that may be in flight. Not waiting is good as it allows everybody’s work to proceed at the maximum speed and without contention.
Here are the things that I personally like about this new feature:
- this is now default behavior for any new database
- it can be turned on for an existing database (set cur_commit = ON using UPDATE DATABASE CONFIGURATION command)
- there is no impact on writers performance
However, the thing I like the most is that there is nothing to manage, administer, configure or optimize. It is not often that we get great things for free; there is often a catch. In this case, there is no catch. All you need to do is get the new DB2 Express-C v9.7 (also FREE) and go at it. If you need to learn a bit about DB2, get, you guessed it, a FREE DB2 book. I am sure there are a lot of questions that this short post did not answer. If you are going to be at the IOD conference in Las Vegas there is a session dedicated to this topic 2187 IBM DB2 9.7 Concurrency: Enhanced Implementation and Improved Performance. Be sure to attend. Last but not least, those of you who are familiar with Oracle will recognize that this behavior indeed similar (but better) to what Oracle implements. If you do want to learn more about this and other Oracle compatibility features set aside an hour to watch this webinar or wait for my future posts.
Just need to be careful if the application expects the old serialization. For instance, you have a reservation system and want to ensure no overbooking occurs. You may really want to wait to see if the update on the desired seat gets committed or rolled back. You can run into the “lost update” problem.
To enjoy the new concurrency mode and still have the particular SELECT wait for the update, I believe a “FOR UPDATE” on the select will try to acquire a U lock, preserving the previous behaviour for this part of the application.
Norm, good point about a change in behavior. Naturally, if you are doing an application that must wait for the updates then the old behaviour is what you want and it is easily enabled by setting cur_commit to DISABLED using UPDATE DATABASE CONFIGURATION command.
Capitulated to MVCC, but dare not say the words, eh? And you (not the singular, but the plural — Armonk) wasted time and money on porting IMS semantics (calling it pureXML). We’ll see what the situation is when/if Gartner releases their database market share report. Odds are, were I a betting man, that the trend continues: z/series is “growth”, all else is either static or diminishing. It’s sad, because I really like DB2 (on *nix). All these years, and can’t seem to beat Larry.
The only thing DB2 capitulated to was customer desire for more flexible locking schemes. Oracle MVCC is very good and many people like it a lot. I think with the addition of the Currently Committed and several other locking enhancements, DB2 9.7 provides locking semantics that, in my opinion, are more powerful than MVCC. As nice a s MVCC is it does have some really annoying things about it like dealing with redo segments or versions of data that are no longer accessible.
Don’t really get your point on pureXML being a port of IMS semantics. pureXML allows you to store XML documents in DB2 in such a way that you still preserve searcheability inside the document. The only thing that remotely resembles IMS is the fact that XML documents have a hierarchical organization. That is where all similarities end. Also, this has nothing to do with DB2 but is an innate nature of XML.
As to the market share, Oracle is a major force in the DBMS market and nobody would deny it. However, DB2 has doing extremely well and has been growing at double digit rates well outpacing Oracle. And the DB2 I ma talking about is DB2 for Linux, Unix and Windows.
There is difference between FOR UPDATE statement in DB2 or Oracle. Oracle keeps row X locks until commit/rollback, db2 releases U lock after moving to next row in result set.
Good point.
In fact we introduced SELECT INTO .. FOR UPDATE to DB2 and keep teh U-Lock until the end of the Tx.
I don’t think we touched the behavior for regular FOR UPDATE cursors.
*sigh*There goes another bit in DB2_COMPATIBILITY_VECTOR….
Gartner hasn’t (yet) released the PR for the report. I did find that Oracle has its own PR referencing the 12 June report:
Gartner 2008 Worldwide RDBMS Market Share Reports 48.9% Share for Oracle (*)
Gartner has published their market share numbers by operating system for 2008 based on total software revenues. According to Gartner, Oracle
* Continues to be #1 overall with 48.9 per cent share
* Continues to hold more market share than its six closest competitors combined
* Continues to be #1 on Linux with 75.8 per cent share
(*) Source: Market Share: Relational Database Management System Software by Operating System, Worldwide, 2008 – Colleen Graham, Bhavish Sood, Horiuchi Hideaki, Dan Sommer – June 12, 2009
I couldn’t get a search to return an equivalent PR from IBM bragging out the Gartner report. Not conclusive, but lends support to my speculation that DB2 continues to lag, save for VSAM conversions on z/series.
Do you have a link to an IBM release referencing Gartner?
>> However, DB2 has doing extremely well and has been growing at double digit rates well outpacing Oracle. And the DB2 I ma talking about is DB2 for Linux, Unix and Windows.
This statement seems, pretty obviously, to be contradicted by Oracle’s quote. Unless IBM is claiming that going from 1% to 2% (or similar small numbers) is 100% growth (which is accurate arithmetic). My point isn’t to pump Oracle here, but to remind Armonk, through this blog, that they need to take non-mainframe DB2 far more seriously than they have been for over a decade. I really, really wish that more DB2 were being done on LUW. As it stands, Oracle/SQLServer/MySql each dwarf DB2. DB2, in my opinion, is the better database (the MVCC issue aside) than either of the other three. It’s just getting more difficult to find places to do DB2. Without jobs in DB2/LUW, there won’t be a DB2/LUW. And that would be terrible.
Robert, I don’t think anyone is disputing that Oracle is the major force in the DBMS market or the fact that IBM share of that market is smaller than Oracle’s. I think the discussion is about the trends. DB2 is indeed doing very well and is growing revenue faster than the market. Oracle is not doing as well. As a matter of fact, Oracle’s announcement of the results for their latest quarter is a clear demonstration of the fact 22% drop in Database and Middleware new license revenue. The big drop in new license revenue is offset by a sharp increase in the services revenue. Which is a pretty clear confirmation of what has become the most hated Oracle practice: extracting more money from existing customers. Last year Oracle jacked up prices by 15% and with it the price for support went up. Earlier this year IBM System p customers found their prices go up by an additional 33% and with it the price for support. Most customers don’t appreciate these types of tactics and many are actively looking for alternatives. They are finding that DB2 is a great alternative and the new Oracle compatibility features in DB2 v9.7 make it much easier to get Oracle application working on DB2. The new concurrency model I talked about in this post is one such feature. Ability to run PL/SQL code, new data types, CLP+ are examples of other features that make it very easy for the growing number of disgruntle Oracle customers to switch to DB2. I think your advice to Armonk to concentrate on DB2 for Linux, Unix and Windows is well taken. I also think advice to Redwood Shores to stop taking their customer base for granted would probably be a wise move. But we in DB2 land are very happy to take former Oracle customers in to our fold 🙂 http://news.prnewswire.com/DisplayReleaseContent.aspx?ACCT=104&STORY=/www/story/09-15-2009/0005094629
PS. I really like your blog and I do appreciate your point of view that you are expressing in your comments on this blog. Keep it coming, please.