Results 1 to 13 of 13
  1. #1
    Join Date
    Jun 2006
    Posts
    9

    Red face Unanswered: DB2 in read only mode ??

    Guys,

    I want some help over how to put my Db2 database in readonly mode for short period of time ( let say 2-3 minutes ) so that i can take export of my important tables.

    I have Db2 version 8.1 running on Linux. I dont want to disconnect my applications which are using DB2 at the time of creating export ( means dont want to do a force disconnection to Db2 applications )... my intention is to put tables in readonly mode one by one ( just in similar way we put oaracle tables and tablespaces in begin backup/end backups statements structure).

    Is this thing possible in DB2 ... please advice!!!


    Regards

    Jones

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    look up the quesce command.
    Hope this helps,
    Grofaty

  3. #3
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    i think Db2 is smart enough to maintain proper concurrency while exporting
    you dont need to worry:-)
    CS isolation is maintained during export
    chk this tech article
    http://www-128.ibm.com/developerwork...nyk/index.html

    --Rahul

  4. #4
    Join Date
    Jan 2003
    Posts
    1,605
    rahul_s89, if I understood Huyed_Jones, he would like to export multiple tables, not only one table.

    There is one more solution: db2 lock table table_name in exclusive mode
    only locks the table desired to lock.

    Quesce command locks database or tablespace depending what you would like to lock.

    Hope this helps,
    Grofaty

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    At least on z/OS, you may use the -START DATABASE command with ACCESS(RO), and later use ACCESS(RW) again.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    grofty
    but with quiesce only users with admin or quiesce_connect authority will be able to connect. I understand that jones want to have only select privilege
    during the period of Export.
    1 soln can be
    * revoke insert,update ,delete privilege from appropriate users just before exporting from the table
    * export table
    * grant the privilege again to the users

    regards
    Rahul Singh

  7. #7
    Join Date
    Jun 2006
    Posts
    9

    Red face Re:DB2 in read only mode??

    Guys
    many thanks for your appreciable responses !!

    From the IBM article ( as mentioned by rahul ) , it appears that during export, DB2 applies table lock , row by row .. so that if write opeartion happens to any specific row being exported , it should not happen..... but still applications and users are allowed to do the write operations during db2 export operation.... so how the whole database consistency will be ensured??

    As i have mentioned earlier, in oracle , i can quiesce write operations table by table ( begin backup and end backup statements ) and then even use simple copy commands as a good way of having online backups..... my objective was to achieve same in DB2.. but now it seems difficult ....except as suggested by rahul to withdraw write previliges from tables during export operations...

    I will appreciate , if any body can come with some more good ideas


    Thanks to all of you for ur support

    Jones,

  8. #8
    Join Date
    Jan 2003
    Posts
    1,605
    jones,
    grant/revoke is probably good solution. But you can also try using lock/unlock table:
    1. lock table table_name in share mode
    2. export
    3. unlock table table_name

    From IBM documentation Lock table:
    IN SHARE MODE: For a lock on a table that is not an auxiliary table, requests the acquisition of a lock that prevents other processes from executing anything but read-only operations on the table.

    IN EXCLUSIVE MODE: Requests the acquisition of an exclusive lock for the application process. Until the lock is released, it prevents concurrent processes from executing any operations on the table.
    Last edited by grofaty; 01-23-07 at 07:36.

  9. #9
    Join Date
    May 2006
    Posts
    82

    Read Only Mode

    I think Revoking the access and granting them again is not a good idea.
    The reason being, Once the access is removed, The applications accessing the table would fail stating that they donot have the required previliges. But here the requirement is, neither applications nor users should get disturbed.

    If LOCK TABLE tablename IN EXCLUSIVE MODE is used, there will be an exclusive lock which doesnt even allow READ operation. As one gentle man said SHARE MODE would be a good idea.

    LOCK TABLE tablename IN SHARE MODE;
    Here this statement tries to acquire a table level lock and it may held the lock or this statement itself timeout if it waits for a long time. By using this the users will wait until the predifined set of lock wait time expires instead of abnormally terminating (revoke access). I'm from DB2 for Z/OS background.
    Please feel free to correct me if am wrong. Thanks

  10. #10
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by vini_srcna
    Here this statement tries to acquire a table level lock and it may held the lock or this statement itself timeout if it waits for a long time.
    The only problem that I can think of is lock escalation problem if multiple application accesses this locked table. But if the same thing is done on Oracle database, I assume there is not a lot of application trying to access the same table.

    So decision between grant/revoke and lock/unlock is the matter what should happen if application tries to use insert/update/delete operation on table. If application should get an error then grant/revoke is appropriate, but if application should just wait (freeze) for the 'export time' then lock/unlock is more suitable.

    Hope this helps,
    Grofaty
    Last edited by grofaty; 01-23-07 at 07:44.

  11. #11
    Join Date
    May 2006
    Posts
    82
    In Db2 thats called as LOCK ESCALATION. Internally every lock needs some space. If there are many no of row level locks,and if this exceeds the predefined Lock size (IN DDL OF TABLESPACE) then this would escalate to a table level lock which is called as LOCK Escalation. If you can acquire a lock in share mode, then it is understood that not many applications are using or it is used for read only (UR).

  12. #12
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    <see below>
    Last edited by Peter.Vanroose; 01-23-07 at 08:25.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  13. #13
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    I completely agree:

    If it's only a matter of data consistency during the EXPORT, the only "correct" solution is forcing a table lock (in one way or the other).

    Simplest way to do this is indeed the SQL statement "LOCK TABLE ... IN SHARED MODE". This lock will be released after the next COMMIT, so no need for an UNLOCK, just a COMMIT (or autocommit) is fine after the EXPORT.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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