Results 1 to 5 of 5
  1. #1
    Join Date
    May 2005
    Posts
    4

    Unanswered: Deleting all records in many tables

    Hi everyone,

    This is my first time posting. I am quite new to DB2. My company is using DB2/UDB v8.1.6 on a Win2k3 Box to run J.D. Edwards application. I am the unofficial DB2 administrator in the company.

    I have a question which I hope someone can help me with. It's regarding deleting a whole bunch of tables in a script files.

    To help me speed up the deletion, I know I have to issue a command:

    ALTER TABLE table1 ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;

    to reduce the logging overhead.

    My question is that, in my script. If I have 100 tables, do I need to run the ALTER TABLE command for the 100 tables 100 times?

    E.g. Script1.sql:

    ALTER TABLE table1 ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
    DELETE FROM table1;
    ALTER TABLE table2 ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
    DELETE FROM table2;
    ALTER TABLE table3 ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
    DELETE FROM table3;
    .
    .
    .
    ALTER TABLE table100 ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
    DELETE FROM table100;

    COMMIT;

    Many thanks if anyone could provide some insights.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Issue 100 IMPORT REPLACE or LOAD REPLACE statements with an empty data file. The filled tables will all be replaced with the data of the empty file, resulting in empty tables.

    db2 import from emptyfile.del of del replace into table1
    db2 import from emptyfile.del of del replace into table2
    ....
    db2 import from emptyfile.del of del replace into table100
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    May 2005
    Posts
    4
    Hi Wim,

    Thanks for the suggestion.

    I am just wondering whether this will take long to run? Reason I'm asking is because some of the tables I'm trying to delete contains millions of records.

  4. #4
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    The best way to delete such huge table is to IMPORT blank files into those tables as Wim suggested.
    We have tried this for tables with more than 50 Million records and it got deleted in few seconds.

    Create a blank file blank.csv and run the following:

    db2 "import from blank.csv of del replace into tb_tabl1"

    Thanks,
    Jay

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Well, actually the option you specify, WITH EMPTY TABLE, should delete all data in the table, so you don't need to do the DELETE part.

Posting Permissions

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