DB2 luw v8.2 - Question on INPLACE REORG performance

Jack Campbell

DB2 luw v8.2 - Question on INPLACE REORG performance
I am trying to implement the use of INPLACE REORG for 2 OLTP databases
on the same server. I am new(ish) to the Unix enviroment and can not seem
to find any information regarding tuning DB CONFIG parameters to improve
the performance of an inplace reorg.

DatabaseA - is the larger database and has most of the resource allocations
(as least 80% of total memory) - inplace reorg performs well

DatabaseB - is a smaller database, and has minimal resource allocations -
inplace reorg runs very slowly (>24 hrs for larger tables)

I looked around, but have been unable to find any "pool" setting
recommendations for inplace reorg............any pointers / advice is most
appreciated

Regards

Jack

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Philip Gunning

Re: DB2 luw v8.2 - Question on INPLACE REORG performance
(in response to Jack Campbell)
Jack, number of indexes on a table, yes even if only doing a table reorg and setting of util_impact_lim will affect running of online reorg. By default it is set to 10 which means throttle online utilities to use only 10 pct of available cpu. You can set this to a higher number or set to 100 to disable online utility throttling. Also, locking activity on the table being reorg'd. If the table is highly active reorg will spend time waiting for locks, this is with allow write access also. You can eliminate truncation at the end by specifying the no truncate option, which means space is not reclaimed. We have been using online reorgs extensively in high volume OLTP environment with no problems. As an aside, if the db2reorg task is waiting, you can see this in an application snapshot. Monitor status of reorg with
Db2pd -d dbname -reorgs.

PG

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jack Campbell
Sent: Monday, January 25, 2010 12:42 PM
To: [login to unmask email]
Subject: [DB2-L] DB2 luw v8.2 - Question on INPLACE REORG performance

I am trying to implement the use of INPLACE REORG for 2 OLTP databases
on the same server. I am new(ish) to the Unix enviroment and can not seem
to find any information regarding tuning DB CONFIG parameters to improve
the performance of an inplace reorg.

DatabaseA - is the larger database and has most of the resource allocations
(as least 80% of total memory) - inplace reorg performs well

DatabaseB - is a smaller database, and has minimal resource allocations -
inplace reorg runs very slowly (>24 hrs for larger tables)

I looked around, but have been unable to find any "pool" setting
recommendations for inplace reorg............any pointers / advice is most
appreciated

Regards

Jack

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L