Setting Limitations on Resources Used by the QZDASOINIT Prestart Jobs

Troubleshooting

Problem

Resolving The Problem

This document lists options to limit the amount of resources that are used by all or some of the QZDASOINIT prestart jobs to make more resources available for other jobs. It also provides more helpful references.

The following are options to limit the amount of resources that are used by all or some of the QZDASOINIT prestart jobs to make more resources available for other jobs:

Query Supervisor Starting at IBM i 7.3: https://www.ibm.com/docs/en/i/7.3?topic=tools-query-supervisor "The Db2 for i SQL Query Engine (SQE) provides a Query Supervisor, which enables real-time monitoring of resource consumption by SQL and native queries, for example the Open Query File (OPNQRYF) command. Query Supervisor threshold levels can be established for general or specific scenarios. When a threshold is met or exceeded, the query execution is interrupted, and user-supplied exit programs are called."
Workload capping Starting in 7.1, the concept of workload capping was introduced; it provides a mechanism for you to define the number of processors assigned to a workload group. Workload capping can be used to limit the number how many processors can be used for all or certain QZDASOINIT jobs. To have it affect only certain jobs, you need to route those jobs to a specific subsystem that is defined as part of a workload group. Connections can then be routed to a custom subsystem based on the client IP address: `Host Server Customization, Routing Connections By IP Address or by Routing Connections to Subsystems Based On User ID`. Managing workload groups
Work Management Resources `Changing the Run Priority of a Host Server Prestart Job` Notes: 1. Prestart job entries assign a Pool and a Class to a job while it is being started. 2. Classes are system objects that contain parameters used only during the time a job is active. The parameters are: Run priority, timeslice in milliseconds, Eligible for purge, Default wait time in seconds, Maximum CPU time in milliseconds, and Maximum temporary storage in K bytes. They are manipulated with the Create Class (CRTCLS), Delete Class (DLTCLS), Display Class (DSPCLS), Change Class (CHGCLS), and Work with Classes (WRKCLS) commands.
Pool Subsystem Pools Overview Separating Batch Work from BASE (refer to the section on Private Pools) Note:* Consult the iGSC performance support team for details and implications of use of this option.
Temporary Storage The following document relies on client-side settings: Limiting Temporary Storage Used by QZDASOINIT Managing Jobs Maximum CPU time and MaximumTemporary Storage
CPU Time CPU time can be limited by changing the CLASS used by QZDASOINIT. The disadvantage of trying to limit resources in this way is that when the CPU Time is limited, the job ends when that limit is reached. In most cases, it is more desirable to decrease the run priority or something of that sort.
Query timeout The Change Query Attributes (CHGQRYA) command can be used to set a Query timeout. When the ODBC call is made, Query Optimizer estimates how long the query takes to run. If it is over the value set by CHGQRYA, a message, SQL0666, is issued and the query is not run. Note: Job control special authority (*JOBCTL) is required to change the query timeout value for a job, see the following document for details. `Job Control Required To Set Query Timeout Limit With Database Host Server`
IBM Navigator for i Monitors https://www.ibm.com/docs/en/i/7.4?topic=i-navigator-monitors "IBM® Navigator for i monitors track current information about the performance of your system. Additionally, you can use them to carry out predefined actions when a specific event occurs. Monitors continue to monitor and perform any threshold commands or actions you specified until you stop the monitor."
SMP (Symmetric Multiprocessing) The Change Query Attributes (`CHGQRYA`) command can be used to specify settings for SMP. Setting SMP to NONE 'shuts off' multiprocessing and can help reduce the amount of system resources used by the QZDASOINIT jobs. Notes:* 1. The SMP setting is only applicable on systems that have multiple processors. 2. Consult the iGSC database support team for the details in implications of the use of this option.
QAQQINI library The IBM i client data providers all have properties that allow setting the library containing a customized QAQQINI file. See the QAQQINI documentation for all the query options that file allows. IBM Toolbox for Java (jt400.jar) uses "qaqqinilib". IBM i Access ODBC data sources provide a setting for the "Query options file library" on the "Diagnostic" tab of an ODBC DSN. The same value can be set as a connection property in an ADO/OLE DB connection. The Db2 for i .Net provider (IBM.Data.DB2.iSeries) uses property "QueryOptionsFileLibrary" For all data providers, the user profile used for the DB2 connection must have authority to the CHGQRYA command and the QAQQINI file residing in the specified Query Options File Library.

Related Information

Work Management Reference Manual

Controlling queries dynamically with the query options file QAQQINI

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"Host Servers","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"Version Independent","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]

Historical Number

351081544

Was this topic helpful?

Document Information

Modified date:
24 October 2022

UID

nas8N1019190

Tips