hi,
This is what im trying to do. Need to archive data from daily table to archive table. While I do the export from the base table and successfully import data into the archive table I want to ensure that no new records are added to the base table. Also upon successfully import i need to truncate the base table.
In order to see that the lock promotion work correctly, wrote a sample test script.

db2 connect to mydb;

db2 lock table test.daily in share mode;

db2 "import from /dev/null of del replace into test.daily";

db2 connect reset;

From what I assumed, the command "db2 import from /dev/null..." should be locked out. However since autocommit is on it doesnt work.
I tried to add : db2 "update command options using c off"; it doesnt work.

Any ideas anybody??

Thank you in advance.

Best,
Poorvi

You cannot lock yourself out. If you put a lock on something, you own it, so you get to continue playing.

If you want to prevent others from accessing the table you should lock in EXCLUSIVE MODE.

You should use LOAD instead of IMPORT to truncate the table. Less overhead involved.

And yes, you need to turn auto-commit off to get this to work.

Andy

Put this in a SQL script and run it with "db2 -f -c- <script>". The "-c-" (or "+c") option turns auto-commit off.