Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Posts
    1

    Unanswered: zero space left to delete large table.

    I have a current issue where I have only a few megs of disc space left on a system which houses an application and a DB2 database. The main table currently has 700k rows in it.
    The problem I have when I try to delete all the data in the table is that the transaction logs fill the disc up and hence causing the delete to stop. Therefore I need a way to either truncate or drop the table or commit after each 100 row deletes.
    I have been looking into the following;

    1) Truncate
    2) import null file as replace
    3) Create a copy of the table structure, drop the original table, then rename the copy.
    4) commit after every 100 rows?

    I am using the command center to interface with the database, which is a bit


    1) No equivalent command in db2.
    2) IMPORT FROM /DEV/NULL OF DEL REPLACE INTO TABLENAME - does not work, still getting similar errors to a standard delete.
    3) Not sure the correct syntax
    4) Not sure the syntax.


    My preference would be to 2 if I can get it working, if not then 4 would be fine, I dont mind if the process runs for a few hours.

    Any help would be appreciated.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: zero space left to delete large table.

    Delete a couple of old log files or if the need for the log files is felt necessary, copy them to another machine and delete them ...

    If you have only the active number of logfiles ..

    2) There may be a little bit of log space required for this operation, but am surprised when you say the transaction log files fill up

    3) Have a look at the db2look command in the command reference ... Get the DDL using this script ... Then
    db2 drop table
    followed by creating the table ...
    4) To commit every hundered rows, you may need to write a stored procedure or manually segment your data

    HTH

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

  3. #3
    Join Date
    Jul 2002
    Location
    ATL
    Posts
    170
    import from /dev/null of del replace into tabname
    /dev/null should be lower case

  4. #4
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    35
    Since your table is just 700K has stated in previous post it is probably because a bunch of old logs that are lying around; but for future reference a way to ¨truncate¨ a table and not generate logs would be to initially create the table with the the clause NOT LOGGED INITIALLY then when you want to truncate it you can use:

    alter table x acitvate not logged initially empty table;

    Caution: After this their are no rollback possible.

  5. #5
    Join Date
    Sep 2003
    Posts
    1

    Talking Re: zero space left to delete large table.

    hi,
    you can create a txt file for zero bytes using notepad.
    then import from <filename> of del replace into tablename.
    thanks
    shailendra

  6. #6
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    35
    The REPLACE in the import is equivalent to a DELETE e.i. it generate logs ...

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    I beg to differ ... REPLACE Option is not equivalent to DELETE .. REPLACE Option does write to logs and they are purely some sort of control information and has nothing to do with the data being replaced ...

    And these log records are very small in number

    Cheers
    Sathyaram

    Originally posted by dlafreni
    The REPLACE in the import is equivalent to a DELETE e.i. it generate logs ...
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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