Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2005
    Posts
    80

    Unanswered: Avoid Transaction log full

    I have a situation here. whenever I try to delete huge volumes of data the transaction logs are getting filled. so I cannot use plain delete from statement. what is the alternative to avoid such scenario's????

  2. #2
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    didnt get cant do plain delete..?
    try in blocks of say 10000 and commit
    or else increase logfilesz
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  3. #3
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    or use the alter table command with notlogged initially option
    and delete
    see infodoc for details of alter command
    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

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Or import/load from an empty file with the REPLACE option (but that will truncate the whole table).
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Sep 2008
    Posts
    6
    Hi,
    If you issue the command like

    $db2 "load from /dev/null of del replace into schema.tableName"

    it will delete all the data from the above mentioned table.

    Shubhrojyoti
    IBM Certified DB2 UDB DBA for LUW v8 and v9

  6. #6
    Join Date
    Aug 2002
    Posts
    76

    also can try table partitioning

    didnt say what version or platform you were on, but table partitioning LUW V9 and aboveis good option for planning out deletes of large amounts of data without impact on logs. setting up ranges based on a date, you can delete specific ranges rather than the whole table like the replace into on imports.

Posting Permissions

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