Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2008
    Posts
    11

    Unanswered: How to make already existing table to be read-only?

    Hi,

    I want to make a already existing DB2 table to be read-only. Can anyone let me know the command for doing that?

    I am using DB2 - V8.1/Linux OS

    Thanks,
    Arunvijay.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You could use the REVOKE statement to revoke insert, update, and delete authorization from certain users or groups.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Or you create a small program that locks the table in shared mode, which will also prevent DBAs from bypassing the privilege checking. (But then, a SYSADM could force this program and it wouldn't be water-tight.)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by stolze
    Or you create a small program that locks the table in shared mode, which will also prevent DBAs from bypassing the privilege checking. (But then, a SYSADM could force this program and it wouldn't be water-tight.)
    I wonder if that would cause the logs to go into secondary extents because the transaction is still open?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    No, it wouldn't. Since the lock does not cause a log record to be written, the locking transaction does not refer to any active log file.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by stolze
    No, it wouldn't. Since the lock does not cause a log record to be written, the locking transaction does not refer to any active log file.
    Doesn't DB2 have to record the beginning of the UOW? Maybe not, but in any case, I don't think it is a good idea to hold locks like that.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The beginning of the UOW is needed only when the first data modification occurs. In fact, transactions with only queries do not appear at all in the log - at least that's how things worked in DB2 LUW V8, and I doubt that this changed in V9.

    And I fully agree: holding locks like that is not smart at all, for example, if lock timeout is set to infinity...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •