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

Hi,
look up the quesce command.
Hope this helps,
Grofaty

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

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

At least on z/OS, you may use the -START DATABASE command with ACCESS(RO), and later use ACCESS(RW) again.

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

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,

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.

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

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

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).

<see below>

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.