IBM® Informix® 12.10

Enabling the database server to allocate PDQ memory

You can set the IMPLICIT_PDQ session environment option of the SET ENVIRONMENT statement to enable the database server to calculate the amount of PDQ memory to allocate to queries during the current session. This potentially overrides the current PDQPRIORITY setting.

The maximum amount of memory that the database server can allocate, however, is limited by the physical memory available to your system, and by the settings of these parameters:

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.

The IMPLICIT_PDQ session environment option is available only on systems that support PDQPRIORITY.

By default, the IMPLICIT_PDQ session environment variable is set to OFF. When IMPLICIT_PDQ is set to OFF, the server does not override the current PDQPRIORITY setting.

To enable the database server to calculate memory allocations for queries and to distribute memory among query operators according to their needs, enter the following statement before you issue the query:

SET ENVIRONMENT IMPLICIT_PDQ ON;

If you instead set the IMPLICIT_PDQ value to an integer in the range from 1 to 100, the database server scales its estimate by the specified value. For example, the following example restricts memory allocation in aubsequent queries of the session to half of the current PDQPRIORITY setting:

SET ENVIRONMENT IMPLICIT_PDQ '50';

If you set a low IMPLICIT_PDQ value, the amount of memory assigned to the query is proportionally reduced, which might increase the amount of query-operator overflow.

The IMPLICIT_PDQ functionality for a query requires at least LOW distribution statistics on all tables that the query accesses. If distribution statistics are missing for one or more tables that the query references, the IMPLICIT_PDQ setting has no effect on the resources available for query execution. This restriction also applies to star-join queries, which are not supported in the case of missing statistics.

Limiting PDQ resource allocation by setting BOUND_IMPL_PDQ

If IMPLICIT_PDQ is set to ON or to a numeric value, you can also use the BOUND_IMPL_PDQ session environment option of the SET ENVIRONMENT statement of SQL to specify that the allocated PDQ memory should be no greater than the current explicit PDQPRIORITY value or range. If the IMPLICIT_PDQ session environment setting is OFF, whether explicitly off by default, then the BOUND_IMPL_PDQ setting has no effect.

For example, the following statement forces the database server to use explicit PDQPRIORITY values as guidelines in allocating memory, if the IMPLICIT_PDQ session environment option has already been set:

SET ENVIRONMENT BOUND_IMPL_PDQ ON;

If the IMPLICIT_PDQ setting is an integer in the range from 1 to 100, the explicit PDQPRIORITY value is scaled by that setting as a percentage during the current session.

When the BOUND_IMPL_PDQ session environment option is set to ON (or to one), 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 you include an integer value in the SET ENVIRONMENT statement, you must put quote marks around the value. However, do not put quote marks around the ON and OFF keywords.

The following examples are statements with integer values:

SET ENVIRONMENT IMPLICIT_PDQ "50";
SET ENVIRONMENT BOUND_IMPL_PDQ "1";

Examples exchange | Troubleshooting

To find the PDF, see Publications for the IBM Informix 12.10 family of products.
For the release notes, documentation notes, and/or machine notes, see the Release Notes page.