AS400 OS升级到V7R2 默认数据库引擎CQE变为SQE

tomroom

AS400 OS V6R1升级到V7R2 默认数据库引擎CQE变为SQE 现象WRKQRY变慢以及单个SESSION 制定数据库引擎为CQE方法20150609。

现象 ：SIT  AS400  OS V6R1升级到V7R2  应用同仁反馈WRKQRY 里速度查询变慢很多 。
比如WRKQRY表 CNBWPDDATD/ CFLOG       F5查询 后 ，Position to line  . . . . .  输入B 总共150左右数据表，要好几秒时间才能 。 每秒跳数据条数 10几条。

IBM解释：(V7.2  版本数据库的默认引擎变更 了从CQE变为SQE ， CQE(以后IBM不再优化)
http://www-01.ibm.com/support/kn ... aq9osDBSQEvsCQE.htm



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

拷贝QSYS  LIB下的QAQQINI     *FILE    (Query 的配置文件)  拷贝到新增加的LIB ： OCBCYJ 下  :
在绿屏幕里 输入CHGQRYA DEGREE(*NONE) QRYOPTLIB(OCBCYJ)  （ 第一个参数DEGREE修改不跟从系统值 (*NONE)，这个修改是调试问题时候用到 后续是否需要修改还需要确认）（第二个参数 修改当前Session 绿屏 读取QUERY参数lib，Query options file library . . .   QUSRSYS 从默认QUSRSYS 修改为制定新建Lib：OCBCYJ）
(上面CHGQRYA 这个修改每次重新登录都要重新修改，只对当前以及登录的绿屏幕有效）

Navigator 里  "数据库" "/服务器序列号/"
  右下角 选择 "运行SQL脚本"


在新窗口 ""运行SQL脚本"
选择菜单  "选项"   "更改查询属性"

最下面 勾选 "使用以下模式中的查询文件"

" 模式" 里 输入 上面制定  OCBCYJ     后 点击右边 "编辑选项" 在 SQE_NATIVE_ACCESS 的值修改从*DEFAULT为 *NO  
修改完成可以5250里  RUNQRY *N OCBCYJ/QAQQINI 验证  SQE_NATIVE_ACCESS  为 *NO  
在上面修改chgqrya的session里重新wrkqry 查询CNBWPDDATD \CFLOG       发现WRKQRY速度 和另外V6R1的服务器上接近。（但是，session推出 后CHGQRYA失效）

后续 ：是否修改整个系统的这个值 还是只对部分用户ID生效 （ 可以在USER  ID  INIT LIB 或者制定新增LIB里（比如OCBCYJ LIB） 对应的QAQQINI文件 ） 需要应用讨论确认。

附录：按照IBM 800 说法 ：QAQQINI query配置文件
•        若CHGQRYA里有特别制定的用户自建的LIB里QAQQINI 就最优先生效。
•        若CHGQRYA里Query options file library . . .   QUSRSYS 从默认QUSRSYS 里有QAQQINI 配置
•        再次若上面2个都没有，系统就从QSYS   的LIB里QAQQINI   query配置文件QAQQINI 设置生效