Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2004
    Posts
    53

    Question Unanswered: Can I INSERT without logging? (Commit every...)

    I am doing an INSERT in a table and I am getting a transaction logs full type error. Is there a way to do this INSERT committing every, say, 1000 rows? I don't care if it goes wrong or the target table gets corrupted. I won't want to roll anything back. I don't want to increase my transaction logs just to store transactions I do not want to store.

    TIA.

  2. #2
    Join Date
    Apr 2004
    Location
    Inside Intel
    Posts
    165

    Talking

    Try using the LOAD command. It doesnt require log files.

    Newbie

  3. #3
    Join Date
    Jul 2004
    Posts
    53
    I would have to do an EXPORT and then a LOAD, just to simulate a "carefree" INSERT. Does not make a lot of sense to me, especially from a performance perspective.

    I guess, if there's no other solution...

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    The other solution is to to use 'Not Logged Initially' ...

    db2 "CREATE TABLE TAB1(I INT) NOT LOGGED INTIALLY" ;

    db2 +c " ALTER TABLE TAB1 NOT LOGGED INTIALLY"
    db2 +c "INSERT INTO TAB1 VALUES(1)"
    db2 commit

    Here, until you commit or rollback , the inserts are not logged ...

    But you are at a risk of loosing the table data if there is a rollforward operation or the above operation fails before committing ...
    If the data can be (easily) recreated, this is a good option

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Jul 2004
    Posts
    53
    I thought of NOT LOGGED INITIALLY, but how would that work if I am not creating the table? Instead, this is an existing table I am inserting table into?

    TIA



    Quote Originally Posted by sathyaram_s
    The other solution is to to use 'Not Logged Initially' ...

    db2 "CREATE TABLE TAB1(I INT) NOT LOGGED INTIALLY" ;

    db2 +c " ALTER TABLE TAB1 NOT LOGGED INTIALLY"
    db2 +c "INSERT INTO TAB1 VALUES(1)"
    db2 commit

    Here, until you commit or rollback , the inserts are not logged ...

    But you are at a risk of loosing the table data if there is a rollforward operation or the above operation fails before committing ...
    If the data can be (easily) recreated, this is a good option

    Cheers
    Sathyaram

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    I may be wrong ...
    But, I remember V7 required explicit 'NOT LOGGED INITIALLY' when creating but V8 accepted ALTER anyways ...

    If you are on V8, can you check ?

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Jul 2004
    Posts
    53
    Sathyaram,

    I am not quite following. Sorry.

    From what I understand, you can use NOT LOGGED INITIALLY when creating or altering a table and this will cause that no transactions will be logged within that job or until you implicitly COMMIT.

    However, I am not creating nor altering this table. This is a database table I am simply inserting a large amount of rows into.

    By the way, you don't have to specify NOT LOGGED INITIALLY in either version when creating a table, if that's what you mean. We work with both v7 and v8 and none of our CREATE TABLE statements are using that at the time.

    Thanks again!



    Quote Originally Posted by sathyaram_s
    I may be wrong ...
    But, I remember V7 required explicit 'NOT LOGGED INITIALLY' when creating but V8 accepted ALTER anyways ...

    If you are on V8, can you check ?

    Cheers
    Sathyaram

  8. #8
    Join Date
    Feb 2004
    Location
    Terre Haute, IN
    Posts
    7
    Is a cursor load an option?

    ------------------------------------------------------------------
    declare C01 cursor for
    ..(select A.*
    .....from schema.table
    ...where something_is_true);

    load from C01 of cursor messages "./message_file.msgs" replace into
    .....schema.target_table nonrecoverable;

    COMMIT;
    ------------------------------------------------------------------
    store the above in a file whatever.sql (the . above represents a space for viewing purposes only)

    then call with clp

    db2 -tvf whatever.sql > whatever.log

  9. #9
    Join Date
    Feb 2004
    Location
    Terre Haute, IN
    Posts
    7
    Quote Originally Posted by Razor1973
    Sathyaram,

    I am not quite following. Sorry.

    From what I understand, you can use NOT LOGGED INITIALLY when creating or altering a table and this will cause that no transactions will be logged within that job or until you implicitly COMMIT.

    However, I am not creating nor altering this table. This is a database table I am simply inserting a large amount of rows into.

    By the way, you don't have to specify NOT LOGGED INITIALLY in either version when creating a table, if that's what you mean. We work with both v7 and v8 and none of our CREATE TABLE statements are using that at the time.

    Thanks again!
    In order to disable logging, a table should be declared with "not logged initially" as the option. If not, you can not turn off logging.

  10. #10
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    If you wish to INSERT without logging then you will have to ALTER the table and ACTIVATE NOT LOGGED INTIALLY in the same UOW as the INSERTs

    Version 7
    db2 "create table tnli(i int)"
    DB20000I The SQL command completed successfully.
    db2 "alter table tnli activate not logged initially"
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL1749N The NOT LOGGED INITIALLY attribute cannot be activated for table
    "TNLI" since it was not created with NOT LOGGED INITIALLY. SQLSTATE=429AA


    Version 8 FP 5 :
    db2 "create table tnli(i int)"
    The SQL command completed successfully.
    db2 "alter table tnli activate not logged initially"
    DB20000I The SQL command completed successfully.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  11. #11
    Join Date
    Jul 2004
    Posts
    53
    I think this could be another solution, although I fear it might be slower. Keep in mind we're talking millions of rows.

    I need to try it as well.

    Thanks!



    Quote Originally Posted by ccsoftball7
    Is a cursor load an option?

    ------------------------------------------------------------------
    declare C01 cursor for
    ..(select A.*
    .....from schema.table
    ...where something_is_true);

    load from C01 of cursor messages "./message_file.msgs" replace into
    .....schema.target_table nonrecoverable;

    COMMIT;
    ------------------------------------------------------------------
    store the above in a file whatever.sql (the . above represents a space for viewing purposes only)

    then call with clp

    db2 -tvf whatever.sql > whatever.log

  12. #12
    Join Date
    Jul 2004
    Posts
    53

    Thumbs up

    Another reason to go v8 in production soon!

    Thanks!



    Quote Originally Posted by sathyaram_s
    If you wish to INSERT without logging then you will have to ALTER the table and ACTIVATE NOT LOGGED INTIALLY in the same UOW as the INSERTs

    Version 7
    db2 "create table tnli(i int)"
    DB20000I The SQL command completed successfully.
    db2 "alter table tnli activate not logged initially"
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL1749N The NOT LOGGED INITIALLY attribute cannot be activated for table
    "TNLI" since it was not created with NOT LOGGED INITIALLY. SQLSTATE=429AA


    Version 8 FP 5 :
    db2 "create table tnli(i int)"
    The SQL command completed successfully.
    db2 "alter table tnli activate not logged initially"
    DB20000I The SQL command completed successfully.

Posting Permissions

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