use_dwa session environment variable

Use the use_dwa session environment variable to control query acceleration and to set the database client environment for Informix® Warehouse Accelerator.

To set the use_dwa session environment variable, you can run SQL statements from the database client. Alternatively, you can add the SQL statements to the sysdbopen() stored procedure on the database server. Modify the sysdbopen() stored procedure only if you want the use_dwa settings to apply to all sessions. If you specify the use_dwa session environment variable in the sysdbopen() stored procedure, you do not need to modify your applications.

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------------------------------------------------------'

Table 1. Keywords and variables in the use_dwa environment variable
A three-column table that lists the syntax keywords and variables, and their purposes and restrictions.
Keyword and variable	Purpose	Restrictions
accelerate	Controls query acceleration. By default, queries are not accelerated. accelerate on: Turns on acceleration. Queries that match one of the accelerated query tables (AQTs) are sent to the accelerator server for processing. Use this setting to accelerate queries. accelerate off: Turns off acceleration. Queries are not sent to the accelerator server even if the queries meet the required criteria.
fallback	Controls what happens when Informix® Warehouse Accelerator cannot accelerate the queries. For example, the accelerator server is offline or the queries do not match one of the accelerated query tables (AQTs). The fallback on option is the default setting. fallback off: If Informix® Warehouse Accelerator cannot accelerate the queries, the queries are not sent to the Informix® database server for processing. Use the fallback off option when you do not want subsequent queries that are processed by the Informix® database server. fallback on: If Informix® Warehouse Accelerator cannot accelerate the queries, the queries are sent to the Informix® database server for processing.	The fallback option applies only if you set the accelerate on option.
probe	Activates query probing. Query probing is gathering information about a query workload. Query probing is used in workload analysis to create a data mart definition. The probe stop option is the default setting. probe start: Activates query probing. probe stop: Deactivates query probing.
debug	Activates debugging. The debug off option is the default setting. debug on: Turns on debugging. The default log file is the online.log file as specified by the MSGPATH configuration parameter. debug off: Turns off debugging.	The debug option applies only if you set the accelerate on option or the probe start option.
debug file debug file `file_name`	Sets a debug log file. Use the `file_name` option to specify a different log file than the default online.log file. The default log file is specified by the MSGPATH configuration parameter.	The debug on option is required for the debug file option to take effect. If you specify a file name, use a path and file name that does not have any special characters, such as new lines, spaces, or tabs. The value of `file_name` is case-sensitive. If you later specify the debug file option without the `file_name`, the debugging output destination resets to the default online.log file as specified by the MSGPATH configuration parameter.
uniquecheck	This parameter controls uniqueness checking during the creation of a data mart. The uniquecheck on is the default setting. You can use uniquecheck off to skip checking for the relevant index of parent table when creating a data mart. With uniquecheck off, you can create 1:n references where the parent table is a synonym that references a remote table or a view.	If you specify uniquecheck off, you must ensure the uniqueness of data in the parents key columns by other means. If you create a 1:n reference with non-unique data values in the key columns of the parent table, you will get incorrect query results for queries accelerated by Informix®.
session `session_id`	Sets the use_dwa environment variable settings for a different session as identified by the `session_id` number. Use this option to change the settings for a session that originates from an application that opens the connection to the database one time and does not close the connection.	Only user informix can specify the session option. The SESSION ID must be numeric and must identify an existing session. Important: Do not include the session `session_id` option in the sysdbopen() stored procedure.
loadpdq `resources`	This parameter controls the PDQPRIORITY setting for the database sessions that extract data from the database tables during a data mart load operation. The setting is effective for full data mart load, partition load and refresh mart. As with other database sessions, this PDQPRIORITY is then used together 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.	resources is a positive integer value between 0 and 100 (inclusive).
probe cleanup	The probe cleanup option removes all probing data that is produced by all past and current sessions in the database.

Usage

The SET ENVIRONMENT use_dwa statement takes one argument, which is a string value within single or double quotation marks. Keywords are not case-sensitive.

Examples

The following example turns on 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 sent to the Informix® database server for processing. 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 sets 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 text shows example debug output. This output shows that a query has matched an AQT and is being sent to Informix® Warehouse Accelerator for processing.

15:00:35  SQDWA: select MIN(s_suppkey) from partsupp x0 left join supplier x1 
on x0.ps_
15:00:35  SQDWA: Identified 1 candidate AQTs for matching
15:00:35  SQDWA: matched: aqt48a93f10-d192-404f-8911-e01d67aadde2
15:00:35  SQDWA: matching successful (0 msec) aqt48a93f10-d192-404f-8911-
e01d67aadde2
15:00:35  SQDWA: offloading successful (22 msec)