IBM Support

PM93789: NEW FUNCTION FOR DB2 10 FOR Z/OS (PART 4)

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as new function.

Error description

  • New function for DB2 10 for z/OS (part 4)
    

Local fix

  • n/a
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 10 for z/OS users who have or are    *
    *                 interested in the IBM DB2 Analytics          *
    *                 Accelerator and who use either of the        *
    *                 following DB2 functions:                     *
    *                   - ROWSET queries and multi-row FETCH       *
    *                   - static SQL queries                       *
    ****************************************************************
    * PROBLEM DESCRIPTION: DB2 10 for z/OS new function support    *
    *                      for the following features for query    *
    *                      acceleration on the IBM DB2 Analytics   *
    *                      Accelerator Version 4 is now added to   *
    *                      DB2 via APARs PM93786 PM93787 PM93788   *
    *                      and PM93789 :                           *
    *                      (1) limited DB2 ROWSET fetching         *
    *                          (aka multiple-row fetching / MRF )  *
    *                          of local dynamic or static          *
    *                          accelerated queries                 *
    *                      (2) DB2 acceleration of static SQL      *
    *                          queries                             *
    *                      These new DB2 features  also require    *
    *                      the following IBM DB2 Analytics         *
    *                      Accelerator Version 4 new function      *
    *                      APARs: PM92398 PM92399 PM92400 PM92402  *
    *                             PM92403 PM92404 PM92405          *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    ----------------------------------------------------------------
    // DB2 ROWSET or Multiple-Row Fetching of Accelerated Queries //
                                                                   .
    DB2 ROWSET or multiple-row fetching uses a cursor defined as
    WITH ROWSET POSITIONING and an SQL FETCH that specifies rowset-
    positioning. Prior to these APARs, DB2 only supported ROWSET
    fetching for non-accelerated queries.  With these APARs, DB2 10
    extends limited multiple-row fetching to accelerated local
    dynamic or static queries, in order to support high performance
    access to accelerated query data.
                                                                   .
    For this limited multiple-row fetch support for accelerated
    local queries, the following restrictions apply to the FETCH
    statement used for the accelerated ROWSET query:
      - All FETCH requests must be rowset fetches
      - All FETCH requests must specify a FOR N ROWS clause
      - All FETCH requests must specify the same rowset size
      - All FETCH requests must specify target host variables
        (that is, the FETCH cannot be targetless for positioning)
                                                                   .
    A remote ROWSET query (where DB2 for z/OS is the remote server
    receiving the query request from a client) does not qualify for
    query acceleration. Acceleration of ROWSET queries is only
    extended to local queries.
    ----------------------------------------------------------------
    ----------------------------------------------------------------
    // DB2 Acceleration of Static SQL Queries                     //
                                                                   .
    Previously, DB2 10 for z/OS only supported acceleration of
    dynamic SQL queries. With these new function APARs, DB2 10 now
    also supports acceleration of static SQL queries in DB2
    applications by using the following new bind options and values
    for DB2 packages:
      QUERYACCELERATION (NONE | ENABLE | ENABLEWITHFAILBACK |
                         ELIGIBLE | ALL)
                                                                   .
      GETACCELARCHIVE (NO | YES)
                                                                   .
    As with existing dynamic SQL query acceleration, these new
    acceleration bind options only apply to cursor queries and
    the SELECT portion of the SQL INSERT from SELECT statement.
    The static SQL SELECT INTO statement cannot be bound for
    query acceleration.
                                                                   .
    The new acceleration bind options are supported on the follow-
    ing DSN subcommands and bind operations:
     - BIND/BIND COPY/REBIND  PACKAGE (local and remote binds)
     - BIND DEPLOY (for SQLPL procedure)
     - SQL ALTER/CREATE PROCEDURE for native SQLPL procedure and
       SQL ALTER/CREATE FUNCTION for SQLPL scalar function, using
       the following SQL syntax:
         QUERY ACCELERATION  NONE | ENABLE | ENABLE WITH FAILBACK |
                             ELIGIBLE | ALL
                                                                   .
         GET_ACCEL_ARCHIVE  NO | YES
                                                                   .
                                                                   .
    The new bind options are not supported for either CREATE/ALTER
    TRIGGER or REBIND TRIGGER PACKAGE.
                                                                   .
    The functions and behavior of these acceleration bind options
    for static SQL queries are the same as the comparable existing
    DB2 special registers CURRENT QUERY ACCELERATION and
    CURRENT GET_ACCEL_ARCHIVE that are used for dynamic query
    acceleration, except the DB2 decision to accelerate a static
    query and bind it for acceleration occurs at BIND/REBIND PACKAGE
    time, not at query execution when the application is run.
    The description of the new bind options and their values is as
    follows:
                                                                   .
    QUERYACCELERATION
                                                                   .
      NONE   -  No static SQL query in the application is bound for
                acceleration or will be accelerated when
                the application is run.
                                                                   .
      ENABLE -  A static SQL query is bound for acceleration if
                it satisfies the acceleration criteria, including
                the cost and heuristics criteria, and the query is
                routed to an accelerator when the application is
                run. Otherwise, if the static query does not satisfy
                the acceleration criteria, then the query is bound
                for execution in DB2.
                                                                   .
                If an error condition such as one of the following
                examples occurs while executing the accelerated
                static query when the application is run, then DB2
                will fail the static query and return a negative
                SQL code to the application:
                  - if a failure occurs while executing the static
                    query on the accelerator
                  - the accelerator returns an error for the query
                  - the accelerator is not started and DB2 cannot
                    route the static query to the accelerator for
                    execution
                                                                   .
      ENABLE WITH FAILBACK  -
                Same as ENABLE behavior, except if one of the error
                condition examples listed occurs on first OPEN of
                the accelerated static query when the application is
                run, then instead of failing the static query and
                returning a negative SQL code to the application,
                DB2 will 'failback' the static query to DB2 by
                performing a temporary 'statement-level' incremental
                bind of the query and running the query in DB2.
                The application does not see the acceleration
                failure.
                                                                   .
                No query 'failback' to DB2 is possible after
                the application does a successful OPEN for the query
                on the accelerator.
                                                                   .
      ELIGIBLE  -  A static SQL query is bound for acceleration if
                the query meets the basic acceleration criteria,
                regardless of cost or heuristics criteria, and
                the query will be routed to the accelerator when
                the application is run.
                                                                   .
      ALL    -  ALL static SQL queries in the application are to be
                bound for acceleration and routed to the accelerator
                when the application is run. If DB2 determines that
                a static query cannot be bound to run on
                the accelerator and the query references a user base
                table/view, DB2 fails the BIND/REBIND PACKAGE
                operation with an error message for that query.
                (A failure exception is made for Declared Global
                Temporary Tables (DGTTs) and Created Global Temp
                Tables (CGTTs) because these tables cannot be
                accelerated).
                                                                   .
      Except as noted for ENABLE WITH FAILBACK, if an error
      condition occurs while executing the accelerated static
      query when the application is run, DB2 will fail the static
      query and return a negative SQL code to the application.
                                                                   .
                                                                   .
    GETACCELARCHIVE
                                                                   .
      NO  - No static SQL query is bound to retrieve archived data
            from the accelerator. If the static query is also not
            bound for acceleration, then the query will be bound
            to run in DB2. If the static query is bound for
            acceleration because bind option QUERYACCELERATION was
            specified, then when the application is run the query
            will be routed to the accelerator, but the query will
            only retrieve 'active' data on the accelerator and
            no archived data on the accelerator will be retrieved.
                                                                   .
      YES - If bind option QUERYACCELERATION is also specified and
            the static query references an accelerated table that
            has partitioned data archived on an accelerator, and
            the static query statisfies the acceleration criteria
            (as specified by QUERYACCELERATION), then the query is
            bound for acceleration and when the application is run
            the query retrieves the archived data on
            the accelerator.
                                                                   .
            If the static query cannot be bound for acceleration
            because it does not statisfy the criteria according to
            the QUERYACCELERATION specified, then DB2 will fail
            the BIND/REBIND PACKAGE operation with an error
            message for that query.
                                                                   .
    The default value for both new bind options is  option not
    specified . The default for the bind options are NOT taken
    from the comparable DB2 installation system parameters
    (zparms) QUERY_ACCELERATION and GET_ACCEL_ARCHIVE.
                                                                   .
    For a successful bind using the new accelerated bind options,
    the bind option values are NOT stored in DB2 catalog table
    SYSIBM.SYSPACKAGE. Instead, the new bind option values are
    stored in new DB2 table SYSACCEL.SYSACCELERATEDPACKAGES that
    the user must create using DB2-supplied DDL shipped in these
    APARs. This table is required before using the acceleration
    bind options. More details are provided below under
    "Setup required for static SQL query acceleration".
                                                                   .
    The bind options can also be used for acceleration of dynamic
    queries. If the bind options are specified, when the application
    is run the bind options will also set the initial values of
    the comparable acceleration special registers CURRENT QUERY
    ACCELERATION and CURRENT GET_ACCEL_ARCHIVE if the special
    registers have not already been explicitly SET prior to that
    application being invoked.  Those special register values
    will then be used for dynamic queries in that application
    so you do not need to change the application by adding an
    explicit SET statement for the acceleration special register(s).
    The special registers do not apply for static queries.
    Precedence order (low to high) for setting the initial value of
    the special registers is as follows:
     (1) DB2 Installation system parameters (QUERY_ACCELERATION and
         GET_ACCEL_ARCHIVE)
     (2) Bind option value if specified (QUERYACCELERATION and
         GETACCELARCHIVE)
     (3) explicit SET statement for the special register
                                                                   .
    ----------------------------------------------------------------
    Important usage notes to remember for static SQL query
    acceleration:
    (1) Before doing the BIND/REBIND PACKAGE (or CREATE/ALTER
        PROCEDURE/FUNCTION) with the acceleration bind options to
        bind static SQL queries for acceleration, you must
         - first accelerate the referenced DB2 table(s) to
           an accelerator on IBM DB2 Analytics Accelerator,
         - then you BIND/REBIND the query's package specifying
           the desired QUERYACCELERATION behavior to bind that
           static query for acceleration.
        If you want a static query to use data that is archived to
        IBM DB2 Analytics Accelerator, you must
         - first archive at least one partition of the referenced
           DB2 table(s) (and this includes having previously
           accelerated the DB2 table to IBM DB2 Analytics
           Accelerator),
         - then you BIND/REBIND the query's package specifying
           GETACCELARCHIVE(YES) and the desired QUERYACCELERATION
           behavior to bind that query for acceleration and retrieve
           archived data.
                                                                   .
        These steps apply anytime you choose a new static query to
        accelerate or retrieve archived data from IBM DB2 Analytics
        Accelerator.  You must repeat the BIND/REBIND PACKAGE
        (or ALTER PROCEDURE/FUNCTION) to accelerate the static query
        for any newly accelerated/archived table.
                                                                   .
        Note that the accelerator need not be 'started' before doing
        the bind. The validation that the target accelerator for
        the query's accelerated table is started/active is done when
        the static query is run, not at bind package time.
                                                                   .
    (2) For a static query that has been bound for acceleration,
        in the SYSIBM.SYSPACKSTMT entry for the package's query,
        the column STATUS will have the new value 'O' for the
        DECLARE CURSOR, as well as, the OPEN, FETCH, and CLOSE.
        This is one easy way to see static SQL queries that are
        accelerated in a package.
                                                                   .
    (3) If the existing bind option EXPLAIN(YES | ONLY) is specified
        along with the new acceleration bind options, the DB2 bind
        package process will insert a row for the static query in
        the tables DSN_QUERYINFO_TABLE and PLAN_TABLE .
        The DSN_QUERYINFO_TABLE row will have a special reason code
        in column REASON_CODE if the static query did not qualify
        for acceleration, just as is currently done during
        execution-time PREPARE for dynamic queries.
                                                                   .
    (4) Under VALIDATE(RUN) or SQLERROR(CONTINUE), static query that
        requires a 'statement-level' incremental bind when the query
        is executed also will not qualify for acceleration.
                                                                   .
    ----------------------------------------------------------------
    Setup required for static SQL query acceleration after applying
    these APARs to the DB2 subsystem --
                                                                   .
    (1) As with acceleration of dynamic queries and the usage of
        the acceleration special registers, usage of the new bind
        options requires that the DB2 zparm ACCEL be set to a value
        other than NONE to enable the DB2 subsystem for query
        acceleration to the IBM DB2 Analytics Accelerator.
                                                                   .
    (2) In addition, the creation of new user-created DB2 table
        SYSACCEL.SYSACCELERATEDPACKAGES and its two indexes,
        SYSACCEL.DSNACP01 and SYSACCEL.DSNACP02, are required
        before using the new bind options. DB2 supplies the DDL
        needed to create these new SYSACCEL objects in updated job
        DSNTIJAS of these APARs (also in the HOLD instructions).
        If you already accelerate dynamic SQL queries and have
        created the other SYSACCEL objects defined by the DDL in
        DSNTIJAS, you only need to run the DDL to create
        SYSACCEL.SYSACCELERATEDPACKAGES and the two indexes
        SYSACCEL.DSNACP01 and SYSACCEL.DSNACP02 .
        Please see the HOLD for further instructions for this DDL.
                                                                   .
    (3) It is recommended that these PTFs be applied to all DB2
        members of a Data Sharing group before you begin using
        the new acceleration bind options, in case you attempt to
        use or run a package bound with an accelerated static query
        on a DB2 member that does not have these PTFs applied.
        If this scenario occurs, the following are examples of
        existing standard error messages you will receive because
        package is marked with higher functional release dependency
        mark 'P' (even though fallback from DB2 11 for z/OS did not
        occur):
        - For an attempt to run the package (on a DB2 subsystem that
          has the DB2 11 for z/OS fallback SPE applied), SQLCODE904
          with reason code 00E7009E / RC00E7009E is issued by DB2:
          SQLCODE -904
          SQLERRM 00E7009E.00000801.collid.pkgname.contoken
          SQLERRP DSNXEAAL
          SQLERRD -110
          SQLSTATE57011
                                                                   .
        - For an attempt to REBIND the package, message DSNT246I or
          DSNT236I is issued by DB2.
        - For an attempt to BIND COPY the package, message DSNT207I
          or DSNT208I is issued by DB2.
        - For an attempt to run the package on a DB2 subsystem that
          does not have the DB211 fallback SPE applied, the
          following intentional DB2 application abend will occur:
            ABND=04E-00E30068 C=101.SCC -PKGAUTOB
            ABEND04E / AB04E RC00E30068 / 00E30068
    ----------------------------------------------------------------
    Please see the HOLD DOC information in these APARs/PTFs for
    the following new messages and SQLCODEs related to this new
    function support:
      - SQLCODE -4746
      - DSNT320I DSNT321I DSNT322I DSNT323I DSNT324I
    ----------------------------------------------------------------
    

Problem conclusion

Temporary fix

Comments

  • Additional search keywords:  IDAAV4R1/K SQLMULTIROW SQLFETCH
    MULTI-ROW SQLPROCEDURE SQLCODE4746 MSGDSNT236I MSGDSNT246I
    MSGDSNT207I MSGDSNT208I MSGDSNT320I MSGDSNT321I MSGDSNT322I
    MSGDSNT323I MSGDSNT324I
    

APAR Information

  • APAR number

    PM93789

  • 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-07-25

  • Closed date

    2013-11-18

  • Last modified date

    2013-12-02

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

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

Modules/Macros

  •    DSNXEAAL DSNXEBPG DSNXECL  DSNXECLF DSNXECW
    DSNXECWA DSNXECWU DSNXEDP  DSNXEEZT DSNXEMG1 DSNXEPM  DSNXEPP
    DSNXERB  DSNXERBP DSNXERT  DSNXERT2 DSNXESX4 DSNXGRTM DSNXIEPL
    DSNXOBFC DSNXODML DSNXOD5  DSNXOEX1 DSNXOFF  DSNXOIN  DSNXOLTD
    DSNXOMD  DSNXONZA DSNXONZB DSNXONZC DSNXONZH DSNXONZO DSNXOOP
    DSNXOOS1 DSNXOQ2  DSNXOTF  DSNXOW1  DSNXOYPL DSNXRACC DSNXRFF
    DSNXRFN  DSNXROHR HDBAA10J
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UK98716

       UP13/11/28 P F311

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

Modified date: 02 December 2013


Translate this page: