PI26443: SQLCODE0 RETURNED INSTEAD OF SQLCODE100 WITH UPDATE IN UDF AND NULL PREDICATE 14/09/25 PTF PECHANGE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• SQLCODE0 RETURNED INSTEAD OF SQLCODE100 WITH UPDATE IN UDF AND
  NULL PREDICATE
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 11 for z/OS users who have           *
  *                 applied PTF UI15944 for apar PI08902.        *
  ****************************************************************
  * PROBLEM DESCRIPTION: After applying apar PI08902 (v11 PTF    *
  *                      UI15944), an UPDATE or DELETE statement *
  *                      with a boolean term predicate that      *
  *                      contains an expression that pre-        *
  *                      evaluates to NULL, could result in rows *
  *                      being updated or deleted when no rows   *
  *                      are expected to be affected.            *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  After the application of apar PI08902 (PTF UI15944), a DELETE
  statement with a boolean term predicate containing an expression
  that pre-evaluates to NULL could result in rows being deleted
  unexpectedly.  The problem occurs if the expression in the
  boolean term predicate qualifies for pre-evaluation and results
  to NULL.  If so, DB2 does not handle the NULL result properly.
  
  Please note that an UPDATE statement can also have this problem.
  
  PI08902 corrected a problem where an SQLCODE100 was not being
  returned for an UPDATE statement containing a UDF in a boolean
  term predicate when the UDF returned NULL. In DB2 11, the
  NULL result was not handled properly.
  
  The following example helps to illustrate the problem.
  
    DELETE
    FROM T1
    WHERE YEAR(TS_COL) BETWEEN
                      (CASE WHEN 'A' <> 'A' THEN 1995
                       ELSE :HV1:IND1 END) AND 1996;
  
  If the host variable in the above statement is NULL, rows could
  be incorrectly deleted.  No rows are expected to be deleted.
  

Problem conclusion

• Code has been modified to handle the host variable NULL value
  correctly for DELETE and UPDATE statements.
  
  Additional Keywords: SQLDELETE SQLUPDATE
  

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:

  UI24113

Modules/Macros

•    DSNXGINB DSNXGRTS
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI24113

     UP15/01/08 P F501 «

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.