PI18965: DB2 10 AND 11 FOR Z/OS NEW FUNCTION

Obtain the fix for this APAR.

APAR status

• Closed as new function.

Error description

• DB2 10 and 11 for z/OS new function
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 for z/OS users of query acceleration *
  ****************************************************************
  * PROBLEM DESCRIPTION: This APAR addresses the following query *
  *                      acceleration items:                     *
  *                                                              *
  *                      (1) This APAR provides new function to  *
  *                          accelerate a static SELECT INTO     *
  *                          statement that is run locally.      *
  *                                                              *
  *                      (2) After a REBIND PACKAGE specifying   *
  *                          QUERYACCELERATION for a native      *
  *                          SQL (or SQLPL ) procedure package,  *
  *                          SQLCODE -313 occurs when a static   *
  *                          query bound for acceleration is run *
  *                          from that SQLPL procedure.          *
  *                                                              *
  *                      (3) This APAR provides support to honor *
  *                          the FETCH FIRST n ROWS ONLY clause  *
  *                          specified in the PREPARE ATTRIBUTES *
  *                          clause for an accelerated dynamic   *
  *                          cursor query. This could improve    *
  *                          the overall performance of this     *
  *                          accelerated dynamic query by        *
  *                          reducing the query's result size.   *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  (1) Acceleration of a static SQL SELECT INTO statement:
  ----------------------------------------------------------------
  With this APAR/PTF you can now bind a static SQL SELECT INTO
  for acceleration to an accelerator.  The SELECT INTO must be
  run locally in order to be accelerated -- a SELECT INTO that is
  to be run remotely at a DB2z server will not be accelerated when
  it is run.
                                                                 .
  As with the existing support for static cursor query
  acceleration, the existing bind options QUERYACCELERATION and
  GETACCELARCHIVE are used to bind the static SELECT INTO for
  acceleration. Please see the existing DB2 documentation for more
  details describing the usage of these bind options for query
  acceleration.  To bind a SELECT INTO for acceleration,
  a package must be bound or rebound 'after' application of this
  PTF. For an SQL PL routine containing the SELECT INTO, instead
  of REBIND PACKAGE you must use ALTER PROCEDURE or ALTER FUNCTION
  specifying option QUERY ACCELERATION.
  Packages or SELECT INTO statements previously bound before
  this PTF is applied are not affected and those SELECT INTO
  statements are not accelerated.
                                                                 .
  After application of this PTF, if a BIND or REBIND PACKAGE with
  QUERYACCELERATION (or an ALTER PROCEDURE/FUNCTION with
  QUERY ACCELERATION for SQL PL routine) is done for application
  containing a SELECT INTO that is to be run remotely at a DB2z
  server, the SELECT INTO is bound for acceleration during BIND or
  REBIND PACKAGE time (or ALTER PROCEDURE/FUNCTION time).
  However, when that SELECT INTO is run remotely, DB2 will fail it
  with SQLCODE -4742 and reason code 23.
                                                                 .
  This PTF adds new reason code 23 for existing SQLCODE -4742
  as follows:
    23
         The SELECT INTO is bound for acceleration but is run as
         a remote SELECT INTO, which is not supported for
         acceleration.
                                                                 .
    Programmer Response:
    23   Remove the SELECT INTO statement, or REBIND the package
         with bind option QUERYACCELERATION(NONE).
         If the program is an SQL PL procedure or function, use
         one of the following alternatives instead of using BIND
         or REBIND PACKAGE:
          - Alter the procedure or function by using ALTER
            PROCEDURE or ALTER FUNCTION
          - Drop and then re-create the procedure or function by
            using CREATE PROCEDURE or CREATE FUNCTION
                                                                 .
                                                                 .
  This PTF also provides support for 'accelerator modeling' for
  SELECT INTO. Prior to this PTF, accelerator modeling shows
  zeroes for "accelerator eligible execution-time elapsed and
  CPU times" in the accounting trace IFCID 3 for a SELECT INTO
  that is run, which is correct because SELECT INTO could not be
  accelerated.
  Now, after application of this PTF, if the existing DB2
  subsystem parameter ACCELMODEL=YES then a new BIND or REBIND
  PACKAGE (or new ALTER PROCEDURE/FUNCTION for SQL PL) will enable
  an eligible SELECT INTO for 'accelerator modeling' as it is run
  in DB2. If that SELECT INTO is run locally and is eligible for
  acceleration, then IFCID 3 will show non-zero values for
  "accelerator eligible elapsed and CPU times".
  However, if the SELECT INTO is run remotely, then IFCID 3 will
  continue to show zeroes for those fields because a SELECT INTO
  that is run remotely cannot be accelerated.
                                                                 .
  Note that 'accelerator modeling' does not accelerate a query to
  an accelerator -- the query runs only in DB2.
  ================================================================
                                                                 .
                                                                 .
  (2) After REBIND PACKAGE with bind option QUERYACCELERATION for
      a native SQL procedure (SQL PL procedure) package, a static
      query bound for acceleration gets SQLCODE -313 when
      the procedure is invoked:
  ----------------------------------------------------------------
  For a native SQL procedure (or SQL PL procedure), REBIND PACKAGE
  with QUERYACCELERATION for the procedure's package does not
  perform all the necessary steps to bind a static query for
  acceleration -- this is by design and expected. Instead,
  an ALTER PROCEDURE specifying QUERY ACCELERATION is required.
  As with attempts to use many other bind options on a REBIND
  PACKAGE for an SQL PL procedure package, an attempt to specify
  bind option QUERYACCELERATION on the REBIND should fail with
  existing message DSNT215.  Instead, in this case DB2 accepted
  the REBIND PACKAGE with QUERYACCELERATION specified and rebound
  the SQL PL package, but, as expected, without completing all the
  steps necessary for binding a static query for acceleration in
  an SQL PL procedure. As a result, when the procedure was invoked
  and DB2 accelerated the query, SQLCODE -313 occurred.
                                                                 .
  With this PTF, DB2 now correctly fails the REBIND PACKAGE with
  QUERYACCELERATION for an SQL PL package and issues message
  DSNT215 as expected. Note that this problem and fix also apply
  to a compiled SQL scalar function (or SQL PL function or UDF ).
  An ALTER PROCEDURE or ALTER FUNCTION should be used instead.
                                                                 .
  This problem and restriction is specific to REBIND PACKAGE for
  SQL PL routine packages, and does not occur for REBIND PACKAGE
  of a regular DB2 package where QUERYACCELERATION bind option is
  supported.
  ================================================================
                                                                 .
                                                                 .
  (3) Honor FETCH FIRST n ROWS ONLY clause in PREPARE ATTRIBUTES
      for an accelerated dynamic query:
  ----------------------------------------------------------------
  This PTF also provides the support to honor the FETCH FIRST n
  ROWS ONLY clause specified in the PREPARE ATTRIBUTES for
  an accelerated dynamic cursor query. Prior to this PTF, for
  an accelerated dynamic query only the FETCH FIRST clause
  specified in the query text itself was honored, and a FETCH
  FETCH FIRST clause specified in the PREPARE ATTRIBUTES clause
  was ignored.  So in the PREPARE ATTRIBUTES case all rows
  satisfying the accelerated query predicate were returned from
  the accelerator, instead of the number of rows specified in
  the FETCH FIRST clause of the ATTRIBUTES.  Now, by honoring
  the ATTRIBUTES FETCH FIRST clause, as we honor FETCH FIRST
  specified in the query text, the result size returned from this
  case of accelerated query is reduced according to the number
  of rows specified in the FETCH FIRST. This can improve
  the overall performance of the accelerated dynamic query that
  uses PREPARE ATTRIBUTES to specify FETCH FIRST n ROWS ONLY.
                                                                 .
  If you are using a tool to submit your dynamic query to DB2 for
  acceleration and that tool specifies FETCH FIRST n ROWS ONLY in
  the PREPARE ATTRIBUTES clause, then after application of this
  PTF fewer rows may now be returned by the query and available to
  your application when compared to behavior 'before' this PTF.
                                                                 .
  Note again that prior to this PTF, a FETCH FIRST n ROWS ONLY
  clause specified in the query text itself is already honored for
  an accelerated query.
  

Problem conclusion

Temporary fix

Comments

• Additional search keywords:  IDAAV3R1/K IDAAV4R1/K SQLQUERY
                               SQLFFNR SQLNATIVESQLPL
                               SQLPROCEDURE SQLSCALARUDF SQLUDF
                               SQLCODE313 SQLCODE4742 MSGDSNT215I
  

APAR Information

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

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

  UI19543 UI19544

Modules/Macros

• DSNTBAB2 DSNTBBP2 DSNTBRB2 DSNXEAAL DSNXEPP  DSNXERT  DSNXERT2
  DSNXODML DSNXONZC DSNXONZO
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UI19543

     UP14/07/29 P F407

• RB10 PSY UI19544

     UP14/07/29 P F407

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.