rebuild index after reorg

Ramyasshree RS

rebuild index after reorg
We did reorg of a table, after purging historical data. Table reorg was
followed by rebuild of indexes.

Server, OS: Unix, Solaris 5.8
DB2 version: 7.2
Table size: > 200,000,000 rows
Time for table reorg: 30 min
Time to rebuild primary index: 55 hours

The time taken to rebuild the indexes was huge. Please let know if there are
any steps we can take / any parameters we could modify, to reduce the time
taken to rebuild the indexes.

Please find below comments from IBM on the issue:
_____________________________________________


Based on the analysis done by both IBM and Sun Solaris, it looks like we
might have hit a Solaris bug # 4336082 on the OS.

Unfortunately there is no fix for this except to upgrade the OS Solaris
version from 8 to 9. See the analysts notes from Sun Microsystems below:



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

from the below info looks like this could be

*Bug ID:* 4336082* Synopsis:* fsync() call on files with no/few dirty pages
is very slow



The options for a customer with this problem include:



1) Upgrade to Solaris 9 or above -- *This is really the best option and the
only one which addresses the root cause.*

2) Modify the application so that it calls fsync() less often.



3) Modify the application so that it opens the files concerned using the
O_DSYNC flag and no longer calls fsync() (man -s2 open). This blocks on
writes until the underlying device driver reports that the data is written.
Writes therefore go slower but reads will still obtain data from the cache.


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


Thanks a lot!
Ramya

---------------------------------------------------------------------------------
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

Venu

Re: rebuild index after reorg
(in response to Ramyasshree RS)

Just a thought not sure what will be impact on your system.

How about doing the following
1) Drop the indexes before the Reorg
2) Reorg the table (without the indexes)
3) Create the indexes back

Thanks,
Venu.

________________________________

From: DB2 Data Base Discussion List on behalf of Ramyasshree RS
Sent: Thu 1/4/2007 8:29 AM
To: [login to unmask email]
Subject: [DB2-L] rebuild index after reorg


We did reorg of a table, after purging historical data. Table reorg was followed by rebuild of indexes.

Server, OS: Unix, Solaris 5.8
DB2 version: 7.2
Table size: > 200,000,000 rows
Time for table reorg: 30 min
Time to rebuild primary index: 55 hours

The time taken to rebuild the indexes was huge. Please let know if there are any steps we can take / any parameters we could modify, to reduce the time taken to rebuild the indexes.

Please find below comments from IBM on the issue:
_____________________________________________


Based on the analysis done by both IBM and Sun Solaris, it looks like we might have hit a Solaris bug # 4336082 on the OS.

Unfortunately there is no fix for this except to upgrade the OS Solaris version from 8 to 9. See the analysts notes from Sun Microsystems below:



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

from the below info looks like this could be

*Bug ID:* 4336082* Synopsis:* fsync() call on files with no/few dirty pages is very slow



The options for a customer with this problem include:



1) Upgrade to Solaris 9 or above -- *This is really the best option and the only one which addresses the root cause.*

2) Modify the application so that it calls fsync() less often.



3) Modify the application so that it opens the files concerned using the O_DSYNC flag and no longer calls fsync() (man -s2 open). This blocks on writes until the underlying device driver reports that the data is written. Writes therefore go slower but reads will still obtain data from the cache.



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


Thanks a lot!
Ramya
--------------------------------------------------------------------------------- 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 information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments.

WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email.

www.wipro.com

---------------------------------------------------------------------------------
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

harby ariza

Re: rebuild index after reorg
(in response to Venu)

_______________________________________________________________________________________

Note: This e-mail is subject to the disclaimer contained at the bottom of this message.
_______________________________________________________________________________________



Maybe why you should do is:

1.- Upgrade db2 to v8 64 bits.
2.- Providing that you have enough memory increase bufferpools
accordingly.
3.- Mount the database filesystems with CIO/DIO options.

After these changes and providing that you have plenty of resources
(CPU's/Memory) combine with parallelism the time of this reorg should
drop significantly.

Cheers,

Harby.


_______________________________________________________________________________________

The information transmitted in this message and its attachments (if any) is intended
only for the person or entity to which it is addressed.
The message may contain confidential and/or privileged material. Any review,
retransmission, dissemination or other use of, or taking of any action in reliance
upon this information, by persons or entities other than the intended recipient is
prohibited.

If you have received this in error, please contact the sender and delete this e-mail
and associated material from any computer.

The intended recipient of this e-mail may only use, reproduce, disclose or distribute
the information contained in this e-mail and any attached files, with the permission
of the sender.

This message has been scanned for viruses with Symantec Scan Engine and cleared by
MailMarshal.
_______________________________________________________________________________________

---------------------------------------------------------------------------------
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