DB2 REORG INDEX not using prefetch

Charles Tsao

DB2 REORG INDEX not using 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 [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)
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 [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm