Re: Large files

---------------------- Forwarded by Mike Crump/IS/Ball-Foster on 06/30/97
03:06 PM ---------------------------


Mike Crump
12/06/96 11:41 AM

To:   midrange-l@midrange.com
cc:
Subject:  "Turbo Query" PTFs Available for V3R7

FYI:

 For those customers running query workloads on their systems for
 Decision Support, Data Warehousing or Report Writing applications, IBM
 is delivering performance enhancements for the DB2/400 optimizer
 through several new PTFs.

 These PTFs can have a SIGNIFICANT positive impact on the performance of
 queries. These performance enhancements were previously only available
 as part of the DB2 Symmetric Multiprocessing (SMP) licensed feature
 of OS/400 (FC 1698). The new PTFs allow non-parallel components of the
 high-performing SMP features to be used for boosting query performance
 even in a single processor AS/400 configuration. A customer with V3R7
 can download these PTFs and see improvements in JOIN and GROUP-BY type
 SQL processing from applications such as Query/400, or those using
 SQL, OPNQRYF (OpenQueryFile), or ODBC interfaces to DB2/400.

 NOTE: These PTFs do NOT replace the DB2 SMP feature of OS/400.
 The SMP feature is still highly recommended for getting the fastest
 query performance from multi-processor AS/400 configurations.
 Since these PTFs are not available for systems running with V3R1 & V3R2,
 the DB2 SMP licensed feature is also recommended for systems at these
 versions running heavy query workloads.

 The PTF numbers are: SF36173, SF36216, and SF36215 (now superseded by
 SF38147).

 Additional information on query optimization can be found in the SQL
 Programming Guide (SC41-4612) or the OS/400 Database Programming
 Guide (SC41-4701).

 Use your own judgement.....I would check out the options on CHGQRYA
 DEGREE(*NONE,
 *IO, *OPTIMIZE, *MAX, *SYSVAL, *ANY) etc......If you are running on a RISC
  machine
 you may have some performance boosts available to you.  My experience on a
  one-way
 processor is that is there is no impact on CPU utilization but it will
 have an impact
 on main memory demands, Disk IOP utilization, and disk arm utilization.
 It has
 been impossible for me to quantify the utilization impact but I would only
  recommend
 usage of this capability if you are somewhat below the thresholds of you
 IOP and disk
 arm utilization.  In my case we are well below the IBM guidelines for
 both.  We have
 had mixed results (this will not improve all jobs).  We have had jobs with
  little to
 no improvement to one nasty SQL job that has improved 70% in clock
 time....

 Your mileage may vary....




* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* This is the Midrange System Mailing List!  To submit a new message,   *
* send your mail to "MIDRANGE-L@midrange.com".  To unsubscribe from     *
* this list send email to MAJORDOMO@midrange.com and specify            *
* 'unsubscribe MIDRANGE-L' in the body of your message.  Questions      *
* should be directed to the list owner / operator: david@midrange.com   *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *