Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2005
    Posts
    4

    Unanswered: milions of delete

    Hi boys
    I need your help!!
    Ive a tablespaces not partitioned with one table inside in a Db2 for os390 v 7.2.
    every year I've to load 10 milions rows group by 10-15 companies

    when i've to reload a company i've to delete that company-year and load it.
    here is the problem.....i've to do quite 2 milions delete to clear a company!!!!
    the mainframe crash !!!!!!!!!!!!
    how can i do???

    help meeeeeeeeeeeeeeeeeeeeeeeeeeee

    sandrino

  2. #2
    Join Date
    May 2003
    Location
    Amsterdam
    Posts
    65
    It would be a lot easier if the tablespace was partitioned on year.
    But if you are loading all the companies at the same time
    you could a load (replace) for the first company. This empties the complete tablespace. Then do a load (resume yes) for the remaining companies.
    Anton Versteeg
    IBM Netherlands

  3. #3
    Join Date
    Mar 2005
    Posts
    4
    Quote Originally Posted by antonv
    It would be a lot easier if the tablespace was partitioned on year.
    thanks antonv!!!

    con i make the partition with an "alter tablespaces"?
    and then what can i do?

  4. #4
    Join Date
    May 2003
    Location
    Amsterdam
    Posts
    65
    No you must define a new partitioned tablespace . Ask your DBA.
    Anton Versteeg
    IBM Netherlands

  5. #5
    Join Date
    Jan 2005
    Posts
    191
    Another possibilty is to use REORG ... DISCARD.

    BTW There is no V7.2 on OS390 - it's just v7.

    James Campbell

  6. #6
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    On AIX (db2 udb ese)

    we could use : db2 " import from /dev/null of del replace into tablename" to delete millions of records together.

    Is there any equivalent of this available for M/F platform too for deletion of millions of rows?

    Jayanta Datta
    Xansa India

  7. #7
    Join Date
    Jan 2005
    Posts
    191
    On m/f you can do a LOAD REPLACE with an empty input file to do the same thing. However both this and a load from /dev/null will delete all rows in the table. The orginal requirement was for a selective delete.

    James Campbell

  8. #8
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    James thanks a lot for the info.

    Would it be a good suggestion:
    If this delete process is really taking enough time: then--
    a> Export the selected rows to some flat file xx.dat
    b> do a import from /dev/null to the table
    c> Load the xx.dat file into the table.

    Thx

Posting Permissions

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