After bringing IBM i OS 7.4 PTFs current, SFC500 Shop Order Maintenance and PRO150 List Price Book Maintenance displayed no records

This alert is from Infor and is on their site at:
https://community.infor.com/lx/f/lx-discussions/26991/after-bringing-ibm-i-os-7-4-ptfs-current-sfc500-shop-order-maintenance-and-pro150-list-price-book-maintenance-displayed-no-records/72169

From Bryan White:

IBM provided the following solution. The mitigation will not be available in IBM i OS 7.5 so worth a read. In our case, we had the PTF but we hadn't updated the environmental variable so once that was done then our data displayed in the Infor programs.

Yes, if you've moved up to 7.4 fixpack level 13 or higher, you can start seeing the CPD4374 RC7, SQL0302 RC6 or SQL0678 RC6, due to PTF SI76030. Many customers have hit this issue, and this is the information we send to customer that need assistance in circumventing and finding the problematic SQL statements:

Previous to PTF SI76030 on IBM i 7.4 (part of SF99704-13) and PTF SI76035 (part of SF99703-24) on IBM i 7.3, a query, or SQL statement that included an invalid comparison of a numeric value to a blank(s) or an empty string would replace the blank or empty string to a 0 and allow the query to run without failure even though blanks or an empty string are both incompatible with numeric values. With PTFs SI76030 / SI76035 applied any such query or SQL statement will now fail with SQLSTATE: '22023'/-302 or '53045'/-678 as this is the correct behavior.

The Memo To Users at R730 and R740 were updated to include the new behavior. However, IBM understands this change might require clients to make programming changes mid release to code to the correct behavior. Because of this IBM created a way for clients to revert to the incorrect behavior and allow for the comparison of empty strings or blanks to numeric fields. This circumvention will only be made available at R730 and R740 and will NOT be supported in the next release of IBM i. Clients must make the necessary coding changes to correctly compare numeric fields to valid numeric data. After making the programming changes, the circumvention should be removed.

PTF SI76529 (7.3) and SI76674 (7.4) need to be applied to use the circumvention.

Note - The job needs to be restarted after the environment variable is put in place.

Activating the circumvention system wide for all jobs:
ADDENVVAR ENVVAR(QIBM_QUERY_CONVERT_BLANK_TO_0) LEVEL(*SYS)

Activating the circumvention at the job level:
Run the following in the job you want to revert to the incorrect behavior.
Note: This must be added before the job does any SQL or query. Otherwise, it will not be picked up.

ADDENVVAR ENVVAR(QIBM_QUERY_CONVERT_BLANK_TO_0) LEVEL(*JOB)

The circumvention is only available at R730 and R740.

Removing the circumvention:
RMVENVVAR ENVVAR(QIBM_QUERY_CONVERT_BLANK_TO_0) LEVEL(*SYS)
RMVENVVAR ENVVAR(QIBM_QUERY_CONVERT_BLANK_TO_0) LEVEL(*JOB)

Link to the Memo To Users:
R740
www.ibm.com/.../7.4<https://www.ibm.com/docs/en/i/7.4?topic=information-memo-users>

R730
www.ibm.com/.../7.3<https://www.ibm.com/docs/en/i/7.3?topic=documentation-memo-users>

Additional Support Provided (to find SQL statements)

With 7.4 TEST PTFs SI77018/SI77019 and 7.3 TEST PTFs SI77020/SI77021 applied, when the environment variable QIBM_QUERY_CONVERT_BLANK_TO_0 has been created for a job, message CPF9898 is sent to QSYSOPR the first time the job converts a blank or empty string to 0. CPF9898 will include the job information. A QSQSVCDMP spool file is also generated in QUSRSYS/QEZDEBUG output queue that includes the call stack of the job at the time the conversion was done.

NOTE: If the user profile does not have *SPLCTL authority, then the stack will not be dumped. The dump of the stack depends on SQL control structures, SQL must have run in the job, otherwise the stack will not be dumped either.

Example of CPF9898 message text:
DB2 CONVERTED BLANK OR EMPTY STRING TO 0.

Note: The jobs must be recycled after the PTFs have been applied to generate the CPF9898 message and dump.

Example:
1) CALL KTRISKO/SIMPLE_INC
Or SBMJOB CMD(CALL PGM(KTRISKO/SIMPLE_INC))

2) DSPMSG QSYSOPR will show a CPF9898 - DB2 CONVERTED BLANK OR EMPTY STRING TO 0.

3)F1, F9 on that message will show the job information:

From job . . . . . . . . . . . : QDFTJOBD

User . . . . . . . . . . . . : KTRISKO
Number . . . . . . . . . . . : 543009

4)WRKJOB 543009/KTRISKO/QDFTJOBD
Will show a QSQSVCDMP was created.

5) QSQSVCDMP shows the *PGM on the stack

The message details for CPF9898 will also include the user profile of the user that sent the message.
For example:
Display Message Details

Message ID . . . . . . : CPF9898 Severity . . . . . . . : 40
Date sent . . . . . . : 09/16/21 Time sent . . . . . . : 13:58:07
Message type . . . . . : Information

From . . . . . . . . . : USER1 CCSID . . . . . . . . : 65535

From job . . . . . . . . . . . : QZDASOINIT

User . . . . . . . . . . . . : QUSER
Number . . . . . . . . . . . : 027369

Let us know if there are questions. All PTFs are approved and can be downloaded. Please confirm that you have received this update.


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com