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 .

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.

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.

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)?
What is your db edition and version? OS name and version?
Regards,
Grofaty

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

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.

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

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