By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,756 Members | 1,721 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,756 IT Pros & Developers. It's quick & easy.

ReadOnly DB? - Improved Performance for Data Mart

P: n/a
We have a DataMart (Approx 10GB) that is updated in batch at night. Is
there a way that the database can be configured to be in a "Read Only"
mode during the day?
MS SQL offers this - improved performance as there is no locking of
tables, logging, etc. Thanks.

Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Not aware of any benefits to making it 'read-only' for you here:
- your writes occur during the night, so they won't impact
queries during the day anyway
- selects just result in share locks
- there are other very easy ways to stop users from writing to
a database

Are you trying to eliminate logging of multi-step sql statements?

Anyway, you'll probably find better performance from
- modeling
- sql tuning
- storage layout for tempspace, indexes and storage
- memory layout for bufferpools and sort heaps
- server configuration for parallelism, etc

IBM has "redbooks" that you might want to check out for details on
these subjects.
ken

Nov 12 '05 #2

P: n/a

You normally enforce a Read Only Database through the use of
authorizations. You can create all of the users into a group that has
SELECT privileges only on the Database Tables.
Graham Martin
http://www-306.ibm.com/software/data/db2/migration/

Nov 12 '05 #3

P: n/a
You can check if Isolation Level change can help (setting to
Uncommitted Red).

Check

http://www.dbazine.com/db2/db2-disarticles/gulutzan6

"...
A transaction at the READ UNCOMMITTED isolation level is very weak: it
makes no locks and ignores others' locks.
...."

Dario

Nov 12 '05 #4

P: n/a
QUIESCE TABLESPACES FOR TABLE your_table SHARE
will prevent from writing to that tablespace/table.

QUIESCE TABLESPACES FOR TABLE your_table RESET
will remove quiesced state.

You can also use table level locking.

However if you are looking for performance I agree with previous posts
-- start from tuning your system (for "big" queries pay attention to
system temporary tablespace). Also, you can think about Automated
Summary Tables, MDC.

-- Artur Wronski

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.