How to roll-out old data in V8 EEE, migrated to V9.5 is not an option
in [DB2]
|
Prev: Installed client V9.5 on Linux --> file access error
Next: How to roll-out old data in V8 EEE, migrated to V9.5 is not anoption
From: Richard on 27 May 2010 00:59 All, For business reason we have a large datawarehouse on V8.1 AIX, customer will not let us migrate db2 to V9.x. So we can't take advantage of the table range partitioning feature to perform roll-in/ out. This datawarehouse has reached the prescribed limit, that is to keep 7 yr worth of data. Beginning very soon, like next month, we need to start taking off old data based on trandate. Trandate is one of the columns of the partitioning key. We can delete nightly to drop off single day's data that way won't overwhelm logspace. Or we can try weekly even monthly. Questions are: Is there a better way to do this ? how do we reclaim the space left open by the deletes ? Can you REORG it like you would on a regular table ? Thank you in advance.
From: Richard on 27 May 2010 12:07 Amazing that you can make up a sort of table partition using check constraint before IBM formally released TP. Thanks for info. So i have to reorg the table after deletes to reuse the space. About the table reorg operation, REORG table xx.mytable index xx.myprimarykey on all dbpartitionums hmm, the partitioning key on the large table tells it to distribute rows according to the partitioning key hashed values, now the INDEX clause on REORG forces the rows to be cluster according to index, primary key in my case, causing conflict with the partitioning columns ? Yes, I will have to REORG the indexes too. That part is straight forward. What about the "REDISTRIBUTE database partitiongroup mypartitiongroup UNIFORM" command ? It's a bigger operations, but we have kept the partitiongroups small in the sense not too many tablespaces in each group, so a redistribute will only touch a few targeted tablespaces and tables wherein. Will REDISTRIBUTE replaces the REORG then ? Thank you in advance. RS
From: Mark A on 27 May 2010 13:39 "Richard" <rsl101(a)gmail.com> wrote in message news:e1181ecf-4b2d-4109-a676-9503c662148e(a)v18g2000vbc.googlegroups.com... > Amazing that you can make up a sort of table partition using check > constraint before IBM formally released TP. > Thanks for info. UNION ALL views work great, except that if you want to partition by date, you constantly have to change the view definition, which could be a problem on a system that requires continuous availability.
From: Helmut Tessarek on 27 May 2010 18:15 Hi Richard, > hmm, the partitioning key on the large table tells it to distribute > rows according to the partitioning key hashed values, now the INDEX > clause on REORG forces the rows to be cluster according to index, > primary key in my case, causing conflict with the partitioning > columns ? wow, are you telling me that you are using a pk which is different than the partitioning key or a subset of it? please post the columns for your partitioning key and for your primary key. > What about the "REDISTRIBUTE database partitiongroup > mypartitiongroup UNIFORM" command ? You only need to redistribute your data, if you add/remove a node or if you create your own partitioning map. In your case, you only have to do a reorg. > Will REDISTRIBUTE replaces the REORG then ? No. -- Helmut K. C. Tessarek DB2 Performance and Development /* Thou shalt not follow the NULL pointer for chaos and madness await thee at its end. */
From: Helmut Tessarek on 28 May 2010 02:19
>> hmm, the partitioning key on the large table tells it to distribute >> rows according to the partitioning key hashed values, now the INDEX >> clause on REORG forces the rows to be cluster according to index, >> primary key in my case, causing conflict with the partitioning >> columns ? > > wow, are you telling me that you are using a pk which is different than the > partitioning key or a subset of it? > please post the columns for your partitioning key and for your primary key. Still waiting for an answer to my question.... :-) -- Helmut K. C. Tessarek DB2 Performance and Development /* Thou shalt not follow the NULL pointer for chaos and madness await thee at its end. */ |