Index Reorgs following dummy LOAD REPLACE

We have a large partitioned table that gets emptied weekly via a dummy
LOAD REPLACE. Data is then Inserted into throughout the week.. The indexes
have never been reorged... I believe that there is still value in
performing a reorg on these indexes to clean up the logically deleted data.

I'm seeking second opinions (before Saturday).. THANKS

[login to unmask email]

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

The index does get rebuild when the load happens. A
Hi Jeff,

The index does get rebuild when the load happens. A separate rebuild
index may not be required at that point.

HTH

-KT

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]
Sent: Wednesday, November 30, 2005 4:59 PM
To: [login to unmask email]
Subject: [DB2-L] Index Reorgs following dummy LOAD REPLACE

We have a large partitioned table that gets emptied weekly via a dummy
LOAD REPLACE. Data is then Inserted into throughout the week.. The
indexes
have never been reorged... I believe that there is still value in
performing a reorg on these indexes to clean up the logically deleted
data.

I'm seeking second opinions (before Saturday).. THANKS

[login to unmask email]

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

-----------------------------------------
Use of email is inherently insecure. Confidential information,
including account information, and personally identifiable information,
should not be transmitted via email, or email attachment. In no event
shall Citizens or any of its affiliates accept any responsibility for
the loss, use or misuse of any information including confidential
information, which is sent to Citizens or its affiliates via email, or
email attachment. Citizens does not guarantee the accuracy of any email
or email attachment, that an email will be received by Citizens or that
Citizens will respond to any email.

This email message is confidential and/or privileged. It is to be used
by the intended recipient only. Use of the information contained in
this email by anyone other than the intended recipient is strictly
prohibited. If you have received this message in error, please notify
the sender immediately and promptly destroy any record of this email.


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

load replace resets all the indexes to clean and empty if it specifies the
full table not just one partition.

If you are doing rolling partitions, then you may be leaving deleted data
for the next insert or not, depending on the key structure. You should be
able to tell from the runstats for the index.

I hope you specify REUSE for the load replace so you don't have to take
extents during the week.

Mike Bell
HLS Technologies

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Jeff Kluth
Sent: Wednesday, November 30, 2005 3:59 PM
To: [login to unmask email]
Subject: [DB2-L] Index Reorgs following dummy LOAD REPLACE

We have a large partitioned table that gets emptied weekly via a dummy
LOAD REPLACE. Data is then Inserted into throughout the week.. The indexes
have never been reorged... I believe that there is still value in
performing a reorg on these indexes to clean up the logically deleted data.

I'm seeking second opinions (before Saturday).. THANKS

[login to unmask email]

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
The IDUG List Admins can be reached at [login to unmask email] Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Jeff,
Your LOAD REPLACE will clean up the tablespace partition, and the
partitioning index (PI). It will _not_ clean up any nonpartitioning indexes
(NPIs) which are defined for the table. a LOAD, which does not REPLACE all
partitions, will execute key deletes against values in the NPIS (in much the
way SQL DELETE will work as it cleans up index entries associated with its
row deletes). This can be quite long, if a large amount of data is being
removed from an NPI with a low CLUSTERRATIOF.

You should periodically REORG your NPIs, assuming you have defined some on
this table, and should probably use fairly large freespace values (FREEPAGE
and PCTFREE). Note that I'm not defining "large" in this case, as ... "IT
DEPENDS!"

--Phil S.


On 11/30/05, Jeff Kluth <[login to unmask email]> wrote:
>
> We have a large partitioned table that gets emptied weekly via a dummy
> LOAD REPLACE. Data is then Inserted into throughout the week.. The indexes
> have never been reorged... I believe that there is still value in
> performing a reorg on these indexes to clean up the logically deleted
> data.
>
> I'm seeking second opinions (before Saturday).. THANKS
>
> [login to unmask email]
>
>
> ---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
> home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
> select "Join or Leave the list". The IDUG DB2-L FAQ is at
> http://www.idugdb2-l.org. The IDUG List Admins can be reached at
> [login to unmask email] Find out the latest on IDUG conferences
> at http://conferences.idug.org/index.cfm
>



--
--Phil Sevetson
IBM Certified Solutions Expert -- DB2 V7.1 Database Administration for
OS/390
IBM Certified Database Administrator -- DB2 Universal Database V8.1 for zOS
Wakefern Food Corporation
Edison, NJ, USA
ANY OPINIONS OR RECOMMENDATIONS EXPRESSED IN THIS EMAIL
ARE MY OWN, NOT MY EMPLOYER'S OR IBM'S.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm