In today's blog, we'd like to update you about two recent improvements in the area of SQL packages:
As you all know, SAP on IBM i uses SQL packages to keep prepared statement information for later reuse to ensure good database performance. Although SQL packages are persistent objects, we continuously search for ways to keep them transparent to you and to reduce administration efforts, e.g. by removing stale packages automatically.
Unfortunately, we sometimes fail, and this is typically when you see an SQL0904 error, type 7, which means that an SQL package exceeded one of its two physical limits:
- Maximum package size: 512 MB / 1 GB
- Maximum number of SQL statements per package: 16,384
Typically, it is the first limit that leads to package overflows. It has hence been possible for a while to increase the maximum size of an SQL package from 512 MB to 1 GB by setting QAQQINI parameter SQL_INCREASE_PKG_LIMIT to *YES. But this is a manual effort, and not everybody wants to maintain one or more QAQQINI files according to SAP note 820325. Based on our feedback, the DB2 for IBM i database team in Rochester has thus decided to make the bigger size the default: With the following DB fixpacks, SQL packages can now grow up to 1 GB automatically:
- IBM i 7.1: DB group (SF99701): level 33
- IBM i 7.2: DB group (SF99702): level 4
In addition to that, we are working closely together with the database team to reduce space consumption per statement, e.g. through compression or displacement of least-recently-used data. As an important step to better understand how packages "behave" in practice, IBM Rochester has created two new system catalogs, QSYS2.SYSPACKAGESTAT and QSYS2.SYSPACKAGESTMTSTAT. These are available with
- IBM i 7.1: DB group (SF99701): level 29 + PTF SI55753
- IBM i 7.2: base + PTF SI55754
You can use them yourself to monitor for next-to critical packages. The following query shows e.g. all SQL packages for SAP system PRD, which have more than 10,000 statements, or are bigger than 750,000,000 bytes. To be sure not to miss anything, the query also checks for such packages that have the so-called pending-full flag set:
SELECT PACKAGE_SCHEMA, PACKAGE_NAME, NUMBER_STATEMENTS, PACKAGE_USED_SIZE
FROM QSYS2/SYSPACKAGESTAT
WHERE SYSTEM_PACKAGE_SCHEMA LIKE 'R3PRD%‘
AND ( PENDING_FULL = 'YES'
OR NUMBER_STATEMENTS > 10000
OR PACKAGE_USED_SIZE > 750000000 )
Identifying critical packages early, hopefully gives you more time to react appropriately, e.g. by finding the critical ABAP statement and adding a hint and to schedule a package deletion after that. Or, in case you can't get a system downtime quickly enough, by switching to the alternate package set like described in SAP note 972460.
In any case, we'd be happy if you could help us to further improve package handling by turning in big SQL packages to the SAP or IBM support for further analysis, prior to deletion. Feel free to open a ticket for that, or to contact us over a different channel.
