Following are just guesses so don't read too much into them.
Check memory pool allocations, machine pool size, QAQQINI settings, system
value differences... Use WRKSYSSTS at the time it's happening and check
page faulting levels.
SMP shouldn't play a role since it's single processor box but perhaps having
it installed and turned-on on one box vs the other make query optimizer
behave differently.
Size of write cache on disks and otherwise differently performing drives
will make a difference (number of arms, data balanced etc.).
Amount of implicit journaling may play a role - SMAPP. System turns on
implicit journaling for large access paths so that may play a role from
resource conflict standpoint. Use EDTRCYAP to check that setting.
All that said, I'm guessing CPU is the bottleneck and just the fact that
there is other work going on concurrently and competing for CPU is causing
the bottleneck.
HTH, Elvis
Celebrating 10-Years of SQL Performance Excellence
http://centerfieldtechnology.com/training.asp
-----Original Message-----
Subject: SPAM-BIGDOG-- EDTRBDAP Question
I have a job that runs every night that effectively replicates a library
from our production box (810 - P10 processor with 8 gb ram) to our test
box (270 - P10 processor with 4 gb ram). Both machines are running V5R4
and are current with ptfs.
The job works like this.
10:30 pm on production: Six files are copied into a library named
BKPyymmdd. When this finishes the BKPyymmdd library is sent to the test
system using savrstlib. No problems here. When this finishes the identical
BKPyymmdd library exists on both systems.
2:00 am (after backups of both systems run at midnight), an identical job
starts on both systems that populates the 'audit' library on each system
using the six data files from the BKPyymmdd library. This process first
does a clrpfm on the six data files in the 'audit' library, and then does
a 'chglf xxfile MAINT(*REBLD)' for all of the logical files/sql indexes
that exist over these six physical files - (there are 23
logicals/indexes). Then cpyf is done (starting at RRN 1) to repopulate the
files, and when all of the cpyf's finish 'chglf xxfile MAINT(*IMMED)' is
done to rebuild the indexes. This method seems to be the fastest method
I've developled for completing this task overnight. The files are rather
large, three of them have over 25 million records (and there are no
deleted records).
The question is this: While this job runs on both sytems simultaneously,
when I monitor the access path rebuilds via EDTRBDAP, there is a column
that shows the estimated time to rebuild the access paths, but the funny
thing is that on our production system the estimated time is 30 to 50
percent greater than the estimated time for the same indexes on the test
system.
Our production system has double the ram and more cpw (810 vs 270). So why
would the rebuild of these access paths finish faster on a small box with
less horsepower? Granted there are a few other jobs on the production
system that do run overnight but typically these are queued and last night
(4:30 this morning when I checked it) there were only two other batch jobs
running. The test system is also our Domino Email Server so it's not
always idle overnight either. The production system also has more
available/free diskspace too.
I expect the usual answer - "It Depends"... but am looking for other clues
as to what could be causing this type of behavior, that results in the
longer access path rebuild time on production.
Anyone with any thoughts?
Regards, Jerry
midrange.com
