Control queries dynamically with the query options file QAQQINI

 

The query options file QAQQINI support provides the ability to dynamically modify or override the environment in which queries are executed through the Change Query Attributes (CHGQRYA) command and the QAQQINI file. The query options file QAQQINI is used to set some attributes used by the database manager.

For each query that is run the query option values are retrieved from the QAQQINI file in the schema specified on the QRYOPTLIB parameter of the CHGQRYA CL command and used to optimize or implement the query.

Environmental attributes that you can modify through the QAQQINI file include:

• APPLY_REMOTE

• ASYNC_JOB_USAGE

• COMMITMENT_CONTROL_LOCK_LIMIT

• FORCE_JOIN_ORDER

• IGNORE_DERIVED_INDEX

• IGNORE_LIKE_REDUNDANT_SHIFTS

• LOB_LOCATOR_THRESHOLD

• MATERIALIZED_QUERY_TABLE_REFRESH_AGE

• MATERIALIZED_QUERY_TABLE _USAGE

• MESSAGES_DEBUG

• NORMALIZE_DATA

• OPEN_CURSOR_CLOSE_COUNT

• OPEN_CURSOR_THRESHOLD

• OPTIMIZE_STATISTIC_LIMITATION

• OPTIMIZATION_GOAL

• PARALLEL_DEGREE

• PARAMETER_MARKER_CONVERSION

• QUERY_TIME_LIMIT

• REOPTIMIZE_ACCESS_PLAN

• SQLSTANDARDS_MIXED_CONSTANT

• SQL_FAST_DELETE_ROW_COUNT

• SQL_STMT_COMPRESS_MAX

• SQL_SUPPRESS_WARNINGS

• SQL_TRANSLATE_ASCII_TO_JOB

• STAR_JOIN

• STORAGE_LIMIT

• SYSTEM_SQL_STATEMENT_CACHE

• UDF_TIME_OUT

• VARIABLE_LENGTH_OPTIMIZATION

• Specifying the QAQQINI file
  Use the Change Query Attributes (CHGQRYA) command with the QRYOPTLIB (query options library) parameter to specify which schema currently contains or will contain the query options file QAQQINI.

• Creating the QAQQINI query options file
  Each server is shipped with a QAQQINI template file in schema QSYS. The QAQQINI file in QSYS is to be used as a template when creating all user specified QAQQINI files.

• QAQQINI query options file format
  The QAQQINI file is shipped in the schema QSYS. It has a predefined format and has been pre-populated with the default values for the rows.

• Setting the options within the query options file
  The QAQQINI file query options can be modified with the INSERT, UPDATE, or DELETE SQL statements.

• QAQQINI query options file authority requirements
  QAQQINI is shipped with a *PUBLIC *USE authority. This allows users to view the query options file, but not change it. Because changing the values of the QAQQINI file affect all queries that are run on the system, only the system or database administrator should have *CHANGE authority to the QAQQINI query options file.

• QAQQINI file system supplied triggers
  The query options file QAQQINI file uses a system-supplied trigger program in order to process any changes made to the file. A trigger cannot be removed from or added to the file QAQQINI.

• QAQQINI query options
  There are different options available for parameters in the QAQQINI file.

 

Parent topic:

Change the attributes of your queries with the Change Query Attributes (CHGQRYA) command

 

Related reference

Look ahead predicate generation (LPG)