DB2 11 for z/OS: BIF_COMPATIBILITY vs CHAR9 and VARCHAR9 ... will they always be supported

kapil mathur

DB2 11 for z/OS: BIF_COMPATIBILITY vs CHAR9 and VARCHAR9 ... will they always be supported

Hello DB2-L members,
                 I am in a DB2 11 for z/OS shop and the rather old issue w/ BIF_COMPATIBILITY ... with IBM "reformatting" the output of the CHAR(decimal) and VARCHAR(decimal) functions for ANSI SQL compliance (first introduced after DB2 10 migration) is being addressed ... some of the DBAs are reluctant to recommend to applications team that they replace their CHAR() and VARCHAR() functions with the CHAR9() and VARCHAR9() functions respectively because they "unofficially heard" at IDUG conferences that IBM will pull the plug on these two band-aid functions, sooner or later, since they are "release-specific" and could soon be deprecated. If this happens, the applications teams would have a second unplanned  round of code-changes/retesting on their hands , if they currently switch to CHAR9 and VARCHAR9. Can anybody please confirm/comment on the potential longevity of CHAR9 and VARCHAR9 functions in the DB2 for z/OS environment. It has been proposed that the developers could alternatively use the VARCHAR_FORMAT functions instead (with appropriate picture-format clauses). What is the IBM preferred solution for BIF_COMPATIBILITY incompatibilities?  

Also, we have multiple IDAA appliances in our shop ... we are on Accelerator Server: 5.1.4.201702171406 ... and the IDAA boxes do not appear to support the CHAR9() or VARCHAR9() SQL functionality ... are any of the potential BIF_COMPATIBILITY solutions IDAA-compatible?  

Thanks in advance           

Michael Hannan

RE: DB2 11 for z/OS: BIF_COMPATIBILITY vs CHAR9 and VARCHAR9 ... will they always be supported
(in response to kapil mathur)

In Reply to kapil mathur:

Hello DB2-L members,
                 I am in a DB2 11 for z/OS shop and the rather old issue w/ BIF_COMPATIBILITY ... with IBM "reformatting" the output of the CHAR(decimal) and VARCHAR(decimal) functions for ANSI SQL compliance (first introduced after DB2 10 migration) is being addressed ... some of the DBAs are reluctant to recommend to applications team that they replace their CHAR() and VARCHAR() functions with the CHAR9() and VARCHAR9() functions respectively because they "unofficially heard" at IDUG conferences that IBM will pull the plug on these two band-aid functions, sooner or later, since they are "release-specific" and could soon be deprecated. If this happens, the applications teams would have a second unplanned  round of code-changes/retesting on their hands , if they currently switch to CHAR9 and VARCHAR9. Can anybody please confirm/comment on the potential longevity of CHAR9 and VARCHAR9 functions in the DB2 for z/OS environment. It has been proposed that the developers could alternatively use the VARCHAR_FORMAT functions instead (with appropriate picture-format clauses). What is the IBM preferred solution for BIF_COMPATIBILITY incompatibilities?  

Also, we have multiple IDAA appliances in our shop ... we are on Accelerator Server: 5.1.4.201702171406 ... and the IDAA boxes do not appear to support the CHAR9() or VARCHAR9() SQL functionality ... are any of the potential BIF_COMPATIBILITY solutions IDAA-compatible?  

Kapil,

I don't see why it makes sense that IBM would provide CHAR9 and VARCHAR9 and then later deprecate them. Why would they have a burning need to take them away, having provided them as a solution? Makes little sense. Of course, I do not speak on behalf of IBM. I am willing to bet though, that they are not going to state a deprecation is coming.

BIF_COMPATIBILITY is a different story of course. That is the "temporary Band-Aid".

You could use a more complex expression to simulate CHAR9 or VARCHAR9, if you don't want to trust them.

Something like (off the top of my head without testing it):

SUBSTR('-  ',SIGN(decimal-expression)+2, 1) concat INSERT(DIGITS(decimal-expression), p-q+1, 0, '.')

 

where p is total decimal digits, and q is digits after the decimal point. So get the SQL replaced one way or another. Note that I avoid using CASE to get better performance. Probably then need to include a CAST of the Varchar result to CHAR and length required, or use SUBSTR of result with a fixed desired length value.

Many times, the SQL rewrite can be simpler, due to circumstances where the SQL did not want the entire decimal expression anyway, but then substringed it, or there are no digits following the decimal point. It is quite rare that applications really needed CHAR(decimal expression) in the first place. Seen a few ugly cases though where a site decided to store a date or time in a decimal column, with no apparent good reason. 

Could use a UDF to do the job, excepting when requiring a fixed length result for some reason, if willing to accept worse performance. Performance should be fine provided UDF is a single expression internal in-line SQL Function (requiring no package but merely inserting SQL text into each SQL that uses it). See all the rules for in-line UDFs.

Suppose CHAR9 was deprecated in the future. Will still work for existing usage? Could replace new usage with a UDF at that time. Maybe that includes Dynamic SQL usage. I am unclear of deprecation rules.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Apr 12, 2018 - 07:29 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Apr 12, 2018 - 07:33 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Apr 12, 2018 - 07:35 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Apr 12, 2018 - 07:38 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Apr 12, 2018 - 07:45 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Apr 12, 2018 - 07:48 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Apr 12, 2018 - 07:51 AM (Europe/Berlin)