PH57774: SQLCODE950 MAY OCCUR WHEN ACCESSING IDAA DATA

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• When running a SELECT statement containing host variables,
  SQLCODE -950 may occur when accessing IDAA data.
  
  Additional keywords: SQLHOSTVAR
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All Db2 for z/OS users of QUERY              *
  *                 ACCELERATION and the IBM Data Server         *
  *                 Driver for JDBC and SQLJ Type 2, for SQLJ    *
  *                 applications containing embedded static      *
  *                 SQL that reference Accelerator-Only tables   *
  *                 (AOT) or Accelerator-Only functions.         *
  ****************************************************************
  * PROBLEM DESCRIPTION: When using Db2 for z/OS, the IBM Db2    *
  *                      Analytics Accelerator for z/OS, and     *
  *                      the IBM Data Server Driver Type 2 for   *
  *                      SQLJ to run an SQLJ application that    *
  *                      contains a static SQL statement bound   *
  *                      for QUERY ACCELERATION and referencing  *
  *                      an Accelerator-Only table (AOT) or      *
  *                      Accelerator-Only functions, Db2z        *
  *                      issues SQLCODE -950 for that static     *
  *                      SQL statement when it is run in Db2z.   *
  *                      .                                       *
  *                      Other negative SQLCODES or unexpected   *
  *                      behavior may be possible when           *
  *                      this type of SQLJ static SQL statement  *
  *                      using QUERY ACCELERATION is run in      *
  *                      DB2z.                                   *
  ****************************************************************
  * RECOMMENDATION: Apply corrective PTF when available          *
  ****************************************************************
  The IBM JCC Driver Type 2 db2sqljcustomize command is used to
  bind an SQLJ application program that contains a static SQL
  statement referencing an Accelerator-only table ( AOT ) or an
  Accelerator-only function, and db2sqljcustomize specifies a
  value other than NONE for the parameter -bindoptions
  QUERYACCELERATION , and parameter ' -onlinecheck YES '
  is either specified or defaulted as follows:
     Db2sqljcustomise -url jdbc:db2://...
     -bindoptions "QUERYACCELERATION ELIGIBLE"  -onlinecheck YES
     user_profile.ser
  .
  For this scenario, bindoption QUERYACCELERATION must be a
  Db2z-supported value other than NONE , because the SQL
  application program contains static SQL statements with
  Accelerator-only functions.
  .
  The SQLJ customize parameter ' -onlinecheck YES ' notes that
  during the customization step, the JCC Type 2 Driver should
  perform online checking of data types and metadata used by the
  static SQL statements in the SQLJ application program. The
  default value is YES if the -url or -datasource parameters are
  specified.  Otherwise the default is NO.  Either value applies
  for the scenario and problem described here.
  .
  In the case described here, during the customization step the
  online checking failed with SQLCODE -4742 and REASON code 2
  (as shown below), but the SQLJ application was bound
  successfully and the Db2z package was created :
    [jcc][sqlj] Begin Customization
    [jcc][sqlj] Loading profile: user_profile
    [jcc][sqlj] Failed to online-check the following statement:
    [jcc][sqlj]      SELECT...FROM Accelerator-only-table WITH UR
    [jcc][sqlj] THE STATEMENT CANNOT BE EXECUTED BY DB2 OR IN
                     THE ACCELERATOR (REASON 2). SQLCODE=-4742,
                     SQLSTATE=560D5, DRIVER=n.nn.nn
    [jcc][sqlj] SQL Error - SQLCODE : -4742, SQLSTATE : 560D5
    [jcc][sqlj] Customization continuing...customizer will guess
                     metadata
    [jcc][sqlj] Customization complete for profile
                     user_profile.ser
    [jcc][sqlj] Begin Bind
    [jcc][sqlj] Loading profile: user_profile
    [jcc][sqlj] User bind options: QUERYACCELERATION ELIGIBLE
    [jcc][sqlj] Driver defaults(user may override): BLOCKING ALL
                     VALIDATE BIND
    ...
    [jcc][sqlj] Bind complete for user_profile
  .
  This specific static SQL SELECT FROM AOT statement failure
  during the bind customization step does not prevent the
  successful Db2z bind and package creation (as you can also use
  the parm ' -onlinecheck NO ') because the SQLJ parameter
  -bindoptions QUERYACCELERATION had a value other than NONE
  (required for static SQL that reference AOT or other
  Accelerator-only functions).
  .
  If the online checking failed, or if ' -onlinecheck NO ',
  when this SQLJ static SELECT FROM AOT statement is later run in
  Db2z, the JCC Type 2 Driver must request that DB2z return data
  type and metadata information for that statement and that
  request for Db2z should succeed.
  .
  However in this case, when this static SELECT FROM AOT
  statement was run, Db2z incorrectly failed the statement with
  SQLCODE -950 .  This SQLCODE -950 failure occurred when, during
  execution, the JCC Type 2 Driver requested the return data type
  and metadata information about the SELECT statement from Db2z.
  .
  This statement's execution should have succeeded in Db2z,
  whether the customization online check failed, or if
  ' -onlinecheck NO ' was used.
  

Problem conclusion

• This APAR/PTF modifies Db2 for z/OS code to address the DB2z
  execution-time SQLCODE -950 failure for a SQLJ static SQL
  statement that was successfully bound with QUERY ACCELERATION
  during db2sqljcustomization, but the SQLJ online checking was
  either not done, or did not succeed.  Other Db2z errors or
  unexpected behavior at execution-time may be possible for
  different SQLJ static SQL statements under the same scenario,
  and this APAR/PTF should address those as well.
  .
  Additional search keywords:
  IDAAV5R1/K IDAAV7R1/K IDAAV7R5/K
  JCCT2ZOS JCCSQLJ
  

Temporary fix

Comments

APAR Information

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

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

  UI96860 UI96861

Modules/Macros

• DSN6SPRC DSNDQWPZ DSNDSPRM DSNGEPLC DSNXDMPS DSNXEAAL DSNXEALS
  DSNXEBIS DSNXECLF DSNXECMP DSNXECT  DSNXECW  DSNXECWA DSNXECWU
  DSNXEDP  DSNXEDS1 DSNXEDSC DSNXEET  DSNXELS1 DSNXEMG1 DSNXERT
  DSNXERT2 DSNXESDL DSNXESES DSNXESX  DSNXESX4 DSNXGICM DSNXGRES
  DSNXGTBC DSNXGTRE DSNXGTRF DSNXGVCM DSNXIEPL DSNXOD2  DSNXOGP
  DSNXOMPS DSNXOW2F DSNXRBND DSNXRRP
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI96860

     UP24/05/24 P F405

• RD10 PSY UI96861

     UP24/05/24 P F405

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.