DB2 REORG INDEX not using prefetch

Charles Tsao

DB2 REORG INDEX not using prefetch
On Fri, 31 Dec 2004 09:25:01 +0100, Michael Ebert <[login to unmask email]>
wrote:

>I'm not sure whether the situation you describe is possible. How do you
>know you've got 150M+ pseudo-deleted entries?
>
>I've rarely used REORG INDEX SHRLEVEL CHANGE, but if I remember correctly,
>DB2 does an unload in sorted order - it does not do a sequential scan
>followed by a sort (it also expands all RID lists, stores the data in the
>unload file, and then reverses the whole process in the reload phase -
>dramatic improvement possibilities for I/O and CPU are obvious). So I'd say
>your index is simply highly fragmented. Remember, an index as a B-tree
>variant is always in sorted order; however the 2-dimensional index tree
>structure has to be imbedded into a linear page sequence in the VSAM file.
>This sequence is ok (properly sorted) after a REORG, but inserts and
>deletes will sooner or later scramble it.
>
>Dr. Michael Ebert
>DB2 Database Administrator
>aMaDEUS Data Processing
>Erding / Munich, Germany
>
>
>
>
>From: Charles Tsao <[login to unmask email]>@IDUGDB2-L.ORG on 30-12-2004
> 13:49 CST
>
>Please respond to DB2 Database Discussion list at IDUG
> <[login to unmask email]>
>
>Sent by: DB2 Data Base Discussion List <[login to unmask email]>
>
>
>
> To: [login to unmask email]
>
>
> cc:
>
>
>
>
>
>
> Subjec [DB2-L] DB2 REORG INDEX not using
> t: prefetch
>
>
>
>
>
>
>
>
>Fellow DBAs,
>
>We have a 60M rows NPI with 150M+ pseudo delete entries. When I try a REORG
>INDEX .. SHRLEVEL CHANGE against it. DB2 uses SYNC I/O during the UNLOAD
>phase. On a different NPI of similar size but with fewer Pseudo delete
>entries, it uses prefetch.
>
>My guess is because of the high ratio of pseudo deletes and fragmented
>index pages. Can someone confirm this and is there anyway I can influence
>DB2 to use prefetch?
>
>Many thanks
>
>C. Tsao
>
>---------------------------------------------------------------------------
------
>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 DB2-L-
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

Thanks for answering my call for help. The pseudo delete entries count was
from RUNSTAT. I sort of expected that because this is a heavy insert/delete
table. We plan to REORG the index more frequent so the UNLOAD phase can be
more compact and hopefully more prefetch. In this situation, DB2 does not
reuse the pseudo deleted space which prompted the REORG in the first place.
Our ultimate goal is to rely on the regular pruning process to deal with
the growth. It is working for the TS and Partition Index but not this NPI.

C. Tsao
DBA

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

Michael Ebert

Re: DB2 REORG INDEX not using prefetch
(in response to Charles Tsao)
Cool. I've never noticed the column SYSINDEXPART.PSEUDO_DEL_ENTRIES
before... this might be useful for some housekeeping automation.

Dr. Michael Ebert
DB2 Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany

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