IBM Support

PI22697: IF TABLE IS ADDED TO BOTH REAL AND VIRTUAL ACCELS, AFTER ALTER COLUMN + RE-ADD OF TABLE TO REAL ACCEL, DB2 DOES NOT ACCEL QUERY

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • A table was added to both a 'real' accelerator AND a 'virtual'
    accelerator.  Later, an ALTER TABLE table-name ALTER COLUMN
    was done for a column in the table. The table was then removed
    from the 'real' accelerator, re-added to the real accel, and
    loaded once again. The table was not removed from the virtual
    accelerator. Subsequent queries that referenced the altered /
    changed column were not accelerated by DB2 to the real
    accelerator; instead, the queries were run only in DB2.
    This was incorrect DB2 acceleration behavior for this scenario.
    .
    .
    An EXPLAIN of the query will show DSN_QUERYINFO_TABLE  column
    REASON = 15 and column QINAME1 = virtual-accelerator-name .
    In this case, EXPLAIN means only one of the following explain
    features:
     - SQL EXPLAIN statement
     - special register CURRENT EXPLAIN MODE = EXPLAIN
     - bind option EXPLAIN(ONLY)
    .
    Additional search keywords: IDAAV4R1/K IDAAV3R1/K IDAAV2R1/K
    

Local fix

  • To circumvent the problem, remove the table from the 'virtual'
    accelerator, then rerun the query.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 for z/OS users of an accelerated     *
    *                 static SQL query.                            *
    ****************************************************************
    * PROBLEM DESCRIPTION: This APAR fixes the following           *
    *                      problems for static query               *
    *                      acceleration:                           *
    *                      (1) BIND / REBIND PACKAGE specifying    *
    *                        bind options QUERYACCELERATION(ALL)   *
    *                        or GETACCELARCHIVE(YES) fails with    *
    *                        SQLCODE -4742 RC=14 when a target     *
    *                        accelerated table exists on           *
    *                        a virtual accelerator                 *
    *                      (2) An accelerated static query fails   *
    *                        at run time with SQLCODE -950 when    *
    *                        the targeted accelerated table        *
    *                        exists on a virtual accelerator       *
    *                      (3) AB04E at DSNLXREL . DSNSVSFB +08C6  *
    *                        occurs when an accelerated static     *
    *                        SQL query is run when the target      *
    *                        accelerated table is archived         *
    *                        'after' the last BIND / REBIND        *
    *                        PACKAGE bound the static query for    *
    *                        acceleration                          *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    This APAR fixes the following problems found for static query
    acceleration:
    (1) BIND / REBIND PACKAGE with bind option
        QUERYACCELERATION ( ALL ) or GETACCELARCHIVE ( YES ) fails
        with SQLCODE -4742 and reason code 14 when a virtual
        accelerator exists. This can occur when all of the
        following conditions are true:
        (a) A table exists on both a real and virtual accelerator
        (b) The ALTEREDTS value in SYSACCEL.SYSACCELERATEDTABLES
            for the table for the virtual accelerator is older than
            ALTEREDTS in SYSIBM.SYSCOLUMNS for the table
    (2) Execution of an accelerated static SQL query fails with
        SQLCODE -950 when a virtual accelerator exists. This can
        occur when a virtual accelerator is defined but stopped (or
        not active) at BIND / REBIND PACKAGE.
    (3) ABEND04E at DSNLXREL . DSNSVSFB OFFSET08C6 occurs when
        an accelerated static SQL query is run when the target
        accelerated table is archived 'after' the last BIND/REBIND
        PACKAGE bound the static query for acceleration.
    
    Keywords:
    IDAAV3R1/K IDAAV4R1/K SQLCODE4742 SQLCODE950 ABEND04E
    

Problem conclusion

  • DB2 code is fixed for expected behavior.
    

Temporary fix

Comments

APAR Information

  • APAR number

    PI22697

  • 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

    2014-07-24

  • Closed date

    2014-11-10

  • Last modified date

    2014-12-01

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

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

    UI22920 UI22921

Modules/Macros

  •    DSNXEDP  DSNXEPP  DSNXERT  DSNXERT2 DSNXONZA
    DSNXONZB
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UI22920

       UP14/11/26 P F411

  • RB10 PSY UI22921

       UP14/11/26 P F411

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 #: PI22697

Modified date: 01 December 2014


Translate this page: