Using MQTs during Query optimization

 

Before using MQTs, consider your environment attributes.

To even consider using MQTs during optimization the following environmental attributes must be true:

• The query must specify ALWCPYDTA(*OPTMIZE) or INSENSITIVE cursor.

• The query must not be a SENSITIVE cursor.

• The table to be replaced with a MQT must not be update or delete capable for this query.

• The MQT currently has the ENABLE QUERY OPTIMIZATION attribute active

• The MATERIALIZED_QUERY_TABLE_USAGE QAQQINI option must be set to *ALL or *USER to enable use of MQTs. The default setting of MATERIALIZED_QUERY_TABLE_USAGE does not allow usage of MQTs.

• The timestamp of the last REFRESH TABLE for a MQT is within the duration specified by the MATERIALIZED_QUERY_TABLE_REFRESH_AGE QAQQINI option or *ANY is specified which allows MQTs to be considered regardless of the last REFRESH TABLE. The default setting of MATERIALIZED_QUERY_TABLE_REFRESH_AGE does not allow usage of MQTs.

• The query must be capable of being run through SQE.

• The following QAQQINI options must match: IGNORE_LIKE_REDUNDANT_SHIFTS, NORMALIZE_DATA, and VARIABLE_LENGTH_OPTIMIZATION. These options are stored at CREATE materialized query table time and must match the options specified at query run time.

• The commit level of the MQT must be greater than or equal to the query commit level. The commit level of the MQT is either specified in the MQT query using the WITH clause or it is defaulted to the commit level that the MQT was run under when it was created.

 

Parent topic:

Materialized query table optimization