CL Command Reference - CHGQRYA

CL Command List > CHGQRYA Reference

Description:

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.


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).

Note: 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.