Home

Optimize the performance on your V5R2 & V5R3 SAP System (as of 4.6D Kernel)

SAP released a new kernel (or better: lib_dbsl) patch with a new functionality for performance optimization. OS/400 V5R2 & V5R3 have a special new "Optimze for All I/O" function, that is now used for SAP Queries.
As SAP normally fetches all rows, that are selected via the where-cluase "Optimze for First I/O" can be misleading. Especially large batch jobs, that are grabbing through complete tables, can benefit on this new setting, as they will now decide for a full table scan, because so many rows are selected.

There was a first attempt to built this feature in with the following LIB_DBSL patches:
So, there is a new profile parameter available as of the latest lib_dbsl patches (4.6D - 1779, 6.20 - 1351), but this die never work at all. It just had the old functionality ...

Now, there are new lib_dbsl patches available, that allow you to control this behaviour: (4.6D - 1982, 6.20 - 1775, 6.40 - 52)
dbs/db4/nqe_optimize_method = O (SAP Default)

The valid values are:
'O' - All I/O (default of SAP) - SAP Improved Queries (O = Optimized way so that the old Optimizer receives "First IO" and the new Optimizer receives "All IO")
'A' - All I/O - Everywhere "All IO" - sometimes worse performance for queries running through the old optimizer
'F' - First I/O (old behavior) - Designed for "Screen at Time" (default of the interface (IBM))

Further information is available in SAP Note 705888.

Please test this feature very good before moving to production as this is a fundamental change in the Optimizer behaviour and therefore may have implcations!

Have fun and enjoy the improved performance with this new feature.

Let us know your results in the SAPoniSeries group, so that we can convince as many people as possible on this new feature ...

The promissing thing is, that this really made huge differences at several customers on V5R3 - so get and activate it ...

If you are running on releases lower than 4.6, you can participate as well with special settings in the QAQQINI file. Just let us know, we can help you on that and let you know the inbound possible problems as well.

( AS/400, AS400 )