Change Query Attributes (CHGQRYA)

Change Query Attributes (CHGQRYA)

Where allowed to run: All environments (*ALL)
Threadsafe: Yes Parameters
Examples
Error messages

The Change Query Attributes (CHGQRYA) command specifies attributes for database queries and database file keyed access path builds, rebuilds, and maintenance that are run in a job. Database queries include the open of a SQL view and the running of SQL data manipulation statements.
Restrictions: You must have job control (*JOBCTL) special authority to use this command.

Top

Parameters

Keyword Description Choices Notes
JOB Job name Single values: *
Other values: Qualified job name Optional, Key
Qualifier 1: Job name Name
Qualifier 2: User Name
Qualifier 3: Number 000000-999999
QRYTIMLMT Query processing time limit 0-2147352578, *SAME, *NOMAX, *SYSVAL Optional
DEGREE Parallel processing degree Single values: *SAME, *NONE, *IO, *OPTIMIZE, *MAX, *SYSVAL, *ANY
Other values: Element list Optional
Element 1: Processing option *NBRTASKS
Element 2: Number of tasks 2-9999
ASYNCJ Asynchronous job usage *SAME, *DIST, *LOCAL, *ANY, *NONE Optional
APYRMT Apply CHGQRYA to remote *SAME, *YES, *NO Optional
QRYOPTLIB Query options file library Name, *SAME Optional
QRYSTGLMT Query temporary storage limit 0-2147352578, *SAME, *NOMAX Optional

Top

Job name (JOB)

Specifies the job for which the query attributes are to be changed.
Single values

*

The query attributes of the job running the CHGQRYA command are to be changed.

Qualifier 1: Job name

name

Specify the name of the job whose query attributes are to be changed. If no job user name or job number qualifiers are specified, all of the jobs currently in the system are searched for the specified simple job name. If duplicates of the specified job name are found, specify a job user name or job number that uniquely identifies the job to be changed.

Qualifier 2: User

name

Specify the name of the user of the job whose query attributes are to be changed.

Qualifier 3: Number

000000-999999

Specify the number of the job whose query attributes are to be changed.

Top

Query processing time limit (QRYTIMLMT)

Specifies a limit for database queries allowed to be started based on the estimated number of elapsed seconds that the query requires to process.
The initial value of the QRYTIMLMT attribute for a job is *SYSVAL.

*SAME

The value does not change.

*NOMAX

There is no maximum number of estimated elapsed seconds.

*SYSVAL

The query time limit should be obtained from the system value QQRYTIMLMT.

0-2147352578

Specify the maximum value that is checked against the estimated number of elapsed seconds required to run a query. If the estimated elapsed seconds is greater than this value, the query is not started.
When 0 is specified all database queries issue a CPA4259 inquiry message. Setting a query time limit of 0 can be useful when attempting to tune database queries for better performance because the technical description of the CPA4259 inquiry message explains the type of access plan used by the query.

Top

Parallel processing degree (DEGREE)

Specifies the parallel processing option and, optionally, the number of tasks that can be used when running database queries and database file keyed access path builds, rebuilds, and maintenance in the job.
The specified parallel processing option determines the types of parallel processing allowed. There are two types of parallel processing:

Input/Output (I/O) parallel processing
With I/O parallel processing, the database manager uses multiple tasks for each query to do the I/O processing. The central processor unit (CPU) processing is still done serially.
Symmetric Multiprocessing (SMP)
SMP assigns both the CPU and I/O processing to tasks that will run the query in parallel. Actual CPU parallelism requires a system with multiple processors. SMP parallelism can only be used if the system feature, DB2 Symmetric Multiprocessing for OS/400, is installed.
Use of SMP parallelism can affect the order in which records are returned. Applications which depend on records being returned from database queries in arrival sequence or keyed access path sequence that have not explicitly defined the ordering sequence in the query, should not be run in jobs which have specified a parallel processing option that enables SMP processing.

The initial value of the DEGREE attribute for a job is *SYSVAL.
Single values

*SAME

The value does not change.

*NONE

No parallel processing is allowed for database query processing or database file keyed access path build, rebuild, or maintenance.

*IO

Any number of tasks can be used when the database query optimizer chooses to use I/O parallel processing for queries. SMP parallel processing is not allowed.

*OPTIMIZE

The query optimizer can choose to use any number of tasks for either I/O or SMP parallel processing to process the query or database file keyed access path build, rebuild, or maintenance. SMP parallel processing is used only if the system feature, DB2 Symmetric Multiprocessing for OS/400, is installed. Use of parallel processing and the number of tasks used is determined with respect to the number of processors available in the system, this job's share of the amount of active memory available in the pool in which the job is run, and whether the expected elapsed time for the query or database file keyed access path build or rebuild is limited by CPU processing or I/O resources. The query optimizer chooses an implementation that minimizes elapsed time based on the job's share of the memory in the pool.

*MAX

The query optimizer chooses to use either I/O or SMP parallel processing to process the query. SMP parallel processing will only be used if the system feature, DB2 Symmetric Multiprocessing for OS/400, is installed. The choices made by the query optimizer are similar to those made for parameter value *OPTIMIZE except the optimizer assumes that all active memory in the pool can be used to process the query or database file keyed access path build, rebuild, or maintenance.

*SYSVAL

The processing option used is set to the current value of the system value, QQRYDEGREE.

*ANY

This value has the same meaning as *IO. The *ANY value is maintained for compatibility with prior releases.

Element 1: Processing option

*NBRTASKS

The number of tasks to be used for SMP parallel processing is specified by the second element of the DEGREE parameter.

Element 2: Number of tasks

2-9999

Specify the number of tasks to be used when the query optimizer chooses to use SMP parallel processing to process a query. I/O parallelism is also allowed. SMP parallel processing is used only if the system feature, DB2 Symmetric Multiprocessing for OS/400, is installed.
Using a number of tasks less than the number of processors available on the system restricts the number of processors used simultaneously for running a given query or database file keyed access path build, rebuild, or maintenance. A larger number of tasks ensures that the query or database file keyed access path build, rebuild, or maintenance is allowed to use all of the processors available on the system to run the query. Too many tasks can degrade performance because of the over-commitment of active memory and the overhead cost of managing all of the tasks.

Top

Asynchronous job usage (ASYNCJ)

Specifies the circumstances in which asynchronous (temporary writer) jobs can be used to help process database queries in the job.
The specified usage option determines which types of database queries can use asynchronous jobs (running in parallel) to help in completing the query.
An asynchronous job is a separate job on the system that handles query requests from jobs that are running database queries. For each request, the asynchronous job processes the request and puts the results into a temporary file. This intermediate temporary file is then used by the main job to complete the database query.
The advantage of using an asynchronous job is that it can be processing its request at the same time (in parallel) that the main job is processing another step of the database query. The disadvantage of using an asynchronous job is that it may encounter a situation that it cannot handle in the same way as the main job. For example, the asynchronous job may receive an inquiry message from which it would have to cancel, whereas the main job could have chosen to ignore the message and continue.
There are two different types of database queries that can use asynchronous jobs:

Distributed queries
These are database queries that involve distributed files. Distributed files are provided through the system feature DB2 Multi-System for OS/400.
Local queries
These are database queries that involve only files local to the system where the database queries are being run.

The initial value of the ASYNCJ attribute for a job is *LOCAL.

*SAME

The value does not change.

*DIST

Asynchronous jobs may be used for database queries that involve distributed files.

*LOCAL

Asynchronous jobs may be used for database queries that involve only files local to the system where the database queries are being run.
In addition, for queries involving distributed files, this option allows the communications required to be asynchronous. This allows each system involved in the query of the distributed files to run its portion of the query at the same time (in parallel) as the other systems.

*ANY

Asynchronous jobs may be used for any database query.

*NONE

No asynchronous jobs are allowed to be used for database query processing.
In addition, all processing for queries involving distributed files occurs synchronously. Therefore, no inter-system parallel processing will occur.

Top

Apply CHGQRYA to remote (APYRMT)

Specifies, for database queries involving distributed files, whether or not the query attributes are applied to the jobs on the remote systems associated with this job. The query attributes applied are those from this command and those specified in the QAQQINI file in the library specified for the Query options file library (QRYOPTLIB) parameter.
The specified option determines whether the query attributes specified for the job are applied to the associated jobs on the systems applicable to the distributed file or files.
The initial value of the APYRMT attribute for a job is *YES.

*SAME

The value does not change.

*YES

The query attributes for the job are applied to the remote jobs used in processing database queries involving distributed files. The query attributes applied are those specified on this command and those in the QAQQINI file in the library specified for the QRYOPTLIB parameter. For attributes where *SYSVAL is specified, the system value on the remote system is used for the remote job. This option requires that, if CHGQRYA was used for this job, the remote jobs must have authority to use the CHGQRYA command.

*NO

The CHGQRYA attributes for the job are not applied to the remote jobs. The remote jobs will use the attributes associated to them on their systems.

Top

Query options file library (QRYOPTLIB)

Specifies which library currently contains, or will contain, the query options file (QAQQINI).
The query options file is used to set or modify the attributes used by the Query Optimizer that will determine how a query will be implemented in the job specified.
The query options file uses a system-supplied trigger program associated with the file QAQQINI in order to process any changes made to the file. A template for the file is shipped in the library QSYS with the base trigger program already attached. In order to maintain and use the query options file correctly, it is recommended that Create Duplicate Object (CRTDUPOBJ) be used to create a copy of the file QAQQINI into the library specified for this parameter.
For more information, refer to the SQL Programming information in the iSeries Information Center at http://www.ibm.com/eserver/iseries/infocenter or Database information in the iSeries Information Center at http://www.ibm.com/eserver/iseries/infocenter.
The initial value of the QRYOPTLIB attribute for a job is QUSRSYS.

*SAME

The value does not change.

name

Specify the name of the library where the QAQQINI query options file is (or will be) located.

Top

Query temporary storage limit (QRYSTGLMT)

Specifies a temporary storage limit for database queries. If the query is expected to use more than the specified amount of storage, the query will not be allowed to start. The value specified is in megabytes.
The initial value of the QRYSTGLMT attribute for a job is *NOMAX.

*SAME

The value does not change.

*NOMAX

There is no maximum temporary storage limit.

0-2147352578

Specify the maximum amount of temporary storage that may be used. This value is checked against the estimated amount of temporary storage required to run the query, as calculated by the query optimizer. If the estimated amount of temporary storage is greater than this value, the query is not started. The value specified is in megabytes.
When 0 is specified most database queries will exceed the specified temporary storage limit and will issue a CPA4259 inquiry message. Setting a query temporary storage limit of 0 can be useful when attempting to tune database queries for better performance because the technical description of the CPA4259 inquiry message explains the type of access plan used by the query.

Top

Examples

Example 1: Changing the Query Time Limit
 CHGQRYA   QRYTIMLMT(60)
This command changes the query time limit to 60 seconds.
Example 2: Controlling Query and Database Parallel Processing
 CHGQRYA   DEGREE(*IO)
This command specifies that any number of tasks may be used when the database query optimizer chooses to use I/O parallel processing for queries. SMP parallel processing is not allowed.
Example 3: Controlling Query Parallel Processing
 CHGQRYA   DEGREE(*OPTIMIZE)
This command specifies that the query optimizer can choose to use any number of tasks for either I/O or SMP parallel processing to process a query, database file keyed access path build or rebuild, or database file I/O keyed access path maintenance. SMP parallel processing will only be used if the system feature DB2 Symmetric Multiprocessing is installed.
Example 4: Controlling Query Parallel Processing
 CHGQRYA   DEGREE(*MAX)
This command specifies that the query optimizer can assume that all active memory in the pool can be used to process a query, database file keyed access path build or rebuild, or database file I/O keyed access path maintenance and can choose to use any number of tasks for either I/O or SMP parallel processing to process a query, database file keyed access path build or rebuild, or database file I/O keyed access path maintenance. SMP parallel processing will only be used if the system feature DB2 Symmetric Multiprocessing is installed.
Example 5: Controlling Query Number of Tasks
 CHGQRYA   DEGREE(*NBRTASKS 12)
This command specifies that the 12 tasks are to be used when the query optimizer chooses to use SMP parallel processing to process a query, database file keyed access path build or rebuild, or database file I/O keyed access path maintenance. I/O parallelism will also be allowed. SMP parallel processing will only be used if the system feature DB2 Symmetric Multiprocessing is installed.
Example 6: Controlling Query Parallel Processing
 CHGQRYA   DEGREE(*SYSVAL)
This command specifies that the query, database file keyed access path build or rebuild, or database file I/O keyed access path maintenance, should be optimized with the current value of system value QQRYDEGREE when the query, database file keyed access path build or rebuild, or database file I/O keyed access path maintenance is run.
Example 7: Disabling Asynchronous Job Usage for Distributed File Processing
 CHGQRYA   ASYNCJ(*LOCAL)
This command prevents asynchronous jobs from being used for queries involving distributed files.
Example 8: Disabling Asynchronous Job Usage
 CHGQRYA   ASYNCJ(*NONE)
This command prevents asynchronous jobs from being used for any queries. In addition, for queries involving distributed files, communication to remote systems is done in a synchronous fashion.
Example 9: Specifies Query Options File Library
 CHGQRYA   QRYOPTLIB(QUSRSYS)
This command specifies that library QUSRSYS is to be searched for the existence of the query options file (QAQQINI).

Use this command in addition to STRDBG UPDPROD(*YES) and all optimizer debug messages (local and remote) will show up in this job's job log.
Example 10: Specifies Query Options File Library for a Different Job
 CHGQRYA   QRYOPTLIB(LIB41)  JOB(134543/QPGMR/DSP01)
This command specifies that library LIB41 is to be searched for the existence of the query options file (QAQQINI) for job number 134543. The job name is DSP01 and was started by the user named QPGMR. This library may exist in more than one independent ASP (auxiliary storage pool); the library in the namespace of the originator's job will always be used.
Example 11: Changing the Query Resource Limit
 CHGQRYA   QRYSTGLMT(200)
This command changes the query temporary storage limit to 200 megabytes.

Top

Error messages

*ESCAPE Messages

CPF1321

Job &1 user &2 job number &3 not found.

CPF436E

Job &1 user &2 job number &3 is not active.

CPF9810

Library &1 not found.

Top

Keyword	Description	Choices	Notes
JOB	Job name	Single values: * Other values: Qualified job name	Optional, Key
	Qualifier 1: Job name	Name
	Qualifier 2: User	Name
	Qualifier 3: Number	000000-999999
QRYTIMLMT	Query processing time limit	0-2147352578, SAME, NOMAX, *SYSVAL	Optional
DEGREE	Parallel processing degree	Single values: SAME, NONE, IO, OPTIMIZE, MAX, SYSVAL, ANY Other values: Element list*	Optional
	Element 1: Processing option	*NBRTASKS
	Element 2: Number of tasks	2-9999
ASYNCJ	Asynchronous job usage	SAME, DIST, LOCAL, ANY, *NONE	Optional
APYRMT	Apply CHGQRYA to remote	SAME, YES, *NO	Optional
QRYOPTLIB	Query options file library	Name, *SAME	Optional
QRYSTGLMT	Query temporary storage limit	0-2147352578, SAME, NOMAX	Optional