Troubleshooting
Problem
A message SQL0904 RC7 can indicate that an SQL package might be full. This document will discuss how to determine if an SQL package is full and what the maximum size limits are.
In the case of database host server (QZDASOINIT jobs), the database server attempts to detect the package full condition (msgSQL0904, reason code 7). When a database server job detects the package full condition it sends one PWS0018 message to the job log but no warning or error to the client (ODBC or JDBC).
Resolving The Problem
Message SQL0904 RC7 can indicate that an SQL package might be full.
How big is an SQL package?
Prior to V4R3, SQL packages were limited to 16MB. Furthermore, ODBC packages had additional restrictions on the number of statements allowed.
How big is an SQL package?
Prior to V4R3, SQL packages were limited to 16MB. Furthermore, ODBC packages had additional restrictions on the number of statements allowed.
In V4R3, the maximum size of a package was increased to 16,384 statements or approximately 512 MB, whichever comes first.
In V4R3, ODBC package size limits have also been removed. Note that the SQL package must be created on V4R3 to be capable of this new larger size. They are not converted automatically. If upgrading to V4R3, extended dynamic SQL packages should be deleted to take advantage of the larger size.
As of V5R2, the IBM® OS/400® can support package sizes of up to 1 Gigabyte through the use of a new QAQQINI SQL_INCREASE_PKG_LIMIT option. The default remains at 512MB.
As of V5R2, the IBM® OS/400® can support package sizes of up to 1 Gigabyte through the use of a new QAQQINI SQL_INCREASE_PKG_LIMIT option. The default remains at 512MB.
| o | The statement limit for a full package is 16,384 as indicated above. The seamless rollover changes will include an internal concept of pending full, which is SQ's way of telling Host Servers and Client Access that we are close to full and that we recommend that they stop adding statements to it. That happens at 16,368. To actually check the value in the SQL package, you need to do a DMPSYSOBJ command against the SQL package. On the OS/400 or IBM® i5/OS® command line, type the following: DMPSYSOBJ OBJ(EXAMPLE) CONTEXT(QGPL) OBJTYPE(*SQLPKG) Press the Enter key. (Browse the output listing and scan for the word PRIMARY.) PRIMARY ASSOCIATED SPACE- 000000 00000000 00000000 00000000 00000000 00000000 ... 000020 00000000 00000000 00000000 00000000 00000000 ... 000040 00000800 00030800 00003FF0 00000000 00000000 ... The highlighted and underlined field is the entry count for the entire SQL package. '4000'X is 16K and indicates it is full; '3FF0'X is 16K-16 and indicates it is pending full. |
| o | The size limitation indicated is approximate. The minimum size for a Client Access ODBC SQL package is 64K. As the SQL package needs to grow, we add additional secondary associated spaces and allow up to 31 of them. Each of them can range from approximately 14MB up to 16MB; therefore, doing the math, we get something from 434MB up to 496MB. If it is in that range, it is full enough to be interesting even if it is not quite full yet. |
| o | The process of adding the additional 16 MB to the SQL Package will be seamless (no intervention is needed by the end user). |
Example:
- Use
QUSRSYS for a default system wide setting or a specific library in the example (-yourlib-) for a job by job / connection by connection basis.INSERT into -yourlib-.QAQQINI
VALUES ('SQL_INCREASE_PKG_LIMIT','*YES',null)
[{"Line of Business":{"code":"LOB57","label":"Power"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000001i4oAAA","label":"IBM i Db2->QAQQINI - query options file"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Version(s)"}]
Historical Number
N1017994
Was this topic helpful?
Document Information
Modified date:
26 October 2020
UID
nas8N1017994