Optimizing large tablespace

Question

We have a very large table LARGEHISTORY table which is having millions of records. Currently the table is using 50GB of table space and maximum allowed table space is 65GB in DB2 (DB2 v9.5.301.436). The table is growing very fast and we need to do something very fast to overcome the problem.

Database: DB2 v9.5.301.436

Solutions which we already though of:

1. Increase the table space size, which can be achieved using below two options:

   1. Alter table space of LARGEHISTORY from 65GB to 2 TB

      Advantage:

      • Only few commands need to be executed to increase the table space.

      Disadvantage:

      • We need to execute reorg which will require huge downtime.

      • The increase in table space will not show effect unless we do reorg

   2. Create a new separate (2 TB) table space and move the LARGEHISTORY table from old to new table space using procedure or using insert select statement

      Advantage:

      • Only few commands need to be executed to create a new table space.

      • The Table can be safely moved to new table space.

      Disadvantage:

      • The LARGEHISTORY should be brought offline to ensure there is no transaction till the migration is complete.

      • Copying 50 GB of data might require huge downtime. There is a chance of procedure failing as it might not be able to handle such huge data.

      • Insert select statement may not work for such a huge data.

      Note: the latest version of DB2 have feature of moving table from old to new without downtime.

2. Archiving or pruning old data

   Advantage:

   • Can be done easily using a scheduled job.

   Disadvantage:

   • The old data will go offline, if there is any requirement of fetching report we will need manual intervention.

   • All the existing reports will be impacted and will require change.

   • We will require executing reorg frequently to optimize the table and table space to improve the performance.

3. Create a new table in a new table space and rename the old table to LARGEHISTORY_OLD

   Advantage:

   • We can easily insert new data in new table very fast.

   Disadvantage:

   • While retrieving we need to make use of join/union all on old and new table or create a new view.

   • We cannot update the data using view (unless materialized view is used which is expensive)

   • All the existing reports will be impacted and will require change.

Please let me know if you have a better option. As of now we are considering Option 1.1. With Option 1.1 I'm not sure whether there will any impacts on code.

Answer 1

Use table partitioning. It requires a downtime, however it will prevent future problems: http://www.ibm.com/developerworks/data/library/techarticle/dm-0605ahuja2/

You create a table partition for each period (month, semester), and you just attach and detach partitions.

You have to check the license (db2 edition)

Answer 2

create table t1
(    ...
,    sales_date date not null 
         check ( sales_date between '2012-01-01' and '2012-12-31' )
) in tblspc1 index in inxspc1 long in longspc1;

create table t2
(    ...
,    sales_date date not null 
         check ( sales_date between '2013-01-01' and '2013-12-31' )
) in tblspc2 index in inxspc2 long in longspc2;

create table t3
(    ...
,    sales_date date not null 
         check ( sales_date between '2014-01-01' and '2014-12-31' )
) in tblspc3 index in inxspc3 long in longspc3;


create view LARGEHISTORY as (
    select ..., sales_date from t1
    union all 
    select ..., sales_date from t2
    union all 
    select ..., sales_date from t3
)

attach/detach partitions by recreate the view