i5/OS V5R4: Analyze your Database Performance with a Plan Cache Dump

[christian_bartels]

You may have heard already that IBM has introduced a new SQL optimizer beginning with OS/400 V5R2M0. Initially, this new SQL optimizer (called "SQL Query Engine" or short SQE) could only process a subset of the SQL statements that SAP executes. The remaining SQL statements were still processed by the old optimizer (called "Classic Query Engine" or short CQE). With each new release, more SQL statements could be processed by the SQE, and less statements were left for CQE. With i5/OS V5R4M0, all the SQL statements that SAP executes can be processed by the SQE.

One of the key features of SQE is a system-wide plan cache, which keeps information about SQL statements that have been executed. The main purpose of this plan cache is to speed up subsequent executions of the statements in the cache by reusing the optimization information. As a side-effect, you can also dump the plan cache to understand how the SQL statements in the cache were implemented.

Prior to i5/OS, this kind of information was only available through the database monitor and could be analyzed through the SAP transactions ST04 or DB4Cockpit. The database monitor caused some overhead to the system, and in some cases we have seen that the system locked up due to errors in the database monitor.

That's why IBM and SAP made the SQE plan cache dump available through the SAP system. If you are running i5/OS V5R4, you can use SAP Note 1083218 to obtain the necessary PTF's and SAP patches and set the necessary profile parameters to activate the SQL plan cache dump. The data can then be displayed in the usual way through transactions ST04 and DB4Cockpit. I would like to invite you to try out this new function and check if it meets the requirements of your business.

If you have questions or comments, feel free to post them in this forum. If you should encounter problems, please open a problem message at SAP for component BC-DB-DB4 and reference SAP Note 1083218.

Kind regards,

Christian Bartels

SAP on i5/OS Porting Team

[Former Member]

We recently upgraded our As400 to V5.R4 and we are noticing the that interactive sql is not reconizing the logical file access paths. When using ORDER by it seems to be scanning every record in the file, and most of the time were killing the job. I am reading up on the differences of CQE and SQE within these releases.

My question is do we need to make sql index's where we have created DDS logical file indexes..... or

is there something we can set system wide so that it still will use are DDS access paths....or am I totally on the wrong path?

[Former Member]

Hi darlenew,

in general, we would always recommend to use SQL indexes as this is "the nature of SAP" - but for interfaces to RPG DDS indexes are nice as well.

IBM has a QAQQINI parameter IGNORE_DERIVED_INDEX - this one was default *NO in the past. At least since 6.1 it is *YES in default. I'm not sure on V5R4.

You could change QUSRSYS/QAQQINI to IGNORE_DERIVED_INDEX = *NO - perhaps this helps for you ...

But:

You should migrate to SQL indexes ...

Regards

Volker Gueldenpfennig, consolut international ag

http://www.consolut.com http://www.4soi.de http://www.easymarketplace.de