PM89587: SQLCODE805 ON DYN PREPARE OF QUERY FOR IDAA WHEN USING SET CURRENT PACKAGESET AND BIND PLAN W/ PKLIST

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• SQLCODE -805 issued from DB2 csect DSNXEPM with SQLERRD1 = -251
  on an SQL PREPARE of a dynamic query when ALL of the following
  apply:
  1) either special register CURRENT QUERY ACCELERATION or DB2
     Installation parameter (aka zparm) QUERY_ACCELERATION is used
     to accelerate / offload dynamic queries to
     the IBM DB2 Analytics Accelerator (IDAA), and
  2) SET CURRENT PACKAGESET is used to specify the collection-id
     to use for the package, and
  3) BIND PLAN with a PKLIST specification is used where location
     '*' is not used as part of the target collection-id
     entry as follows:
      PKLIST (collid1.*, collid2.*) or
      PKLIST (location-name.collid.*, location-name.collid.*)
     The target collection-id entry is the one for the package
     that is issuing dynamic queries to be accelerated/offloaded
     to IDAA
  

Local fix

• To circumvent the pblm, one of the following can be done:
  - Change the PKLIST specification to use location '*' as
    part of the target collection-id entry as follows, then issue
    the BIND PLAN again:
     BIND PLAN ... PKLIST (*.collid1.*, *.collid2.*)
  - OR, if SET CURRENT PACKAGESET is not necessary, then removing
    that SET will also circumvent the problem, even if you don't
    use location '*' in the PKLIST collection-id entry.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 users of the IBM DB2 Analytics       *
  *                 Accelerator for z/OS, BIND PLAN with PKLIST, *
  *                 and special register CURRENT PACKAGESET      *
  ****************************************************************
  * PROBLEM DESCRIPTION: When using the IBM DB2 Analytics        *
  *                      Accelerator (IDAA) to accelerate DB2    *
  *                      queries, DB2 issued SQLCODE -805 with   *
  *                      reason code 02  and the SQLCA message   *
  *                      token SQLERRM shows an IDAA accelerator *
  *                      name as the location name in the fully  *
  *                      qualified package name --               *
  *                                                              *
  *                       accelerator-name.collid.package-name   *
  *                                                              *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  If all of the following apply when running an application:
   (1) either special register CURRENT QUERY ACCELERATION or DB2
       Installation parameter (aka zparm) QUERY_ACCELERATION is
       used to request that DB2 accelerate the application's query
       to the IBM DB2 Analytics Accelerator ( IDAA ), and
   (2) the application uses special register CURRENT PACKAGESET to
       specify the collection-id that DB2 should use for
       the application/package, and
   (3) BIND PLAN with a PKLIST specification was previously done
       for the application's associated plan, where location '*'
       was not used as part of the PKLIST collection-id entry for
       the application's package that issues the dynamic queries
       to be accelerated to IDAA, as shown in these PKLIST
       examples --
         PKLIST (collid1.*, collid2.*, ...)   or
         PKLIST (collid1.pkgname, collid2.pkgname, ...)   or
         PKLIST (location-name.collid.*, ... ) or
         PKLIST (location-name.collid.pkgname, ...)
                                                                 .
  then, DB2 issued SQLCODE -805 with reason code 02 when user
  attempted to run that application/package. In the returned SQLCA
  the SQLERRP = DSNXEPM and the SQLERRM message tokens show
  a fully qualified package name where the location name is
  the name of an IDAA accelerator --
     accelerator-name.collid.package-name
                                                                 .
  The SQLCODE805 is an invalid failure in this scenario, and
  DB2 should not require the accelerator name as the location name
  in the target collection-id entry of the PKLIST when doing
  query acceleration to IDAA.
                                                                 .
  This error only occurs when CURRENT PACKAGESET is used in
  the described scenario.  If CURRENT PACKAGESET is not used,
  the query is accelerated to IDAA.  To circumvent the problem,
  one of the following can be done:
   - if applicable, in the PKLIST on BIND / REBIND PLAN specify
     a location name of '*' in the collection-id entry for
     the package that is issuing the accelerated query, as
     shown in the following examples:
       BIND / REBIND PLAN (...) PKLIST (*.collid.* , ...) or
                                       (*.collid.package-name)
     and reissue the BIND / REBIND PLAN w/ PKLIST (no REBIND of
     the package is needed),  OR
   - if removal of the SET CURRENT PACKAGESET can be done
     successfully, then remove that SET. This will avoid
     the problem, even leaving the original PKLIST specification
     'as is'.
  

Problem conclusion

• DB2 code was corrected to not issue SQLCODE805 for the
  previously described scenario where both CURRENT PACKAGESET and
  BIND PLAN with PKLIST are used and a query is accelerated to
  to the IBM DB2 Analytics Accelerator.
                                                                 .
  Additional search keywords: IDAAV2R1/K IDAAV3R1/K
  

Temporary fix

• AM89587
  

Comments

APAR Information

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

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

  UK96625 UK96626 UK96627

Modules/Macros

• DSNXEPM
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK96625

     UP13/08/27 P F308

• RB10 PSY UK96626

     UP13/08/27 P F308

• R910 PSY UK96627

     UP13/08/27 P F308

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.