IBM Support

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

A fix is available

Subscribe

You can track all active APARs for this component.

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 number

    PM84769

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED UR1

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-03-13

  • Closed date

    2013-05-20

  • Last modified date

    2013-07-02

  • 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.



Document information

More support for: DB2 for z/OS

Software version: 10.1

Reference #: PM84769

Modified date: 02 July 2013