IMPLICIT_PDQ session environment option

Use the IMPLICIT_PDQ session environment option to allow the database server to determine the amount of memory allocated to a query, ignoring the explicit system default PDQPRIORITY environment variable setting.


IMPLICIT_PDQ environment option

|--SET ENVIRONMENT IMPLICIT_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

Only systems that support PDQPRIORITY can reset the IMPLICIT_PDQ session environment variable, which can have the following values:

The The IMPLICIT_PDQ environment option can be set to the following values:

OFF: Disable IMPLICIT_PDQ. The server calculates no implicit PDQPRIORITY.
ON: The server automatically calculates an implicit PDQPRIORITY to use for each query.
'integer' or "integer": Use integer% of the server-calculated implicit PDQPRIORITY value for each query, where integer is a nonnegative whole number in the range 1-100.

Unless BOUND_IMPL_PDQ is also set, the database server ignores the explicit setting of the PDQPRIORITY environment variable when IMPLICIT_PDQ is set to ON or to "100".

The database server does not allocate more memory, however, than is available when PDQPRIORITY is set to "100". 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 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

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.

By default, IMPLICIT_PDQ is not enabled. When IMPLICIT_PDQ is set to OFF, whether explicitly or by default, the database server does not override the current PDQPRIORITY setting when allocating resources to queries. For example, the following statement prevents the server from using implicit PDQPRIORITY for subsequent queries in the session:

SET ENVIRONMENT IMPLICIT_PDQ OFF;

If you set IMPLICIT_PDQ to an integer value between 1 and 100, the database server scales its estimate by the specified percentage. If you set a low value, the amount of memory allocated to the query is reduced, which might increase the risk of query-operator overflow. The following statement restricts the memory available for subsequent queries in the session to 75% of the calculates implicit PDQPRIORITY:

SET ENVIRONMENT IMPLICIT_PDQ '75';

To request the database server to determine memory allocations for queries and distribute memory among query operators according to their needs, enter the following statement:

SET ENVIRONMENT IMPLICIT_PDQ ON;

To require the database server to use explicit PDQPRIORITY settings as the upper bound and optional lower bound of memory that it grants to a query, set the BOUND_IMPL_PDQ session environment option.

Star-join query execution plans require PDQ priority to be set. Setting the IMPLICIT_PDQ session environment option to enable implicit PDQ offers an alternative. If IMPLICIT_PDQ is set to ON for the session, then a star-join execution plan will be considered without explicit setting PDQPRIORITY. The SET ENVIRONMENT IMPLICIT_PDQ ON statement can be issued by a sysdbopen routine, so that users automatically enable implicit PDQ when they open the database. In this case, the query optimizer automatically considered a star join without explicit PDQPRIORITY setting by the user.

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

For information on creating a sysdbopen routine and on specifying the users whose sessions it will affect, see the topic Using SYSDBOPEN and SYSDBCLOSE Procedures. For information about the PDQPRIORITY environment variable, see the IBM® Informix® Guide to SQL: Reference. For information about the DS_TOTAL_MEMORY and MAX_PDQPRIORITY configuration parameters, see the IBM Informix Administrator's Reference.