IBM Support

PI46834: REBIND PACKAGE(XXXX.*) MAY CAUSE SOME SQL QUERIES IN SOME PACKAGES TO BE UNEXPECTEDLY BOUND FOR QUERY ACCELERATION

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • REBIND PACKAGE(XXXX.*) may cause some packages in the collection
    XXXX to inherit the QUERYACCELERATION behavior for its static
    queries if at least 1 package in the collection was previously
    explicitly bound with QUERYACCELERATION bind option.
    .
    SYSIBM.SYSPACKSTMT entries for the static queries in the package
    show STATUS='O', however, there is no SYSACCEL.
    SYSACCELERATEDPACKAGES entry for the package.
    

Local fix

  • REBIND the individual packages in the collection with
    separate REBIND PACKAGE subcommands (no wildcard/'*'), and
    do not specify option QUERYACCELERATION on the REBIND.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 users of bind options                *
    *                 QUERYACCELERATION or GETACCELARCHIVE for     *
    *                 acceleration of static SQL queries           *
    ****************************************************************
    * PROBLEM DESCRIPTION: After a single REBIND PACKAGE sub-      *
    *                      command specifying multiple packages to *
    *                      rebind (either by using a '*'           *
    *                      specification or a list of packages),   *
    *                      where at least one of the packages      *
    *                      had been previously explicitly bound    *
    *                      with QUERYACCELERATION or               *
    *                      or GETACCELARCHIVE bind options, static *
    *                      SQL queries in one or more packages in  *
    *                      the group were unexpectedly bound for   *
    *                      acceleration, even though the package   *
    *                      had not been previously explicitly      *
    *                      bound with options QUERYACCELERATION or *
    *                      GETACCELARCHIVE.                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    User issued a single REBIND PACKAGE subcommand to rebind
    multiple packages, either thru some form of '*' specification
    (e.g., 'collectionid.*') or thru a list of package names, and
    neither the bind options QUERYACCELERATION nor GETACCELARCHIVE
    were specifed on the REBIND PACKAGE subcommand.
                                                                   .
    However, after the REBIND PACKAGE subcommand completed, the user
    ran one or more of the packages that had been rebound and
    noticed that static SQL queries in some packages were being
    unexpectedly accelerated to an accelerator when run, even though
    the package had not been previously explicitly bound with bind
    option QUERYACCELERATION (or GETACCELARCHIVE).
                                                                   .
    Further investigation showed that for the packages's SYSIBM.
    SYSPACKSTMT entries for these specific queries, column STATUS
    has a value 'O', indicating that the static query was bound for
    acceleration.  However, the SYSACCEL.SYSACCELERATEDPACKAGES
    table does not have an entry for the package itself, indicating
    that the package had not been explicitly bound with
    QUERYACCELERATION.
                                                                   .
    IBM DB2 Development determined that during the REBIND PACKAGE
    for multiple packages, at least one of the packages in the group
    had been previously explicitly bound with bind option
    QUERYACCELERATION (or GETACCELARCHIVE). During the REBIND for
    that package, the package's QUERYACCELERATION (or
    GETACCELARCHIVE) bind option value was copied from the SYSACCEL.
    SYSACCELERATEDPACKAGES entry for that package and stored in
    a DB2 internal control block used during the REBIND PACKAGE
    subcommand. For REBIND of the subsequent packages in the group,
    if the subsequent package had not been previously explicitly
    bound with a QUERYACCELERATION (or GETACCELARCHIVE) bind option
    value, the bind of the static SQL queries in that package
    incorrectly inherited the QUERYACCELERATION behavior from
    the previous package. Thus, resulting in those static SQL
    queries bound for acceleration.  However, there was no
    SYSACCEL.SYSACCELERATEDPACKAGES entry for that package created
    from that REBIND PACKAGE of multiple packages. This means that
    the package itself was not permanently changed to use
    the QUERYACCELERATION (or GETACCELEARCHIVE) bind option.
                                                                   .
    If you experience the scenarios previously described and you see
    for that package SYSIBM.SYSPACKSTMT entries having STATUS='O',
    but you do not see a SYSACCEL.SYSACCELERATEDPACKAGES entry for
    that package, then the problem described here likely applies
                                                                   .
    If one of the packages in the group being rebound had been
    previously bound with QUERYACCELERATION(ALL), one or more of
    the subsequent packages in the REBIND PACKAGE subcommand could
    fail the 'rebind' with SQLCODE -4742.
    
    To circumvent either of the problems previously described,
    simply issue a new REBIND PACKAGE for the individual package
    that you have identified.  The REBIND PACKAGE subcommand should
    not specify QUERYACCELERATION (or GETACCELARCHIVE) bind option.
    Given that no SYSACCEL.SYSACCELERATEDPACKAGES entry was created
    for that package during the previous REBIND PACKAGE for multiple
    packages, the affected package was not permanently changed to
    use QUERYACCELERATION. So this new single REBIND PACKAGE for
    that package  will not use QUERYACCELERATION for the bind of
    the static SQL queries in the package.
                                                                   .
    If more than one package is affected, you can specify those
    packages as a 'list' of packages in the single REBIND PACKAGE
    subcommand only if
     (1) you specify each package as fully-qualified
         collection-id.package-name, without usage of a '*'/wildcard
         specification, and
     (2) NONE of the packages had been previously explicitly bound
         with QUERYACCELERATION bind option
                                                                   .
                                                                   .
    Until you have applied the PTF for this apar, you should avoid
    using a single REBIND PACKAGE subcommand to rebind multiple
    packages if at least one of the packages in the group was
    previously explicitly bound with the QUERACCELERATION bind
    option.
    

Problem conclusion

  • DB2 REBIND code for rebinding multiple packages was corrected
    so that the rebind of a package's static statements does not
    inherit the QUERYACCELERATION or GETACCELARCHIVE bind behavior
    of a previous package in the group that is being rebound.
                                                                   .
    Additional search keywords: IDAAV3R1/K IDAAV4R1/K
                                SQLCODE4742
    

Temporary fix

Comments

APAR Information

  • APAR number

    PI46834

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2015-08-12

  • Closed date

    2015-12-18

  • Last modified date

    2016-02-01

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

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

Modules/Macros

  •    DSNTBBP2
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UI33954

       UP16/01/05 P F601

  • RB10 PSY UI33955

       UP16/01/05 P F601

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

Modified date: 01 February 2016


Translate this page: