Results 1 to 9 of 9

Thread: Bulk Delete

  1. #1
    Join Date
    Feb 2007
    Posts
    31

    Unanswered: Bulk Delete

    Hi,
    Can you suggest methods to do bulk delete.? The table contain 7 million rows in that.

    Regards
    Mohan
    Regards
    Mohan

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    IMPORT FROM /dev/null of del replace into tablename

    or

    ALTER TABLE tablename NOT LOGGED INITIALLY WITH EMPTY TABLE

    The first one is a command and cannot be issued from a SQL Interface like JDBC.

    The second one is SQL but requires higher privileges on the table.

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

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by sathyaram_s
    IMPORT FROM /dev/null of del replace into tablename
    Hi, this command deletes all rows from table.

    On Windows there is:
    import from nul: of del replace into tablename.

    Hope this helps,
    Grofaty

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Also on z/OS this is indeed the fastest way to bulk delete:
    - "LOAD REPLACE" from an empty dataset.
    or
    - REORG ... DISCARD FROM table WHEN (0 = 0)

    As can be expected, REORG ... DISCARD WHEN is also the most efficient way to bulk delete only part of the table rows: use the condition which you would put in "DELETE ... WHERE" as the "REORG ... DISCARD ... WHEN" condition. (stage-1 / sargable only)
    Last edited by Peter.Vanroose; 03-07-07 at 14:56.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    Feb 2007
    Posts
    31
    Hi,
    I want to delete a praticular(eg:empid) 10 lakh rows .The total rows in that table contain 35 lakh rows. Can I use the Load command or is there any specific command is there ..?
    Regards
    Mohan

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You can either:
    1. send one (or more) DELETE statements, or
    2. you export the data you want to keep, empty/truncate the table and then import/load the previously exported data.


    Maybe it is an option for you to use NOT LOGGED INITIALLY when doing the DELETE. That avoids logging, but has same side effects that may not be an option in your environment.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    What are "lakh rows" ?

    Quote Originally Posted by d_mohan81
    Hi,
    I want to delete a praticular(eg:empid) 10 lakh rows .The total rows in that table contain 35 lakh rows. Can I use the Load command or is there any specific command is there ..?
    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

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    It is a different(????) number(!!) system where 10 lakhs = 1 million ...
    So, the OP is talking about 1 m and 3.5 m records

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

  9. #9
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by d_mohan81
    I want to delete a praticular(eg:empid) 10 lakh rows .The total rows in that table contain 35 lakh rows. Can I use the Load command or is there any specific command is there ..?
    On z/OS, use "REORG ts DISCARD FROM table WHEN (EMPID = value)"
    See http://publibz.boulder.ibm.com/cgi-b...20040209165609
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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