RE: SMP experiences -- MIDRANGE-L

There is a lot of LF updating I believe.  We have three files that get hit
pretty hard every night.  The smallest has 94+ million records with 21
indexes and the largest is over 400 million with 6 indexes.

Rick --

If you have more than one job accessing these files you might want to check that you are using the new improved terabyte indexes for all the access paths built over these three files. If you are using the gigabyte indexes the locking during index maintenance is at the file level. The new improved terabyte indexes lock at the page level. It is controlled with the: Access path size (ACCPTHSIZ) parameter either *MAX4GB or *MAX1TB. When you change it, it will rebuild the access path.

-- Charly


Merlin Inc
Box 640
Gig Harbor WA 98335 USA
253 265-6244

From: "Chevalier, Rick" <Rick.Chevalier@xxxxxxxxxxxxxxx>
Reply-To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
To: "'Midrange Systems Technical Discussion'" <midrange-l@xxxxxxxxxxxx>
Subject: RE: SMP experiences
Date: Tue, 24 Jun 2003 11:30:18 -0500


> SMP will help Query Optimizer a great deal so if you are using lots of
> SQL or OPNQRYF in your processing, you should see a notable performance
> improvement.

Most of our nightly processing uses native I/O but a lot of the newer stuff
and enhancements are being done using SQL.

> On the native I/O side of things, the only benefit I could think of with
> SMP is parallel index maintenance, which in itself may give you a boost
> in performance if your processing is update intensive and you have a lot
> of indexes (LFs) being maintained.

There is a lot of LF updating I believe. We have three files that get hit pretty hard every night. The smallest has 94+ million records with 21 indexes and the largest is over 400 million with 6 indexes. Currently the QQRYDEGREE system value is set to *I/O prior to nightly processing. I'm having trouble finding much information on this option. Are we already getting the benefit of parallel index maintenance or should it be changed to something else?


> As for drawbacks, the only one I heard a customer mention is the
> aggressive utilization of system resources.  What I mean is, if you have
> some really intensive query app, optimizer will try to use 100% of CPU
> to fulfill its requests, which may slow down your interactive
> throughput.  It doesn't sound like that would be a problem on your
> system though, as you say you have 16 processors and you plan to use SMP
> to help nightly batch processing.  And even if it was a problem, there
> are ways (and products) that can help you deal with resource allocation
> management.

Doesn't this only happen if SMP is set to *MAX?  What I have read so far
implies that the *OPTIMIZE setting sets boundaries based on the memory pool
the job runs in to prevent what you have described from happening.  Am I
missing something?

Rick
_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

_________________________________________________________________ The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail