PM84769: FLAGGING NEXT RELEASE AFTER V10'S RESERVED WORD INCOMPATIBILITIES IN V10

Obtain the fix for this APAR.

APAR status

• Closed as new function.

Error description

• DB2 for z/OS V10 will issue IFCID 0366 trace records with three
  new values for the QW0366FN field when an SQL statement uses a
  future reserved word as an ordinary identifier in a context
  which may cause an incompatibility when the application is
  migrated to a future release.  The new QW0366FN values are:
  
  QW0366FN = 4 indicates that the statement uses the word
  ARRAY_EXISTS as an unqualified user-defined function name in
  a context that may be incompatible with future releases.
  QW0366FN = 5 indicates that the statement uses the word
  CUBE as an unqualified user-defined function name in
  a context that may be incompatible with future releases.
  QW0366FN = 6 indicates that the statement uses the word
  ROLLUP as an unqualified user-defined function name in
  a context that may be incompatible with future releases.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: ALL DB2 users using ARRAY_EXISTS, ROLLUP,    *
  *                 or CUBE as SQL identifiers (object names)    *
  ****************************************************************
  * PROBLEM DESCRIPTION: New syntax support in newer release     *
  *                      of DB2 will most likely introduce       *
  *                      new reserved words.                     *
  *                      Among them, most are softened,          *
  *                      while some or a few cannot be softened  *
  *                      within certain SQL contexts due to      *
  *                      ambiguity.                              *
  *                      Customers are faced with potential      *
  *                      problems on existing object names       *
  *                      once they have migrated to the newer    *
  *                      release.                                *
  *                                                              *
  *                      While circumventions are provided,      *
  *                      such as using a delimited identifier in *
  *                      the newer release, this APAR            *
  *                      provides an early detection on DB2      *
  *                      10 for z/OS (V10)                       *
  *                      prior to the migration to the next      *
  *                      release of DB2.                         *
  *                      It allows customers to act upon and     *
  *                      to fix the problem ahead  of the        *
  *                      migration.                              *
  *                                                              *
  *                      This APAR is only applicable on V10     *
  *                      and serves as an                        *
  *                      early detection aid by providing        *
  *                      IFCID 0366 trace records.               *
  *                                                              *
  *                      There are three new reserved words      *
  *                      being identified which may cause        *
  *                      incompatibilities depending on context. *
  *                                                              *
  *                      The three words are:                    *
  *                      ARRAY_EXISTS, CUBE, and ROLLUP.         *
  *                                                              *
  *                                                              *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  
  Here is an illustration of the problem the customer might
  face after migrating to the next release after DB2 10 for z/OS
  (V10).
  
  A V10 application could have created a user-defined function
  named ARRAY_EXISTS, and could have an SQL statement invoking
  the function as follows:
  
  SELECT X FROM T WHERE ARRAY_EXISTS(Y,2) = 2;
  
  In the next release of DB2,
  ARRAY_EXISTS is the name of a new predicate via a new
  SQL support, and in a similar SQL construct, ARRAY_EXISTS will
  be treated as the new predicate and it can not map to
  the user-defined function that the user anticipated as if it
  were in V10.
  
  Similar problems exist for the new reserved words, CUBE and
  ROLLUP when they are used as an unqualified ordinary identifier
  for a user-defined function name.
  
  When the customer enables the IFCID 0366 trace while they are
  on V10,  and if the usage of the above three reserved words are
  detected, DB2 will produce the following IFCID 0366 trace
  records indicated by the three new values in the QW0366FN field.
  
  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 the release after V10.
  
  This value is issued in V10 only.
  
  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 the release after V10.
  This value is issued in V10 only.
  
  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 the release after V10.
  This value is issued in V10 only.
  
  From the IFCID 0366 trace records collected, if QW0366FN
  has values, 4,5, or 6, then adjustments to the application
  should be made to avoid the conflict - either by changing the
  word to a delimited identifier, or by changing the word to
  a name that is not reserved in the release after V10.
  
  For example:
   ARRAY_EXISTS1 in place of ARRAY_EXISTS.
  
  For CUBE and ROLLUP, in addition to the above recommended
  circumvention, the reference can be qualified to avoid
  the conflict.
  

Problem conclusion

Temporary fix

Comments

• This is a new feature in V10 and is only available and has
  meaning on V10. It can be run on either NFM or CM mode.
  
  Once migrated on to the next release after V10, no IFCID 0366
  record will be issued for the three words above
  since the three new words will subject
  to syntax validation based on all syntax
  for the next release.
  

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  UK94459

Modules/Macros

• DSNDQW05 DSNHSMF3 DSNXRTIM
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK94459

     UP13/06/05 P F306

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.