IT24989: SQL0420N ERROR MIGHT OCCUR WHEN RUNNING UPDATE STATEMENTS CONTAINING SCALAR SUB-QUERIES

Db2 Version 11.1 Mod4 Fix Pack4 iFix001 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 4 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 6 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 5 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 7 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• SQL0420N Error Might Occur When Running Update Statements
  Containing Scalar Sub-queries
  
  DB2 users might observe SQL0420N error when running an update
  statement with the following characteristics:
  1. the update statement contains at least one scalar sub-query
  2. the scalar-subquery has correlated predicates
  3. the select list in the scalar-subquery contains CAST
  functions that converts character strings to numbers
  4. the character string columns under conversion contain
  characters that cannot be converted to numbers
  5. the correlated predicates in the scalar sub-query can filter
  out the rows containing characters that cannot be converted to
  numbers
  

Local fix

• Work-around:
  db2set -im DB2COMPOPT=NO_SCALARQ2LOJ
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * ALL                                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to Db2 11.1 Mod 4 Fixpack 4 or higher                *
  ****************************************************************
  

Problem conclusion

• First fixed in Db2 11.1 Mod 4 Fixpack 4
  

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:

Fix information

• Fixed component name

  DB2 FOR LUW

• Fixed component ID

  DB2FORLUW

Applicable component levels

• RB10 PSN

     UP