The following list documents known native open and query processing differences between CQE and SQE.

Note: These behavior differences are not included within the MTU document because these are thought to be obscure and rare differences in behavior.
   

As in previous releases, IBM® i 7.2 extends SQE as the default choice for optimization.

With IBM i 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 open where the target of the open is an SQL view, unless the open is for *OUTPUT only
• Any open of a database file where Column Mask is defined and enabled, unless the open is for *OUTPUT only
• Any open of a database file where a Row Permission is defined and enabled

SQE versus CQE behavior differences for native database access

**Unless noted, these apply to OPNQRYF, native open of a view, and Query/400 (5770QU1 - IBM Query for i )

Operational differences:

• Contents of fetch buffer for fields that are null or in error

Most programming languages contain modern methods for predictably handling null values that are returned when reading rows from a file that has null-capable fields or for fields that result in null for a mapping error. For example, divide by zero. Prior to these modern techniques, some programmers relied on a result derived from default values being returned in the record buffer for a null value. For example, in RPG, programmers used the ALWNULL(*INPUTONLY) or ALWNULL(*YES) option. Only application programs that rely on this older technique are impacted.  

CQE will use default values to map data into the record buffer for any fields that are null.SQE only maps data to the record buffer for fields that are not null. For example,

    Assume the following DDS is used to create a file called dftvalue, and a single record with
    nulls supplied for both fields is inserted.

     A          R FORMAT                                                     
     A            VALUE1         9B         DFT(1) ALWNULL      
     A            VALUE2         9B         DFT(3) ALWNULL       
    
    Assume an application program opens file dftvalue.
    With CQE, a READ will return the values 1 and 3 into the record buffer. SQE will not.
 
    Assume an application program uses the following OPNQRYF command:
     OPNQRYF FILE((DFTVALUE)) FORMAT(DFTVALUEF) MAPFLD((SUM 'value1+value2')).

    With CQE, the value in the record buffer for the null field will be 4, while for SQE, it will be 0.  However, in both cases, the null indicator will be set, meaning the associated field data is not to be considered valid.
 

• Relative Record Number (RRN) positioning may not be supported for certain join queries**

Most programming languages contain methods for processing files by relative record number. For example, specifying RECNO in RPG  or, specifying ORGANIZATION IS RELATIVE  in COBOL or in C, specifying  __RRN_EQ and _Rreadd().
These methods can be used in conjunction with OPNQRYF or native opens of a view.

Consider the query specified by OPNQRYF FILE((T) (T2)) FORMAT(T2) JFLD((T/K T2/K2 *EQ)) JORDER(*ANY).  Because JORDER(*ANY) is specified (the default), the optimizer may choose to reorder the joined files for performance. For example, join from t2 to t in order to take advantage of an existing index, etc.  This can happen for both the CQE and SQE optimizers.  However, CQE  allows RRN positioning operations for this join query even though the results are not predictable (the RRN values may come from T2 or T1 depending on whatever order the CQE optimizer thinks will perform better).

To avoid confusion, SQE will reject any attempted RRN positioning operations with a CPF5149 escape message (Operation for program device or member T, file T in library MYLIB is  not valid), if the optimizer changed the join order of the file specified first in the query.  Furthermore, RRN feedback will be from the first file specified in the join, regardless of which position it is actually joined.  Specify JORDER(*FILE) to avoid these situations and ensure the files are joined in the specified order.  Join logical files are implicitly treated as JORDER(*FILE).

For cases where the OPNQRYF KEYFLD or GRPFLD parameters specify one or more fields from a file that is not specified as the first joined file, a temporary result may need to be built, or the join order changed in order to implement the query.  In either case, RRN positioning attempts will also be rejected by SQE with the CPF5149 escape message.  In cases where the KEYFLD parameters all come from one file, specifying it as the first joined file and using JORDER(*FILE) may allow RRN positioning, however, this positioning will be relative to that file.
** This difference does not apply to Query/400

• A query of a file that has multiple data members specified may require a temporary result unless an index that spans all the data members exists **

In either OPNQRYF with a KEYFLD parameter or in Query/400 with a sort specification, the following two cases may perform worse with SQE than with CQE unless an index that spans all the data members exists.

Also, attempts to perform key positioning on an OPNQRYF query without a spanning index and ALWCPYDTA specified with a value other than *OPTIMIZE, may fail with Rule option not valid for cursor (MCH1825).

If a logical file member is specified that is created over multiple data members (DTAMBRS parameter)    
If a partitioned table is specified and *ALL members are specified

For both CQE and SQE, if an appropriate index that spans all the data members exists, that index can be used.
Otherwise,  
For CQE, such a spanning temporary index is implicitly created and used for these two cases.
For SQE, a temporary result and a sort or MTI is then created on the temporary result for these two cases.
In both cases the query is successful, but the performance of the SQE implementation may be worse.
** This difference does not apply to a native open of a view

• No SEQONLY(*YES) override for native queries**

For certain native queries, i.e., grouping, etc., CQE implementation restrictions may result in the override of the SEQONLY parameter from *NO to *YES.  CQE indicates this by sending a CPF4198 (Open of member V1UROW_R changed to SEQONLY(*YES)) message.  For many of these cases, SQE does not require this override, and so SEQONLY(*NO) processing will be performed, as requested. SEQONLY(*YES) typically performs better than SEQONLY(*NO). To get the same performance, delete the override or change it to SEQONLY(*YES).
** This difference does not apply to Query/400 or the native open of a view

Error or Message differences:

• Fewer and more detailed CPD4019 diagnostic messages generated for selection errors

Consider the following table & view:  
    create table t (k int , v int)
    insert into t values(1,1)
    create view boom as select k from t where (k/v/k/v/0)>1

When this view is opened and read, CQE will produce CPD4019 diagnostic messages for each attribute in the expression getting the divide by zero error.  For CQE, the failure appears as:
      Select or omit error on field V member K.
      Select or omit error on field K member K.
      Select or omit error on field K member K.
      Select or omit error on field V member K.

Because of different error handling for SQE, the expression is flagged as being in error, and the associated field name returned by the optimizer includes the complete expression.  SQE will only surface one CPD4019 diagnostic message with this long field name identified.

       Select or omit error on field T_1.K/T_1.V/T_1.K/T_1.V/0 member BOOM
       RSN  6 - A data mapping error occurred on the field before the select or omit operation was attempted.                                                

• Index build failure deferred to first fetch. **

For native queries that require a temporary index to be built, CQE will report any build failures on the query open. Because SQE defers the index build until an attempt is made to read from the query, the open will be successful, and any index build failures will be reported on the first read attempt.

** This difference does not apply to Query/400

• Positioning to deleted records may fail with escape message MCH1825**

Most programming languages contain methods for processing files by relative record number. For example, in RPG specifying RECNO or in COBOLORGANIZATION IS RELATIVE or in C, __RRN_EQ and _Rreadd(). These methods can be used in conjuction with OPNQRYF or native opens of a view.

The query optimizer may choose to implement a native query with a left-most index even if KEYFLD is not specified, if that is determined to be the best performing access plan.  For these queries, CQE will reject attempts to position to deleted records with CPF5119 (Data option was not allowed with open option) or CPF5149 (Operation for program device or member &4, file &2 in library &3 is not valid), as indexes do not contain deleted records.  Due to an open flow difference, SQE may reject the attempted positioning operation with MCH1825 (Rule option not valid for cursor).  

Assume the following steps have been performed.

create table t (k int)
insert into t values(1)
create index t_idx on t (k asc)
inzpfm file(t) records(*DLT) totrcds(100000)

It is likely that OPNQRYF FILE((T)) will result in an access plan that uses index t_idx to avoid processing all of the deleted records. If deleted record positioning is desired, then KEYFLD(*FILE) should be specified.

** This difference does not apply to Query/400

I/O Feedback differences

• Duplicate key feedback processing does not consider records omitted by join processing**

Some programming languages contain methods to determine if duplicate keys exist when a record that is read. For example, in COBOL DUPKEYCHK or in C dupkey. This feedback is returned along with each record that is read, as an example, the dup_key field of the _RIOFB_T feedback structure returned on a _Rreadf() call.  
Assume the following steps have been performed.

create table t (k int, v int)  
insert into t values (1,2),(1,1)    
create table t2 (k2 int, v2 int)   
insert into t2 values (2,2)    

For OPNQRYF FILE((T) (T2)) FORMAT(T) KEYFLD((K)) JFLD((1/V 2/K2 *EQ)) DUPKEYCHK(*YES),
CQE will indicate that the single selected record has a duplicate.  SQE will not, because the duplicate value in file t  (record (1,1)) is not returned due to the specified join selection.
** This difference does not apply to Query/400 or native open of a view

• Key feedback is only provided for OPNQRYF if a KEYFLD parameter is specified**

Most programs provide methods to access key information about the most recent database I/O operation. For example, in RPG INFDS or in COBOL I-O-FEEDBACK or in C _Riofbk().  For an OPNQRYF grouping query, for example, OPNQRYF GRPFLD(F), CQE will implicitly assume KEYFLD(F), and provide key feedback.  SQE requires that  KEYFLD(F) be explicitly specified if key feedback is desired.
** This difference does not apply to Query/400 or native open of a view

Database query access which will continue to utilize CQE:

• Database file queries initiated through the Query (QQQQRY) API 
  (Note: SQE will be used when QQQQRY() targets a table which has RCAC controls activated)
• Distributed file access (Db2 for i Multisystem)  
• Database file queries or native access of a view where a read trigger exists and is enabled
• Query/400 queries of program described files
• Database file queries or native access of a view that specifies an ICU 2.6.1 sort sequence
• Native open of partition table with a MBR(*ALL) override
• Database file queries that references more than 1000 table members (data spaces)