We need to test how an application will fail when the DB2 database cannot be written to.

What is the easiest way to make a DB2 table read-only? Is this considered locking?

Thanks in advance, Bert

  • The error returned to the application will be different, depending on why the table cannot be updated, so you'll need to specify the exact scenario you are trying to reproduce. You can lock a table, but that only tests one specific scenario: what happens when the table is locked. Depending on how the database is configured, that may not even return an error, the application will simply wait indefinitely for the lock to be released. – mustaccio Aug 11 '15 at 20:51
  • You could have an unauthorised user try to update - that should fail! What's the app written in? – Vérace Aug 11 '15 at 21:38

There are a couple of ways to test it:

  • Use LOCK TABLE and put it in "exclusive" mode
  • REVOKE the privilege to INSERT or UPDATE on that table from the test user
  • REVOKE the privilege to connect to that database
  • Change or remove TCP/IP access to the database
  • ...

There are many testing scenarios and most of them will result in a different error message.


You can create a trigger on insert, delete or update on the table that will raise asignal automatically for these operations. This will make the tBle as read only and the security remain the same


On the DB2 commands screen try


Source here.

  • You may want to mention that this solution is specific to DB2 for z/OS; it's unknown what version or platform the OP was asking about. – mustaccio Nov 3 '15 at 19:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy