IBM Support

PI18965: DB2 10 AND 11 FOR Z/OS NEW FUNCTION

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as new function.

Error description

  • DB2 10 and 11 for z/OS new function
    

Local fix

  • N/A
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 for z/OS users of query acceleration *
    ****************************************************************
    * PROBLEM DESCRIPTION: This APAR addresses the following query *
    *                      acceleration items:                     *
    *                                                              *
    *                      (1) This APAR provides new function to  *
    *                          accelerate a static SELECT INTO     *
    *                          statement that is run locally.      *
    *                                                              *
    *                      (2) After a REBIND PACKAGE specifying   *
    *                          QUERYACCELERATION for a native      *
    *                          SQL (or SQLPL ) procedure package,  *
    *                          SQLCODE -313 occurs when a static   *
    *                          query bound for acceleration is run *
    *                          from that SQLPL procedure.          *
    *                                                              *
    *                      (3) This APAR provides support to honor *
    *                          the FETCH FIRST n ROWS ONLY clause  *
    *                          specified in the PREPARE ATTRIBUTES *
    *                          clause for an accelerated dynamic   *
    *                          cursor query. This could improve    *
    *                          the overall performance of this     *
    *                          accelerated dynamic query by        *
    *                          reducing the query's result size.   *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    (1) Acceleration of a static SQL SELECT INTO statement:
    ----------------------------------------------------------------
    With this APAR/PTF you can now bind a static SQL SELECT INTO
    for acceleration to an accelerator.  The SELECT INTO must be
    run locally in order to be accelerated -- a SELECT INTO that is
    to be run remotely at a DB2z server will not be accelerated when
    it is run.
                                                                   .
    As with the existing support for static cursor query
    acceleration, the existing bind options QUERYACCELERATION and
    GETACCELARCHIVE are used to bind the static SELECT INTO for
    acceleration. Please see the existing DB2 documentation for more
    details describing the usage of these bind options for query
    acceleration.  To bind a SELECT INTO for acceleration,
    a package must be bound or rebound 'after' application of this
    PTF. For an SQL PL routine containing the SELECT INTO, instead
    of REBIND PACKAGE you must use ALTER PROCEDURE or ALTER FUNCTION
    specifying option QUERY ACCELERATION.
    Packages or SELECT INTO statements previously bound before
    this PTF is applied are not affected and those SELECT INTO
    statements are not accelerated.
                                                                   .
    After application of this PTF, if a BIND or REBIND PACKAGE with
    QUERYACCELERATION (or an ALTER PROCEDURE/FUNCTION with
    QUERY ACCELERATION for SQL PL routine) is done for application
    containing a SELECT INTO that is to be run remotely at a DB2z
    server, the SELECT INTO is bound for acceleration during BIND or
    REBIND PACKAGE time (or ALTER PROCEDURE/FUNCTION time).
    However, when that SELECT INTO is run remotely, DB2 will fail it
    with SQLCODE -4742 and reason code 23.
                                                                   .
    This PTF adds new reason code 23 for existing SQLCODE -4742
    as follows:
      23
           The SELECT INTO is bound for acceleration but is run as
           a remote SELECT INTO, which is not supported for
           acceleration.
                                                                   .
      Programmer Response:
      23   Remove the SELECT INTO statement, or REBIND the package
           with bind option QUERYACCELERATION(NONE).
           If the program is an SQL PL procedure or function, use
           one of the following alternatives instead of using BIND
           or REBIND PACKAGE:
            - Alter the procedure or function by using ALTER
              PROCEDURE or ALTER FUNCTION
            - Drop and then re-create the procedure or function by
              using CREATE PROCEDURE or CREATE FUNCTION
                                                                   .
                                                                   .
    This PTF also provides support for 'accelerator modeling' for
    SELECT INTO. Prior to this PTF, accelerator modeling shows
    zeroes for "accelerator eligible execution-time elapsed and
    CPU times" in the accounting trace IFCID 3 for a SELECT INTO
    that is run, which is correct because SELECT INTO could not be
    accelerated.
    Now, after application of this PTF, if the existing DB2
    subsystem parameter ACCELMODEL=YES then a new BIND or REBIND
    PACKAGE (or new ALTER PROCEDURE/FUNCTION for SQL PL) will enable
    an eligible SELECT INTO for 'accelerator modeling' as it is run
    in DB2. If that SELECT INTO is run locally and is eligible for
    acceleration, then IFCID 3 will show non-zero values for
    "accelerator eligible elapsed and CPU times".
    However, if the SELECT INTO is run remotely, then IFCID 3 will
    continue to show zeroes for those fields because a SELECT INTO
    that is run remotely cannot be accelerated.
                                                                   .
    Note that 'accelerator modeling' does not accelerate a query to
    an accelerator -- the query runs only in DB2.
    ================================================================
                                                                   .
                                                                   .
    (2) After REBIND PACKAGE with bind option QUERYACCELERATION for
        a native SQL procedure (SQL PL procedure) package, a static
        query bound for acceleration gets SQLCODE -313 when
        the procedure is invoked:
    ----------------------------------------------------------------
    For a native SQL procedure (or SQL PL procedure), REBIND PACKAGE
    with QUERYACCELERATION for the procedure's package does not
    perform all the necessary steps to bind a static query for
    acceleration -- this is by design and expected. Instead,
    an ALTER PROCEDURE specifying QUERY ACCELERATION is required.
    As with attempts to use many other bind options on a REBIND
    PACKAGE for an SQL PL procedure package, an attempt to specify
    bind option QUERYACCELERATION on the REBIND should fail with
    existing message DSNT215.  Instead, in this case DB2 accepted
    the REBIND PACKAGE with QUERYACCELERATION specified and rebound
    the SQL PL package, but, as expected, without completing all the
    steps necessary for binding a static query for acceleration in
    an SQL PL procedure. As a result, when the procedure was invoked
    and DB2 accelerated the query, SQLCODE -313 occurred.
                                                                   .
    With this PTF, DB2 now correctly fails the REBIND PACKAGE with
    QUERYACCELERATION for an SQL PL package and issues message
    DSNT215 as expected. Note that this problem and fix also apply
    to a compiled SQL scalar function (or SQL PL function or UDF ).
    An ALTER PROCEDURE or ALTER FUNCTION should be used instead.
                                                                   .
    This problem and restriction is specific to REBIND PACKAGE for
    SQL PL routine packages, and does not occur for REBIND PACKAGE
    of a regular DB2 package where QUERYACCELERATION bind option is
    supported.
    ================================================================
                                                                   .
                                                                   .
    (3) Honor FETCH FIRST n ROWS ONLY clause in PREPARE ATTRIBUTES
        for an accelerated dynamic query:
    ----------------------------------------------------------------
    This PTF also provides the support to honor the FETCH FIRST n
    ROWS ONLY clause specified in the PREPARE ATTRIBUTES for
    an accelerated dynamic cursor query. Prior to this PTF, for
    an accelerated dynamic query only the FETCH FIRST clause
    specified in the query text itself was honored, and a FETCH
    FETCH FIRST clause specified in the PREPARE ATTRIBUTES clause
    was ignored.  So in the PREPARE ATTRIBUTES case all rows
    satisfying the accelerated query predicate were returned from
    the accelerator, instead of the number of rows specified in
    the FETCH FIRST clause of the ATTRIBUTES.  Now, by honoring
    the ATTRIBUTES FETCH FIRST clause, as we honor FETCH FIRST
    specified in the query text, the result size returned from this
    case of accelerated query is reduced according to the number
    of rows specified in the FETCH FIRST. This can improve
    the overall performance of the accelerated dynamic query that
    uses PREPARE ATTRIBUTES to specify FETCH FIRST n ROWS ONLY.
                                                                   .
    If you are using a tool to submit your dynamic query to DB2 for
    acceleration and that tool specifies FETCH FIRST n ROWS ONLY in
    the PREPARE ATTRIBUTES clause, then after application of this
    PTF fewer rows may now be returned by the query and available to
    your application when compared to behavior 'before' this PTF.
                                                                   .
    Note again that prior to this PTF, a FETCH FIRST n ROWS ONLY
    clause specified in the query text itself is already honored for
    an accelerated query.
    

Problem conclusion

Temporary fix

Comments

  • Additional search keywords:  IDAAV3R1/K IDAAV4R1/K SQLQUERY
                                 SQLFFNR SQLNATIVESQLPL
                                 SQLPROCEDURE SQLSCALARUDF SQLUDF
                                 SQLCODE313 SQLCODE4742 MSGDSNT215I
    

APAR Information

  • APAR number

    PI18965

  • 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

    2014-05-29

  • Closed date

    2014-07-11

  • Last modified date

    2014-08-04

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

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

    UI19543 UI19544

Modules/Macros

  • DSNTBAB2 DSNTBBP2 DSNTBRB2 DSNXEAAL DSNXEPP  DSNXERT  DSNXERT2
    DSNXODML DSNXONZC DSNXONZO
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UI19543

       UP14/07/29 P F407

  • RB10 PSY UI19544

       UP14/07/29 P F407

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

Modified date: 04 August 2014


Translate this page: