Do you know the difference between BIF & ICI?
Release migration: Do you know which role ICIs are playing in the Db2 compatibility?
Just over a year ago I did a little newsletter all about IFICDs 366 and 376, what I call BIFCIDs, that report usage of a function that has changed or a function that works now, but will either fail or work differently, at some point in the future, e.g.: after a Db2 release migration.
When is a BiF not a BiF?
Now, back then I labelled these guys “BIFCIDs” and asked: “Where’s the BiF?” I see now that the name I chose was actually pretty inaccurate! If you take the time to study the macro versions of 366 and 376 (all described in the SDSNMACS library member DSNDQW05) you see that from the 22 IFCIDs only six are actually for a Built-in Function “problem” – all the rest are really ICIs.
What’s an ICI? It’s a TLA (another nice acronym), from Db2 development and stands for “Incompatible Change Indicator”. A really catchy name for a really nasty problem! So let us review the IFCID details, but now from the perspective of “Are you a BIFCID or are you an ICI?”
The details for BIFCIDs – die BIFs der IFCIDS list
*********************************************************************** ** QW0366FN = 1 * ** Indicates that the pre Version 10 CHAR built-in function has * ** been invoked. There is an incompatible change to the output of * ** the CHAR function for some decimal data. The zparm * ** BIF_COMPATIBILITY and/or the SYSCOMPAT_V9 schema have been used * ** by this application to get the old behavior. Please make the * ** appropriate changes and rebind with the SYSCURRENT schema to * ** use the Version 10 CHAR(decimal) built-in function. * ** (PM29124 V10 only, usermod V8/V9) * ** QW0366FN = 2 * ** Indicates that the pre Version 10 VARCHAR built-in function or * ** CAST(decimal AS CHAR or VARCHAR) has been invoked. * ** QW0366FN = 10 * ** RTRIM, LTRIM or STRIP version 9 being used with mixed data * ** QW0366FN = 1109 * ** Indicates that CAST(string AS TIMESTAMP) was processed for the * ** input string of length 8 and input was treated as a store clock * ** value (or input string was of length 13 and was treated as a * ** GENERATE_UNIQUE value). This behavior is incorrect for a CAST * ** and is valid for TIMESTAMP built-in function only. This behavior* ** is being corrected in Db2 11 so that input to CAST is not * ** treated as a store clock value nor GENERATE_UNIQUE. * ** QW0366FN = 1110 * ** Indicates the integer argument of SPACE function is greater * ** than 32764. * ** QW0366FN = 1111 * ** Indicates the optional integer argument of VARCHAR function * ** has a value greater than 32764. * ***********************************************************************
IFCIDS list follow: the details for ICIs
*********************************************************************** ** QW0366FN = 3 * ** Indicates that an unsupported character representation of a * ** timestamp string was used. PM48741 V10 only. * ** QW0366FN = 4 * ** A QW0366FN 4 record indicates that the statement uses the * ** word ARRAY_EXISTS as an unqualified user-defined function name * ** in a context that may be incompatible with Version 11. * ** QW0366FN = 5 * ** A QW0366FN 5 record indicates that the statement uses the * ** word CUBE as an unqualified user-defined function name * ** in a context that may be incompatible with Version 11. * ** QW0366FN = 6 * ** A QW0366FN 6 record indicates that the statement uses the * ** word ROLLUP as an unqualified user-defined function name * ** in a context that may be incompatible with Version 11. * ** QW0366FN = 7 * ** A QW0366FN 7 record indicates that Db2 for z/OS server issued * ** a SQLCODE -301 for incompatible data type conversion from * ** string data type (e.g. CHAR, VARCHAR, GRAPHIC, VARGRAPHIC * ** etc.) to numeric data type in V10 CM mode when implicit * ** cast is not supported or V10 NFM mode when DDF_COMPATIBILITY * ** zparm is set to DISABLE_IMPCAST_NJV or SP_PARMS_NJV to * ** disable implicit cast, and the client is CLI driver * ** or v11 NFM mode & APPLCOMPAT = V10R1 when DDF_COMPATIBILITY * ** is set to SP_PARMS_NJV or DISABLE_IMPCAST_NJV to disable * ** implicit cast either from string data type to numeric or * ** from numeric data type to string data type. * ** QW0366FN = 8 * ** A QW0366FN 8 record indicates that Db2 for z/OS server * ** returned output data match the data types of the * ** corresponding CALL statement arguments when DDF_COMPATIBILITY * ** zparm is set to SP_PARMS_NJV. * ** * ** QW0366FN = 9 * ** A QW0366FN 9 record indicates a data type conversion from * ** a TIMESTAMP WITH TIME ZONE input to a TIMESTAMP data * ** during input host variable bind-in process on server when * ** DDF_COMPATIBILITY zparm is set to IGNORE_TZ to ignore the * ** time zone information sent by Java IBM Data Server Driver. * ** QW0366FN = 1101 * ** Indicates that the INSERT statement that inserts into an XML * ** column without XMLDOCUMENT function has been processed (which * ** should result in SQLCODE -20345 when run on Db2 release prior * ** to V11). Starting with V11, SQL error will no longer be issued. * ** Application will no longer recieve SQLCODE for this statement. * ** QW0366FN = 1102 * ** Indicates that V10 XPath evaluation behavior was in effect which* ** resulted in an error. For instance, a data type conversion error* ** could have occured for a predicate that would otherwise be * ** evaluated to false. Starting from V11, such "irrelevant" errors * ** might be suppressed so an application might no longer recieve * ** the SQLCODE for this statement. * ** QW0366FN = 1103 * ** Indicates that a dynamic SQL uses the ASUTime limit that has * ** been set for the entire thread for RLF reactive governing. * ** For instance, when a dynamic SQL is processed from package A, * ** if the ASUTime limit is already set during other dynamic SQL * ** processing from package B in the same thread, the SQL from * ** package A will use the ASUTime limit set during the SQL * ** processing from package B. Stating with v11, dynamic SQLs from * ** multiple packages will use the ASUTime limit that is set * ** considering its own package information. * ** QW0366FN = 1104, 1105, 1106, 1107 * ** Indicates that CLIENT special register (CLIENT_USERID, * ** CLIENT_WRKSTNNAME, CLIENT_APPLNAME, CLIENT_ACCTNG) has been set * ** to a value that is longer than what is supported prior to V11. * ** A shorter value has been used instead. * ** QW0366FN = 1108 * ** Indicates that CLIENT special register (CLIENT_USERID, * ** CLIENT_WRKSTNNAME, CLIENT_APPLNAME, CLIENT_ACCTNG) has been set * ** to a value that is longer than what is supported prior to V11. * ** Truncated values upto the supported lengths prior to v11 have * ** been used for RLF table search instead. * ** QW0366FN = 1112 * ** Indicates the empty XML element is serialized to <X></X> * ** instead of <X/>. * ***********************************************************************
What does this all mean?
You might well be wondering: “What’s the point?” Well, the point is to try and get the ICI’s acknowledged in your company and to start checking for them now. Why do I say this? Because the APPLCOMPAT is only going to work for two down levels. What that means is that APPLCOMPAT at Db2 10 is good for Db2 11 and Db2 12 but *dies* horribly at Db2 12 +1 which, if IBM stick to their track record of Db2 delivery dates, will be October 2019. Not that far away in the future is it?
The BiF problem is fixed – long live the ICI Problem!
OK, you might have traced and tracked your BIFs, but what about the ICIs? At one firm I visited, all the BIFCIDs were clean until this February which, as a leap year, had 29 days. This caused different programs to run and…Tra La! New BIFCIDs came creeping out of the woodwork…along with some new ICIs…
Freeware to the rescue
To help you save time, just download and test our little BiF Freeware, which tracks down all ICIs and BiFs for you. Register, download, and then see where your system stands in the ICI rankings. We have also changed our Use Case “BIF usage” in WorkloadExpert to be only for BiFs and have introduced a new Use Case called “BIF/ICI occurrences” which shows them all.
As usual, any question or comments gladly welcome!
TTFN
Roy Boxwell