As a long-term administrator of SAP on IBM i, you are most likely familiar with SAP’s usage of SQL packages and had to deal with them from time to time. SQL packages are a double-edged sword: While they improve your SAP system performance by caching the query optimizer information for frequently used SQL statements, they also can grow quite large and sometimes even hit a size limit, which results in an SQL error -904 in your application. The SAP code has algorithms in place to distribute the SQL statements over many different SQL packages, and the joint IBM and SAP teams continuously try to improve the SQL package handling.
One approach is to look at the needed space for query optimizer information of SQL statements that are executed only once, or maybe only a few times. As the number of SQL statements in an SQL package is growing over time, the question arises if it is really necessary to keep all the information, even if an SQL statement will most likely never be executed again. IBM has developed an algorithm to compress the query optimizer information in an SQL package when an SQL statement has rarely been used. With QAQQINI option SQL_STMT_COMPRESS_REUSE_MIN, you can control which SQL statements can be compressed based on their number of executions.
If you want to benefit from this work with an SAP NetWeaver system based on kernel 7.2x or 7.49, you can specify the parameter SQL_STMT_COMPRESS_REUSE_MIN with a value of e.g. 16 in a QAQQINI file that is used by your SAP system.
SAP Note 820325 – IBM i: Use of QAQQINI with SAP describes how to specify and modify the QAQQINI file for your SAP system.
With SAP Kernel version 7.53, patch level 728 or higher, the QAQQINI file is no longer needed to control statement compression in SQL packages when certain PTFs are applied.
SAP Note 2982555 – DB4: Minimal Single Execution Packages is listing the PTFs that are needed for this. With that, you can now use the profile parameter dbs/db4/stmt_compress_reuse_min to control the compression. With its default of 16, you can benefit from the new functionality without any configuration changes by just updating your system to the current PTF level and the latest kernel patches for 7.53.
If you are interested in more details, including the required IBM i PTF levels, you can find them in
SAP Note 2618157 – IBM i: Compressing infrequently used SQL statements in SQL packages.
