In V5R1, a change was made to DB2 UDB for iSeries so that it returns the proper SQLCODE warning on certain types of FETCH and SELECT INTO statements. If an application wasn't coded properly to check the SQLCODE value, then it will start failing on V5R1 systems.
SQL lets you execute a Fetch or Select Into statement that doesn't provide as many host variables as the number of columns specified in the associated cursor's list of columns. Prior to
V5R1, this operation generates the following status values:
SQLSTATE='01503' & SQLCODE=0.
According to the SQL standard, this condition should have generated a positive SQLCODE value (+30) to signal a warning. DB2 UDB for iSeries was changed to conform to the standard, so in V5R1 any FETCH & SELECT statements that do not provide enough host variables, will now receive an SQLCODE=+30, SQLSTATE='01503, and the MSGSQL0030 warning message. Here's
the new message:
SQL0030 Number of host variables less than result values.
Most of the application was coded (incorrectly) to check for an SQLCODE <> 0 to identify an error condition. Applications coded in this manner which receive an MSGSQL0030 warning, will fail.
Applications that check the SQLSTATE would properly catch this condition.
To correct an application, it is recommended that you change tests of SQLCODE to use SQLSTATE and then handle SQLSTATE 01503 appropriately. For example, you could ignore the warning
if you've intentionally coded fewer host variables than columns.
Usage of SQLSTATE is recommended is due to the fact that the SQLCODE has been deprecated in the latest version of the SQL standard. However, DB2 UDB for iSeries will continue to support SQLCODE.
If you need a temporary fix after upgrading to V5R1, IBM has created the following V5R1 PTFs to address this issue: LPP: 5722SS1
PTF numbers: SI01901, SI01902, SI01903
When these PTFs are applied, you can use a new SQL_SUPPRESS_WARNINGS option in the QAQQINI file. In V5R1, the
SQL_SUPPRESS_WARNINGS *YES option suppresses the SQLCODE only for the SQL0030 message. The SQLSTATE is
unaffected. In future releases, this option will suppress all warnings. (The default for the SQL_SUPPRESS_WARNINGS option is *NO.)
It is recommended to change failing applications to correctly interpret SQLCODE and SQLSTATE values. The new QAQQINI option should be used as a circumvention until deployment of an application fix is possible.
