QAQQINI query options

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

The following table summarizes the query options that can be specified on the QAQQINI command:

Table 1. Query Options Specified on QAQQINI Command
Parameter Value Description
ACTIVE_JOBS

This option specifies what should be used for active jobs during optimization. The number of active jobs is used in determining the optimizer fair share of memory.

*DEFAULT Average active jobs will be computed based on system information. This is the recommended setting.
Integer value (1 :: 8192)

The number of active jobs that will be used by the query optimizer to determine fair share. Specify a value between 1 and 8192. Specifying a value other than default means that you may not get all of the performance benefits from the optimizer determining a plan using the memory fair share based on actual system usage. Usage of this setting is intended primarily for debug or modeling query plans based on an optimizer fair share.

ALLOW_ADAPTIVE_QUERY_PROCESSING

Specifies whether Adaptive Query Processing (AQP) processing is done for a query.

Adaptive query processing uses runtime statistics to look for poor performing queries and potentially replace the poor plan with an improved plan.

*DEFAULT The default value is set to *YES.
*YES Allows Adaptive query processing to occur for this query.

The existing QAQQINI options that affect AQP are the following:

  • If the REOPTIMIZE_ACCESS_PLAN QAQQINI option is set to *ONLY_REQUIRED, AQP does not reoptimize the original plan. *ONLY_REQUIRED indicates the user does not want the query reoptimized unless there is a functional reason to do so. *ONLY_REQUIRED takes precedence over AQP.
  • Join order requirements specified by the user in the FORCE_JOIN_ORDER QAQQINI option take precedence over AQP. If the user specifies the primary table in the join order, any AQP primary recommendations will be placed after the primary table if they are different.
*NO Adaptive query processing cannot be used for this query.

ALLOW_ARRAY_VALUE_CHANGES

Specifies whether changes to the values of array elements are visible to the query while the query is running.

*DEFAULT The default value is set to *NO.
*NO Do not allow changes to values in arrays referenced in the query to be visible after the query is opened.

All values which could be referenced in a query are copied during query open processing. Any changes to values in arrays after the query is opened are not visible.

Produces queries with predictable and reproducible results, but might have a performance penalty when working with large arrays or large array elements. The penalty is less if all the references to arrays are simple non-column values, for example, :ARRAY[1] or :ARRAY[:hv2].

Use of column values from a table to index the ARRAY, or using the UNNEST() function results in copies of the entire array being made. These copies have the largest performance penalty.

*YES Allow changes to values in arrays to be visible to the query while the query is running. The arrays are not copied during the open processing of the query. If the array values are changed during the processing of queries, the results of the query might be unpredictable.

Performance might be improved for queries which reference large arrays in complex array index lookup operations, such as :Array[column-name], or when using UNNEST. Large arrays include arrays that have thousands of elements, or elements with a large size. Array index lookups using simple index values, such as :ARRAY[1] or :ARRAY[:hv2], see minimal performance improvements.

Performance of some queries might be negatively impacted. For example, later queries that could reuse the results if they were cached to avoid recalculation where the cached result is applicable.

Procedures that can run with *YES and still expect predictable results have the following characteristics:

  1. Contain no cursor declarations.
  2. Receive arrays as input parameters:
    • and do not contain SET statements which reference arrays on the left side of the SET, and
    • and have no SQL statements with INTO clauses referencing arrays.
  3. Do not contain SET statements which reference arrays on the left side of the set:
    • and have no SQL statements with INTO clauses referencing arrays while a cursor is open for a query which references an array.
ALLOW_DDL_CHANGES_WHILE_OPEN

Specifies whether certain DDL operations against a database file are allowed while another user has the database file (or table) open or a logical file (or view) over a physical file open in another job.

*DEFAULT The default is set to *NO.
*YES The following DDL operations are supported:
  • Trigger operations
    • SQL CREATE TRIGGER, ALTER TRIGGER, and DROP TRIGGER

      Any open cursors in concurrent jobs that have open cursors over the target table will continue to operate as if the operation was not performed until the cursor is closed.

    • SQL COMMENT ON TRIGGER and LABEL ON TRIGGER statements

      There is no effect on concurrent jobs.

    • CL ADDPFTRG, RMVPFTRG, and CHGPFTRG commands

      Any open cursors in concurrent jobs that have open cursors over the target table will continue to operate as if the operation was not performed until the cursor is closed.

    This option is ignored for INSTEAD OF triggers and READ triggers.
  • Start of changeGrant and revoke operations
    • SQL GRANT and REVOKE for database files

      Any fully open cursors in concurrent jobs that have open cursors over the target database file will continue to operate as if the operation was not performed until the cursor is closed. If this is not a grant of UPDATE, DELETE, or INSERT, pseudo-closed cursors in other jobs will not be closed. Otherwise, all pseudo-closed cursors will be fully closed.

    • CRTOBJAUT and RVKOBJAUT CL commands for database files

      Any fully open cursors in concurrent jobs that have open cursors over the target database file will continue to operate as if the operation was not performed until the cursor is closed. If this is not a grant of *UPD, *DLT, *ADD, or *EXCLUDE, pseudo-closed cursors in other jobs will not be closed. Otherwise, all pseudo-closed cursors will be fully closed.

    End of change
*NO SQL DDL operations can fail to complete, with an SQL0913 error, if an exclusive lock cannot be acquired for the target object.
ALLOW_EVI_ONLY_ACCESS

Specifies whether encoded vector index RRN probe can be considered by the optimizer.

*DEFAULT The default is set to *YES.
*YES Specifies whether encoded vector index RRN probe can be considered by the optimizer to replace table accesses. An EVI must exist for every column being accessed in the table.
*NO Encoded vector index RRN probes cannot replace table access.

ALLOW_TEMPORARY_ INDEXES

Specifies whether temporary indexes can be considered by the optimizer. If temporary indexes are not allowed, then any other viable plan is chosen regardless of cost to implement this query.

*DEFAULT The default value is set to *YES.
*YES Allow temporary indexes to be considered.
*ONLY_ REQUIRED Do not allow any temporary indexes to be considered for this access plan. Choose any other implementation regardless of cost to avoid the creation of a temporary index. Only if no viable plan can be found, is a temporary index allowed.

APPLY_REMOTE

Specifies for database queries involving distributed files, whether the CHGQRYA query attributes are applied to the jobs on the remote systems associated with this job.

*DEFAULT The default value is set to *YES.
*NO The CHGQRYA attributes for the job are not applied to the remote jobs. The remote jobs use the attributes associated to them on their systems.
*YES The query attributes for the job are applied to the remote jobs used in processing database queries involving distributed tables. For attributes where *SYSVAL is specified, the system value on the remote system is used for the remote job. This option requires that, if CHGQRYA was used for this job, the remote jobs must have authority to use the CHGQRYA command.

ASYNC_JOB_USAGE

Specifies the circumstances in which asynchronous (temp writer) jobs can be used to help process database queries in the job. The option determines which types of database queries can be used in asynchronous jobs (running in parallel) to help complete the query.

An asynchronous job is a separate job that handles query requests from jobs running the database queries on the system. The asynchronous job processes each request and puts the results into a temporary file. This intermediate temporary file is then used by the main job to complete the database query.

The advantage of an asynchronous job is that it processes its request at the same time (in parallel) that the main job processes another query step. The disadvantage of using an asynchronous job is that it might encounter a situation that it cannot handle in the same way as the main job. For example, the asynchronous job might receive an inquiry message from which it cancels, whereas the main job can choose to ignore the message and continue.

There are two different types of database queries that can run asynchronous jobs:

  1. Distributed queries. These are database queries that involve distributed files. Distributed files are provided through the system feature DB2® Multi-System for IBM® i.
  2. Local queries. there are database queries that involve only files local to the system where the database queries are being run.
*DEFAULT The default value is set to *LOCAL.
*LOCAL Asynchronous jobs might be used for database queries that involve only tables local to the system where the database queries are being run.

In addition, this option allows the communications required for queries involving distributed tables to be asynchronous. Each system involved in the query of the distributed tables can run its portion of the query at the same time (in parallel).

*DIST Asynchronous jobs might be used for database queries that involve distributed tables.
*ANY Asynchronous jobs might be used for any database query.
*NONE No asynchronous jobs are allowed to be used for database query processing. In addition, all processing for queries involving distributed tables occurs synchronously. Therefore, no intersystem parallel processing occurs.

CACHE_RESULTS

Specifies a way for the user to control the cache results processing. For queries involving temporary results, for example, sorts or hashes, the database manager often saves the results across query pseudo-close or pseudo-open. The results are saved as long as they are not large, with the hope that they can be reused for the next run of the query. Beginning in V5R3, the database manager saves these temporary results even when a job is finished with them. The database manager assumes that another job can later reuse the results.

The database manager automatically controls the caching of these results, removing cache results as storage usage becomes large. However, the amount of temporary storage used by the database can be noticeably more than in previous releases.

*DEFAULT The default value is the same as *SYSTEM.
*SYSTEM The database manager might cache a query result set. A subsequent run of the query by the same job can reuse the cached result set. Or, if the ODP for the query has been deleted, any job can reuse the cached result set.

In many cases, this option works well. However, you need to consider if the query is doing work outside of the database manager which could affect temporary results. In that case, *JOB or *NONE may be a more appropriate setting. For example, if field procedures that mask data are used or swapping of user profiles in a UDF can occur, this option should specify *NONE.

*JOB The database manager might cache a query result set from one run to the next for a job. Caching can occur as long as the query uses a reusable ODP. When the reusable ODP is deleted, the cached result set is destroyed. This value mimics V5R2 processing.
*NONE The database does not cache any query results.

COLLATE_ERRORS

Specifies how data errors are handled on the GROUP BY and ORDER BY expression during hash or sort processing within queries.

*DEFAULT The default value is *NO.
*NO A value of *NO causes the query to be ended with an error when a grouping or ordering expressions results in an error.
*YES A value of *YES indicates that the grouping or sort continues.

COMMITMENT_CONTROL_LOCK_LIMIT

Specifies the maximum number of records that can be locked to a commit transaction initiated after setting the new value.

The value specified for COMMITMENT_CONTROL_LOCK_LIMIT does not affect transactions running in jobs that have already started commitment control. For the value to be effective, it must be changed before starting commitment control.

*DEFAULT *DEFAULT is equivalent to 500,000,000.

If multiple journals are involved in the transaction, the COMMITMENT_CONTROL_LOCK_LIMIT applies to each journal, not to the transaction as a whole.

For example, files F1 to F5 are journaled to journal J1, and files F6 to F10 are journaled to J2. The COMMITMENT_CONTROL_LOCK_LIMIT is set to 100,000. 100,000 record locks can be acquired for files F1 to F5. 100,000 more locks can be acquired for files F6 to F10.

Integer Value The maximum number of records that can be locked to a commit transaction initiated after setting the new value.

The valid integer value is 1–500,000,000.

CONCURRENT_ACCESS_BEHAVIOR

Controls how queries with an isolation level of Cursor Stability (CS) or Read Stability (RS) interact with uncommitted table changes.

*DEFAULT The default value is *OPTIMIZE
*OPTIMIZE

Uncommitted changes that delete or update records so that they are no longer selected by the query will not be considered as candidates for query synchronization.

*STRICTSCAN

All records referenced by a table scan query access plan will synchronize with any changes that are not yet committed. Serialization behavior depends on the concurrent access resolution used by the query, for example, SKIP LOCKED DATA, USE CURRENTLY COMMITTED, or WAIT FOR OUTCOME (default). Since the table scan attempts to serialize with any pending transactions for deleted and non-selected records, query performance will be reduced, as compared to *OPTIMIZE. Queries may contain many access plan types, but this option is only supported for table scan access. All other plan types will use *OPTIMIZE behavior.

DETERMINISTIC_UDF_SCOPE

Specifies the scope or lifetime of the deterministic setting for User Defined Functions (UDFs) and User Defined Table Functions (UDTFs).

It is recommended that you specify STATEMENT DETERMINISTIC on any CREATE FUNCTION statement that should be considered deterministic for a single instance of a query open rather than using the *OPEN option. DETERMINISTIC_UDF_SCOPE applies to all deterministic UDFs and UDTFs in every query while this QAQQINI option is in effect.

*DEFAULT The default value is *ALWAYS.
*ALWAYS The UDF is always considered deterministic. Query temporary objects might be shared across query opens and the UDF might not run for a particular query open.
*OPEN The UDF is considered deterministic only for a single instance of a query open. Query temporary objects are not shared across query open. The UDF is run at least once in the query for a given set of input parameters.

FIELDPROC_ENCODED_COMPARISON

Specifies the amount of optimization that the optimizer might use when queried columns have attached field procedures

*DEFAULT The default value is *ALLOW_EQUAL.
*NONE No optimization to remove field procedure decode option 4 or transformations to optimize field procedure invocations is allowed. For example, the optimizer cannot transform fieldProc(4, column) = ‘literal' to column = fieldProc(0, ‘literal'). This option is used when the field procedure is not deterministic.
*ALLOW_
EQUAL
Optimization allowed for equal and not equal predicates, GROUP BY, and DISTINCT processing. For example, the optimizer might choose to change the predicate fieldProc(4, column) = ‘literal' to column = fieldProc(0, ‘literal') in order to facilitate index matching. This option is useful when the field procedure is deterministic but no ordering can be determined based on the result of the field encoding.
*ALLOW_
RANGE
Transformation allowed for MIN, MAX grouping functions, ORDER BY, and all predicates except LIKE in addition to the transformations supported by *ALLOW_EQUAL. This option is useful when the field procedure is deterministic and the encoded value implies ordering
*ALL Transformation allowed for all predicates including LIKE, in addition to the transformations supported by *ALLOW_RANGE.

FORCE_JOIN_ORDER

Specifies to the query optimizer that the join of files is to occur in the order specified in the query.

*DEFAULT The default is set to *NO.
*NO Allow the optimizer to reorder join tables.
*SQL Only force the join order for those queries that use the SQL JOIN syntax. This option mimics the behavior for the optimizer before V4R4M0.
*PRIMARY
nnn
Only force the join position for the file listed by the numeric value nnn into the primary position (or dial) for the join. nnn is optional and defaults to 1. The optimizer then determines the join order for all the remaining files based upon cost.
*YES Do not allow the query optimizer to specify the order of join tables as part of its optimization process. The join occurs in the order in which the tables were specified in the query.

IGNORE_LIKE_ REDUNDANT_SHIFTS

Specifies whether redundant shift characters are ignored for DBCS-Open operands when processing the SQL LIKE predicate or OPNQRYF command %WLDCRD built-in function.

*DEFAULT The default value is set to *OPTIMIZE.
*ALWAYS When processing the SQL LIKE predicate or OPNQRYF command %WLDCRD built-in function, redundant shift characters are ignored for DBCS-Open operands. The optimizer cannot use an index to perform key row positioning for SQL LIKE or OPNQRYF %WLDCRD predicates involving DBCS-Open, DBCS-Either, or DBCS-Only operands.
*OPTIMIZE When processing the SQL LIKE predicate or the OPNQRYF command %WLDCRD built-in function, redundant shift characters might be ignored for DBCS-Open operands. These characters are ignored depending on whether an index is used to perform key row positioning for these predicates. This option enables the query optimizer to consider key row positioning for SQL LIKE or OPNQRYF %WLDCRD predicates involving DBCS-Open, DBCS-Either, or DBCS-Only operands.
KEY_RANGE_ESTIMATE_TIMEOUT

Specifies the amount of time the query optimizer may use for any individual key range estimate operation. Key range estimates are used with indexes to approximate the number of rows for a given predicate. This option may help to reduce the time spent waiting for some queries to complete optimization.

*DEFAULT The default value is *OPTIMIZE.
*OPTIMIZE The amount of time used for key range estimate operations is determined by the query optimizer.
*NONE No time limit is specified for key range estimate operations, and every estimate will run to completion regardless of the time required. This is the behavior of the query optimizer in releases before IBM i 7.3.
Integer Value

The number of seconds a key range estimate operation may execute before returning an estimate to the query optimizer. At the end of this time interval, the optimizer will continue optimizing the query, and the estimate operation will continue in a background process. A smaller value may reduce optimization time but may also cause less accurate estimates to be used by the optimizer.

Valid values are 1-86400.

LIMIT_PREDICATE_ OPTIMIZATION

Specifies that the query optimizer can only use simple isolatable predicates (OIF) when performing its index optimization.

An OIF is a predicate that can eliminate a record without further evaluation. Any predicate that cannot be classified as an OIF is ignored by the optimizer and needs to be evaluated as a non-key selection predicate.

A=10 and (A => 10 AND B=9) are OIFs.

A=10 OR B=9 are not OIFs.

Note: *YES impairs or limits index optimization.

*DEFAULT Do not eliminate the predicates that are not simple isolatable predicates (OIF) when doing index optimization. Same as *NO.
*NO Do not eliminate the predicates that are not simple isolatable predicates (OIF) when doing index optimization.
*YES Eliminate the predicates that are not simple isolatable predicates (OIF) when doing index optimization.

LOB_LOCATOR_THRESHOLD

Specifies either *DEFAULT or an Integer Value -- the threshold to free eligible LOB locators that exist within the job.

*DEFAULT The default value is set to 0. This option indicates that the database does not free locators.
Integer Value If the value is 0, then the database does not free locators. For values 1 through 250,000, on a FETCH request, the database compares the SQL current LOB locator count for the job against the threshold value. If the locator count is greater than or equal to the threshold, the database frees host server created locators that have been retrieved. This option applies to all host server jobs (QZDASOINIT) and has no impact to other jobs.

MATERIALIZED_QUERY_ TABLE_REFRESH_AGE

Specifies the ability to examine which materialized query tables are eligible to be used based on the last time a REFRESH TABLE statement was run.

*DEFAULT The default value is set to 0.
0 No materialized query tables can be used.
*ANY Any tables indicated by the MATERIALIZED_ QUERY_TABLE_USAGE INI parameter can be used.
Timestamp_
duration
Only tables indicated by MATERIALIZED_ QUERY_TABLE_USAGE INI option which have a REFRESH TABLE performed within the specified timestamp duration can be used.

MATERIALIZED_QUERY_ TABLE_USAGE

Specifies the usage of materialized query tables in query optimization and runtime.

*DEFAULT The default value is set to *NONE.
*NONE Materialized query tables cannot be used in query optimization and implementation.
*ALL User-maintained materialized query tables may be used.
*USER User-maintained materialized query tables can be used.

MEMORY_POOL_PREFERENCE

Specifies the preferred memory pool that database operations uses. This option does not guarantee use of the specified pool, but directs database to perform its paging into this pool when supported by the database operation.

*DEFAULT The default value is set to *JOB.
*JOB Paging is done in the pool of the job. This option is normal paging behavior.
*BASE Attempt to page storage into the base pool when paging is needed and a database operation that supports targeted paging occurs.
nn Attempt to page storage into pool nn when paging is needed and a database operation that supports targeted paging occurs.
*NAME PoolName Attempt to page storage into a named storage pool when paging is needed and a database operation that supports targeted paging occurs.
*PRIVATE Library/Subsystem/PoolNumber Attempt to page storage into a private storage pool in specified library and subsystem when paging is needed and a database operation that supports targeted paging occurs.

MESSAGES_DEBUG

Specifies whether Query Optimizer debug messages are displayed to the job log. These messages are regularly issued when the job is in debug mode.

*DEFAULT The default is set to *NO.
*NO No debug messages are to be displayed.
*YES Issue all debug messages that are generated for STRDBG.

NORMALIZE_DATA

Specifies whether normalization is performed on Unicode constants, host variables, parameter markers, and expressions that combine strings.

*DEFAULT The default is set to *NO.
*NO Unicode constants, host variables, parameter markers, and expressions that combine strings is not normalized.
*YES Unicode constants, host variables, parameter markers, and expressions that combine strings is normalized

OPEN_CURSOR_CLOSE_ COUNT

Specifies either *DEFAULT or an Integer Value: the number of cursors to full close when the threshold is encountered.

*DEFAULT *DEFAULT is equivalent to 0. See Integer Value for details.
Integer Value

This value determines the number of cursors to be closed. The valid values for this parameter are 1 - 65536. The value for this parameter is less than or equal to the number in the OPEN_CURSOR_THREHOLD parameter.

If the number of open cursors reaches the value specified by the OPEN_CURSOR_THRESHOLD, pseudo-closed cursors are hard (fully) closed. The least recently used cursors are closed first.

This value is ignored if OPEN_CURSOR_THRESHOLD is *DEFAULT. If OPEN_CURSOR_THRESHOLD is specified and the value is *DEFAULT, the number of cursors closed is equal to OPEN_CURSOR_THRESHOLD multiplied by 10 percent. The result is rounded up to the next integer value.

OPEN_CURSOR_CLOSE_COUNT is used with OPEN_CURSOR_THRESHOLD to manage the number of open cursors within a job. Open cursors include pseudo-closed cursors.

OPEN_CURSOR_ THRESHOLD

Specifies either *DEFAULT or an Integer Value -- the threshold to start full close of pseudo-closed cursors.

*DEFAULT *DEFAULT is equivalent to 0. See Integer Value for details.
Integer Value

This value determines the threshold to start full close of pseudo-closed cursors. When the number of open cursors reaches this threshold value, pseudo-closed cursors are hard (fully) closed with the least recently used cursors being closed first. The number of cursors to be closed is determined by OPEN_CURSOR_CLOSE_COUNT.

The valid user-entered values for this parameter are 1 - 65536. A default value of 0 indicates that there is no threshold. Hard closes are not forced based on the number of open cursors within a job.

OPEN_CURSOR_THRESHOLD is used with OPEN_CURSOR_CLOSE_COUNT to manage the number of open cursors within a job. Open cursors include pseudo-closed cursors.

OPTIMIZATION_GOAL

Specifies the goal that the query optimizer uses when making costing decisions.

*DEFAULT Optimization goal is determined by the interface (ODBC, SQL precompiler options, OPTIMIZE FOR nnn ROWS clause).
*FIRSTIO All queries are optimized with the goal of returning the first page of output as fast as possible. This option works well when the output is controlled by a user likely to cancel the query after viewing the first page of data. Queries coded with OPTIMIZE FOR nnn ROWS honor the goal specified by the clause.
*ALLIO All queries are optimized with the goal of running the entire query to completion in the shortest amount of elapsed time. This option is better when the output of a query is written to a file or report, or the interface is queuing the output data. Queries coded with OPTIMIZE FOR nnn ROWS honor the goal specified by the clause.

OPTIMIZE_STATISTIC_ LIMITATION

Specifies limitations on the statistics gathering phase of the query optimizer.

One of the most time consuming aspects of query optimization is in gathering statistics from indexes associated with the queried tables. Generally, the larger the size of the tables involved in the query, the longer the gathering phase of statistics takes.

This option provides the ability to limit the amount of resources spend during this phase of optimization. The more resources spent on statistics gathering, the more accurate (optimal) the optimization plan is.

*DEFAULT The amount of time spent in gathering index statistics is determined by the query optimizer.
*NO No index statistics are gathered by the query optimizer. Default statistics are used for optimization. (Use this option sparingly.)
*PERCENTAGE
integer value
Specifies the maximum percentage of the index that is searched while gathering statistics. Valid values for are 1 - 99.
*MAX_
NUMBER_OF_
RECORDS_
ALLOWED
integer value
Specifies the largest table size, in number of rows, for which gathering statistics is allowed. For tables with more rows than the specified value, the optimizer does not gather statistics and uses default values.

PARALLEL_DEGREE

Specifies the parallel processing option that can be used when running database queries and database file keyed access path builds, rebuilds, and maintenance in the job. The specified parallel processing option determines the types of parallel processing allowed. There are two types of parallel processing:
  1. Input/Output (I/O) parallel processing. With I/O parallel processing, the database manager uses multiple tasks for each query to do the I/O processing. The central processor unit (CPU) processing is still done serially.
  2. Symmetric Multiprocessing (SMP). SMP assigns both CPU and I/O processing to tasks that run the query in parallel. Actual CPU parallelism requires a system with multiple processors. SMP can only be used if the system feature, Db2 Symmetric Multiprocessing, is installed. Use of SMP parallelism can affect the order in which records are returned.
*DEFAULT The default value is *SYSVAL.
*SYSVAL Set to the current system value QQRYDEGREE.
*IO Any number of tasks can be used. SMP parallel processing is not allowed.
*OPTIMIZE Any number of tasks for:
  • I/O or SMP parallel processing of the query
  • database file keyed access path build, rebuild, or maintenance.
SMP parallel processing is used only if the system feature, Db2 Symmetric Multiprocessing for IBM i, is installed.

Use of parallel processing and the number of tasks used is determined by:

  • the number of processors available in the system
  • the job share of the amount of active memory available in the pool in which the job is run
  • whether the expected elapsed time for the query or database file keyed access path build or rebuild is limited by CPU processing or I/O resources.

The query optimizer chooses an implementation that minimizes elapsed time based on the job share of the memory in the pool.

*OPTIMIZE
nnn
Like *OPTIMIZE, with the value nnn indicating a percentage from 1 to 200, used to influence the number of tasks. If not specified, 100 is used.

The query optimizer determines the parallel degree for the query using the same processing as is done for *OPTIMIZE. Once determined, the optimizer adjusts the actual parallel degree used for the query by the percentage given.

Allows the user to override the parallel degree used without having to specify a particular parallel degree under *NUMBER_OF_TASKS.

nnn The query optimizer chooses to use either I/O or SMP parallel processing to process the query. SMP parallel processing is used only if the system feature, Db2 Symmetric Multiprocessing for IBM i, is installed.

nnn is a percentage from 1 to 200 and is used to influence the number of tasks. If not specified, 100 is used.

The choices made by the query optimizer are like those choices made for parameter value *OPTIMIZE. The exception is the assumption that all pool active memory can be used for query processing, database file keyed access path build, rebuild, or maintenance.

PARALLEL_DEGREE (continued) *NONE No parallel processing is allowed for database query processing or database table index build, rebuild, or maintenance.
*NUMBER_
OF _TASKS
nnn
Indicates the maximum number of tasks that can be used for a single query. The number of tasks is limited to either this value or the number of disk arms associated with the table.

Not recommended if running SQE. The SQE optimizer attempts to use this degree and override many of the normal costing mechanisms. For SQE, use *OPTIMIZE with a percentage.

*MAX xxx Like *MAX, with the value xxx indicating the ability to specify an integer percentage value 1 - 200. The query optimizer determines the parallel degree for the query using the same processing as is done for *MAX. Once determined, the optimizer adjusts the actual parallel degree used for the query by the percentage given. This option provides the user the ability to override the parallel degree used to some extent without having to specify a particular parallel degree under *NUMBER_OF_TASKS.

PARAMETER_MARKER_ CONVERSION

Specifies whether to allow literals to be implemented as parameter markers in dynamic SQL queries.

*DEFAULT The default value is set to *YES.
*NO Constants cannot be implemented as parameter markers.
*YES Constants can be implemented as parameter markers.

PREVENT_ADDITIONAL_CONFLICTING_LOCKS

The following SQL DDL statements require an exclusive, no read lock on the target table. If the application activity cannot be quiesced, it can be hard to accomplish these operations.

The PREVENT_ADDITIONAL_CONFLICTING_LOCKS QAQQINI option provides a control for customers to use to direct the operating system to favor a request for an exclusive, no read lock over new requests to lock the object for reading.

*DEFAULT The default value is set to *NO
*NO

When a job requests an exclusive lock on an object, do not prevent concurrent jobs from acquiring additional locks on the object.

*YES

When *YES is chosen, any new requests for these lower-level read locks will be kept behind the exclusive lock request and could surface to applications as the table is unavailable for use for querying.

  • ALTER TABLE (Add, Alter or Drop Column)
  • CREATE TRIGGER
  • LOCK TABLE
  • RENAME TABLE

PSEUDO_OPEN_CHECK_HOST_VARS

This option can be used to allow SQE to check the selectivity of the host variable values at pseudo open time. If the new set of host variable values require a different plan to perform well, SQE will re-optimize the query.

This option is most appropriate when there is considerable variability in the selectivity of host variable in the queries predicates.

*DEFAULT The default value is set to *NO
*NO The optimizer does not check host variables for selectivity changes once in pseudo-open.
*OPTIMIZE The optimizer will determine when a host variable selectivity should be checked. In general, the optimizer will monitor the query and if after a certain number of runs it determines that there is no advantage to checking host variable selectivity at pseudo open time, it will stop checking. Full opens do normal plan validation.
*YES The optimizer will always check host variable selectivity at pseudo open time.
Note: If the REOPTIMIZE_ACCESS_PLAN INI option is set to *ONLY_REQUIRED then this INI option has no effect.

QUERY_TIME_LIMIT

Specifies a time limit for database queries allowed to be started based on the estimated number of elapsed seconds that the query requires to process.

*DEFAULT The default value is set to *SYSVAL.
*SYSVAL The query time limit for this job is obtained from the system value, QQRYTIMLMT.
*NOMAX There is no maximum number of estimated elapsed seconds.
integer value Specifies the maximum value that is checked against the estimated number of elapsed seconds required to run a query. If the estimated elapsed seconds are greater than this value, the query is not started. Valid values range from 0 to 2,147,352,578.

REOPTIMIZE_ACCESS_PLAN

Specifies whether the query optimizer reoptimizes a query with a saved access plan.

Queries can have a saved access plan stored in the associated storage of an HLL program, or in the plan cache managed by the optimizer itself.

Note: If you specify *NO the query could still be revalidated.

Some of the reasons this option might be necessary are:

  • The queried file was deleted and recreated.
  • The query was restored to a different system than the one on which it was created.
  • An OVRDBF command was used.
*DEFAULT The default value is set to *NO.
*NO Do not force the existing query to be reoptimized. However, if the optimizer determines that optimization is necessary, the query is optimized.
*YES Force the existing query to be reoptimized.
*FORCE Force the existing query to be reoptimized.
*ONLY_
REQUIRED
Do not allow the plan to be reoptimized for any subjective reasons. For these cases, continue to use the existing plan since it is still a valid workable plan. This option could mean that you might not get all the performance benefits that a reoptimization plan might derive. Subjective reasons include file size changes, new indexes, and so on. Non-subjective reasons include deletion of an index used by existing access plan, query file being deleted and recreated, and so on.

SQE_NATIVE_ACCESS_POSITION_BEHAVIOR

This option controls the positioning behavior of native opens or queries implemented by SQE. By specifying an option other than *DEFAULT, performance benefits may be realized.

*DEFAULT Normal positioning behavior is performed.
*NO_ROLLBACK
_HOLD

The current cursor position is unchanged by a rollback.

*NO_KEY_
FAILURE_HOLD

If an attempted key positioning operation fails,the cursor position prior to the attempted operation will not be restored. It is assumed that another absolute positioning operation, such as first, last, or key equal, will be attempted before any relative positioning operations, such as next or previous.

*NO_HOLD

Behavior is the same as defined for *NO_ROLLBACK_HOLD and *NO_KEY_FAILURE_HOLD values.

SQLSTANDARDS_MIXED_ CONSTANT

Specifies whether to allow IGC constants to always be treated as IGC-OPEN in SQL queries.

Note: When *NO is specified, Db2 for i is not compatible with the other Db2 platforms.
*DEFAULT The default value is set to *YES.
*YES SQL IGC constants are treated as IGC-OPEN constants.
*NO If the data in the IGC constant only contains shift-out DBCS-data shift-in, then the constant are treated as IGC-ONLY, otherwise it is treated as IGC-OPEN.

SQL_CONCURRENT_ACCESS_RESOLUTION

Specifies the concurrent access resolution to use for an SQL query.

*DEFAULT The default value is set to *WAIT.
*WAIT The database manager must wait for the commit or rollback when encountering data in the process of being updated, deleted, or inserted. Rows encountered that are in the process of being inserted are not skipped. This option applies if possible when the isolation level in effect is Cursor Stability or Read Stability and is ignored otherwise.
*CURCMT The database manager can use the currently committed version of the data for read-only scans when it is in the process of being updated or deleted. Rows in the process of being inserted can be skipped. This option applies if possible when the isolation level in effect is Cursor Stability and is ignored otherwise.

SQL_DECFLOAT_WARNINGS

Specifies the warnings returned for SQL DECFLOAT computations and conversions involving:
  • division by 0.
  • overflow.
  • underflow.
  • an invalid operand.
  • an inexact result.
*DEFAULT The default value is set to *NO.
*YES A warning is returned to the caller for DECFLOAT computations and conversions involving division by 0, overflow, underflow, invalid operand, inexact result, or subnormal number.
*NO
An error or a mapping error is returned to the
caller for DECFLOAT computations and
conversions involving division by 0, overflow,
underflow, or an invalid operand.  
A warning or error is not returned for an inexact result or a subnormal number.

SQL_FAST_DELETE_ROW_COUNT

Specifies how the delete is implemented by the database manager. This value is used when processing a DELETE FROM table-name SQL statement without a WHERE clause.

*DEFAULT The default value is set to 0.

0 indicates that the database manager chooses how many rows to consider when determining whether fast delete could be used instead of traditional delete.

When using the default value, the database manager will most likely use 1000 as a row count. This means that using the INI option with a value of 1000 results in no operational difference from using 0 for the option.

*NONE This value forces the database manager to never attempt to fast delete on the rows.
*OPTIMIZE This value is same as using *DEFAULT.
Integer Value Specifying a value for this option allows the user to tune the behavior of DELETE. The target table for the DELETE statement must match or exceed the number of rows specified on the option for fast delete to be attempted. A fast delete does not write individual rows into a journal.

The valid values are 1 - 999,999,999,999,999.

SQL_GVAR_BUILD_RULE

Determines whether global variables must exist or not when building SQL routines or executing SQL pre-compiles.

This option has no affect on dynamic SQL statements.

*DEFAULT The default value is set to *DEFER
*DEFER Global variables do not need to exist when an SQL routine is created or the SQL pre-compiler is run. Since global variables are not required to exist, the create will not fail when an incorrect column name or routine variable is encountered. Incorrect name usage will result in SQL0206 - "Column or global variable &1 not found." failures when the statement is executed.
*EXIST Global variables referenced by SQL must exist when the SQL routine is created or the SQL pre-compiler is run. Using this option, an SQL0206 will be issued at create time.

SQL_MODIFIES_SQL_DATA

From the SQL Standard, no MODIFIES SQL DATA operations are allowed in an SQL BEFORE trigger.

The Informix® database allows MODIFIES SQL DATA operations in SQL BEFORE triggers. Setting the option to *YES allows SQL BEFORE triggers to perform the SQL MODIFIES SQL DATA operations.

*DEFAULT The default value is set to *NO.
*NO No MODIFIES SQL DATA operations are allowed in an SQL BEFORE trigger.
*YES MODIFIES SQL DATA operations are allowed in an SQL BEFORE trigger.

SQL_PSEUDO_CLOSE

Before V6R1: SQL cursor open processing checks for the presence of a data area named QSQPSCLS1 in the library list of the job. If the data area is found, all reusable cursors are marked as candidates for reuse. They are pseudo-closed the first time rather than the second time the application closes the cursor. Without this data area, a cursor does not become reusable until the second close.

Pseudo-closing the first time results in leaving some cursors open that might not be reused. These open cursors can increase the amount of auxiliary and main storage required for the application. The storage can be monitored using the WRKSYSSTS command. For the amount of auxiliary storage used, look at the "% system ASP used." For the amount of main storage, examine the faulting rates on the WRKSYSSTS display.

The format and the contents of the data area are not important. The data area can be deleted using the following command: DLTDTAARA DTAARA(QGPL/QSQPSCLS1).

The existence of the data area is checked during the first SQL open operation for each job. It is checked only once and the processing mode remains the same for the life of the job. Because the library list is used for the search, the change can be isolated to specific jobs. Create the data area in a library that is included only in the library lists for those jobs.

*DEFAULT

The default behavior depends upon whether the QSQPSCLS1 *DTAARA exists.

If the QSQPSCLS1 *DTAARA was found on the first OPEN within the job, then SQL cursors are marked as candidates for reuse. The cursors are pseudo-closed on the first close.

If the QSQPSCLS1 *DTAARA was not found on the first OPEN within the job, then SQL cursors are marked as candidates for reuse. The cursors are pseudo-closed on the second close.

Integer Value Specifies a value greater than zero that indicates when a cursor is pseudo-closed. The value of this option minus 1 indicates how many times the cursor is hard closed before being marked as candidate for pseudo-close. Valid values are 1 - 65535.

SQL_STMT_COMPRESS_MAX

Specifies the compression maximum setting, which is used when statements are prepared into a package.

*DEFAULT The default value is set to 2. The default indicates that the access plan associated with any statement will be removed after a statement has been compressed twice without being executed.
Integer Value The integer value represents the number of times that a statement is compressed before the access plan is removed to create more space in the package. Executing the SQL statement resets the count for that statement to 0. The valid Integer values are 1 - 255.

SQL_STMT_REUSE

Specifies the number of times the statement must be prepared in the same connection before the statement is stored in the SQL extended dynamic package. If the number of times the statement has been prepared in the same connection is less than the specified INI option, a temporary copy of the statement is used. Any other job preparing the statement does a complete prepare.

*DEFAULT The default value is 3. The statement is stored on the third prepare of the statement.
1::255 The number of times the statement must be prepared in the same connection before the statement is stored in the SQL package.

SQL_SUPPRESS_MASKED_DATA_DETECTION

*DEFAULT

The default value is set to *NO.

*YES

If masked data is being used to insert into or update a table, detection of this masked data will not be done and a SQ20478 with reason code 30 will not be sent.

*NO

If masked data is being used to insert into or update a table with activated column access control directly from an expression involving a column with an active column mask, detection of this masked data will be done and a SQ20478 with reason code 30 will be sent.

SQL_SUPPRESS_WARNINGS

For SQL statements, this parameter provides the ability to suppress SQL warnings.

*DEFAULT The default value is set to *NO.
*YES Examine the SQLCODE in the SQLCA after execution of a statement. If the SQLCODE is one of the listed warnings, then alter the SQLCA so that no warning is returned to the caller.

Set the SQLCODE to 0, the SQLSTATE to '00000' and SQLWARN to ' '.

Warnings:

  • SQL0030
  • SQL0335
  • Start of changeSQL0387End of change
  • SQL7909 (on a DROP PROCEDURE/ROUTINE/FUNCTION)
*NO Specifies that SQL warnings are returned to the caller.

SQL_TRANSLATE_ASCII_ TO_JOB

Specifies whether to translate SQL statement text on the application server (AS) according to the CCSID of the job. This option applies when using DRDA to connect to an IBM i as the AS where the application requestor (AR) machine is an ASCII-based platform.

*DEFAULT The default value is set to *NO.
*YES Translate ASCII SQL statement text to the CCSID of the IBM i job.
*NO Translate ASCII SQL statement text to the EBCIDIC CCSID associated with the ASCII CCSID.

SQL_XML_DATA_CCSID

Specifies the CCSID to be used for XML columns, host variables, parameter markers, and expressions, if not explicitly specified.

See SQL_XML_DATA_CCSID QAQQINI option

*DEFAULT The default value is set to 1208.
*JOB The job CCSID is used for XML columns, host variables, parameter markers, and expressions, if not explicitly specified. If the job CCSID is 65535, the default CCSID of 1208 is used.
Integer Value The CCSID used for XML columns, host variables, parameter markers, and expressions, if not explicitly specified. This value must be a valid single-byte or mixed EBCDIC CCSID or Unicode CCSID. The value cannot be 65535.

STAR_JOIN

Note: Only modifies the environment for the Classic Query Engine.

Specifies enhanced optimization for hash queries where both a hash join table and a Distinct List of values is constructed from the data. This Distinct List of values is appended to the selection against the primary table of the hash join

Any EVI indexes built over these foreign key columns can be used to perform bitmap selection against the table before matching the join values.

The use of this option does not guarantee that star join is chosen by the optimizer. It only allows the use of this technique if the optimizer has decided to implement the query by using a hash join.

*DEFAULT The default value is set to *NO
*NO The EVI Star Join optimization support is not enabled.
*COST Allow query optimization to cost the usage of EVI Star Join support.

The optimizer determines whether the Distinct List selection is used based on how much benefit can be derived from using that selection.

STORAGE_LIMIT

Specifies a temporary storage limit for database queries. If a query is expected to use more than the specified amount of storage, the query is not allowed to run. The value specified is in megabytes.

*DEFAULT The default value is set to *NOMAX.
*NOMAX Never stop a query from running because of storage concerns.
Integer Value The maximum amount of temporary storage in megabytes that can be used by a query. This value is checked against the estimated amount of temporary storage required to run the query as calculated by the query optimizer. If the estimated amount of temporary storage is greater than this value, the query is not started. Valid values range from 0 through 2147352578.
Start of change

SUPPRESS_INQUIRY_MESSAGES

Specifies that certain inquiry messages will not be issued.

End of change
Start of change*DEFAULTEnd of change Start of changeThe default value is set to *NO.End of change
*YES System inquiry message CPA32B2 will not be issued when altering a table.
*NO No system inquiry messages will be suppressed.

SYSTEM_SQL_STATEMENT_ CACHE

Specifies whether to disable the system-wide SQL Statement Cache for SQL queries.

*DEFAULT The default value is set to *YES.
*YES Examine the system-wide SQL Statement Cache when an SQL prepare request is processed. If a matching statement exists in the cache, use the results of that prepare. This option allows the application to potentially have better performing prepares.
*NO Specifies that the system-wide SQL Statement Cache is not examined when processing an SQL prepare request.
SYSTIME_PERIOD_ADJ

For update and delete operations involving system-period temporal tables, this option resolves conflict for a historical row when its row end value could be less than its row begin value.

*DEFAULT The default value is set to *ERROR.
*ERROR An SQ20528 error will be signaled.
*ADJUST
These three things will happen:
  1. The row end value of the history row will be set to the row begin value plus a small delta of one microsecond.
  2. The row begin value of the temporal table current row will also be set to the adjusted value.
  3. An SQL warning SQ20528 will be signaled.

TEXT_SEARCH_DEFAULT_TIMEZONE

Specifies the time zone to apply to any date or dateTime value specified in an XML text search using the CONTAINS or SCORE function. The time zone is the offset from UTC (Greenwich mean time. It is only applicable when a specific time zone is not given for the value.

*DEFAULT Use the default as defined by database. This option is equivalent to UTC.
sHH:MM A time zone formatted value where
  • s is the sign, + or –
  • HH is the hour
  • MM is the minute

The valid range for HH is 00 - 23. The valid range for MM is 00 - 59. The format is specific. All values are required, including sign. If HH or MM is less than 10, it must have a leading zero specified.

UDF_TIME_OUT

Note: Only modifies the environment for the Classic Query Engine.

Specifies the amount of time, in seconds, that the database waits for a User Defined Function (UDF) to finish processing.

*DEFAULT The amount of time to wait is determined by the database. The default is 30 seconds.
*MAX The maximum amount of time that the database waits for the UDF to finish.
integer value Specify the number of seconds that the database waits for a UDF to finish. If the value given exceeds the database maximum wait time, the maximum wait time is used by the database. Minimum value is 1 and maximum value is system defined.

VARIABLE_LENGTH_ OPTIMIZATION

Specifies whether aggressive optimization techniques are used on variable length columns.

*DEFAULT The default value is set to *YES.
*YES Enables aggressive optimization of variable-length columns, including index-only access. It also allows constant value substitution when an equal predicate is present against the columns. As a consequence, the length of the data returned for the variable-length column might not include any trailing blanks that existed in the original data. As a result, the application can receive the substituted value back instead of the original data. Function calls could operate on the substituted value instead of the original string value.
*NO Do not allow aggressive optimization of variable length columns.
Note: The following QAQQINI options will be ignored for SQE native query access. These options were previously honored for CQE native query access.
  • LIMIT_PREDICATE_OPTIMIZATION
  • STAR_JOIN
  • UDF_TIME_OUT
Note: The following QAQQINI options will be honored for SQE native query access. These options were previously ignored for CQE native query access.
  • DETERMINISTIC_UDF_SCOPE
  • FIELDPROC_ENCODED_COMPARISON
  • MATERIALIZED_QUERY_TABLE_USAGE
  • MATERIALIZED_QUERY_TABLE_REFRESH_AGE
  • MEMORY_POOL_PREFERENCE
  • VARIABLE_LENGTH_OPTIMIZATION