BOUND_IMPL_PDQ session environment option

You can set the BOUND_IMPL_PDQ session environment option to limit PDQ resource allocation. If the IMPLICIT_PDQ session environment option is set to ON or to a positive integer value no greater than 100, you can use the BOUND_IMPL_PDQ environment option to specify that the allocated memory should be bounded by the current explicit PDQPRIORITY value or by a range.

The IMPLICIT_PDQ and the BOUND_IMPL_PDQ session environment options are available only on systems that support PDQPRIORITY. If IMPLICIT_PDQ is set to OFF, then the BOUND_IMPL_PDQ setting is ignored, and the server does not override the current PDQPRIORITY setting.

The BOUND_IMPL_PDQ session environment option has this syntax:


BOUND_IMPL_PDQ environment option

|--SET ENVIRONMENT BOUND_IMPL_PDQ -+-OFF---------+--------------|
                                   +-ON----------+   
                                   '-' integer '-'

Element	Description	Restrictions	Syntax
integer	Nonnegative integer limiting the memory available to a query in the session as this percentage of the PDQPRIORITY value	Must be in the range `0 < integer < 101`	Quoted String

Usage

The BOUND_IMPL_PDQ environment option can be set to the following values, but its effects depend on the IMPLICIT_PDQ session environment option being enabled:

OFF: No upper bound on implicit PDQPRIORITY calculation.
ON: The server uses the explicitly specified PDQPRIORITY setting as the upper bound for calculating the implicit PDQPRIORITY.
'integer' or "integer": The explicit PDQPRIORITY value is scaled by this percentage in the current session.

For example, you might execute the following statement to force the database server to use explicit PDQPRIORITY values as guidelines in allocating memory if the IMPLICIT_PDQ environment option is enabled for the current session:

SET ENVIRONMENT BOUND_IMPL_PDQ ON;

If you instead specify a positive integer in the range from 1 to 100, the explicit PDQPRIORITY value is scaled by that setting during the current session. The specified integer must be delimited by quotation marks, as in the following example, which specifies 75% of available PDQ memory as the upper bound:

SET ENVIRONMENT BOUND_IMPL_PDQ "75";

By default, BOUND_IMPL_PDQ is not enabled. When the BOUND_IMPL_PDQ session environment option is set to ON for the current session, you require the database server to use the explicit PDQPRIORITY setting as the upper bound for memory that can be allocated to a query. If you set both IMPLICIT_PDQ and BOUND_IMPL_PDQ, then the explicit PDQPRIORITY value determines the upper limit of memory that can be allocated to a query. If PDQPRIORITY is specified as a range, the database server grants memory within the range specified.

The following example disables BOUND_IMPL_PDQ in the current session, so that the server is not restricted in the proportion of the PDQPRIORITY setting that it can allocate to a query:

SET ENVIRONMENT BOUND_IMPL_PDQ OFF;

See also the discussion of parallel database query (PDQ).