IBM Support

PM95612: NEW FUNCTION FOR DB2 11 FOR Z/OS (PART 3)

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 11 FOR Z/OS (PART 3)
    

Local fix

  • n/a
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All users of DB2 11 for z/OS who have or are *
    *                 interested in the IBM DB2 Analytics          *
    *                 Accelerator and who use static SQL queries   *
    ****************************************************************
    * PROBLEM DESCRIPTION: DB2 11 for z/OS new function support    *
    *                      for the DB2 acceleration of static SQL  *
    *                      queries on the IBM DB2 Analytics        *
    *                      Accelerator Version 4 is now added to   *
    *                      DB2 via APARs PM95610 PM95611 PM95612 . *
    *                                                              *
    *                      This DB2 new function support also      *
    *                      requires the following IBM DB2          *
    *                      Analytics Accelerator Version 4 new     *
    *                      function APARs:                         *
    *                      PM92398 PM92399 PM92400 PM92402         *
    *                      PM92403 PM92404 PM92405                 *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    // DB2 Acceleration of Static SQL Queries                     //
                                                                   .
    Previously, DB2 11 for z/OS only supported acceleration of
    dynamic SQL queries. With these new function APARs, DB2 11 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 satisfies 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 satisfy 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 .
        If the static query did not qualify for acceleration during
        the BIND/REBIND, the DSN_QUERYINFO_TABLE columns QI_DATA and
        REASON_CODE will have a special reason text and reason code
        respectively, explaining why, 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) Just as for acceleration of the SELECT in a dynamic SQL
        INSERT from SELECT, the existing DB2 zparm
        QUERY_ACCEL_OPTIONS option #2 must be YES in order to
        bind the SELECT of a static INSERT from SELECT for query
        acceleration. Option #2 is specifically for acceleration of
        the SELECT of an SQL INSERT from SELECT statement.
                                                                   .
    (4) 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, for execution of the bound query
        you will get unexpected failures or DB2 application abends,
        and for a REBIND in this situation the query will not be
        bound for acceleration.
                                                                   .
    ----------------------------------------------------------------
    Please see the HOLD DOC information in these APARs/PTFs for
    the following new messages and modified SQLCODE related to this
    new function acceleration of static SQL queries:
      - SQLCODE -4746  (new reason codes)
      - DSNT320I DSNT321I DSNT322I DSNT323I DSNT324I
    ----------------------------------------------------------------
    

Problem conclusion

Temporary fix

Comments

  • Additional search keywords:  IDAAV4R1/K SQLPROCEDURE SQLCODE4746
    MSGDSNT320I MSGDSNT321I MSGDSNT322I MSGDSNT323I MSGDSNT324I
    

APAR Information

  • APAR number

    PM95612

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    B10

  • Status

    CLOSED UR1

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-08-21

  • Closed date

    2013-12-13

  • Last modified date

    2014-02-05

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

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

    UI13530

Modules/Macros

  • DSNXEAAL DSNXEBPG DSNXECL  DSNXECLF DSNXECW  DSNXECWA DSNXECWU
    DSNXEDP  DSNXEMG1 DSNXEPP  DSNXERB  DSNXERBP DSNXERT  DSNXERT2
    DSNXESX4 DSNXGRTM DSNXODML DSNXOD5  DSNXOEX1 DSNXOFF  DSNXOIN
    DSNXOLTD DSNXONZA DSNXONZB DSNXONZC DSNXONZH DSNXONZO DSNXOOP
    DSNXOOS1 DSNXOP1  DSNXOTF  DSNXOW1  DSNXOYPB DSNXRACC DSNXRFFL
    DSNXRIWS DSNXROH1
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RB10 PSY UI13530

       UP14/01/10 P F401

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

Reference #: PM95612

Modified date: 05 February 2014


Translate this page: