SET PDQPRIORITY statement

The SET PDQPRIORITY statement enables an application to set the query priority level dynamically within a routine. The SET PDQPRIORITY statement is an extension to the ANSI/ISO standard for SQL.

Syntax


1  SET PDQPRIORITY
1 DEFAULT
1 LOW
1  OFF
1 HIGH
1 resources
Element Description Restrictions Syntax
resources Integer that specifies the query priority level and the percent of resources to process the query Can range from -1 to 100. See also Allocating Database Server Resources. Literal Number

Usage

The SET PDQPRIORITY statement overrides the PDQPRIORITY environment variable (but has lower precedence than the MAX_PDQPRIORITY configuration parameter). The scope of SET PDQPRIORITY is local to the routine, and does not affect other routines within the same session. When a routine that issues this statement terminates, the setting reverts to the system default value.

Set PDQ priority to a value less than the quotient of 100 divided by the maximum number of prepared statements. For example, if two prepared statements are active, you should set the PDQ priority to less than 50.

For example, assume that the DBA sets the MAX_PDQPRIORITY parameter to 50. Then a user enters the following SET PDQPRIORITY statement to set the query priority level to 80 percent of resources:
SET PDQPRIORITY 80;

When it processes the query, the database server uses the MAX_PDQPRIORITY value to factor the query priority level set by the user. The database server silently processes the query with a priority level of 40. This priority level represents 50 percent of the 80 percent of resources that the user specifies.

The following keywords are supported by the SET PDQPRIORITY statement.
Keyword
Effect
DEFAULT
Uses the setting of the PDQPRIORITY environment variable
LOW
Data values are fetched from fragmented tables in parallel. (In HCL OneDB™, when you specify LOW, the database server uses no other forms of parallelism.)
OFF
PDQ is turned off (HCL OneDB only). The database server uses no parallelism. OFF is the default if you use neither the PDQPRIORITY environment variable nor the SET PDQPRIORITY statement.
HIGH
The database server determines an appropriate PDQPRIORITY value, based on factors that include the number of available processors, the fragmentation of the tables being queried, the complexity of the query, and others. HCL reserves the right to change the performance behavior of queries when HIGH is specified in future releases.
The following SET PDQPRIORITY statements have the same effects:
SET PDQPRIORITY DEFAULT;

SET PDQPRIORITY -1;
Both replace any prior SET PDQPRIORITY level with the setting of the PDQPRIORITY environment variable. See also, however, the following description of the order of precedence among methods for allocating query priority resources.

Precedence of methods for allocating PDQ priority

For memory available in operations that are affected by PDQPRIORITY, the maximum amount of memory that the database server can allocate is limited by the physical memory available to your system, and by the settings (in ascending order of lowest precedence to highest precedence) of the following environment variables, SQL statements, configuration parameters, and session environment variables:
  • The PDQPRIORITY environment variable
  • The most recent SET PDQPRIORITY statement of SQL
  • The MAX_PDQPRIORITY configuration parameter
  • The DS_TOTAL_MEMORY configuration parameter
  • The BOUND_IMPL_PDQ session environment variable, if the IMPLICIT_PDQ session environment variable is enabled in the current session by the SET ENVIRONMENT statement of SQL.
The scope of the SET ENVIRONMENT BOUND_IMPL_PDQ and SET ENVIRONMENT IMPLICIT_PDQ statements is the current session.

When concurrent queries are running, the DS_MAX_QUERIES configuration parameter setting can also restrict the amount of PDQ memory available for a new query.