USE_DWA session environment options

Use the USE_DWA session environment options of the SET ENVIRONMENT statement to control various aspects of workload analysis, data mart creation, and query acceleration by setting the database client environment for Informix® Warehouse Accelerator sessions. Each of the USE_DWA options has an effect only with Informix® Warehouse Accelerator.

The USE_DWA options of the SET ENVIRONMENT statement have this syntax:

>>-SET ENVIRONMENT--use_dwa------------------------------------->

                                                   .-off-.                        
>--'--+-+---------------------+------+-accelerate--+-on--+-----------+-----+--'-><
      | '-session--session_id-'      |           .-on--.             |     |      
      |                              +-fallback--+-off-+-------------+     |      
      |                              |        .-stop--.              |     |      
      |                              +-probe--+-start-+--------------+     |      
      |                              |        .-off-.                |     |      
      |                              +-debug--+-on--+----------------+     |      
      |                              +-debug file--+-+-----------+-+-+     |      
      |                              |               '-file_name-'   |     |      
      |                              |              .-on------.      |     |      
      |                              +-uniquecheck--+-+-----+-+------+     |      
      |                              |                '-off-'        |     |  
      |                              '-loadpdq---resources-----------'     |     
      '-probe cleanup------------------------------------------------------'


Element	Description	Restrictions	Syntax
file_name	File path of the debug log file, overriding the default MSGPATH configuration setting.	This name is case-sensitive, and cannot include any special characters, such as the new line, blank space, or tab characters.	File path name
session_id	The unsigned integer identifier of a session	Must be the identifier of an existing session. Only user informix can issue the SET ENVIRONMENT USE_DWA SESSION session_id statement, which is not valid in sysdbopen( ) routines.	Literal Number
resources	The PDQPRIORITY setting for the database sessions that extract data from the database tables during a data mart load operation.	resources is a positive integer value between 0 and 100 (inclusive).	Literal Number

Usage

In SET ENVIRONMENT USE_DWA statements of SQL, the keywords SET ENVIRONMENT USE_DWA must be followed by a string argument that is delimited between single ( ' ) or double ( " ) quotation marks. The keywords of the USE_DWA option are not case-sensitive. Within that string, the first syntax token must be one of the following keywords, or ordered set of keywords, in some cases followed by a session identifier or by a file path:

SESSION session_id

Only user informix can include the SESSION session_id option in SET ENVIRONMENT USE_DWA statements to specify USE_DWA environment variable settings for a different session, as identified by the session_id number.

This option can change the USE_DWA settings for a session originating from an application that opens the connection to the database and does not close the connection. Besides requiring user informix, the SESSION option has these additional restrictions:

The SESSION session_id option is not valid in SET ENVIRONMENT USE_DWA statements that also include the PROBE CLEANUP option
The SESSION session_id option is not valid in SET ENVIRONMENT USE_DWA statements within the sysdbopen( ) session configuration routine.

ACCELERATE ON

Enables query acceleration, so that subsequent queries that match one of the accelerated query tables (AQTs) are sent to the accelerator server for processing.

ACCELERATE OFF

Disables query acceleration, so that subsequent queries are not sent to the accelerator server, even if the queries meet the required AQT criteria.

DEBUG ON

Turns on debugging. This option has an effect during workload analysis and during query acceleration, when either the ACCELERATE ON or the PROBE START option of the SET ENVIRONMENT USE_DWA statement is already in effect for the current session. The default log file for query acceleration debugging is the online.log file, as specified by the MSGPATH configuration parameter.

DEBUG OFF

Turns off debugging.

DEBUG FILE

This option has an effect when either or both of the ACCELERATE ON or the PROBE START option of the SET ENVIRONMENT USE_DWA statement is already in effect.

DEBUG FILE file_name

Makes the specified file the log file for subsequent query acceleration debugging in the current session. This replaces the default online.log file, as specified by the MSGPATH configuration parameter, or replaces a nondefault log file from a previous SET ENVIRONMENT USE_DWA 'DEBUG FILE file_name' statement. This option has an effect when either the ACCELERATE ON or the PROBE START option of the SET ENVIRONMENT USE_DWA statement is already in effect, and the path and the file_name include no special characters.

FALLBACK ON

If Informix® Warehouse Accelerator cannot accelerate subsequent queries, the queries are processed by the Informix® database server. For example, the accelerator server is offline, or the queries do not match one of the accelerated query tables (AQTs). This option has an effect during query acceleration, when the ACCELERATE ON option of the SET ENVIRONMENT USE_DWA statement is already in effect.

FALLBACK OFF

If Informix® Warehouse Accelerator cannot accelerate subsequent queries, the queries are not processed by the Informix® database server. This option has an effect when the ACCELERATE ON option of the SET ENVIRONMENT USE_DWA statement is already in effect.

PROBE START

Activates query probing. Query probing is gathering information about query workload. Query probing is used in workload analysis to create a data mart definition.

PROBE STOP

Deactivates query probing.

LOADPDQ resources

Controls the PDQPRIORITY setting for the database sessions that extract data from the database tables during a data mart load operation.

This setting is effective for full data mart load, partition load and refresh mart.

The PDQPRIORITY is used with the MAX_PDQPRIORITY configuration parameter to calculate the effective PDQ priority for the data extraction database sessions.

The default PDQPRIORITY for data extraction database sessions is 2.

PROBE CLEANUP

Removes from the database all probing data that any past and current query workload analysis produced in the database. The SESSION keyword option cannot be specified in the same SET ENVIRONMENT USE_DWA statement that includes the PROBE CLEANUP keywords.

UNIQUECHECK ON

Enables uniqueness checking for primary key columns and for unique constraint keys during the creation of a data mart. By default, uniqueness checking is enforced for data mart creation.

UNIQUECHECK OFF

Disables uniqueness checking This can be useful when creating a data mart that requires one-to-many references to a parent table in a database of a remote server instance, or one-to-many references to a parent table that the query specifies as a synonym for a view. In these contexts where the uniqueness of index key column values in the parent table cannot be validated, avoiding the uniqueness check can prevent failure-to-validate exceptions during data mart creation.

Important: For synonyms of tables in remote database server instances, and for views, information on the uniqueness of data values in key columns is not available while the data mart is being created. For these table objects, the UNIQUECHECK OFF setting enables you to create one-to-many references where the parent table is a synonym that references a remote table or a view. Because this check is skipped, the user is responsible for ensuring the uniqueness of data in the parent-key column(s) by other means. But if you create a one-to-many reference with nonunique data values in the key columns of the parent table, your accelerated queries will return incorrect results.

Examples of setting USE_DWA environment options

The following example turns on query acceleration for the current session.

SET ENVIRONMENT USE_DWA 'ACCELERATE ON';

As for all USE_DWA options, double ( " ) quotation-mark delimiters are also valid, as in this statement that the same effect of turning on query acceleration:

SET ENVIRONMENT USE_DWA "ACCELERATE ON";

The following example removes all the probing data that was previously collected for the current database.

SET ENVIRONMENT USE_DWA 'PROBE CLEANUP';

The following example turns on acceleration and turns off fallback. The queries are not processed by the Informix® database server. Queries that cannot be accelerated by Informix® Warehouse Accelerator will fail.

SET ENVIRONMENT USE_DWA 'ACCELERATE ON';
SET ENVIRONMENT USE_DWA 'FALLBACK OFF';

The following example activates the debug option. By default, the debug information is appended to the online.log file.

SET ENVIRONMENT USE_DWA 'DEBUG ON';

The following example creates probing data for your queries, turns on debugging, and appends the debugging information to a file named /tmp/my_debug_file.

SET ENVIRONMENT USE_DWA 'PROBE START'; 
SET ENVIRONMENT USE_DWA 'DEBUG ON'; 
SET ENVIRONMENT USE_DWA 'DEBUG FILE /tmp/my_debug_file';

The following example turns on debugging. The debug output of query 1 is written to the file /tmp/myDwaDebugFile. The debug output of query 2 is written to the default online.log file.

SET ENVIRONMENT USE_DWA 'DEBUG ON';
SET ENVIRONMENT USE_DWA 'DEBUG FILE /tmp/myDwaDebugFile';
SELECT ... { query 1 }
SET ENVIRONMENT USE_DWA 'DEBUG FILE';
SELECT ... { query 2 }

The following example turns on query acceleration for user session 64.

SET ENVIRONMENT USE_DWA 'SESSION 64 ACCELERATE ON';

This SESSION example of USE_DWA would not be valid in a sysdbopen( ) session configuration routine. The DBSA or user informix can issue the onstat -g ses command to display the session_id identifying numbers of all the currently running user sessions.

The following example deactivates query probing for session 32, and removes from the current database all probing data that past and current query workload analysis produced.

SET ENVIRONMENT USE_DWA "SESSION 32 PROBE";
SET ENVIRONMENT USE_DWA "PROBE CLEANUP";

The following example turns off validating the uniqueness of data values in key columns during data mart creation within session 64.

SET ENVIRONMENT USE_DWA 'SESSION 64 UNIQUECHECK OFF';

For more information about Informix® Warehouse Accelerator support for workload analysis, data marts, accelerated query tables, and installing and configuring the accelerator server, see the Informix® Warehouse Accelerator Administration Guide.