Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Jan 2012
    Posts
    16

    Red face Unanswered: db2 - transaction log is full

    Hi,
    in my db2 database, in a table i have 80,000 rows, when i try to delete programmatically

    "delete from table"

    This end up with error - "SQL0964C The transaction log for the database is full"

    In my config files,
    i have LOGRETAIN = OFF and
    USEREXIT = OFF

    already i have used enough space to allocate log path.

    What else i need to do, to make log off...?

    any idea..?

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    2 options
    alter table xx not logged initially
    delete from table
    both operation in the same uow
    or try to call db2admin stored proc to execute import from /dev/null of del replace into xx
    from application
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Nov 2011
    Posts
    334
    another option:
    increase log file size or/and log file number....

  4. #4
    Join Date
    Jan 2012
    Posts
    16
    Quote Originally Posted by przytula_guy View Post
    2 options
    alter table xx not logged initially
    delete from table
    both operation in the same uow
    or try to call db2admin stored proc to execute import from /dev/null of del replace into xx
    from application
    I tried the first option,
    "Alter table xxx activate not logged initially" executed this command, But didnt work....

    second option is like changing the query... if i do this..i need to make alot of changes in my app. so envt config settings would be better to do... and which handle future scenarios as well

  5. #5
    Join Date
    Jan 2012
    Posts
    16
    Quote Originally Posted by fengsun2 View Post
    another option:
    increase log file size or/and log file number....
    Increase the log file size is allowing to write more log...
    But this could impact in Hard disk space... i dnt want to to do that....

  6. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    as indicated before :
    both operation in the same uow
    the commit command (explicit or implicit) will remove this setting
    Reducing logging with the NOT LOGGED INITIALLY parameter
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  7. #7
    Join Date
    Jan 2012
    Posts
    16
    "Alter table" and "delete records" these both operation should we use in same unit of work... ? is this what you meant...?

    I am not sure how to make it same unit of work....?

    Could you give me the comments in this....

  8. #8
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    if started from a file in command line - look at command options and autocommit
    LIST COMMAND OPTIONS
    if from an application : no commit in between
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  9. #9
    Join Date
    Nov 2011
    Posts
    334
    basically that means "Alter table" and "delete records" should be in the same transaction.....
    Do not commit work between the operation of alter table and delete records。
    PLZ turn off the autocommit option/attributes in your program if it is on....
    Last edited by fengsun2; 01-05-12 at 09:20.

  10. #10
    Join Date
    Nov 2011
    Location
    Chennai
    Posts
    27
    Hope this would work.

    alter table XXX activate not logged initially;
    delete from XXX;
    commit work;

  11. #11
    Join Date
    Jan 2012
    Posts
    16
    Quote Originally Posted by thawfii88 View Post
    Hope this would work.

    alter table XXX activate not logged initially;
    delete from XXX;
    commit work;
    Hi,
    i have turned off my autocommit options, and in program i applied the above queries.....
    alter table xxxx activate not logged initially;
    and then
    delete from xxxx

    But it throws exception during delete query execution..... says Transaction log is full.....

    am i missing any thing.. ?

  12. #12
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    have you started the script with +c option
    otherwise it is autocommitted and alter table is committed and not used
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  13. #13
    Join Date
    Jan 2012
    Posts
    16
    Quote Originally Posted by przytula_guy View Post
    have you started the script with +c option
    otherwise it is autocommitted and alter table is committed and not used
    This change adding +c with query , will be like modifying queries everywhere i needed... If i have 200+ places, changing it to all the area is not good way of solution. I am trying to make it config level itself.... So that in future the same problem will not happen in any other areas.....

  14. #14
    Join Date
    Jan 2010
    Posts
    335
    You have not specified your environment. If you have DB2 9.7, then you could use truncate!
    TRUNCATE statement - IBM DB2 9.7 for Linux, UNIX, and Windows

  15. #15
    Join Date
    Aug 2011
    Posts
    2
    if you are on db2 9.7 try below command to delete all the rows from the table...hope you already have the backup, just in case...

    db2 "truncate table schema.table reuse storage immediate"

    If you want to reclaim the space try the below command on the tablespace under which this table resides.

    db2 "alter tablespace TSNAME reduce max"

Tags for this Thread

Posting Permissions

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