IBM i 7.2>About IBM i information>Memo to users>IBM i operating system>Database changes>Native database access behavior changes
Native database access behavior changes
SQL Query Engine (SQE) versus Classic Query Engine (CQE) behavior differences for native database access
As in previous releases, IBM® i 7.2 extends SQE as the default choice for optimization.
With 7.2, some native database access begins to use SQE by default, including:
• Work with Query (WRKQRY) command, when one of the run options is used
• Run Query (RUNQRY) command
• Open Query File (OPNQRYF) command
• Open Data Base File (OPNDBF) command or Native database I/O where the target of the open is an SQL view, or a partitioned table and a MBR(*ALL) override is being used unless the open is for *OUTPUT only
• Any open of a database file where a Row Permission or Column Mask is defined and enabled, unless the open is for *OUTPUT only
When moving from CQE to SQE, there are some behavioral differences. The two most noteworthy behavior differences are introduced here.
1. SQE implementation might result in a different result set ordering for WRKQRY, RUNQRY, or OPNQRYF. When a query is executed without explicitly specifying that the results be returned in a specific order, both the SQE or CQE optimizer choose whatever plan performs the best. This means that both SQE and CQE might or might not return the results in a keyed file order. Since CQE has far less advanced capability than SQE, it is more likely to return the results in a keyed order and SQE is less likely to return the results in a keyed order. Hence, if a query is specified with WRKQRY, RUNQRY, or OPNQRYF and the row ordering is important, explicitly specify the Key field(s), and Key field order.
2. Values for fields that are derived from an expression (for example, SUBSTR) that are null or in error might be different when rows are read through a native interface (for example, using RPG READ). When a row is read, CQE evaluates the expression using a default value for all fields that are null or in error. SQE follows the SQL standard and does not evaluate the expression using a default value for all fields that are null or in error. Both CQE and SQE return NULL indicators for fields that are null or in error, however. Applications that are checking the NULL indicators instead of relying on the value in the field will not observe a change in behavior when moving from CQE to SQE.
Note: For full details on other minor behavior differences, refer to this resource and search on SQE: Memorandum To Users (MTU) Supplement
Remediation: In 7.2, there is a new QAQQINI control called SQE_NATIVE_ACCESS with a default value of *YES. When *YES is used, the SQL Query Engine (SQE) attempts to run the query. If SQE is unable to process the query, the query is run using the Classic Query Engine (CQE). When SQE_NATIVE_ACCESS is changed to *NO, CQE is used first and SQE is only used when CQE is unable to run the query.
Parent topic: Database changes