Reorg Prblem

duam lee

Reorg Prblem
Hi List, What is the best way to disorganize a table which is now perfectly re-organized. Deletion of records more that 20 percent and insert the same would be one example I think of now. If record length is variable, then mass update can also do. I am looking for any other way I can disorganize the tablespace soon.With ThanksDuam.
_________________________________________________________________
Express yourself instantly with MSN Messenger! Download today it's FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Mark Horrocks

Re: Reorg Prblem
(in response to duam lee)
By deleting and re-inserting would you not be just plugging the same holes
again?

This would not be the case if the table is in APPEND_MODE.



Many thanks,



Mark Horrocks.



_____

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of duam lee
Sent: 15 January 2008 08:40
To: [login to unmask email]
Subject: [DB2-L] Reorg Prblem



Hi List,



What is the best way to disorganize a table which is now perfectly
re-organized. Deletion of records more that 20 percent and insert the same
would be one example I think of now. If record length is variable, then mass
update can also do. I am looking for any other way I can disorganize the
tablespace soon.



With Thanks
Duam.



_____

Express yourself instantly with MSN Messenger! MSN
< http://clk.atdmt.com/AVE/go/onm00200471ave/direct/01/ > Messenger


The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list
archives, the FAQ, and delivery preferences are at
< http://www.idug.org/lsidug > www.idug.org under the Listserv tab. While at
the site, you can also access the IDUG Online Learning Center, Tech Library
and Code Place, see the latest < http://www.idug.org/lsconf > IDUG conference
information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at no
cost, click on < http://www.idug.org/lsms > Member Services

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Andy Lankester

Re: Reorg Prblem
(in response to Mark Horrocks)
Inserting and then deleting would be better since it would increase space, index levels etc to sizes above what reorg would produce. You must of course insert more than can be accommodated by PCTFREE/FREEPAGE.

Andy

_____

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of duam lee
Sent: Tuesday, January 15, 2008 8:40 AM
To: [login to unmask email]
Subject: [DB2-L] Reorg Prblem


Hi List,



What is the best way to disorganize a table which is now perfectly re-organized. Deletion of records more that 20 percent and insert the same would be one example I think of now. If record length is variable, then mass update can also do. I am looking for any other way I can disorganize the tablespace soon.



With Thanks
Duam.



_____

Express yourself instantly with MSN Messenger! HYPERLINK "http://clk.atdmt.com/AVE/go/onm00200471ave/direct/01/"MSN Messenger
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at HYPERLINK "http://www.idug.org/lsidug"www.idug.org under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest HYPERLINK "http://www.idug.org/lsconf"IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on HYPERLINK "http://www.idug.org/lsms"Member Services


No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.516 / Virus Database: 269.19.2/1224 - Release Date: 14/01/2008 17:39



No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.516 / Virus Database: 269.19.2/1224 - Release Date: 14/01/2008 17:39


The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Doyle Mark

Re: Reorg Prblem
(in response to Andy Lankester)
Duam,

Your question prompts me to ask "why?"

If, for instance, you want to trigger a conditional reorg, there is a
much easier way to do this, namely, updating the FAROFFPOS to be the
cardinality of the table.

Mark


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of duam lee
Sent: Tuesday, January 15, 2008 2:40 AM
To: [login to unmask email]
Subject: Reorg Prblem


Hi List,



What is the best way to disorganize a table which is now perfectly
re-organized. Deletion of records more that 20 percent and insert the
same would be one example I think of now. If record length is variable,
then mass update can also do. I am looking for any other way I can
disorganize the tablespace soon.



With Thanks
Duam.



________________________________

Express yourself instantly with MSN Messenger! MSN Messenger
< http://clk.atdmt.com/AVE/go/onm00200471ave/direct/01/ >
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at www.idug.org
< http://www.idug.org/lsidug > under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information
< http://www.idug.org/lsconf > , and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services < http://www.idug.org/lsms >

______________________________________________________________________
This electronic message and any attachment is intended exclusively for the individual or entity to which it is addressed. It may contain information that is privileged, confidential and exempt from disclosure under applicable law. Any unauthorized disclosure, copying, distribution or use of this electronic message or any attachment is prohibited. If you have received this message in error, please return it to the sender and delete this original from your system.

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Phil Grainger

Re: Reorg Prblem
(in response to Doyle Mark)
to run reproducible benchmarks perhaps?

That's ALWAYS a challenge

An easier way is to DSN1COPY the table BEFORE you reorg it and then put
the copy back to run the next test - also ensures that all reorgs are
reorganising exactly the SAME disorganised data

Phil Grainger
CA
Product Management Director
Phone: +44 (0)1753 577 733
Mobile: +44 (0)7970 125 752
eMail: [login to unmask email]

Ditton Park
Riding Court Road
Datchet
Slough
SL3 9LL

CA plc a company registered in England and Wales under company
registration number 1282495 with its registered office at the address
set out above. VAT number 697904179.




________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Doyle Mark
Sent: 15 January 2008 16:12
To: [login to unmask email]
Subject: Re: [DB2-L] Reorg Prblem


Duam,

Your question prompts me to ask "why?"

If, for instance, you want to trigger a conditional reorg, there is a
much easier way to do this, namely, updating the FAROFFPOS to be the
cardinality of the table.

Mark


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of duam lee
Sent: Tuesday, January 15, 2008 2:40 AM
To: [login to unmask email]
Subject: Reorg Prblem


Hi List,



What is the best way to disorganize a table which is now perfectly
re-organized. Deletion of records more that 20 percent and insert the
same would be one example I think of now. If record length is variable,
then mass update can also do. I am looking for any other way I can
disorganize the tablespace soon.



With Thanks
Duam.



________________________________

Express yourself instantly with MSN Messenger! MSN Messenger
< http://clk.atdmt.com/AVE/go/onm00200471ave/direct/01/ >
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at www.idug.org
< http://www.idug.org/lsidug > under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information
< http://www.idug.org/lsconf > , and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services < http://www.idug.org/lsms >
______________________________________________________________________
This electronic message and any attachment is intended exclusively for
the individual or entity to which it is addressed. It may contain
information that is privileged, confidential and exempt from disclosure
under applicable law. Any unauthorized disclosure, copying, distribution
or use of this electronic message or any attachment is prohibited. If
you have received this message in error, please return it to the sender
and delete this original from your system.

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at www.idug.org
< http://www.idug.org/lsidug > under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information
< http://www.idug.org/lsconf > , and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services < http://www.idug.org/lsms >

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

duam lee

Re: Reorg Prblem
(in response to Phil Grainger)
Correct Phil,

Bench marks are challenge always. But when we go for the DSN1COPY then what about indexes build. The data portion is disorganized , but I guess index build would make the index more organized than earlier. So any updates/suggestions for this.

With ThanksDuam.


Date: Tue, 15 Jan 2008 16:27:18 +0000From: [login to unmask email]: Re: [DB2-L] Reorg PrblemTo: [login to unmask email]



to run reproducible benchmarks perhaps?

That's ALWAYS a challenge

An easier way is to DSN1COPY the table BEFORE you reorg it and then put the copy back to run the next test - also ensures that all reorgs are reorganising exactly the SAME disorganised data

Phil Grainger
CA
Product Management Director
Phone: +44 (0)1753 577 733
Mobile: +44 (0)7970 125 752
eMail: [login to unmask email]

Ditton ParkRiding Court RoadDatchetSloughSL3 9LL

CA plc a company registered in England and Wales under company registration number 1282495 with its registered office at the address set out above. VAT number 697904179.




From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Doyle MarkSent: 15 January 2008 16:12To: [login to unmask email]: Re: [DB2-L] Reorg Prblem

Duam,

Your question prompts me to ask "why?"

If, for instance, you want to trigger a conditional reorg, there is a much easier way to do this, namely, updating the FAROFFPOS to be the cardinality of the table.

Mark



-----Original Message-----From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of duam leeSent: Tuesday, January 15, 2008 2:40 AMTo: [login to unmask email]: Reorg PrblemHi List, What is the best way to disorganize a table which is now perfectly re-organized. Deletion of records more that 20 percent and insert the same would be one example I think of now. If record length is variable, then mass update can also do. I am looking for any other way I can disorganize the tablespace soon.With ThanksDuam.

Express yourself instantly with MSN Messenger! MSN Messenger The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at www.idug.org under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services ______________________________________________________________________This electronic message and any attachment is intended exclusively for the individual or entity to which it is addressed. It may contain information that is privileged, confidential and exempt from disclosure under applicable law. Any unauthorized disclosure, copying, distribution or use of this electronic message or any attachment is prohibited. If you have received this message in error, please return it to the sender and
delete this original from your system.The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at www.idug.org under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at www.idug.org under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services
_________________________________________________________________
Express yourself instantly with MSN Messenger! Download today it's FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms