Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2005
    Posts
    73

    Unanswered: Removing 50 percent of data

    I have a question about Removing 50 percent od database from a big tables which has got more than one million rows.

    In how many ways i can do this .

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    The general concept would be to use a DELETE SQL statement, with a where condition that excludes the 50% that you need to keep.

    A common approach would be to use a timestamp field to decied what stayes or goes.

    Code:
    Delete from YourTable Where TimeStampField < '1-JAN-2004'
    might be just the ticket, for instance.

    Now, you should possibly keep the old data in an archival table; if so, first copy the records from the active table to the archive table.

    You don't say why you feel you need to remove some of the data, but if it's because of data access delays, you should be able to index the table so that this issue should largely go away. I'm not familiar with DB2, it should allow you to add views to limit the amount of data returned. (you could define a view of your table with a where clause to filter out the old data, for instance)

    If you have a front-end client that access the data, but is slow in retrieving the table, you should take a close look at the design - in particular, make sure that you're not pulling a copy of the entire table to the client if the entire table contents aren't needed - instead, allow the database server to filter the results first.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by dba_udb
    I have a question about Removing 50 percent od database from a big tables which has got more than one million rows.

    In how many ways i can do this .
    Hi,
    what is the problem? Performance? One million rows is not a big table... What is the number of pages and page size (default is 4 kB)?
    Code:
    runstats on table schema.table
    
    select npages, card from syscat.tables where tabschema='schema' and tabname='table'
    What is your db edition and version? OS name and version?
    Regards,
    Grofaty

  4. #4
    Join Date
    Mar 2005
    Posts
    73
    grotafy-

    It was not a problem. But it was one of the questions i was asked in an interview.

    The question is like this.

    How could you delete 50 percent of data in a huge table?
    Which has got about 10 million or so.

    My answer was to export the data which you need ( if it is based on search condition) . and trunce the table data by importing zero bytes and reloading the previouly exported data.

    I would like to know the many more easy options.

    tx

  5. #5
    Join Date
    Apr 2007
    Location
    Chicago
    Posts
    68
    For z/OS DB2, you would want to run the REORG with the DISCARD option. Run it with the STATISTICS, LOG NO, and COPY options to get the biggest bang for the buck.

  6. #6
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by dba_udb
    grotafy-

    It was not a problem. But it was one of the questions i was asked in an interview.

    The question is like this.

    How could you delete 50 percent of data in a huge table?
    Which has got about 10 million or so.

    My answer was to export the data which you need ( if it is based on search condition) . and trunce the table data by importing zero bytes and reloading the previouly exported data.

    I would like to know the many more easy options.

    tx
    Hi,
    what is operating system name and version? DB2 edition and version, fixpack level?

    I would do the following on DB2/LUW:
    1. create new table with the same definition: create table schema.table_name like new_table
    2. insert rows needed into new table: insert into schema.new_table select * from schema.table where rows_you_need
    3. drop original table: drop table schema.table_name
    4. rename new table to old table name: rename table schema.new_table to table

    Hope this helps,
    Grofaty
    Last edited by grofaty; 09-21-07 at 04:39.

  7. #7
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    there is even faster way:
    I would do the following on DB2/LUW:
    1. create new table with the same definition: create table schema.table_name like new_table
    2. declare cursor for records you would like to keep:
    declare c1 cursor for select * from schema.table where rows_you_would_like_to_keep
    3. load from cursor: load from c1 of cursor insert into schema.new_table
    4. drop original table: drop table schema.table_name
    5. rename new table to old table name: rename table schema.new_table to table

    Hope this helps,
    Grofaty

Posting Permissions

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