PM73634: BLOCK OFFLOAD OF NOT IN SUBQUERIES

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Correct offload of NOT IN subqueries and specific removal of
  redundant predicates for offload .
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All users of DB2 9 for z/OS who              *
  *                 are also using IBM DB2 Analytics             *
  *                 Accelerator                                  *
  ****************************************************************
  * PROBLEM DESCRIPTION: This APAR contains the following fixes  *
  *                      for DB2 accelerator suppot              *
  *                      (1) Correct a problem where a query was *
  *                      allowed to offload for a NOT IN         *
  *                      subquery.                               *
  *                                                              *
  *                      (2) Add a new QUERY_ACCEL_OPTIONS       *
  *                      value of 99. The new option, 99,        *
  *                      is for serviceability only and you      *
  *                      should not use it except when advised   *
  *                      to do so by IBM service.                *
  *                                                              *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  (1) Before this APAR, we allowed offload for a NOT IN subquery
  in certain scenarios.  This could
  cause incorrect output for an offloaded query. The offload can
  occur when one or more NOT IN subqueries is under an OR
  predicate.
  
  SELECT C1
  FROM T1
  WHERE T1.C2> 0
  OR T1.C3 NOT IN
     (SELECT C3
      FROM T2
      WHERE T2.C1>0);
  
  (2) Add a new value of 99 for subsystem parameter
  QUERY_ACCEL_OPTIONS.  The new option, 99, is for serviceability
  only and you should not use it except when advised to do so by
  IBM service.
  

Problem conclusion

• (1) Code is fixed to properly block offload of a NOT IN subquery
  
  (2) The new option of 99 is added to QUERY_ACCEL_OPTIONS.
  The value 99 is for serviceability only and you should
  not use it except when advised to do so by IBM service.
  
  Additional Search Keywords:
  IDAAV2R1/K SQLNOTIN SQLINCORR SQLINCORROUT INCORROUT
  DB2INCORR/K
  

Temporary fix

• *********
  * HIPER *
  *********
  

Comments

APAR Information

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

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

  UK90235

Modules/Macros

• DSNDQWPZ DSNDSPRM DSNWZIF9 DSNXONZC DSNXONZO
  DSNXOW1  DSN6SPRM
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• R910 PSY UK90235

     UP12/12/27 P F212

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.