Skip to content

Archive for

How to change Netezza database to read only mode?

Netezza have not documented any procedure to change database in read only mode. But we do have a commands to change database to read only and then change back to read write.

To change the database to read only mode you can run below command in SYSTEM database. Only the admin user or any user with alter privileges on a database can run below commands.

alter database TestDB lock for update;

This commnad will immediately lock TestDB to read only mode. All user sessions (other than user which ran the command) will get error messages if they try to do any modifications to this DB. This will only allow the current user session (user which ran this command) to do any modificatins to this database. Once this session is closed, then no one can make any changes untill we unlock the database for changes.

To verify if your database is in read only mode or not, you can run below query and look for DBLOCKTYPE value as ‘U’

select DATNAME, DBLOCKPID, DBLOCKTYPE from _T_DATABASE

Now to change this DB back to read write mode, run below command in system database.

alter database TestDB unlock;

These scripts will run good for TwinFin systems with Netezza version 6 plus. Have not tested on Netezza version below 6.