Position function argument changes

The form of the POSITION function that uses a comma between the arguments has been changed to interpret the arguments in the opposite order to conform to DB2® SQL family standards.

In releases before IBM® i 7.2, the comma style was interpreted as: POSITION(source-string, search-string)
In release IBM i 7.2 and later, the comma style is interpreted as: POSITION(search-string, source-string)

This behavior change applies to any dynamically executed SQL and to any embedded SQL within a program, view, trigger, or other object that uses the POSITION function that is rebuilt after moving to IBM i 7.2. The result of the POSITION function with the arguments unintentionally reversed returns a value of 0, indicating that the search string was not found in source string. If an object (program, view, trigger) with the POSITION function is unchanged between releases, it works as before and continues to use the form of POSITION in effect when the view or program object was created.

There is a new environment variable, QIBM_SQL_POSITION_LIKE_DB2, that can be used to provide the prior behavior. For example, to use the non-standard, previous behavior across the entire system, execute this CL command: ADDENVVAR ENVVAR(QIBM_SQL_POSITION_LIKE_DB2) VALUE('N') LEVEL(*SYS).