Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2005

    Unanswered: lock promotion in db2

    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.


  2. #2
    Join Date
    Jan 2003
    Provided Answers: 5
    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.


  3. #3
    Join Date
    Jan 2007
    Jena, Germany
    Put this in a SQL script and run it with "db2 -f -c- <script>". The "-c-" (or "+c") option turns auto-commit off.
    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