how to test with disorganized data repeatedly

william giannelli

how to test with disorganized data repeatedly

Does anyone have a "convenient" way of loading or inserting data in a disorganized way to test our reorgs? We are trying to test with a very large table but the inserts takes several hours.

thanks

Bill 

Jack Campbell

RE: how to test with disorganized data repeatedly
(in response to william giannelli)

Bill,

depends what you are trying to mimic in you disorganization (row sequence/index page splits/freespace).

one option might be load(replace) the data , where the UNLOADed data is sorted into an order other than the CLUSTERing index. 

If you need to mimic something more complex maybe start with the above LOAD after setting freepage/pctfree = 0 , which might allow you to do significantly less inserts to get page splits.Then reset freepage/pctfree to true values before the REORG.

Regards
Jack

Horacio Villa

how to test with disorganized data repeatedly
(in response to Jack Campbell)
I did that sometimes beginning with a full copy (data and indexes) and
then recovering TOCOPY before each test.











Michael Hannan

RE: how to test with disorganized data repeatedly
(in response to william giannelli)



In Reply to william giannelli:

Does anyone have a "convenient" way of loading or inserting data in a disorganized way to test our reorgs? We are trying to test with a very large table but the inserts takes several hours.

thanks

Bill 

Others already suggested sorting into some other sequence before LOAD back. Similarly you can change the clustering index to something else (currently a low clustering index), temporarily, Reorg, then change it back again, and Reorg again.

It is not clear to me that one would need to do this more than once or twice to check that Reorg does sequence data in Cluster sequence. If Runstats reports that the cluster index is 100% clustered (CLUSTERRATIOF = 1.0 and DATAREPEATFACTORF should perhaps equal NPAGESF for the data table when perfectly Reorged), that would be good enough for me. Note that 100% will not be achieved for cluster incompatible with partitioning scheme.

Michael Hannan,

DB2 Application Performance Specialist
CPT Global Ltd

Paul Ogborne

how to test with disorganized data repeatedly
(in response to Michael Hannan)
Hi,

- Change the existing clustering index to not clustering.
- Create a new clustering index using some very odd columns with high key cardinality.
- Reorg
- Drop new index
- Reinstate explicit clustering index.
- Test your reorgs.

Regards,
Paul

> On 9 Dec 2018, at 11:06, Michael Hannan <[login to unmask email]> wrote:
>
>
>
> In Reply to william giannelli:
>
> Does anyone have a "convenient" way of loading or inserting data in a disorganized way to test our reorgs? We are trying to test with a very large table but the inserts takes several hours.
>
> thanks
>
> Bill
>
> Others already suggested sorting into some other sequence before LOAD back. Similarly you can change the clustering index to something else (currently a low clustering index), temporarily, Reorg, then change it back again, and Reorg again.
>
> It is not clear to me that one would need to do this more than once or twice to check that Reorg does sequence data in Cluster sequence. If Runstats reports that the cluster index is 100% clustered (CLUSTERRATIOF = 1.0 and DATAREPEATFACTORF should perhaps equal NPAGESF for the data table when perfectly Reorged), that would be good enough for me. Note that 100% will not be achieved for cluster incompatible with partitioning scheme.
>
> Michael Hannan,
>
> DB2 Application Performance Specialist
> CPT Global Ltd
>
>
> 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]
> ESAi has well-regarded tools for Fast Cloning, Buffer Pool Tuning, Log Analysis, TDM & more.
> BCV4, BCV5, BPA4DB2, ULT4DB2... modern power tools to get the job done faster & easier than ever.
> 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
>

Phil Grainger

how to test with disorganized data repeatedly
(in response to Paul Ogborne)
Everyone is laser focussed on clustering

But what about “indref” and “offpos” rows?

There is more to a reorg than reclustering
________________________________
From: Paul Ogborne <[login to unmask email]>
Sent: 09 December 2018 11:33:44
To: [login to unmask email]
Subject: [DB2-L] - RE: how to test with disorganized data repeatedly

Hi,

- Change the existing clustering index to not clustering.
- Create a new clustering index using some very odd columns with high key cardinality.
- Reorg
- Drop new index
- Reinstate explicit clustering index.
- Test your reorgs.

Regards,
Paul

On 9 Dec 2018, at 11:06, Michael Hannan <[login to unmask email]<mailto:[login to unmask email]>> wrote:



In Reply to william giannelli:

Does anyone have a "convenient" way of loading or inserting data in a disorganized way to test our reorgs? We are trying to test with a very large table but the inserts takes several hours.

thanks

Bill

Others already suggested sorting into some other sequence before LOAD back. Similarly you can change the clustering index to something else (currently a low clustering index), temporarily, Reorg, then change it back again, and Reorg again.

It is not clear to me that one would need to do this more than once or twice to check that Reorg does sequence data in Cluster sequence. If Runstats reports that the cluster index is 100% clustered (CLUSTERRATIOF = 1.0 and DATAREPEATFACTORF should perhaps equal NPAGESF for the data table when perfectly Reorged), that would be good enough for me. Note that 100% will not be achieved for cluster incompatible with partitioning scheme.

Michael Hannan,

DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----

-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.

Chris Hoelscher

how to test with disorganized data repeatedly
(in response to Phil Grainger)
I sounds like they want to muck up the clustering ...

So what they really want is a cluster ... muck?

Chris Hoelscher
Technology Architect, Database Infrastructure Services
Technology Solution Services
Humana Inc.
123 East Main Street
Louisville, KY 40202
Humana.com
(502) 476-2538 or 407-7266

From: Grainger, Phil <[login to unmask email]>
Sent: Sunday, December 9, 2018 10:14 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: how to test with disorganized data repeatedly

Everyone is laser focussed on clustering

But what about "indref" and "offpos" rows?

There is more to a reorg than reclustering
________________________________
From: Paul Ogborne <[login to unmask email]<mailto:[login to unmask email]>>
Sent: 09 December 2018 11:33:44
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: how to test with disorganized data repeatedly

Hi,

- Change the existing clustering index to not clustering.
- Create a new clustering index using some very odd columns with high key cardinality.
- Reorg
- Drop new index
- Reinstate explicit clustering index.
- Test your reorgs.

Regards,
Paul

On 9 Dec 2018, at 11:06, Michael Hannan <[login to unmask email]<mailto:[login to unmask email]>> wrote:


In Reply to william giannelli:

Does anyone have a "convenient" way of loading or inserting data in a disorganized way to test our reorgs? We are trying to test with a very large table but the inserts takes several hours.

thanks

Bill

Others already suggested sorting into some other sequence before LOAD back. Similarly you can change the clustering index to something else (currently a low clustering index), temporarily, Reorg, then change it back again, and Reorg again.

It is not clear to me that one would need to do this more than once or twice to check that Reorg does sequence data in Cluster sequence. If Runstats reports that the cluster index is 100% clustered (CLUSTERRATIOF = 1.0 and DATAREPEATFACTORF should perhaps equal NPAGESF for the data table when perfectly Reorged), that would be good enough for me. Note that 100% will not be achieved for cluster incompatible with partitioning scheme.

Michael Hannan,

DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----

-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
-----End Original Message-----

Michael Hannan

RE: how to test with disorganized data repeatedly
(in response to Phil Grainger)



In Reply to Phil Grainger:

Everyone is laser focussed on clustering

But what about “indref” and “offpos” rows?

There is more to a reorg than reclustering

We have realtime Stats REORGNEARINDREF, REORGFARINDREF, REORGUNCLUSTINS, etc. So clearly INDREF values matter a lot because cause scans to do extra Getpages and possible Sync I/Os. So clearly we don't want to let INDREF values get out of hand. 

However I would not consider this something significant for Reorg Testing, the slightly unusual topic, unless we are testing how the performance of the Reorg is affected by INDREF values. A Reorg has to fix INDREF, it has no choice basically, if data rows get unloaded and loaded back in again. Does Reorg have to read INDREF displaced rows indirectly, or can it read all the rows merely by scanning data pages in sequential order? Assuming a Reorg does not try to use an Index to get data in order, the latter should be the case.

I must admit due to the overlaps in what various fields cover in meaning, I pay little attention to NEAROFFPOS and FAROFFPOS columns. Sort of overlap the meaning of UNCLUSTINS. I pay more attention to clustering measures where Data Getpages will be affected, and am a little less I/O focussed (except for INDREF). I generally find that tuning Getpages, causes I/Os to look after themselves (assuming good bufferpools).

Is there anyone out there also worried about declining DATAREPEATFACFORF (DRF), indicating data Getpages blowout/increase for scan via the cluster index (or similar) or is that me only? Bad DRF can be a reason to Reorg for me, and is much easier to understand its affect than non optimal CLUSTERRATIOF. 

Michael Hannan,

DB2 Application Performance Specialist
CPT Global Ltd

Phil Grainger

how to test with disorganized data repeatedly
(in response to Chris Hoelscher)
When the OP said “test” I assumed they were BENCHMARKING Reorgs and thus needed repeatable test data to compare apples with apples

I did not interpret “test” as meaning “we want to prove that our reorg tool is REALLY reorging the data”

In either case I can imagine one vendor whispering in the OPs ear about some other vendors utility – hopefully I am wrong on this latter point, but it would be helpful to have the “question behind the question” to offer a more complete answer
________________________________

Phil Grainger

Principal Enablement Manager

[login to unmask email]

Direct



+44 (0)118 921 8000

Mobile



+44(0)7808 643 479


E2, Eskdale Road
Winnersh
Berkshire
RG41 5TS


[http://media.cms.bmc.com/images/corp_signature_bmclogo_2014.jpg] http://www.bmc.com

[https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png]





From: Chris Hoelscher [mailto:[login to unmask email]
Sent: 09 December 2018 16:45
To: [login to unmask email]
Subject: [DB2-L] - RE: how to test with disorganized data repeatedly

I sounds like they want to muck up the clustering …

So what they really want is a cluster … muck?

Chris Hoelscher
Technology Architect, Database Infrastructure Services
Technology Solution Services
Humana Inc.
123 East Main Street
Louisville, KY 40202
Humana.com
(502) 476-2538 or 407-7266

From: Grainger, Phil <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Sunday, December 9, 2018 10:14 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: how to test with disorganized data repeatedly

Everyone is laser focussed on clustering

But what about “indref” and “offpos” rows?

There is more to a reorg than reclustering
________________________________
From: Paul Ogborne <[login to unmask email]<mailto:[login to unmask email]>>
Sent: 09 December 2018 11:33:44
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: how to test with disorganized data repeatedly

Hi,

- Change the existing clustering index to not clustering.
- Create a new clustering index using some very odd columns with high key cardinality.
- Reorg
- Drop new index
- Reinstate explicit clustering index.
- Test your reorgs.

Regards,
Paul

On 9 Dec 2018, at 11:06, Michael Hannan <[login to unmask email]<mailto:[login to unmask email]>> wrote:


In Reply to william giannelli:

Does anyone have a "convenient" way of loading or inserting data in a disorganized way to test our reorgs? We are trying to test with a very large table but the inserts takes several hours.

thanks

Bill

Others already suggested sorting into some other sequence before LOAD back. Similarly you can change the clustering index to something else (currently a low clustering index), temporarily, Reorg, then change it back again, and Reorg again.

It is not clear to me that one would need to do this more than once or twice to check that Reorg does sequence data in Cluster sequence. If Runstats reports that the cluster index is 100% clustered (CLUSTERRATIOF = 1.0 and DATAREPEATFACTORF should perhaps equal NPAGESF for the data table when perfectly Reorged), that would be good enough for me. Note that 100% will not be achieved for cluster incompatible with partitioning scheme.

Michael Hannan,

DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----

-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
-----End Original Message-----

The information transmitted is intended only for the person or entity to which it is addressed
and may contain CONFIDENTIAL material. If you receive this material/information in error,
please contact the sender and delete or destroy the material/information.

Humana Inc. and its subsidiaries comply with applicable Federal civil rights laws and
do not discriminate on the basis of race, color, national origin, age, disability, sex,
sexual orientation, gender identity, or religion. Humana Inc. and its subsidiaries do not
exclude people or treat them differently because of race, color, national origin, age,
disability, sex, sexual orientation, gender identity, or religion.

English: ATTENTION: If you do not speak English, language assistance services, free
of charge, are available to you. Call 1‐877‐320‐1235 (TTY: 711).

Español (Spanish): ATENCIÓN: Si habla español, tiene a su disposición servicios
gratuitos de asistencia lingüística. Llame al 1‐877‐320‐1235 (TTY: 711).

繁體中文(Chinese):注意:如果您使用繁體中文,您可以免費獲得語言援助
服務。請致電 1‐877‐320‐1235 (TTY: 711)。

Kreyòl Ayisyen (Haitian Creole): ATANSION: Si w pale Kreyòl Ayisyen, gen sèvis èd
pou lang ki disponib gratis pou ou. Rele 1‐877‐320‐1235 (TTY: 711).

Polski (Polish): UWAGA: Jeżeli mówisz po polsku, możesz skorzystać z bezpłatnej
pomocy językowej. Zadzwoń pod numer 1‐877‐320‐1235 (TTY: 711).

한국어 (Korean): 주의: 한국어를 사용하시는 경우, 언어 지원 서비스를 무료로
이용하실 수 있습니다. 1‐877‐320‐1235 (TTY: 711)번으로 전화해 주십시오.
-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
Attachments

  • image001.jpg (8k)
  • image002.png (9.3k)

william giannelli

RE: how to test with disorganized data repeatedly
(in response to Phil Grainger)

Thank you all for your responses.

To clarify we are testing and benchmarking BMC NGT Utilities versus IBM Utilities to see which runs faster and more efficiently.

thanks

Bill 

Ruediger Kurtz

AW: how to test with disorganized data repeatedly
(in response to Chris Hoelscher)
I’m chiming *very* late I know, but wasn’t there a RANDOM-Sort-Sequence when it comes to creating an index?

I haven’t tried it so far and I’m not even sure that would have helped but perhaps someone out there does indeed have some experience with that RANDOM thing?

Rüdiger


Rüdiger Kurtz
Abteilung Informatik - Betrieb

HUK-COBURG
Bahnhofsplatz
96444 Coburg
Telefon: 09561 96-44148
Telefax: 09561 96-44104
E-Mail: [login to unmask email]
Internet: www.huk.de
________________________________
HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg
Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
Vorsitzender des Aufsichtsrats: Prof. Dr. Heinrich R. Schradin.
Vorstand: Klaus-Jürgen Heitmann (Sprecher), Stefan Gronbach, Dr. Hans Olav Herøy, Dr. Jörg Rheinländer (stv.), Sarah Rössler, Daniel Thomas.
________________________________
Diese Nachricht enthält vertrauliche und/oder rechtlich geschützte Informationen.
Wenn Sie nicht der richtige Adressat sind oder diese Nachricht irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Nachricht.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Nachricht ist nicht gestattet.

This information may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this information in error) please notify the
sender immediately and destroy this information.
Any unauthorized copying, disclosure or distribution of the material in this information is strictly forbidden.
________________________________
Von: Chris Hoelscher [mailto:[login to unmask email]
Gesendet: Sonntag, 9. Dezember 2018 17:45
An: [login to unmask email]
Betreff: [DB2-L] - RE: how to test with disorganized data repeatedly

I sounds like they want to muck up the clustering …

So what they really want is a cluster … muck?

Chris Hoelscher
Technology Architect, Database Infrastructure Services
Technology Solution Services
Humana Inc.
123 East Main Street
Louisville, KY 40202
Humana.com
(502) 476-2538 or 407-7266

From: Grainger, Phil <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Sunday, December 9, 2018 10:14 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: how to test with disorganized data repeatedly

Everyone is laser focussed on clustering

But what about “indref” and “offpos” rows?

There is more to a reorg than reclustering
________________________________
From: Paul Ogborne <[login to unmask email]<mailto:[login to unmask email]>>
Sent: 09 December 2018 11:33:44
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: how to test with disorganized data repeatedly

Hi,

- Change the existing clustering index to not clustering.
- Create a new clustering index using some very odd columns with high key cardinality.
- Reorg
- Drop new index
- Reinstate explicit clustering index.
- Test your reorgs.

Regards,
Paul

On 9 Dec 2018, at 11:06, Michael Hannan <[login to unmask email]<mailto:[login to unmask email]>> wrote:


In Reply to william giannelli:

Does anyone have a "convenient" way of loading or inserting data in a disorganized way to test our reorgs? We are trying to test with a very large table but the inserts takes several hours.

thanks

Bill

Others already suggested sorting into some other sequence before LOAD back. Similarly you can change the clustering index to something else (currently a low clustering index), temporarily, Reorg, then change it back again, and Reorg again.

It is not clear to me that one would need to do this more than once or twice to check that Reorg does sequence data in Cluster sequence. If Runstats reports that the cluster index is 100% clustered (CLUSTERRATIOF = 1.0 and DATAREPEATFACTORF should perhaps equal NPAGESF for the data table when perfectly Reorged), that would be good enough for me. Note that 100% will not be achieved for cluster incompatible with partitioning scheme.

Michael Hannan,

DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----

-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
-----End Original Message-----

The information transmitted is intended only for the person or entity to which it is addressed
and may contain CONFIDENTIAL material. If you receive this material/information in error,
please contact the sender and delete or destroy the material/information.

Humana Inc. and its subsidiaries comply with applicable Federal civil rights laws and
do not discriminate on the basis of race, color, national origin, age, disability, sex,
sexual orientation, gender identity, or religion. Humana Inc. and its subsidiaries do not
exclude people or treat them differently because of race, color, national origin, age,
disability, sex, sexual orientation, gender identity, or religion.

English: ATTENTION: If you do not speak English, language assistance services, free
of charge, are available to you. Call 1‐877‐320‐1235 (TTY: 711).

Español (Spanish): ATENCIÓN: Si habla español, tiene a su disposición servicios
gratuitos de asistencia lingüística. Llame al 1‐877‐320‐1235 (TTY: 711).

繁體中文(Chinese):注意:如果您使用繁體中文,您可以免費獲得語言援助
服務。請致電 1‐877‐320‐1235 (TTY: 711)。

Kreyòl Ayisyen (Haitian Creole): ATANSION: Si w pale Kreyòl Ayisyen, gen sèvis èd
pou lang ki disponib gratis pou ou. Rele 1‐877‐320‐1235 (TTY: 711).

Polski (Polish): UWAGA: Jeżeli mówisz po polsku, możesz skorzystać z bezpłatnej
pomocy językowej. Zadzwoń pod numer 1‐877‐320‐1235 (TTY: 711).

한국어 (Korean): 주의: 한국어를 사용하시는 경우, 언어 지원 서비스를 무료로
이용하실 수 있습니다. 1‐877‐320‐1235 (TTY: 711)번으로 전화해 주십시오.
-----End Original Message-----