ReadOnly DB? - Improved Performance for Data Mart

[Rob K.]

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.

[kenfar]

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

[graham_martin]

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/

[Dario]

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

[Artur]

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