IBM Support

PI35818: NEW FUNCTION ON DB2 11 FOR Z/OS

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as new function.

Error description

  • New function on DB2 11 for z/OS (part 2)
    
    Additional search keywords:
    Various abends occur in offloading CHAR FORBITDATA and DB2
    terminated with DSNV086E RC00E50702.
    .
    The following ABENDs could happen when query acceleration is set
    to ALL or ELIGIBLE, or CURRENT GET_ACCEL_ARCHIVE is set to YES;
    - ABEND04E RC00E20005 from DSNSVBK OFFSET1AEE trying to free the
     storage of the rewritten offloading query text that contains
      CHAR FOR BIT DATA columns
    - ABEND0C4 RC4 at DSNXODML +A792 or +A7C0
    - ABEND0C4 RC10 at DSNSVBK +133A
    - ABEND0C4 RC38 at DSNSVBK +0FFA
    

Local fix

  • n/a
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All users of DB2 11 for z/OS using query     *
    *                 acceleration                                 *
    ****************************************************************
    * PROBLEM DESCRIPTION: 1.This APAR adds a new feature:         *
    *                        IDT (In-Database Transformation)      *
    *                        to help users take full               *
    *                        advantage of the fast                 *
    *                        speed of an accelerator for           *
    *                        operations which only occur on        *
    *                        an accelerator.                       *
    *                        To achieve this, new table type       *
    *                        AOT (Accelerator-Only Table) is       *
    *                        added to DB2 and the accelerator.     *
    *                        The characteristic of this new        *
    *                        table type is that when CREATE        *
    *                        TABLE is issued, the table will be    *
    *                        created on both DB2 and the           *
    *                        accelerator. However, data only       *
    *                        exists on the accelerator; i.e.,      *
    *                        DML could only be executed on         *
    *                        the accelerator.                      *
    *                      2.Enable offloading of a Temporal       *
    *                        query to an accelerator.              *
    *                        Currently only Business Time          *
    *                        is supported.                         *
    *                      3.Block offloading when the             *
    *                        length of nchar/nvarchar is >         *
    *                        16000 in the converted query.         *
    *                        SQLCODE -4742 with reason code        *
    *                        11 will be issued.                    *
    *                      4.Fix SQLCODE -904 "Invalid SQL         *
    *                        Statement" for an offloaded           *
    *                        query which includes a minus          *
    *                        literal string.                       *
    *                      5.Fix 04E-00E70005 DSNXOMSF:P170.       *
    *                        After the fix, SQLCODE -4742          *
    *                        with reason code 11 is issued         *
    *                        for an unsupported statement          *
    *                        that contains MEDIAN.                 *
    *                      6.Fix ABEND04E RC00E70005               *
    *                        DSNXGRDS.DSNXESX4:M999 during BIND    *
    *                        or REBIND of an application that      *
    *                        contains a static SQL query using a   *
    *                        'data-change-table-reference' as in   *
    *                          SELECT ... FROM FINAL TABLE         *
    *                           ( INSERT | UPDATE | DELETE |       *
    *                             MERGE ... )                      *
    *                        and ALL of the following conditions   *
    *                        apply:                                *
    *                         (1) DB2 subsystem parameter / zparm  *
    *                             DESCSTAT = NO (default is YES)   *
    *                         (2) DB2 subsystem parameter          *
    *                             ACCELMODEL = YES (default is NO) *
    *                         (3) Bind option EXPLAIN(YES) is used *
    *                             (default is EXPLAIN(NO) )        *
    *                                                              *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    An accelerator improves query performance dramatically.
    However, some operations involving huge data movement
    could not take full advantage of the fast speed of
    an accelerator. For example, some user applications
    save the intermediate result set to a table.
    This table is populated by INSERT with SELECT
    and the table will be used for the next
    step e.g.,data transformation. There is no need to backup
    this table as the data can always be recreated from the
    insert statement. With the current DB2 accelerator support,
    an application needs to explicitly invoke a stored
    procedure to create a table in the accelerator, which does
    not work for some tool-generated statements. In addition,
    only the SELECT part of the INSERT statement can be
    accelerated, which leads to high CPU overhead if the
    SELECT results in a large result set. What's
    more, INSERT occurs on DB2 only, so the subsequent DML
    cannot run in the accelerator because the accelerator does
    not have the data that is newly inserted, or it still needs to
    explicitly invoke the stored procedure to load the data.
    
    This new function APAR now resolves the above scenario with
    the introduction of a new table type: AOT (Accelerator Only
    Table). With the Accelerator Only Table, the whole process
    above is automated and enabled on the accelerator:
     - The table is automatically created in the accelerator when
       the CREATE is issued on DB2.
     - Data is automatically inserted in the accelerator when the
       INSERT is issued on DB2 and the data only exists in the
       accelerator, not on DB2.
     - The query that references the AOT will be accelerated
       (If not, DB2 will issue SQLCODE -4742).
    
    
    Summary of Syntax/Semantics change:
    1. CREATE TABLE ......
                   IN ACCELERATOR <accelerator-name>
     (a). New clause IN ACCELERATOR <accelerator-name> specifies
          that the table is an Accelerator-Only Table.
          "accelerator-name" specifies the accelerator that the
          table will be defined in.
     (b). The DB2 Catalog contains the table and column
          definitions with TYPE 'D'(definition only) for an AOT.
          The data of the table exists only in the
          accelerator, not in DB2.
     (c). When IN ACCELERATOR option is specified, CREATE table
          will also implicitly create the corresponding table in
          the accelerator and populate the DB2
          SYSACCEL.SYSACCELERATEDTABLES pseudo catalog table.
     (d). CURRENT QUERY ACCELERATION special register does not
          need to be set for the successful creation of an AOT.
     (e). When IN ACCELERATOR clause is specified, some options
          of the CREATE TABLE are not allowed. Refer to
          SQL REFERENCE for details.
    2. DROP TABLE
    If the table is defined as Accelerator-Only table,  DROP
    TABLE will also implicitly
      (a) Drop the table from the accelerator.
      (b) Delete the row from SYSACCEL.SYSACCELERATEDTABLES
          pseudo catalog table
      (c) CURRENT QUERY ACCELERATION special register does not
          need to be set for the successful drop of the table.
          The accelerator does not necessarily need to be active
          at the time of DROP.
      (d) DROP table will issue a new warning message +4748 with
          a specific reason code under the following situations:
          Reason 1: A row corresponding to the table does not
                    exist in the SYSACCELERATEDTABLES catalog
                    table
          Reason 2: The accelerator server is not available for
                    one of the following reasons:
                    The accelerator server has not been started.
                    The accelerator server was started in EXPLAIN
                        ONLY mode
                    The accelerator server was started in
                        maintenance mode
                    The accelerator server accelerator does not
                        exist
          Reason 3: A DRDA error was encountered
    
    
    3. DML
    DML that references this table, for example,
    INSERT/UPDATE/DELETE/SELECT
    must be executed in the accelerator, not in DB2. If the
    statement type is not supported by the accelerator or if
    the statement contains any expression that is not
    supported by the accelerator, DB2 will return SQLCODE
    -4742. CURRENT QUERY ACCELERATION cannot be NONE if
    INSERT/UPDATE/DELETE/SELECT references an
    accelerator-only table. Otherwise, SQLCODE -4742 will be
    issued.
    
    4. Utilities: Except for RUNSTATS and REPAIR, the rest of the
    utility statements--for example, LOAD, REORG, UNLOAD, etc.--
    are not allowed on this table. RUNSTATS for Accelerator-Only
    tables will leave all statistics as -1.
    
    5. Accelerator-Only Table is supported in a static
    application.
    
    6. All the existing authorization rules apply and are
    enforced by DB2.
    
    7. Accelerator Alias. An Alias could be created for a
    specific accelerator name.
    
    8. Explain
    New reason code 22
    New value(22) for DSN_QUERYINFO_TABLE columns REASON_CODE
    and QI_DATA are added:
       22 The statement references an accelerator-only table,
          but the statement is not offloadable. For example:
            MERGE statement references an Accelerator-Only table.
            UPDATE or DELETE statement references an
              Accelerator-Only table, but the target table of
              the UPDATE or DELETE is a regular DB2 table:
              UPDATE DB2_TABLE SET ..(SELECT .. FROM
              ACCEL_ONLY_TABLE)
            INSERT, UPDATE or DELETE of a row expression with
              subselect.
              UPDATE ACCEL_ONLY_TABLE SET (C1, C2) =
                (SELECT  C3, C4 FROM TABLE2);
    
    When the target table of INSERT/UPDATE/DELETE is an
    Accelerator-Only Table, the plan table contains one row
    with ACCESSTYPE as 'A'.
    
    For EXPLAIN of a statement that references an
    Accelerator-Only Table and is not eligible for query
    acceleration, the plan table output will be similar
    to the above examples, except ACCESSTYPE will have
    a value of blank, and for a SELECT  statement,
    QBLOCK_TYPE='PRUNED'.
    
    
    9. Catalog statistics
    RUNSTATS for an Accelerator-Only Table will leave all
    statistics as -1.
    The catalog statistics fields will need to be modified
    to reflect this. The following is an example.
       NPAGES: Total number of pages that include rows of the
               table. The value is -1 if statistics have not
               been gathered, or the row describes a view, an
               alias, a created temporary table, an
               Accelerator-Only table or an auxiliary table.
               This column can be updated.
       PCTPAGES: .....
               assigned to the table. The value is -1 if
               statistics have not been gathered, or the row
               describes a view, alias, created temporary table,
               an Acceleraor-Only table or auxiliary table.
               This column can be updated.
    
    Additional Keywords:
    IDAAV4R1/K SQLCODE904 SQLCODE4742
    SQLMEDIAN  SQLFINALTABLE  SQLEXPLAIN
    ABEND04E RC00E70005 DSNXOMSF P170
    ABEND04E RC00E70005 DSNXESX4 M999
    

Problem conclusion

Temporary fix

Comments

  • This APAR (PI35818) requires APARs
    PI35817, PI35819, PI35820 and PI35821.  The 5 combined
    APARs provide new support for IDT for query acceleration.
    

APAR Information

  • APAR number

    PI35818

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    B10

  • Status

    CLOSED UR1

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    YesSpecatt / New Function

  • Submitted date

    2015-02-25

  • Closed date

    2015-07-01

  • Last modified date

    2015-12-07

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

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

    UI29037

Modules/Macros

  •    DSNGDBRT DSNHAPLY DSNHPTAB DSNHSM5  DSNHSM6A
    DSNHSYMH DSNUEXDB DSNXEAAL DSNXEBPG DSNXECL  DSNXECLF DSNXECNT
    DSNXECPD DSNXECW  DSNXECWA DSNXECWU DSNXEDC4 DSNXEDP  DSNXEDSC
    DSNXEDS1 DSNXEPM  DSNXEPP  DSNXERBP DSNXERT  DSNXERT2 DSNXESET
    DSNXIAB9 DSNXIAPR DSNXIATB DSNXIATS DSNXICB3 DSNXICIX DSNXICPR
    DSNXICSY DSNXICTB DSNXICTR DSNXIDCL DSNXIDPR DSNXIDTB DSNXIRTB
    DSNXISB1 DSNXISB6 DSNXISB8 DSNXISUB DSNXI14  DSNXI15  DSNXODML
    DSNXOD1  DSNXOD3  DSNXOD5  DSNXOEX1 DSNXOGP  DSNXOIN  DSNXOIWS
    DSNXOLTD DSNXOMQT DSNXONZA DSNXONZB DSNXONZC DSNXONZH DSNXONZO
    DSNXONZQ DSNXONZS DSNXOOP  DSNXOOS1 DSNXOP0  DSNXORFN DSNXOTG
    DSNXOTL  DSNXOVD  DSNXOW1  DSNXRCUF DSNXROE9 DSNXROHE DSNXROHS
    DSNXROS9 DSNXRRTN
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RB10 PSY UI29037

       UP15/07/17 P F507

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

Modified date: 07 December 2015


Translate this page: