Infor put an alert on their site at
I think I saw a thread on this list about this issue or a similar one.
Basically if you put on a certain DB2 fix pack on 7.4 or 7.3 certain errors may start occurring.
The Memo To Users at R730 and R740 were updated to include the new behavior. As shown below:
Queries that compare a numeric value with blanks or an empty string will
fail with SQLSTATE/SQLCODE ‘22023’/-302 or '53045'/-678
Previous to PTF SI76030 on IBM i 7.4 and PTF SI76035 on IBM i 7.3, a query that included an invalid
comparison between a numeric value and blank(s) or a numeric value and an empty string would not
result in an SQL failure.
Blank characters and empty strings are both incompatible with numeric values. Any such queries will now
fail with SQLSTATE: '22023'/-302 or '53045'/-678.
Infor published an environment variable which came out in a PTF which can be used to circumvent this.
PTF SI76529 (7.3) and SI76674 (7.4) need to be applied to use the circumvention.
They also said something to the effect that 7.4 will be the last release to have this circumvention and you really should get the BMR’s from Infor which will correct the code instead.
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)
I defy you to find this environment variable with any search engine.
IBM Certified System Administrator - IBM i 6.1
Mail to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
As an Amazon Associate we earn from qualifying purchases.