PI72150: DB2 11 FOR Z/OS NEW FUNCTION

Obtain the fix for this APAR.

APAR status

• Closed as new function.

Error description

• DB2 11 for z/OS new function.
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 11 for z/OS users of query           *
  *                 acceleration                                 *
  ****************************************************************
  * PROBLEM DESCRIPTION: Currently, a user cannot specify a      *
  *                      target accelerator for an               *
  *                      accelerated query to be executed. If a  *
  *                      user has multiple accelerators where    *
  *                      the same set of DB2 tables are          *
  *                      accelerated, the accelerator workload   *
  *                      balance algorithm will distribute the   *
  *                      queries depending on queue length on    *
  *                      each accelerator.                       *
  *                                                              *
  *                      This APAR addresses that requirement    *
  *                      by providing the new function support   *
  *                      that allows a user to specify a target  *
  *                      accelerator for accelerated queries.    *
  *                      This APAR requires APAR PI73563 to be   *
  *                      applied together with this APAR.        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  IBM DB2 Analytics Accelerator enables high performance execution
  for complex analytics statements. Currently, a user cannot
  specify a target accelerator for an accelerated query to be
  executed. If a user has multiple accelerators where the same
  set of DB2 tables are accelerated, the accelerator workload
  balance algorithm will distribute the queries depending on queue
  length on each accelerator.
  .
  This APAR helps in the following use cases:
  1. User wants to direct their queries to different accelerators
  depending on the priorities of the workload, with high priority
  queries directed to the fastest, highest capacity accelerator
  and low priority queries directed to a slower accelerator.
  DB2's current acceleration support does not allow this level of
  'directed' control.
  2. User has an additional accelerator on a remote location
  (Disaster Recovery location) in addition to locally deployed
  accelerators. Current algorithm does not take into account
  latency caused by the long network distance and some high
  priority queries are sent to the remote accelerators and incur
  increased elapsed time.
  .
  A new special register and Bind option will allow users to
  direct accelerated queries to a specific accelerator(s).
  .
  (1) CURRENT ACCELERATOR (special register)
  The CURRENT ACCELERATOR special register specifies the name of
  preferred accelerator(s) to which should DB2 send accelerated
  dynamic queries. The special register does not apply to static
  queries.
  When the CURRENT ACCELERATOR special register names eligible
  accelerator servers, DB2 will consider sending eligible
  accelerated queries to one of those servers before other
  accelerator servers. If none of the accelerator servers named
  by CURRENT ACCELERATOR are not available or eligible, DB2 will
  consider other available accelerator servers.
  .
  The data type is VARCHAR(8).
  .
  The initial value of CURRENT ACCELERATOR is determined by
  one of the following settings:
  - If the ACCELERATOR bind option is specified for the
    bind of a package, the default for CURRENT ACCELERATOR is
    the ACCELERATOR bind option value.
    This bind option does not have a default value.
  - Otherwise, the default setting of CURRENT ACCELERATOR is
    blank (no preferred accelerator).
  .
  Example: The following statement sets the CURRENT ACCELERATOR
  special register so that ACCEL1 is the preferred accelerator.
  SET CURRENT ACCELERATOR = ACCEL1;
  .
  Syntax
  .
  >>-SET CURRENT ACCELERATOR--=--+-accelerator-name-+----------><
                                 '--host-variable---'
  .
  1. Nearly all DB2 for z/OS special registers are described as
  being able to be set from a "host-variable".  In practice, this
  really means a "host-variable" or an SQL variable but not a
  global variable.
  .
  Description
  When the CURRENT ACCELERATOR special register names eligible
  accelerator servers, DB2 will consider sending eligible
  accelerated queries to one of those preferred accelerator
  servers before other accelerator servers. If none of the
  accelerator servers named by the CURRENT ACCELERATOR are
  available or eligible, DB2 will consider other available
  accelerator servers.
  .
  The value specified by accelerator-name or host-variable is
  a single accelerator name or an accelerator logical name as
  recorded in SYSIBM.LOCATIONS (the logical name represents one or
  more accelerator server names).
  .
  The following restrictions are in place for the accelerator
  name:
  - It must not consist of more than eight characters.
  - Allowed characters are A-Z, 0-9.
  .
  Defining an accelerator alias enables you to map a logical name
  (alias) to one or more physical accelerator server names that
  are used for a specific DB2 subsystem. A DB2 application can
  then use that alias, which points to accelerator server(s) for
  that DB2 subsystem. You can also set up multiple aliases to
  point to the same accelerator(s), if needed.
  .
  To create an alias for an accelerator:
  Create special rows in the SYSIBM.LOCATIONS communications
  database (CDB) table of one DB2 subsystem. For example:
   INSERT INTO SYSIBM.LOCATIONS (LOCATION, LINKNAME, DBALIAS)
   VALUES ('logical_system_accel_name', 'DSNACCELERATORALIAS',
   'physical_system_accel_name')
  To specify more than one physical accelerator to be represented
  by an alias, concatenate the accelerator names separated by
  whitespace as a string to be inserted as the value for DBALIAS.
  A maximum of 14 accelerator names with a maximum of 8 characters
  for each name can be inserted. The logical name itself should be
  a maximum of 8 characters long, to be compatible with the
  ACCELERATOR special register and Bind option.
  .
  Example 1: The following statement sets the CURRENT ACCELERATOR
  special register so that ACCEL1 is the preferred accelerator.
   SET CURRENT ACCELERATOR = ACCEL1;
  .
  Example 2: The following statements set the CURRENT ACCELERATOR
  special register to the logical name/alias IDAATEST that
  represents 2 accelerators, ACCLPRO1 and ACCLPRO2 :
   INSERT INTO SYSIBM.LOCATIONS (LOCATION, LINKNAME, DBALIAS)
   VALUES (IDAATEST, 'DSNACCELERATORALIAS', 'ACCLPRO1 ACCLPRO2');
  .
   SET CURRENT ACCELERATOR = IDAATEST;
  .
  (2)NEW SQL WARNING
  SQLSTATE 0169D THE ACCELERATOR DOES NOT EXIST.
  SQLCODE +4754 THE ACCELERATOR <accelerator> DOES NOT EXIST.
  Explanation
  A statement referenced an accelerator server that does not
  exist or has not been started yet.  The specified
  accelerator name may not have been defined to DB2 yet or has
  not been started since DB2 was last started.
  .
  <accelerator>
  The name of the accelerator server that does not exist or
  has not been started yet.
  .
  System Action
  Processing continues normally.
  .
  Programmer Response
  No change is required.  The specified accelerator server
  may be defined to DB2 before it is used during the execution
  of accelerated queries that require it.
  However, if the specified accelerator server does not exist
  or is not started when an accelerated query is executed, then
  DB2 ignores the ACCELERATOR special register or bind option at
  that time, including when a logical name or alias is used to
  represent 2 or more accelerator servers and none of the
  accelerator servers are defined or started.
  .
  (3) NEW Reason Code for SQL ERROR -4742
  26: The CREATE TABLE statement clause IN ACCELERATOR specifies
  an accelerator alias that resolves to more than one accelerator.
  
  Programmer response
  
  Use the reason code to determine the cause of the failure, and
  take the appropriate action:
  
  26: Ensure that IN ACCELERATOR clause in the CREATE TABLE
      statement specifies an accelerator alias that resolves to
      only one accelerator.
  .
  (4) ACCELERATOR BIND OPTION
  A new Bind option ACCELERATOR is introduced with this PTF.
  When bind option ACCELERATOR is specified, the bind option
  will:
   - set the target accelerator(s) to be used for static
     statements queries
   - be the default value for the special register if there
     is no SET Statement issued.
  The bind option value, like the special register only affects
  runtime behavior of an accelerated query.
  If one of the specified accelerator(s) does not exist during
  Bind time, then DB2 will issue a warning message and will not
  fail the Bind.
  .
  (5) NEW WARNING FOR BIND COMMAND
  DSNT326I
  csect-name bind-type WARNING FOR PACKAGE = package-name.
  The ACCELERATOR <accelerator> DOES NOT EXIST or has not been
  started yet.
  .
  Explanation
  The BIND or REBIND subcommand referenced an accelerator server
  that does not exist or has not been started yet.  The specified
  accelerator may not have been defined to DB2 yet or has not
  been started since DB2 started.
  .
  csect-name
  The name of the control section that issued the message.
  .
  bind-type
  The type of bind subcommand: BIND or REBIND.
  .
  package-name
  The fully qualified name of the package in the following
  format:
  'location.collection.package.(version)'.
  .
  <accelerator>
  The name of the accelerator server that does not exist or
  has not been started yet.
  .
  System Action
  Processing continues.
  .
  Programmer Response
  No change is required.  The specified accelerator server may be
  defined to DB2 before it is used during the execution of the
  package.
  .
  (6) New Clause for CREATE PROCEDURE (SQL - native),
  ALTER PROCEDURE (SQL - native), CREATE FUNCTION
  (compiled SQL scalar), ALTER FUNCTION
  (compiled SQL scalar)
  >>- ACCELERATOR--=--+-accelerator-name-+---------------><
  .
  specifies an  accelerator that when eligible, DB2 would
  consider sending eligible SQL to that server before other
  accelerator servers.  If the accelerator server named by
  ACCELERATOR is not available, DB2 would consider other
  available accelerator servers.
  .
  RESTRICTIONS
  1. This PTF is only supported on DB2 11 NFM
  2. This PTF is only supported on IDAAV5 and up
  3. DB2 will block flowing the special register value
  to the accelerator in the case where the special register
  is referenced in the query
  4. The following restrictions are in place for the name of
  the accelerator, otherwise SQLCODE -713 will be issued on
  the set statement or an error will be returned on the BIND
  command.
  a. It must be unique within the DB2 subsystem or data sharing
  group.
  b. It must not consist of more than eight characters.
  c. Allowed characters are A-Z, 0-9.
  
  This APAR requires PI73563 to be applied together.
  
  Additional Keywords:
  IDAAV5R1/K  IDAAV6R1/K SQLCODE4754 SQLCODE4742
  

Problem conclusion

Temporary fix

Comments

• DB2 11 FOR Z/OS NEW FUNCTION
  ×**** PE17/08/10 FIX IN ERROR. SEE APAR PI85765  FOR DESCRIPTION
  

APAR Information

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

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

  UI44585

Modules/Macros

• DSNHAPLY DSNHPTAB DSNHSMP2 DSNHSM2V DSNHSM7
  DSNHSYMH DSNHTBLS DSNLXENV DSNLXRCM DSNLXRCS DSNLXRSS DSNLXRWA
  DSNLXSR  DSNLZOSR DSNLZSMT DSNTIJAS DSNXISB6 DSNX8API DSNX8CTG
  DSNX8WAC
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI44585

     UP17/02/28 P F702

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.