StarQuest: keeping data in motion

 

StarQuest Technical Documents

AS/400 iSeries Tuning Tips to Improve Performance when using StarSQL

Last Update: 26 June 2008
Product: StarSQL
Version: 5.x
Article ID: SQV00SQ005

Abstract

This technical article offers iSeries tuning tips to improve performance when using StarSQL.

* StarQuest strongly recommends that you work with an IBM Support Representative when making any of the suggested modifications.

Solution

  1. Check the system value for QQRYDEGREE. This parameter should be set to a value of *MAX to insure that a query will run as fast as possible with respect to parallelism.
  2. Typically, jobs initiated interactively from a workstation run in the Subsystem QINTER, whereas client-server applications such as those using StarSQL run in QSYSWRK. Subsystem Descriptions may and typically do specify different pools of main memory to be exploited in order to optimize jobs running in a given subsystem.

Secondly, individual Job Descriptions may also differ. What is key to the performance will be the job priority associated with the pre-start job "QRWTSRVR" – the Class associated with the JOBD should specify a priority equivalent to that provided for interactive users (i.e. 20 or lower, depending upon what the interactive user's JOBD specifies.)

  1. The TIMESLICE associated with the Pre-start job should be at least 2000 (that's MS) to avoid swapping the Query job. 
  2. Examine the memory pools that are associated with QSYSWRK (*BASE) to determine if the *BASE pool is experiencing a high degree of paging.
  3. The easiest way to insure that the client server queries are running with the same characteristics would be to change the Pre-start job attributes for QRWTSRVR. 

The job QRWTSRVR is usually a pre-start job in subsystem QSYSWRK that defines the user QUSER and the class QSYSCLS20. It should run with an execution priority of 20, but probably runs in the *BASE memory pool. If the pre-start job was moved to the QINTER subsystem it might run in a different memory pool depending on how the system has been set up. Likely you have a memory pool for interactive jobs named *INTERACT. You can view the characteristics of the pre-start job like this:

DSPACTPJ SBS(QSYSWRK)PGM(QSYS/QRWTSRVR)

Specifying that the pre-start job runs in QINTER can be achieved using the following instructions:

To move it to subsystem QINTER you will need to remove the pre-start job from QSYSWRK and add it to QINTER. The ENDPJ command can be used to end the pre-start job in QSYSWRK. Use the DSPPJ command to review the characteristics of the Pre-start Job and then add the Pre-start job to QINTER.  The STRPJ command can be used to start it in QINTER.

DISCLAIMER

The information in technical documents comes without any warranty or applicability for a specific purpose. The author(s) or distributor(s) will not accept responsibility for any damage incurred directly or indirectly through use of the information contained in these documents. The instructions may need to be modified to be appropriate for the hardware and software that has been installed and configured within a particular organization.  The information in technical documents should be considered only as an example and may include information from various sources, including IBM, Microsoft, and other organizations.