Results 1 to 3 of 3
  1. #1
    Join Date
    May 2012
    Posts
    155

    Unanswered: not logged initially

    Hallo everyone,

    I created my table using the option NOT LOGGED INITALLY:
    Code:
    CREATE TABLE schemaname.tablename(
    
    ...
    
    ...
    )IN mytabspc INDEX IN my tabspc NOT LOGGED INITIALLY;
    I have a simple question:
    Since the table has 1 billion records, I would like to run the DELETE statement without ROLLBACK TO SAVEPOINT or anything has to do with logging. Do I still have to "activate" the not logged option in my DELETE sql-code? Should the code look like this?

    Code:
    delete from myschema.mytable 
    where mycolumn = 'university'
    activate not logged initially
    Is it really quaranteed that there is no ROLLBACK TO SAVEPOINT command run? I had once bad experience --> I did not have any not logged option, I wanted to delete some ten millions of rows. The log memory was full, since DB2 tried to ROLLBACK TO SAVEPOINT.

    Thank you for the advice.

    Regards,

    Ratna

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by ratnalein View Post
    Do I still have to "activate" the not logged option in my DELETE sql-code?
    Yes.

    Quote Originally Posted by ratnalein View Post
    Should the code look like this?
    No. It's ALTER TABLE ... ACTIVATE NOT LOGGED INITIALLY. Make sure autocommit is turned off, as the NOT LOGGED state is active until the next commit only.

    Quote Originally Posted by ratnalein View Post
    Is it really quaranteed that there is no ROLLBACK TO SAVEPOINT command run?
    What does it have to do with NOT LOGGED?

    Keep in mind that if your DELETE statement fails for any reason, the entire table becomes inaccessible and all you will be able to do with it is drop it.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    May 2012
    Posts
    155
    Hallo n_i,

    thanks for the quick reply.

    What does it have to do with NOT LOGGED?
    I know my actual problem now:
    I actually want, that if I do a DELETE command, there is no ROLLBACK TO SAVEPOINT command run. Is it actually possible?

    I know that on a temporary table, it is possible to set NOT LOGGED:

    Code:
    DECLARE GLOBAL TEMPORARY TABLE(
    ...
    ...
    ...
    )ON COMMIT PRESERVE ROWS NOT LOGGED;
    At this temporary table, an INSERT command will not log anything.

    But on a table with CREATE TABLE, is it possible not to let db2 performing ROLLBACK TO SAVEPOINT?

    Thank you.

    Regards,

    Ratna

Posting Permissions

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