Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We are building out a data warehouse (and also some data marts as well) in our organization. My DBA skills have mostly been on OLTP type applications, but I am moving into supporting OLAP for our organization.

We ran into an issue recently where one of our ETL programs (in this case it happens to be a Message Driven Bean written in Java) is using the Repeatable Read (RR) isolation level. Because of this, they were running into locks. I obviously question why RR was being used. (EDIT: I originally thought RR on the DB2 side, but learned this was RR from the JDBC side, which maps to RS on the DB2 side. Since then we have also tried RC on the JDBC side, which is CS on the DB2 side. That appears to have resolved some of the locking issues.)

So I started to research what should be the suggested isolation levels for data warehousing, both from the feed/source side, as well as from the retrieval side.

I really can't find much. I have found a few articles that mentioned Uncommitted Read (UR) being good for the query/retrieval side, but nothing about the feeding side. I was thinking that Cursor Stability (CS) or at the highest Read Stability (RS) should be used. I would side with CS, but perhaps that is tainted by OLTP thinking?

In our case the main feed for our warehouse is right now done "real-time" using Java message queues. Retrieval from the warehouse will be done with ETL tool (DataStage in our case), probably kicked off as nightly jobs (some for daily reports, others for monthly, or quarterly reports). These reports will be based off of our marts. So while the warehouse might be fed "real-time", it won't be read from, or actively queried over (at least not right now).

Granted, we use DB2 9.7 FP 4 (and we are hoping to migrate to 10.1 sometime next year) and some of the specifics may differ per vendor, but what isolation levels do you warehouse DBA's recommend for feeds and reads and why?

Also, any input on isolation levels for marts would be appreciated to.

EDIT: Just so it is easier for other vendor comparisons. Here is the table of DB2 isolation level names as mapped to the JDBC isolation levels. (This per the book Understanding DB2: Learning Visually By Examples and thus copyright IBM.)

|            JDBC              |          DB2          |
| TRANSACTION_READ_COMMITTED   | Cursor Stability (CS) |
| TRANSACTION_SERIALIZABLE     | Repeatable Read  (RR) |
share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

For most databases including Data Warehouses Read Committed is a sufficient isolation level. I wouldn't use Read Uncommitted due to the possibility of incorrect answers. On SQL Server we can also add Read Committed Snapshot Isolation. This versions rows and avoids queries being blocked by updates. Don't know if you have something similar on DB2.

share|improve this answer
I recently learned that DB2 9.7 and above introduces the "Currently Committed" isolation level. Not so much a level as a setting that affects the CS and RS levels above. It uses the transaction logs to bypass lock checking to look for uncommitted data, which it then ignores. See here if interested. –  Chris Aldrich Nov 20 '12 at 18:51
add comment

I'm not sure if DB2 automatically picks the most compatible isolation level if you set it to 'read only' mode, but in MS SQL Server it does. You can have it 'read only' until shortly before your jobs run. This way you're sure someone doesn't do a UPDATE on a huge data mart and locking/blocking issues are turned off by the engine since no one will have access to write to it anyways.

Setting isolation levels to READ UNCOMMITTED, as Andy pointed out, can have adverse effects but your readers will not be blocking writers and vice versa. If you do COUNT(*) you will see vastly different results potentially, but it really depends on your load time and patterns.

How long does a typical nightly and monthly load take?

share|improve this answer
Well, at this point, the warehouse is still under development, so we don't have it in production yet for actual load values. As for your first point/question, DB2 allows you to set the isolation level for the current session, application, or statement. So one can change the isolation level from application to application, or even with different statements within the same application. –  Chris Aldrich Nov 20 '12 at 18:46
Thanks Chris, that's good to know! –  Ali Razeghi Nov 20 '12 at 18:54
add comment

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.