IBM Support

PI48163: SELECT ON A VIEW RETURNS SQLCODE = -901, SQLERRMC = 42S22 ATTRIBUTE 'ACC0NN.X' NOT FOUND WHEN RUNNING ON AN ACCELERATOR

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • select on a view returned SQLCODE901
    Attribute 'ACC018.T018C005' not found
    when routed on the accelerator. Same SQL works fine in DB2.
    

Local fix

  • set QUEYR ACCELERATION special register to NONE
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 for z/OS users of QUERY ACCELERATION *
    *                 for queries that reference SQL views         *
    ****************************************************************
    * PROBLEM DESCRIPTION: User requested QUERY ACCELERATION for   *
    *                      a query that referenced a view defined  *
    *                      with UNION ALL and JOIN , and the query *
    *                      failed with the following error when    *
    *                      run on the accelerator:                 *
    *                        SQLCODE -901  SQLSTATE 58004          *
    *                        SQLERRMT TOKEN =                      *
    *                         42S22: ERROR:                        *
    *                         'ACC018.T018C005' not found          *
    *                                                              *
    *                      Error is returned by the accelerator.   *
    *                      Other DB2-generated qualifiers (ACCnnn) *
    *                      and column names can appear in the      *
    *                      error message text.                     *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    When the user specified QUERY ACCELERATION for a query that
    referenced a view defined with UNION ALL and JOIN , the query
    failed with the following error when run on the accelerator:
                                                                   .
      SQLCODE -901  SQLSTATE 58004
      SQLERRMT TOKEN = 42S22: ERROR: 'ACC018.T018C005' not found
                                                                   .
    where
      ACC018   is a DB2-generated AS qualifier, and
      T018C005 is a column name (either DB2-generated or
               user-defined),
    for objects referenced in the view(s).
                                                                   .
    The error is returned by the accelerator .
    Other DB2-generated AS qualifiers (ACCnnn) and column names can
    appear in the error message text.
                                                                   .
    This can occur for QUERY ACCELERATION behaviors ENABLE ,
    ENABLE WITH FAILBACK , ELIGIBLE , or ALL .
                                                                   .
    The column name T018C005 with generated AS qualifier ACC018 was
    "not found" on the accelerator because, when DB2 transformed
    the query for acceleration 'after' merging in the view's
    fullselect, DB2 did not use the correct column name that
    resulted from the view's fullselect merge. This problem occurs
    when the view's fullselect uses UNION ALL and JOIN.
    

Problem conclusion

  • DB2 code for acceleration of queries referencing views was
    modified to use the correct column names when transforming
    the query for acceleration after the view's fullselect is merged
                                                                   .
    To make this fix effective for a static application containing
    accelerated static SQL queries, the application must be rebound
    after application of this PTF.
                                                                   .
    Additional search keywords: IDAAV3R1/K IDAAV4R1/K
                                SQLCODE901 SQLVIEW SQLVIEWMERGE
                                SQLJOIN SQLUNION SQLUNIONALL
                                SQLLEFT SQLLEFTJOIN
                                SQLOUTER SQLOUTERJOIN
                                SQLFULL SQLFULLJOIN
    

Temporary fix

  • AI48163
    

Comments

APAR Information

  • APAR number

    PI48163

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2015-09-04

  • Closed date

    2015-10-28

  • Last modified date

    2015-12-02

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

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

    UI32480 UI32481

Modules/Macros

  •    DSNXONZC
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UI32480

       UP15/11/13 P F511

  • RB10 PSY UI32481

       UP15/11/13 P F511

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: A10

Reference #: PI48163

Modified date: 02 December 2015


Translate this page: