PM72274: CODE TO SUPPORT NEW FUNCTION

Obtain the fix for this APAR.

APAR status

• Closed as new function.

Error description

• Code to support new function.
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All users of DB2 10 for z/OS who use         *
  *                 IBM DB2 Analytics Accelerator                *
  ****************************************************************
  * PROBLEM DESCRIPTION: This APAR provides DB2 10 for z/OS      *
  *                      support for IBM DB2 Analytics           *
  *                      Accelerator V3.  IBM DB2 Analytics      *
  *                      Accelerator V3 introduces the           *
  *                      following new features and              *
  *                      enhancements pertaining to DB2          *
  *                      query offloading:                       *
  *                      1. High Performance Storage Saver       *
  *                         (HPSS) support                       *
  *                      2. New subsystem parameter              *
  *                         GET_ACCEL_ARCHIVE                    *
  *                      3. New value for subsystem parameter    *
  *                         QUERY_ACCEL_OPTIONS                  *
  *                      4. New column ARCHIVE is added to       *
  *                         SYSACCEL.SYSACCELERATEDTABLES        *
  *                      5. DB2 Instrumentation Facility         *
  *                         related to acceleration services     *
  *                         is updated/added for serviceability  *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  This APAR provides DB2 10 for z/OS support for IBM DB2 Analytics
  Accelerator V3.  IBM DB2 Analytics Accelerator V3 introduces
  the following new main features and enhancements pertaining to
  DB2 query offloading:
  1. HPSS support
  2. New subsystem parameter DSN6SPRM.GET_ACCEL_ARCHIVE
  3. New value for subsystem parm DSN6SPRM.QUERY_ACCEL_OPTIONS
  4. New column ARCHIVE is added to SYSACCEL.SYSACCELERATEDTABLES
  5. DB2 Instrumentation Facility related to acceleration services
     is updated/added for serviceability
  
  IBM DB2 Analytics Accelerator V3 is introducing High Performance
  Storage Saver (HPSS) support.  Before HPSS, DB2 keeps all the
  data that are moved into DB2 Analytics Accelerator.  With HPSS,
  the user can choose to only store the active data on DB2 and
  DB2 Analytics Accelerator while leaving the history data on
  DB2 Analytics Accelerator only.
  
  This APAR adds a new DB2 subsystem parameter,
  DSN6SPRM.GET_ACCEL_ARCHIVE.  This parameter specifies the
  default setting of the CURRENT GET_ACCEL_ARCHIVE special
  register.  This is the value used when this special register has
  not been set by means of the SET CURRENT GET_ACCEL_ARCHIVE
  statement.  Valid options are:
    * NO : Specifies that when a table is archived
           in IBM DB2 Analytics Accelerator, the table
           reference does not include the accelerator
           archived data.
           ====> This is the default setting
    * YES: Specifies that when a table is archived in
           IBM DB2 Analytics Accelerator, the table
           reference includes the archived data.
  
  This APAR also adds a new option, 3, to an existing DB2
  subsystem parameter DSN6SPRM.QUERY_ACCEL_OPTIONS.  This
  parameter is used to specify additional types of SQL queries
  to be included in query offloading.  It previously had three
  options:
    * NONE: Query offloading is restricted to the standard SQL
            statements
            ==> This is the default setting.
    * 1   : The queries that include data encoded by
            multi-byte character set EBCDIC encoding
            scheme are not blocked from executing on
            IBM DB2 Analytics Accelerator.
    * 2   : For INSERT statements where a fullselect specifies
            the data to be inserted, the fullselect part is not
            blocked from executing on IBM DB2 Analytics
            Accelerator although the data operated on by the
            fullselect may not be current in the accelerator.
            The fullselect will be considered for offload
            as if it were a stand-alone statement, except that DB2
            will not offload the fullselect if the target
            table of the INSERT has a different encoding scheme
            than any tables referenced in the fullselect.
  
  The new option, 3, specifies that:
    * 3   : The queries that include DB2 byte-based
            string functions on data encoded by
            multi-byte character sets encoding schemes
            (like Unicode) are not blocked from
            executing on IBM DB2 Analytics Accelerator
            although it supports only character-based
            string functions.  If the data on which
            the string function is specified contains
            only single-byte characters, executing
            the function on IBM DB2 Analytics Accelerator
            will return the same result as executing
            the function on DB2 irrespective of what
            encoding scheme is used for the data.
            However, if the data contains multi-byte
            characters, the results will not be the
            same.
  
  NONE remains the default setting for QUERY_ACCEL_OPTIONS and is
  mutually exclusive from all other settings.  Options 1, 2, and 3
  can be specified individually or together.  Examples:
    QUERY_ACCEL_OPTIONS=NONE
    QUERY_ACCEL_OPTIONS=1
    QUERY_ACCEL_OPTIONS=3
    QUERY_ACCEL_OPTIONS=(1,2)
    QUERY_ACCEL_OPTIONS=(1,3)
    QUERY_ACCEL_OPTIONS=(2,3)
  
  This APAR also adds support for a new column, ARCHIVE, to
  SYSACCEL.SYSACCELERATEDTABLES, a table in the accelerator
  database.  This new column indicates whether the table is
  archived in IBM DB2 Analytics Accelerator and what portion
  of the data it contains.  It is set by an IBM DB2 Analytics
  Accelerator stored procedure ACCEL_ARCHIVE_TABLES and used
  by DB2 for query offloading decision making.  Also, the
  lock size of the containing table space, SYSACCEL, is
  changed from PAGE to ROW.
  
  Figure 1:
  =========
  
   Column Name  Data Type  Description
   -----------  ---------  -----------
   ARCHIVE       CHAR(1)   Archive status of the table in the
                           accelerator database.
  
                           A: Table is archived in the
                              accelerator and the corresponding
                              accelerator contains both active
                              and archived data
  
                           C: Table is archived in another
                              accelerator and the corresponding
                              accelerator contains the active
                              (current) data only
  
                           blank: Table is not archived in IBM
                                  DB2 Analytics Accelerator
  
  ARCHIVE is 'A' when a table is archived in the accelerator as
  shown in Figure 1.
  ARCHIVE is set to 'C' after a table is archived on one
  accelerator by invoking IBM DB2 Analytics Accelerator stored
  procedure ACCEL_ARCHIVE_TABLES, and IBM DB2 Analytics
  Accelerator stored procedure ACCEL_LOAD_TABLES is invoked to
  load data from DB2 into another accelerator.
  
  Each table must have at most one row with the value of 'A'.
  If there exists one entry with 'C' for a given table, there
  must also exist an entry with 'A'.  Otherwise, all rows for the
  table must be blank.
  
  There are also some serviceability changes in this APAR:
  
  IFCID 148 field QW0148ST is updated to include the status of
  the thread that is currently processing an activity related
  to query acceleration, using a value of 'AC'.
  
  The mapping of DSNDQWAC will include a new wait time as follows:
  
  QWACAACC              Number of wait trace event processed
                        for requests to an accelerator
  QWACAACW              Accumulated wait time for requests to
                        an accelerator
  
  The mapping of DSNDQPAC will include a new wait time as follows:
  
  QPACAACC             Number of wait trace event processed
                       for requests to an accelerator while
                       executing this package
  QPACAACW             Accumulated wait time for requests to
                       an accelerator while executing this
                       package
  
  These values will be reflected in IFCID 3, IFCID 239, IFCID 148,
  IFCID 147, and IFCID 346.  To trigger the collection of these
  new fields, IFCID 378 will be created to document the beginning
  and IFCID 379 will be created to document the end of an
  accelerator call event.  This record will be included in
  accounting and monitor classes 3 and 8.
  
  ****************************************************************
  * IFCID 0378 marks the beginning of an accelerator call event  *
  ****************************************************************
  *
  QW0378
  QW0378ACL                   Length of Accelerator Name
  QW0378ACN                   Name of Accelerator
  
  ****************************************************************
  * IFCID 0379 marks the end of an accelerator call event        *
  ****************************************************************
  *
  QW0379
  QW0379ACL                   Length of Accelerator Name
  QW0379ACN                   Name of Accelerator
  
  Some Q8ST fields are sometimes zero until the connection is
  terminated.  Q8ST statistics will now be collected more
  frequently so that the statistics data collection is not
  limited to be available only at the end of thread termination.
  
  Some Q8ST fields will become serviceability fields.  Such
  fields in the Q8ST will be marked (S) that were previously
  externalized.  These fields will no longer be formally supported
  so applications relying on their content should be changed.
  See HOLD data for information on these fields.
  
  
  Additional Keywords:
  IDAAV3R1/K  IFCID106 IFCID3 IFCID003 IFCID239 IFCID148
  IFCID147 IFCID346 IFCID378 IFCID379
  

Problem conclusion

Temporary fix

Comments

• Code is added to support IBM DB2 Analytics Accelerator V3
  as described above.  IFCID 106 is updated to report the new
  value of QUERY_ACCEL_OPTIONS and the new parameter
  GET_ACCEL_ARCHIVE.  Serviceability fixes described above are
  added to support the new function.
  ž**** PE13/03/25 FIX IN ERROR. SEE APAR PM84852  FOR DESCRIPTION
  

APAR Information

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

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

  UK83463

Modules/Macros

•    DSN@XAZP DSNB1TAD DSNDQPAC DSNDQWAC DSNDQWPZ
  DSNDQW05 DSNDQ8AC DSNDQ8ST DSNDSPRM DSNHSM2V DSNHSM7  DSNLCDG1
  DSNLCDP1 DSNLCITR DSNLCMBE DSNLCSRR DSNLIAAC DSNLIRCA DSNLISDA
  DSNLTGBK DSNLTSET DSNLXCMT DSNLXCQH DSNLXENV DSNLXHOC DSNLXRBF
  DSNLXRCL DSNLXREL DSNLXRFT DSNLXROP DSNLXRSQ DSNLXRSS DSNLXRUW
  DSNLXSR  DSNLXSSR DSNLZMON DSNLZOSR DSNLZR00 DSNTIDXA DSNTIDXB
  DSNTIJAS DSNTIJUZ DSNTINST DSNTLRAC DSNTXACT DSNTXAZP DSNTXSTB
  DSNVDTA  DSNWAACT DSNWAAPI DSNWACDF DSNWAFRR DSNWARDS DSNWVAPR
  DSNWVINT DSNWVSMT DSNWVSR1 DSNWVZIT DSNWVZPS DSNWVZSA DSNWZIFA
  DSNXECW  DSNXECWA DSNXECWU DSNXEDP  DSNXEDSC DSNXEET  DSNXELX
  DSNXENR  DSNXEPM  DSNXERT  DSNXERT2 DSNXESSR DSNXGRM1 DSNXGRTM
  DSNXOCAS DSNXODML DSNXODTR DSNXOD5  DSNXOEXC DSNXOEX1 DSNXOFF
  DSNXOFN2 DSNXOIN  DSNXOLTD DSNXOMPS DSNXONZA DSNXONZC DSNXONZO
  DSNXONZQ DSNXONZS DSNXOOS1 DSNXOPRU DSNXOST  DSNXOTF  DSNXOV0
  DSNXOV1  DSNXOYDA DSNXOYP1 DSNXRRSP DSNXRSPG DSNXTAD  DSNX8CDA
  DSNX8CMD DSNX8CSA DSNX8CTG DSNX8CXA DSNX8EKG DSNX8INI DSNX8SMF
  DSNX8STA DSN6SPRM
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK83463

     UP12/11/28 P F211

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.