Leaving Database in Read-Only Mode (Not using GRANT with REVOKE

[Pablo Ibarra Duprat]

Hi!

Exist any way to leave the database read-only for all users without use the grant and revoke command combination ?
I think first in Quiesce but this option let only one users to go in...
Any way ?


Thanks in Advance

[Willy Unger]

Not exactly a clean solution, but you COULD use the SET WRITE SUSPEND/RESUME FOR DATABASE command for this, although it's really meant to take a consistent DB backup with hardware facilities.

Best Regards

Willy Unger

DB2 Certified Specialist
***@yahoo.com

[Kelly Schlamb]

Actually, this probably wouldn't work that well -- at least not consistently. SET WRITE SUSPEND doesn't block write activity to the database, it blocks write activity to the underlying disks (containers, logs, meta-data files, etc).

So, people could actually insert/update data in the database and this wouldn't block until the user went to rollback/commit the changes (which would require writing to the logs) or until the work required flushing some data to disk.

And more importantly, read activity might hang as well. For instance, a query could need to bring pages into the bufferpool and in doing so it may need to victimize existing dirty pages to make room for them. When those dirty pages are victimized, the writes will block and this could end up blocking the query.

Regards,
Kelly Schlamb