RE: Access path / index rebuilds

Justin, 

I assume you're seeing this in WRKACTJOB Function column?
This usually means that system is building temporary indexes to satisfy
query request.  Temporary indexes are generally not a good thing.  If
they're being built over large result set (rare, but happens) they
themselves are large and take a while to build, taking toll on your CPU,
main memory and possibly DASD.
If they're small (more frequent case) but your application runs the query
that's causing it 1000s or more times, cumulative cost on your system is
still significant (in terms of resources).  These indexes cannot be reused
between different jobs (i.e. different ODBC connections).

Whenever possible I recommend permanent index be built in its stead.

If it's not building temporary indexes, I suppose it could be something
called index-out-of-index build (less common).  This is where query
optimizer builds sparse index (i.e. select/omit logical) with a subset of
values in the real index.  If this is the case, you really can't do much
about it, as it's probably performing as well as it could.  There are
settings in QAQQINI file you could use to affect this, but I'd only do it if
instructed by IBM or some other vendor to do so.

You can use IBM's STRDBMON command or iNav's SQL Performance Monitor to help
figure out which indexes to build to eliminate temporary index builds.
Alternatively, I work for a tools vendor that specializes in SQL database
tuning on the iSeries and we offer couple of services and tools that might
help (www.centerfieldtechnology.com).

Elvis

-----Original Message-----
Subject: Access path / index rebuilds

Hopefully someone can answer this for me in easy terms.

Why would a system rebuild access paths for files when being used by
many ODBC (QZDASOINIT) jobs?  For example, the jobs will sometimes go in
to an IDX-xxxxxxx where xxxxxxx = the name of a file.  The online help
shows that the IDX status means it's rebuilding the index (access path)
to whichever file it's listing.

What prompts the rebuild?  Is there somewhere we could go look to see
why it's doing that?  Additionally, in Performance Navigator there's a
chart for Index Rebuilds/second - the online help for describing why is
pretty vague there also.

Please advise - thanks in advance for your help and for not biting my
head off if this is basic and I just never learned it - hah!

--
Justin C. Haase - iSeries System Engineer
IBM Certified Systems Expert - eServer i5
Kingland Systems Corporation