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?
How big is an SQL package?
The maximum size of a package was increased to 16,384 statements or approximately 512 MB, whichever comes first.
The IBM® OS/400® can support package sizes of up to 1 Gigabyte through the use of a QAQQINI setting - SQL_INCREASE_PKG_LIMIT option. The default remains at 512MB.
You can query view QSYS2/SYSPACKAGESTAT to get information about the SQL packages on the system. Example SQL statement:
SELECT SUBSTR(PACKAGE_SCHEMA CONCAT '/'
CONCAT PACKAGE_NAME,1,21) as OBJECT,
PACKAGE_TYPE,
PENDING_FULL,
PACKAGE_USED_SIZE as SIZE_IN_BYTES,
NUMBER_STATEMENTS
FROM QSYS2.SYSPACKAGESTAT
ORDER BY PACKAGE_USED_SIZE DESC
FETCH FIRST 100 ROWS ONLY
CONCAT PACKAGE_NAME,1,21) as OBJECT,
PACKAGE_TYPE,
PENDING_FULL,
PACKAGE_USED_SIZE as SIZE_IN_BYTES,
NUMBER_STATEMENTS
FROM QSYS2.SYSPACKAGESTAT
ORDER BY PACKAGE_USED_SIZE DESC
FETCH FIRST 100 ROWS ONLY
For more information on the view - SYSPACKAGESTAT
Information on the QAQQINI file -Creating the QAQQINI query options file Use
QUSRSYS for a system wide setting or a specific library in the example (-yourlib-) for a job by job / connection by connection basis.Example of the insert statement to use larger package size limit:
INSERT into -yourlib-.QAQQINI
VALUES ('SQL_INCREASE_PKG_LIMIT','*YES',null)
[{"Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000001i4oAAA","label":"IBM i Db2-\u003EQAQQINI - 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:
25 November 2024
UID
nas8N1017994