Data Distribution is not Balance in parts

Leila Hosseini

Data Distribution is not Balance in parts







Hello 

We has a huge partitioned table those rows has been archived annually.In this regards row distribution in parts would not be balance.

Table space has 151 parts and last ten parts are so crowded  while first ten parts are almost empty.

Also we have a constrain as stream which force 10 fix numbers as part value of ten parts , for this reason it is not easy to use REORG REBALANCE on whole tablespace which leads to lose streams as part values.

I am wondering to know is there any keyword which says to Db2 after REORG REBALANCE of 10 parts ,just populate data in first 8 parts and leave two parts Empty?

Or any thoughts which may help us to shift Data into the first parts?

Thanks for your help.

Regards 

Leila






Walter Janißen

RE: Data Distribution is not Balance in parts
(in response to Leila Hosseini)

Hi

Some years ago I talked to Haakon Roberts about that topic, e.g. to leave some partitions empty. But I gear, I never raised an RFE. Also, as far as I know, PCTFREE or FREEPAGE is not honored by Reorg REBALANCE. 

What about ALTER LIMITKEY and then a materilizing Reorg.

Michael Hannan

RE: Data Distribution is not Balance in parts
(in response to Leila Hosseini)

In Reply to Leila Hosseini:

Table space has 151 parts and last ten parts are so crowded  while first ten parts are almost empty.

I am wondering to know is there any keyword which says to Db2 after REORG REBALANCE of 10 parts ,just populate data in first 8 parts and leave two parts Empty?

Or any thoughts which may help us to shift Data into the first parts?

Leila,

Not sure what "crowded" means. Not a Db2 term. Crowding could sound good in some ways, depending on what it means. Not sure if you mean getting close to "4 Gig" as example, or just much bigger than some other parts. If part sizes are quite imbalanced, I don't care, unless proved to be messing up parallel access paths. 

I would not get too obsessed with "Balance". There is no law that says partitions must be balanced. The main point is whether a partition has got too large for you to be able to manage. Rebalance is to redistribute some rows between a set of partitions when some parts are deemed too big for one reason or another.

Also I don't understand the requirement to leave some partitions empty. That is a solution to which problem?

So you have 10 fixed numbers that are distributing rows currently among 10 partitions. That does not need to stay in a 1 to 1 mapping.  The partition limit key could be changed to be more than just that 1 column, If not so already, with Reorg required to implement it. So 1 part could have more than 1 of those 10 fixed values, or a single one of those fixed values could be spread in more than 1 partition. 

You might need a smart process to calculate what the new partition range keys should be. Obviously all partitions with a change key range will have to be reorg repopulated, or whatever goes into reorg Pending.

Try it all out of a small test table first. I am not sure how many changes to partitioning can be achieved with the one Reorg.  I have read that there are restrictions when changing the number of parts.

Michael Hannan,

DB2 Application Performance Specialist

Edited By:
Michael Hannan[Organization Members] @ Jun 08, 2020 - 08:59 AM (Australia/Melbourne)
Michael Hannan[Organization Members] @ Jun 08, 2020 - 09:01 AM (Australia/Melbourne)
Michael Hannan[Organization Members] @ Jun 08, 2020 - 09:03 AM (Australia/Melbourne)
Michael Hannan[Organization Members] @ Jun 08, 2020 - 09:04 AM (Australia/Melbourne)
Michael Hannan[Organization Members] @ Jun 08, 2020 - 09:06 AM (Australia/Melbourne)

steen rasmussen

Data Distribution is not Balance in parts
(in response to Michael Hannan)
You can’t leave some parts empty but if you are on Db2 12 you can insert new partitions in the middle so and basically accomplish the same

Steen

Sent from my iPhone

> On Jun 7, 2020, at 6:57 PM, Michael Hannan <[login to unmask email]> wrote:
>
> 
> In Reply to Leila Hosseini:
>
> Table space has 151 parts and last ten parts are so crowded while first ten parts are almost empty.
>
> I am wondering to know is there any keyword which says to Db2 after REORG REBALANCE of 10 parts ,just populate data in first 8 parts and leave two parts Empty?
>
> Or any thoughts which may help us to shift Data into the first parts?
> Leila,
>
> Not sure what "crowded" means. Not a Db2 term. Crowding could sound good in some ways, depending on what it means.
>
> I would not get too obsessed with "Balance". There is no law that says partitions must be balanced. The main point is whether a partition has got too large for you to be able to manage. Rebalance is to redistribute some rows between a set of partitions when some parts are deemed too big for one reason or another.
>
> So you have 10 fixed numbers that are distributing rows currently among 10 partitions. That does not need to stay in a 1 to 1 mapping. The partition limit key could be changed to be more than just that 1 column, with Reorg required to implement it. So 1 part could have more than 1 of those 10 fixed values, or a single one of those fixed values could be spread in more than 1 partition.
>
> You might need a smart process to calculate what the new partition range keys should be. Obviously all partitions with a change key range will have to be reorg repopulated, or whatever goes into reorg Pending.
>
> Try it all out of a small test table first. I am not sure how many changes to partitioning can be achieved with the one Reorg.
>
> Michael Hannan,
>
> DB2 Application Performance Specialist
>
>
> Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription
>
> This email has been sent to: [login to unmask email]
> Try BCV5, the BCV5 Masking Tool, & XDM a rapid Refresh/Clone/TDM Suite for Db2 z & distributed.
> DBARS -Audit,record,& block Db2 accesses to sensitive data real-time, NO audit trace or log required
> http://www.ESAIGroup.com/IDUG
>
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>

Paul Ruane

RE: Data Distribution is not Balance in parts
(in response to Leila Hosseini)

Leila

I raised the following idea with IBM:

https://ibm-data-and-ai.ideas.aha.io/ideas/DB24ZOS-I-1038

It only has seven votes. Please vote for it if you think it would help.

Paul 


In Reply to Leila Hosseini:








Hello 

We has a huge partitioned table those rows has been archived annually.In this regards row distribution in parts would not be balance.

Table space has 151 parts and last ten parts are so crowded  while first ten parts are almost empty.

Also we have a constrain as stream which force 10 fix numbers as part value of ten parts , for this reason it is not easy to use REORG REBALANCE on whole tablespace which leads to lose streams as part values.

I am wondering to know is there any keyword which says to Db2 after REORG REBALANCE of 10 parts ,just populate data in first 8 parts and leave two parts Empty?

Or any thoughts which may help us to shift Data into the first parts?

Thanks for your help.

Regards 

Leila






Leila Hosseini

Data Distribution is not Balance in parts
(in response to Paul Ruane)
Hello everyone Thanks for your response.

PaulThanks.I did vote.
If it happened that would be great.
if REORG REBALANCE could leave some parts empty it would resolve the issue ,as after REBALANCE a few rows left in each part .while it has chance to release some parts and do more efficient  rebalance regards to number of rows!


Sent from Yahoo Mail for iPhone


On Tuesday, June 9, 2020, 3:38 AM, Paul Ruane <[login to unmask email]> wrote:


Leila

I raised the following idea with IBM:

https://ibm-data-and-ai.ideas.aha.io/ideas/DB24ZOS-I-1038

It only has seven votes. Please vote for it if you think it would help.

Paul 


In Reply to Leila Hosseini:








Hello 

We has a huge partitioned table those rows has been archived annually.In this regards row distribution in parts would not be balance.

Table space has 151 parts and last ten parts are so crowded  while first ten parts are almost empty.

Also we have a constrain as stream which force 10 fix numbers as part value of ten parts , for this reason it is not easy to use REORG REBALANCE on whole tablespace which leads to lose streams as part values.

I am wondering to know is there any keyword which says to Db2 after REORG REBALANCE of 10 parts ,just populate data in first 8 parts and leave two parts Empty?

Or any thoughts which may help us to shift Data into the first parts?

Thanks for your help.

Regards 

Leila








Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
Try BCV5, the BCV5 Masking Tool, & XDM a rapid Refresh/Clone/TDM Suite for Db2 z & distributed.
DBARS -Audit,record,& block Db2 accesses to sensitive data real-time, NO audit trace or log required
http://www.ESAIGroup.com/IDUG



Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2



Walter Jani&#223;en

RE: Data Distribution is not Balance in parts
(in response to Leila Hosseini)

But the problem is how should DB2 determine the limitkeys for the empty partitions? If it is the last partition it may be easy, but if not, I think it is impossible.

Leila Hosseini

Data Distribution is not Balance in parts
(in response to Walter Janißen)
If in Db12 it is possible to add part in middle then it would happen to have empty part by REBALANCE.


Sent from Yahoo Mail for iPhone


On Tuesday, June 9, 2020, 2:17 PM, Walter Janißen <[login to unmask email]> wrote:


But the problem is how should DB2 determine the limitkeys for the empty partitions? If it is the last partition it may be easy, but if not, I think it is impossible.

Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
Try BCV5, the BCV5 Masking Tool, & XDM a rapid Refresh/Clone/TDM Suite for Db2 z & distributed.
DBARS -Audit,record,& block Db2 accesses to sensitive data real-time, NO audit trace or log required
http://www.ESAIGroup.com/IDUG



Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2



Paul Ruane

RE: Data Distribution is not Balance in parts
(in response to Walter Janißen)

Thanks Walter. I'll let IBM solve that problem ;-)

In Reply to Walter Janißen:

But the problem is how should DB2 determine the limitkeys for the empty partitions? If it is the last partition it may be easy, but if not, I think it is impossible.

Walter Jani&#223;en

RE: Data Distribution is not Balance in parts
(in response to Leila Hosseini)

Hi Leila

If you insert a partition, this partition is of course empty. But if you REBALANCE including this partition, it will no longer remain empty. REBALANCE never leaves a partition empty.

James Campbell

RE: Data Distribution is not Balance in parts
(in response to Walter Janißen)

"REBALANCE never leaves a partition empty."

What never?  Well hardly ever.

If the rebalanced partitions have only a single row between them ... .

Edge cases are always tricky.

 

James Campbell

 

Post By: Walter Janißen Jun 12, 2020 06:23 AM 

Hi Leila

If you insert a partition, this partition is of course empty. But if you REBALANCE including this partition, it will no longer remain empty. REBALANCE never leaves a partition empty.