This document discusses ways to minimize CPU usage by SQL statements and queries.

1. Isolate and tune the query that is using CPU. High CPU may sometimes indicate a temporary index build is being done and a permanent object can be created to eliminate what it is being done at the query level. You can use an SQL Performance Monitor (DBMON), Index Advisor, and Plancache Snapshot to tune or locate queries that need tuning.

See this TechNote for more information on Database Monitor impacts:  https://www.ibm.com/support/pages/node/882914

2. Check to see if you have DB2 Symmetric Multiprocessing installed. Issue GO LICPGM option 10 and press F11 two times to see the options. SMP is listed under 57xxSS1 - option 26.

This option is controlled by system value QQRYDEGREE or query options file (QAQQINI) parameter, 'PARALLEL_DEGREE' or via the DEGREE parameter on the CHGQRYA command. Issue DSPSYSVAL QQRYDEGREE to determine what your setting is at. IBM generally recommends this setting to *OPTIMIZE. However, using SMP will tend to use more CPU and, if the system is already at a high CPU usage, you may need to change your setting.

Note: Changes of system value QQRYDEGREE can alter how your queries run, and testing and monitoring should be done to determine the proper setting for your environment.

3. Use the QAQQINI query options file to reduce the number of tasks queries use; the parameter and value of interest is PARALLEL_DEGREE = *OPTIMIZE xxx. Reducing the number of tasks used by the query will decrease the amount of CPU seconds used by the query.

PARALLEL_DEGREE = *OPTIMIZE xxx:
o xxx is a percentage from 1 to 200 used to influence the number of tasks.
o If a percentage is not specified, the default value of 100 is used.
o The query optimizer determines the parallel degree for the query using the same processing as is done for *OPTIMIZE; the optimizer will only use a percentage of the number of tasks the query would normally use, not a percentage of CPU on the system.
o Common settings are *OPTIMIZE 50 or *OPTIMIZE 25.

Example: if you have a query that uses 8 tasks and you set the QAQQINI to *OPTIMIZE 50, the query will use 50% of the number of tasks it would normally use (in this example, 4).

To achieve this setting, you would run this statement in an SQL environment (such as STRSQL) to use 50%:

UPDATE QUSRSYS/QAQQINI
SET QQVAL = '*OPTIMIZE 50'
WHERE QQPARM = 'PARALLEL_DEGREE'


Note: Having a version of QAQQINI in QUSRSYS will affect all queries system wide. If you do not want to set this setting system wide and instead would like to set it for certain jobs, use the CHGQRYA command in the job stream to set the query options library to something other then QUSRSYS.

CAUTION: Changes to the QAQQINI query options file can alter how your queries run, and testing and monitoring should be done to determine the proper setting for your environment.

For further information on the QAQQINI query options file, refer to IBM's Documentation: https://www.ibm.com/docs/en/i/7.5?topic=qaqqini-query-options

4. Configure Workload Capping.
Workload capping allows you to set a usage limit for a licensed program by restricting the number of processor cores that are available to be used by the licensed program. https://www.ibm.com/docs/en/i/7.5?topic=groups-setting-up-workload