AS400 OS升級(jí)到V7R2 默認(rèn)數(shù)據(jù)庫(kù)引擎CQE變?yōu)镾QE

tomroom

AS400 OS V6R1升級(jí)到V7R2 默認(rèn)數(shù)據(jù)庫(kù)引擎CQE變?yōu)镾QE 現(xiàn)象WRKQRY變慢以及單個(gè)SESSION 制定數(shù)據(jù)庫(kù)引擎為CQE方法20150609。

現(xiàn)象 ：SIT  AS400  OS V6R1升級(jí)到V7R2  應(yīng)用同仁反饋WRKQRY 里速度查詢變慢很多 。
比如WRKQRY表 CNBWPDDATD/ CFLOG       F5查詢 后 ，Position to line  . . . . .  輸入B 總共150左右數(shù)據(jù)表，要好幾秒時(shí)間才能 。 每秒跳數(shù)據(jù)條數(shù) 10幾條。

IBM解釋：(V7.2  版本數(shù)據(jù)庫(kù)的默認(rèn)引擎變更 了從CQE變?yōu)镾QE ， CQE(以后IBM不再優(yōu)化)
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)  （ 第一個(gè)參數(shù)DEGREE修改不跟從系統(tǒng)值 (*NONE)，這個(gè)修改是調(diào)試問(wèn)題時(shí)候用到 后續(xù)是否需要修改還需要確認(rèn)）（第二個(gè)參數(shù) 修改當(dāng)前Session 綠屏 讀取QUERY參數(shù)lib，Query options file library . . .   QUSRSYS 從默認(rèn)QUSRSYS 修改為制定新建Lib：OCBCYJ）
(上面CHGQRYA 這個(gè)修改每次重新登錄都要重新修改，只對(duì)當(dāng)前以及登錄的綠屏幕有效）

Navigator 里  "數(shù)據(jù)庫(kù)" "/服務(wù)器序列號(hào)/"
  右下角 選擇 "運(yùn)行SQL腳本"


在新窗口 ""運(yùn)行SQL腳本"
選擇菜單  "選項(xiàng)"   "更改查詢屬性"

最下面 勾選 "使用以下模式中的查詢文件"

" 模式" 里 輸入 上面制定  OCBCYJ     后 點(diǎn)擊右邊 "編輯選項(xiàng)" 在 SQE_NATIVE_ACCESS 的值修改從*DEFAULT為 *NO  
修改完成可以5250里  RUNQRY *N OCBCYJ/QAQQINI 驗(yàn)證  SQE_NATIVE_ACCESS  為 *NO  
在上面修改chgqrya的session里重新wrkqry 查詢CNBWPDDATD \CFLOG       發(fā)現(xiàn)WRKQRY速度 和另外V6R1的服務(wù)器上接近。（但是，session推出 后CHGQRYA失效）

后續(xù) ：是否修改整個(gè)系統(tǒng)的這個(gè)值 還是只對(duì)部分用戶ID生效 （ 可以在USER  ID  INIT LIB 或者制定新增LIB里（比如OCBCYJ LIB） 對(duì)應(yīng)的QAQQINI文件 ） 需要應(yīng)用討論確認(rèn)。

附錄：按照IBM 800 說(shuō)法 ：QAQQINI query配置文件
•        若CHGQRYA里有特別制定的用戶自建的LIB里QAQQINI 就最優(yōu)先生效。
•        若CHGQRYA里Query options file library . . .   QUSRSYS 從默認(rèn)QUSRSYS 里有QAQQINI 配置
•        再次若上面2個(gè)都沒(méi)有，系統(tǒng)就從QSYS   的LIB里QAQQINI   query配置文件QAQQINI 設(shè)置生效