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 28 May 2010 15:16 On May 28, 2:19 am, Helmut Tessarek <tessa...(a)evermeet.cx> wrote: > >> 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. > */ On May 28, 2:19 am, Helmut Tessarek <tessa...(a)evermeet.cx> wrote: > >> 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. > */ Hi Helmet, My bad. PK on this olap-order table is: tradedate eg 200512 excluded days because OLAP granularity symtype eg 'stock' symbol eg 'MSFT' stock sym qtyfilled eg 100 for 100 shares traded price eg 28.5000 ordno partitioning key: tradedate ordno This distributes the rows evenly among the nodes. Delete oldest tradedate (trade month actually), and reorg will let me reclaim the space and distribute evenly again. Thinking of converting to MDC table and have tradedate as one of the dimensions. Other dimensions maybe symtype + tradetype (buy,sell,short sell,short cover...) which is not in the pk. Then I should be able to rollout a group of tradedate(months) at a time ? Thanks for looking at it. Let me know what you think.
From: Mark A on 28 May 2010 16:10 >"Richard" <rsl101(a)gmail.com> wrote in message >news:16c894af-0814-446c-8691-5aa9515e50eb(a)z17g2000vbd.googlegroups.com... > On May 28, 2:19 am, Helmut Tessarek <tessa...(a)evermeet.cx> wrote: > partitioning key: > tradedate > ordno > > This distributes the rows evenly among the nodes. Delete oldest > tradedate (trade month actually), and reorg will let me reclaim the > space and distribute evenly again. > > Thinking of converting to MDC table and have tradedate as one of the > dimensions. Other dimensions maybe symtype + tradetype (buy,sell,short > sell,short cover...) which is not in the pk. Then I should be able to > rollout a group of tradedate(months) at a time ? > > Thanks for looking at it. Let me know what you think. Why not just use ordno has DPF partitioning key? The DPF partitioning key does not have to be the entire PK, only included in the PK. Adding date to DPF key seems like just extra processing (but leave it in the PK per comments below). Then use tradedate as Table partitioning key (and partition by month) so you can detach the oldest month at one time. You will need to have both tradedate and ordno in the PK to allow for DPF partitioning and partitioned indexes in 9.7 for fast detachment of a partition (the table partitioning key must be in the PK if you want partitioned indexes). I would be careful about using MDC and don't quite see the need for it in your application as you have described it.
From: Helmut Tessarek on 28 May 2010 16:50 > PK on this olap-order table is: > tradedate eg 200512 excluded days because OLAP granularity > symtype eg 'stock' > symbol eg 'MSFT' stock sym > qtyfilled eg 100 for 100 shares traded > price eg 28.5000 > ordno > > partitioning key: > tradedate > ordno Somehow I thought so, since: If the UNIQUE option is specified, and the table has a distribution key, the columns in the index key must be a superset of the distribution key. That is, the columns specified for a unique index key must include all the columns of the distribution key (SQLSTATE 42997). > This distributes the rows evenly among the nodes. Delete oldest > tradedate (trade month actually), and reorg will let me reclaim the > space and distribute evenly again. Yes, this is correct. > Thinking of converting to MDC table and have tradedate as one of the > dimensions. Other dimensions maybe symtype + tradetype (buy,sell,short > sell,short cover...) which is not in the pk. Then I should be able to > rollout a group of tradedate(months) at a time ? I think Mark already answered this question, although you really would have to migrate to 9.7 to get the advantage of local indexes for table partitioning. -- 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: Richard on 28 May 2010 16:52
On May 28, 4:10 pm, "Mark A" <no...(a)nowhere.com> wrote: > > Why not just use ordno has DPF partitioning key? The DPF partitioning key > does not have to be the entire PK, only included in the PK. Adding date to > DPF key seems like just extra processing (but leave it in the PK per > comments below). > > Then use tradedate as Table partitioning key (and partition by month) so you > can detach the oldest month at one time. You will need to have both > tradedate and ordno in the PK to allow for DPF partitioning and partitioned > indexes in 9.7 for fast detachment of a partition (the table partitioning > key must be in the PK if you want partitioned indexes). > > I would be careful about using MDC and don't quite see the need for it in > your application as you have described it. Thanks for your remarks. Yes I saw that the ordno alone was good DPF partitioning key. The preceding trade month values were put there by previous designer and assume he knew what he was doing is not a foregone conclusion. If I can go to v9.7 I will do table partition by the trade month like you said. That would be ideal. But my customer won't sign off on the migration or pay for it. So I am stuck w/o the nice table partitioning feature, thats why I thought the next best thing is to convert to MDC then I can rolloff by trade month ? I still have to issue delete on the table but maybe the rows will come off faster and without need for REORG ? |