Hello
We are investigating the possibility to partition a large database that's already in production. We are running 64 bit db2 9.5 fp7 on server 2003 x64. The application doesn't support db2 9.7. Its a 3rd party application with very limited support from developers.

I have these questions when trying to get a better understanding of the concept.

Is it possible to partition a table without going offline?

If we partition a table based on month, for example from 2007-01 to 2011-12, and the current date is 2011-03 and the latest data is 2011-03.
- Will new partitions be created automatically when 2011-04 data gets inserted? Or are the partitions pre created empty for empty months?
- What happens 2012-01? Will a new partition be created automatically, or will any new data from that point on be kept in the 2011-12 partition? If the latter, do we need to redo the process again from 2012-01 and forward?

Are there any possibility to get the Admin move table from 9.7 into 9.5?

Regards

Hi,

it's not possible to partition the table without an offline maintenance window. If you create partition from 2007-01 to 2011-12, then for each month the data will be written in correct partition. So there nothing to do for 2011-04. You'll have to create new partitions for 2012-01 and forward.

Cheers
nvk

Hello

Thanks for your answer. I just want to make sure.

In my example, if we partition our table today from 2007-01 to 2011-12 and the database today only contains values equal to or less than 2011-03, you say that there is nothing "to do" for 2011-04. But surely when we arrive in 2011-04 and data gets inserted in month 2011-04, this will work right?

I mean a unique partition with the values of 2011-04 will be filled?

Regards

If you stay with DB2 9.5, all you indexes will be global indexes. The main disadvantage of global indexes is that when you detach a partition, the indexes be will cleaned up in the background, but it could take a while and will consume some resources.

If you do use 9.7 and want to have partitioned indexes, the partitioning key must be part of any unique index (which includes the PK).

You can't convert to a partitioned table on-line, unless you want do it manually (simulating the 9.7 admin move) by loading data into a new table and then rename old table, and rename new table, etc.

Keep in mind that it is extremely unlikely that an application that works in 9.5 will not work in 9.7, even if not officially supported by the vendor.

As you said you have already created partitions till 2011-12. So data for 2011-04 will be stored in the defined partition.

...
STARTING('2011-01-01') INCLUSIVE ENDING('2011-01-31') INCLUSIVE in TS2011-01
STARTING('2011-02-01') INCLUSIVE ENDING('2011-02-28') INCLUSIVE in TS2011-02
STARTING('2011-03-01') INCLUSIVE ENDING('2011-03-31') INCLUSIVE in TS2011-03
STARTING('2011-04-01') INCLUSIVE ENDING('2011-04-30') INCLUSIVE in TS2011-04
STARTING('2011-05-01') INCLUSIVE ENDING('2011-05-31') INCLUSIVE in TS2011-05
...

But you'll get an Error for 2012-01-01 if the last Entry is not specified with MAXVALUE.